Dimension: Multiple Business key dif Atribute

[giang.pham]

I have Dimension Customer (SCD Type 2)
with one customer in database i have data like
-----------------------------------------------------------------------------------------------------
Anchor_id  |   Cst_dim_id  |  Cst_nm     |Cst_type   | Eff_dt          |   End_dt
   10           |      100          |      Smith    |      I          | 1/1/2016     |   30/6/2016
   10           |      101          |      Smith    |     U          | 30/6/2016   |   31/12/2099
-----------------------------------------------------------------------------------------------------
In Cognos Framework Manager, I create 3 layer ( Physical Model, Logical Model and Dimensional Model)
In Physical and Logical show data > Data show 2 rows ( True)
In Dimensional Model: Create Regular Dimension: Customer Dimension (1 level)
- Anchor Id : _businessKey
- Cst_nm    : _memberCaption
- Other : No role
> after I test data > only first row show but Total Rows=2
How I can show 2 rows in report

[bdbits]

The business key by definition has to specify a unique (one and only one) row. Your model is not doing that, so since only one matching row is expected that is all you will see.

I recommend surrogate keys in your warehouse as a solution to handling composite natural keys, among other things.

[giang.pham]

The business key by definition has to specify a unique (one and only one) row. Your model is not doing that, so since only one matching row is expected that is all you will see.

I recommend surrogate keys in your warehouse as a solution to handling composite natural keys, among other things.

If i use cst_dim_id for businesskey, when i drag cst_nm + mesure in fct ( dim join fct by cst_dim_id) one customer have 2 row > It's unreasonable

[bus_pass_man]

Props, as the kids say or, more likely, given that even I've encountered the neologism, used to say, for you having recognized that you have a type 2 SCD.

If i use cst_dim_id for businesskey, when i drag cst_nm + mesure in fct ( dim join fct by cst_dim_id) one customer have 2 row > It's unreasonable

I'm not sure what you mean.  How is it unreasonable? 

Just to confirm, is the question still why do you only get one row returned when you test your dimension when you use anchor_id as the business key?  Is that a correct understanding?  And, as a corollary, why do you get two rows when you use cst_dim_id?  (You don't mention it but you'll only get one member for anchor_id and two for cst_dim_id).

The results that you are seeing are what I would expect given the values for the keys that you have chosen.  There's one unique value for anchor_ID and two for cst_dim_id.  Getting one row returned (and one member generated) for the former and two for the latter is what I would expect, and in fact, what I'd want given what's been modeled and the nature of the data that you say you have.

Dimensions have a slightly different behaviour from query subjects.  This is because you are defining what the members of your dimension are and a query needs to be run to get the data, based on your definition, which will be used to create the members. 

What happens is that a Select distinct query is generated with the business keys.  If the unique key check box is off the higher level keys get dragged into the query.

It is on by default.  It says that you are asserting that the key value for the level is sufficient to uniquely identify a member in the level.  If it is turned off, the keys of the higher levels of the hierarchy are included in the select distinct query.  Don't worry about that right now.

Is the question now, you want to know how to model the type 2 dimension so that the historic data is preserved (that is, assigned to members (and their parents) according to history)?  Or is it you want to know how to model the type 2 dimension so that the historic data gets associated with the current member?

Hope that helps.