cancel
Showing results for 
Search instead for 
Did you mean: 

Why is Key Attribute mandatory in Attribute View?

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member184768
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Answers (3)

Answers (3)

sreehari_vpillai
Active Contributor
0 Kudos

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

former_member186356
Participant
0 Kudos

Thanks Sreehari for answers.

Former Member
0 Kudos

Hi Sreehari,

Thanks for sharing your views on it.

However the Key Attributes do not enforce a unique constraint, that is what I was worried about.

--
Shreepad

former_member182302
Active Contributor
0 Kudos

Yes and also if you have only one table in the attribute view you can activate it without key attribute.

Regards,

Krishna Tangudu

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Thanks Rashmi,

That was really helpful.

But doesn't this mean that this key attribute should have been unique or at least indexed in a different way while the creation of Attribute Views.

When I checked this is not happening. That is exactly my concern.

Thanks & Regards,

Shreepad Patil

former_member186356
Participant
0 Kudos

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

Former Member
0 Kudos

Hi Magge,

Sorry for the late reply.

You can use a Analytic View instead and select only the field(s) as output that you want to see distinctly.

Also make sure that you have a dummy Measure. Otherwise the Analytic View will not get activated.

--

Shreepad

0 Kudos

Good day Rashmi,

Thanks  a lot, if possible will you please take an example (2 to 3 tables) and explain briefly how key attribute behaves.

Please take 1 key attribute in each table,

Best Regards,

Naga.

Former Member
0 Kudos

Hi,

Key Attribute is mandatory because it describes the relationship between the two tables.

Regards,

Sai

Former Member
0 Kudos

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