Forum Discussion
Post PartisanConformed dimensional modeling in Power BI
Post PartisanHi Angelia,
No I don't want to merge two tables.
I am not sure how to upload the image. But let me try to explain the requirement.
Dim D1: It has Product details like P1, P2, P3, P4 etc
Dim D2: It has Hierarchy like VP, MD etc. Products are tagged to VP, MD etc.
Fact F1: It has Funding for all the Products
Fact F2: It has Revenue for all the Products
Both the facts have FK of D1 and D2.
Now I want to find Products tagged to a VP, and the corresponding Revenue and Funding for those Products.
A very starightforward requirement if I consider Oracle BI tool which creates two different SQL query internally one with fact 1 and other with fact 2. And internally stiches the result and show it in the UI.
But in Power BI, it is not allowing me to create joins where there is ambiguity. So looking for some other approach we can use for such scenario. In real time analytics, this is very basic scenario. We cannot always have single star schema to create reports.
Regards,
Akash
Post PartisanIs it not feasible in Power BI ?
v-huizhn-msftMicrosoft Employee
Hi akj2784,
If you can create relationship between 'Dim D1' and 'Dim D2', 'Dim D1' and 'Fact F1', 'Dim D1' and 'Fact F2'. So you can related function to get expected result.
For example, I have two sample table as follows. I want to get funding value from fact table in Table.
TableAFact
So I create relationship between the two tables.
In TableA, create a calculated column to get the funding.Funding = RELATED('Fact'[Funding])
Best Regards,
Angelia- akj2784
Hi Angelia,
There is no direct relationship between Dim D1 and D2 at the db level so I can't create join. It goes via Fact table. I understand your point of view. If it is feasible to club multiple tables we can avoid ambiguity. But my scenario is also a valid scenario wherein Dimensions are linked to each other via Fact. This is a normal datawarehouse concept.
So I am just curious to know if we can create such report in Power BI or not. For a tool like OBIEE, this is a very very basic scenario.
And here I am just giving an example of connecting 2 facts and 2 Dims. However in real time env, we have 8-10 facts table and 20-25 dimensions table. And most of the dimensions are conformed(connected via FK ) to all the facts.
So clubing these tables would not be an good approach I believe. I may be wrong as I am new to this tool.
No where I could find explanation of a complex data model with multiple facts and multiple dimensions. Everywhere people are explaning the basic data model with just few table and that too a pure star schema. I understand this may be good for non technical person but if a Developer wants to create a Self Service model for functional users, how do we do that.
If you can point me a good video or document which has advanced data modeling concept, that would be great.
- v-huizhn-msft