Conformed dimensional modeling in Power BI

[akj2784]

Hi Team,

I have a scenario where there are 2 dimensions D1 and D2 and two facts F1 and F2. 

Both the dimensions have relationship with both the fact. However when I try to create join between D2 and F2, it says ambiguity between D1 and D2 and it doesnt allow me to create the last join after creating join between D1 and F1, D1 and F2, D2 and F1.

Is there any other approach we have to follow to create similar requirement ?

We call it as conformed dimensional modeling.

The reason of doing is I need to query all the tables in one report.

Any help would be appreciated.

Regards,

Akash

[v-huizhn-msft]

Hi @akj2784,

You want to combine the two tables into one, right? If it is, you can combine them in Power Query Editor, please review: 

Append vs. Merge in Power BI and Power Query.

What your data structure looks like? Could you please create sample tables and list expected result, so that we can post solution which is close to your requirement. 

And you said you got error when you join two tables, how did you do that, could you please share the DAX formula for further analysis? For joining tables in DAX, please review more details from From SQL to DAX: Joining Tables.

Best Regards,
Angelia

[akj2784]

Hi 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

[akj2784]

Is it not feasible in Power BI ?

[v-huizhn-msft]

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]

Hi @akj2784,

I notice you open a new case, please follow the new thread.

Best Regards,
Angelia

[akj2784]

Sure.

[v-huizhn-msft]

Hi @akj2784,

Got it, thanks.

Angelia