Model scd by using role-playing?

Question

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:

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:

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

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

Answer 1

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:

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.

Answer 2

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.