Forum Discussion
Role playing dimension issue
Hello, let me try explaining the issue with a simple example.
I have a fact table with two country-keys: one for sender, one for receiver.
The sender-country-key and receiver-country-key are both joined to the country table in my Power BI model.
Country : Fact = 1 : N.
So , I have an active and inactive relation.
I can create two measures using USERELATIONSHIP for the inactive:
- Total Sales by Sender Country
- Total Sales by Receiver Country
So far, so good.
Sender and receiver in the fact table are joined by surrogate key to the country table.
For the end-user I hide the keys, so in the fact table (folder) the end-user doesn’t see them and the country table (folder) only shows the logical country name like ‘Italy’.
If the end-user wants to see Sender Sales by Country, he picks the logical country from the Country table (folder) and the measure 'Total Sales by Sender Country'. If for instance he wants to add Transaction Type, which is part of the fact table, to the visual, this will work. Etcetera.
But here it comes.
I did hide the country keys in country and fact table, because these are meaningless keys for the end-user.
But suppose the end-user builds his own report and only wants to see the facts where the sender-country is ‘Scotland’.
I can drag the logical country name from the country table to the visual. But how do I know whether this is a sender-country or receiver-country??
Does this mean that, in case I have a role playing dimension and I hide the surrogate, meaningless keys, I have to add the logical keys of it to the fact table? So, I have to add sender-country-name and receiver-country-name to the fact table?
Hope someone can help
regards
Ron
MFelix lbendlin
7 Replies
- lbendlinSuper User
You have one country dimension and two measures. If you want to show both sender and receiver country at the same time then indeed you need to pull these into the fact table.
- AnonymousNot applicable
Thnx lbendlin
But also If I don't want to show them both at the same time, I need to pull there logical names into the fact table. Otherwise how does the end-user know he is dealing with a sender- or receiver-country, since the country table only shows one general country name. Am I right?
And same would be if I have for instance an order-date and ship-date, both part of a role playing dimension. If the end-user wants details of all the items that were ordered on '2022-05-06' there should be a field 'order-date' in my fact table.- lbendlinSuper User
Remember you created a second measure with USERELATIONSHIP. Any date list will assume the role assigned to it by the measures.
- lbendlinSuper User
You are correct.