on 09-07-2013 8:44 AM
I am curious about the behavior of key attributes in attribute views.
First I thought it is kind of primary key for the entity Attribute View but then I did a few checks
Created two tables
1. CUST -Customer Table- It has Customer ID and Country ID
2. CTRY -Country Table- It has Country ID and Country Description
An Attribute View on top of these two tables
Simple, nothing complex
Now in Semantics
I am able to activate the attribute view and see the output
but what I do next is interesting..I made the CID as a Key Attribute, though it is not a key in the data ('in' for two rows)
But I am still able to activate the Attribute View and see the data as well. I would like everyone to try this out.
So what I don't understand is if this is allowed why is Key Attribute Mandatory?
I mean why should there be a key attribute if it does not check for Unique Constraint.
Hi Shreepad,
I think it is related to the HANA engine to define the query access pattern when the attribute view is used in the JOIN criteria. The Key attributes may be helpful in deciding the join condition like which table and associated column should be used for the left side of the join and which one on the right. The attribute view would be joined to the transaction table in Analytic view and the column from the attribute view will then be the driver column for the join. HANA anyway redefine the join condition based on the column size and in some cases may re-align the tables, but there has to be a starting condition for the join.
So I think the Key attribute definition will help in establishing join rules.
I don't think it would enforce any data consistency check as it is a view and not persistent object. Hence the unique constraint may not be enforced.
Regards,
Ravi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Ravindra,
I think you're right when you say it will not perform any data consistency checks. But then why the name "Key Attribute".
Also we can have joins in analytic view which are not based on key attributes. So how does the optimization take place.
Do you have some document which explains this concept in detail? I am still not clear after your explanation.
------------
I did some R&D myself to get some clarity on this concept. I found that the Key Attribute is required to identify the Central Table, So the key attributes can come from only one table in an attribute view with multiple tables.
What I did was to turn off the client side validations and then try to activate an attribute view
1. Without any Key Attr., It gave an server side error about 'No Central Table found'.
2. Next with Key Attribute from one table, it activated in both the cases. CUID and CID as expected by now.
3. Last with CID from one table and CUID from other. It gave the same Server side error about Central Table.
So if I make a attribute as key what makes it special apart from identifying the Central Table. If that was the purpose then why don't we have an option in Attribute View for just directly selecting the central table?
Also the joins in analytic views can be made based on an attribute coming from non central table?
I am very confused. Please help
Thanks & Regards,
Shreepad Patil
Hi Shreepad,
Attribute views are the building blocks of analytical views where the fact table would establish n : 1 relation with the attribute views. This is one of the reasons they made the key field mandatory .
Sreehari
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi All,
I am pasting the code generated for this View in _SYS_BIC Schema
CREATE COLUMN VIEW "_SYS_BIC"."public.opensap257.mypackage/AT_KA_TEST" WITH PARAMETERS (indexType=6,
joinIndex="OPENSAP257"."CTRY",
joinIndexType=0,
joinIndexEstimation=0,
joinIndex="OPENSAP257"."CUST",
joinIndexType=0,
joinIndexEstimation=0,
joinCondition=('JOIN_CTRY_CUST_1',
"OPENSAP257"."CTRY",
"CID",
"OPENSAP257"."CUST",
"CID",
'',
144,
0),
joinPath=('PATH',
'JOIN_CTRY_CUST_1'),
viewAttribute=('CUID',
"OPENSAP257"."CUST",
"CUID",
'PATH',
'V_CUST',
'attribute',
'',
'public.opensap257.mypackage/AT_KA_TEST$CUID'),
viewAttribute=('CID',
"OPENSAP257"."CUST",
"CID",
'PATH',
'V_CUST',
'attribute',
'',
'public.opensap257.mypackage/AT_KA_TEST$CID'),
viewAttribute=('COUNTRY',
"OPENSAP257"."CTRY",
"COUNTRY",
'PATH',
'V_CUST',
'attribute',
'',
'public.opensap257.mypackage/AT_KA_TEST$COUNTRY'),
view=('V_CUST',
"OPENSAP257"."CUST"),
defaultView='V_CUST',
'REGISTERVIEWFORAPCHECK'='1',
OPTIMIZEMETAMODEL=0)
From this code I cannot understand where the Key Attribute is used as I cannot see anywhere specified it as key.
The "CID" from "CUST" table is selected as a Key Attribute for this Attribute View.
Please help me understand this mystery.
Thanks & Regards,
Shreepad Patil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Shreepad,
In general, each dimension contains a key attribute. The key attribute is the attribute in a dimension that identifies the columns in the dimension main table that are used in foreign key relationships to the fact table.
Typically, the key attribute represents the primary key column or columns in the dimension table. You can define a logical primary key on a table in a view which has no physical primary key . When defining key attributes, the OLAP Engine and Join Engine try to use the primary key columns of the dimension table in the view. If the dimension table does not have a logical primary key or physical primary key defined, the wizards may not be able to correctly define the key attributes for the dimension.
Thanks and Regards,
Rashmi
Hi Shreepad,
Did you figure out the issue . I have the same problem . I have table which has got repeated
values , but this is not a primary key ..I wanted to display these only once in the filter using the attribute views. I Was also under the assumption that key attribute is the primary key .But it is not .Did you
figure out any options... instead of writing the SQL script to pick the distinct count.
Thanks
Magge
Hi,
Key Attribute is mandatory because it describes the relationship between the two tables.
Regards,
Sai
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Sai,
Can you please elaborate how this relationship works?
What I don't understand is
1. If 'Key Attribute' is like Primary Key then all the values for this should be unique as per normal rules.
But I can go ahead and select any attribute as Key Attribute even if its data is not unique.
Thanks & Regards,
Shreepad Patil
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.