Why is Key Attribute mandatory in Attribute View?

[#1]

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.

[Former Member]

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]

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]

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 Member]

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]

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

[former_member186398]

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.