Atrribute Roles

[petperson]

I am trying to get attribute roles to work and have not been successful. Maybe its the way I am trying to use them. Here's what I have: EMPLOYEE table has EMPID, SUPERVISORID and EMPL_NAME. I am trying to get MSTR to use the Attribute role feature to avoid having to create an alias EMPLOYEE table to get Supervisor Name. I am on 7.5.1 using Oracle and have the VLDB setting turned on at the DB Instance level.

thanks

 

[entaroadun]

What is an attribute role? Is this a new 7.5 feature?

Based on what you are describing, though, it sounds like the existing Table Alias feature.

Go into the Schema Objects\Tables folder. Right-click on the Employee table, and select Create Table Alias from the pop-up menu. Rename this table alias to Supervisor.

For your Supervisor attribute, use EMPID as your ID form from the Supervisor logical table, and use SUPERVISORID as your ID form from the Employee logical table.

For your Employee attribute, be sure to use EMPID as your ID form from the Employee table only. De-select the Supervisor logical table if selected.

 

[petperson]

Actually, attribute roles first appeared in 7i (7.2). There is a VLDB setting under query optimization to enable automatic attribute role recognition. It's supposed to allow you to create multiple attributes that have the same lookup table and MSTR should automatically detect the multiple roles when the multiple attributes are on the same report. Another alternative to table aliasing. I have never been able to get this to work correctly - it appears there are just too many different scenarios where this won't work. In this case, most likely because EMP and SUPER exist on the same table. I will just resort to creating the alias tables for this situation.

I would be curious to know if anyone is using attribute roles though and in what instances you are using it effectively.

thanks

 

[entaroadun]

Oh, right! Now I remember! I never use it; it's too buggy.

It's probably choking on you because you're using it to define two attributes that are directly related.

Just use aliases.

 

[sunnyphoenixier]

Peterson,

My experience is a little different. I found that this feature has been working very well for us so far.

Here is one example. If you have a set of time dimension tables. And you have mulitple time hierarchies such as Purchasing Time, Shipping Time, Loading time, etc. Before this functions, we have to creat multiple views of these sets of tables to do that. Now, with this feature, we can either create table alias or simply turn the Attribute Engine Role Option to ON. In my opinion, this is a big improvement for 7.2. So far it has not given me any trouble and I found it is a very convenient function.

Let me know if there is anythign specific that you need help with.

Phoenixier

 

[reisw]

I've also used this functionality but found it easier to use table aliases instead of the attribute role option. I think it ends up a being a little bit clearer when reading the SQL and the last time I talked to someone at MSTR they actually recommended that I do it that way.

 

[FLB]

The "attribute role" functionality is supposed to "help you" and allow you not to have to create table aliases. It works in most cases, but sometimes it does not....

Still, you want to enable it if two attributes/facts/... are "pointing" to the same table column (because in this case aliases are not going to help).

I personally prefer to control things, so I am a pro-aliases! ;-)

HTH,
FLB

 

[teccum]

All,

How do we define the attribute role for the date dimension with multiple date roles when the foreign keys are in the FACT TABLE since Mstr only mentions about the lookup table for attribute roles. Let's say I have claim fact with four date ids

claim_service_start_date.day_id
claim_service_end _date.day_id
claim received date.day_id
claim_paid_date.day_id

linked to the date table and the date dimension with

day_id
day_date
day_month.. etc.

Is it just turning on the Attribute Role option enough if I my report requirement is claim service start date and claim service end date for the all the claims of a certain vendor.

If I use the Table alias, how do i link the claim_service_start_date_day_id (which is in the fact table) to the claim_service_start_date(attribute created from the alias of Day Date table)?

if I just create four different attributes names connecting four different alias table. Does Mstr automatically recognize the individual relationship between the four alias table and the four different day id in the fact table?

Please clarify.

Thanks
teccum.

 

[reisw]

teccum,

I think your best bet is to create the four different table alias based on your base date table. After you do that duplicate your existing date attribute 4 times and then redefine each one to use one of the new aliased tables as the lookup table. Then you'll need to manually map each attribute to the appropriate column on the fact table, using heterogenoeus column mapping since the column names are going to be the same. Hope that was clear.

 

[teccum]

reisw,

Thanks for the note. I was clear other than last part. I have defined four date attributes (claim_service_start_date, claim_service_end_date, claim_paid_date, claim_received_date)on four different alias tables(claim_service_Start_day, claim_service_end_day, claim_paid_day, claim_received_Day) and have set the respective tables as the lookup table for the respective date attributes and selected the "manual" method in the attribute mapping.

Now how do I manually link in MSTR between the claim_service_start_day attribute and the claim_service_start_day.day_key which is in the claim_fact table. I have created the attribute Day which has day_id as the id in the day dimension table, but since the day_id field in the claim_fact table has four different names(claim_service_start_day_day_id,
claim_service_end _date.day_id,
claim received date.day_id,claim_paid_date.day_id)

thanks
teccum.

 

[teccum]

All,

I have defined Attribute Role through Table Alias option
for all the four claim date fields
(claim_service_start_date
claim_service_end _date
claim received date
claim_paid_date).

On the subject of Hetrogeneous mapping between Non-unique field names on two different tables(a fact table and a dimension table), these are the steps to be done to create an attribute role after defining the table alias. Hope this would be usefull to others who may have doubts on how to make MSTR understand the link between the two different column names and how attribute role works.

1. Create an attribute (eg. claim_service_start_date)
In the Attribute editor, click on New.

2. This opens a Attribute Form window.

3. In the Attribute Form Window, click on New and this
opens another window where one can see the source
table option, including the dimension, lookup and
fact tables and Form Expression window.

4. Select the 1st source table required(dimension or
Lookup table, (eg. DW_Claim_Service_Start_Day). Once
selected it will display all the fields in the
respective table.

5. Select the column form the 1st source table by
double clicking on the fields(eg. Day_id). This moves
into the Form Expression window immediately and then
click OK. In the attribute form window, it will show
all the tables where Day_id is available, select only
the DW_Day and DW_Claim_Service_Start_Day table and
de-select the checkbox on the other tables.

6. Now, onceagain from the Attribute Form Window, Click
on 'New' to create another attribute form. Now ,in
this window select the 2nd source table(eg
Claim_fact) where the second non-unique column(eg.
Claim_Service_Start_day_id) resides.

7. After selecting the 2nd source table and the 2 non-
unique column, click o.k. Note, the mapping will be
turned into manual, allowing you to select the
respective table to be considered while joining for
field. This you can do it by clicking on the box
next to the tables in the Attribute Form window.

8. After these are defined, create another attribute
form that has to be displayed whenever this
attribute is selected in the report and after
defining this 3rd attribute form, enable this
attribute as the display attribute in the Display
tab of the attribute editor window.

9. Define the respective form name, form type, form
description etc and then define the children and
parent for the attributes which creates the
hierarchy. Once this is done then the Attribute role
is defined completely.

You can check whether the attribute role is defined
by selecting the table view. Go to

SCHEMA: -- Graphical View ---> Tables.

The graphical view will now show a physical link
between the two tables where the two non-unique
columns reside.


Hope this helps.


Thanks
Teccum.