0

I have this model, but I am unable to find if its “correct”, and what is the technical name for it.

I am using Power BI, and for example, I have a Clients table:

enter image description here

In my model (cube) I have it twice; I will have tables: Client and Client Current

Data sources: The source for Client is (Select * from client) The source for Client Current is (Select * from client where Valid=1)

And of course in my fact I have:

enter image description here

What is the name for this setup? (one connects through client_surrogate key and the other from client_anotherkey)

enter image description here

I could be wrong, but I see SCD type 2 as a subset of ‘Role-Playing dimensions’?

Chicago1988
  • 970
  • 3
  • 14
  • 35

2 Answers2

1

This is a type-2 SCD. Client_SurrogateKey is the dimension key. And Client_AnotherKey is (typically) the key from the source system table. Note that in a SCD Client_AnotherKey is not a key of the dimension table, as there are multiple rows with the same value.

It's unrelated to a Role-Playing Dimension.

The only thing atypical here is that the fact table has both keys. Typically the fact table has only the dimension key, and would have to join the dimension to get the Client_AnotherKey.

But it's not unusual in dimensional models to copy (or denormalize) a dimension non-key attribute onto the fact table for convenience.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Why do you say "It's unrelated to a Role-Playing Dimension." ? There is only one client table, and its used for both 'Client' and 'Client Current' dimension. Client_AnotherKey is unique in 'Client Current'. Notice that i mention the source as being (Select * from client where Valid=1), I have both keys in the fact table, because I need to connect to both 'Client' and 'Client Current' – Chicago1988 Sep 08 '22 at 12:39
0

SCD type 2 is a DWH technique that tracks the historical changes by adding a row to dimension table as changes are tracked and recorded.

Role Playing is a concept generally related to the date columns. For example: there can be 2 foreign key date columns in fact tables; but you can't join these 2 keys to dimension tables; so you create a 2 different date tables with unique column names in order to have a relationship between them with original data table included in the relationship: See below:

Role Playing

Your Model looks more like SCD Type 2.

client_surrogate key (FK : foreign key) client_anotherkey (NK: Natural key )

I hope This info is enough for you.

Ozan Sen
  • 2,477
  • 2
  • 4
  • 16
  • FYI, role playing dimension in PBI is implemented using USERELATIONSHIP(). i.e. a single date table connected to the same fact table multiple times and you switch the role using DAX to make an active relationship. – Davide Bacci Sep 02 '22 at 15:45
  • Thanks, @David. I appreciate your support. I am using USERELATIONSHIP() very often, but to be honest, I never considered it as role-playing. Thank you for reminding it. – Ozan Sen Sep 02 '22 at 16:17