Need advanced data modeling concepts of Power BI

[akj2784]

Hi All,

Where can I find advanced data modeling concepts for Power BI. No where I could find the explanation of creating complex model with multiple fact tables, multiple dimensions.

Any good explanation of Relationship in Power BI. 

Any help would be really appreciated.

Regards,

Akash

[Anonymous]

@akj2784

Hi @akj2784,

Have you found any other solution for this? 

I am in a question that whether Power BI can replace SAP Business Objects Universe. Can Power BI handle a model with lot of fact and dimension under direct query without impacting the performace of the reports?

Regards,

Maddy

[Anonymous]

@akj2784,

I would recommend you read the Analyzing Data with Power BI and Power Pivot for Excel book mentioned in this blog about creating data model, and review the blogs below to learn more about handling relationships in Power BI.

Multiple Relationships Between Tables in DAX
How to handle fact tables with different granularities in #dax #powerpivot #tabular


Regards,
Lydia

[akj2784]

I have gone through the docs but somehow still need to understand how to model such scenarios where we have multiple fact tables. May I need to unlearn Oracle BI and not think Power BI similar to a traditional reporting tool. I am from datawarehouse background so I am thinking the model from sql point of view. But that's not gonna work.

But based on my current effort in exploring the uses cases, I feel there are not much documentation available from Microsoft the way Oracle has for each and every concepts.

[Anonymous]

@akj2784,

What issue or error message do you get when creating relationship between your tables?

Regards,
Lydia

[akj2784]

 Simple example of the scenario I am trying to achieve. Assume Product 1, 2 from Dim 1 has Funding record in the Funding Fact and its corresponfing VP is also tagged to Dim 2.

Now Product 3, 4 from Dim 1 has revenue record in Revenue Facts but not in Funding Facts. And its corresponding VP us tagged to Dim2. 

I have to query VP Name with some filter, its Total Revenue, total Funding for each product in a report.

Results should be something like this.

 

And at the same time using the same model, I want to create another report with Product , Revenue and Funding.

Let me know what approach I should take. And how the relationship should be defined considering I have only these 4 tables for now. Merging the table is not feasible depending on the data I have.

Thank you for helping me.

Regards,

Akash

[Anonymous · Accepted solution]

@akj2784,

You can create relationships as follows.


Then create table visuals as shown in the screenshots below. 


If above steps don't help, please share sample data of your tables for us to analyze.

Regards,
Lydia

[akj2784]

Hi Lydia,

This works great. Thank you so much for your help. 

Would you mind explaining why you made only 1 relationship as both way and rest of them as one way. I would like to understand the concept so that I don't end up asking similar question again.

Regards,

Akash

[Anonymous]

@akj2784,

It will not impact the results even if you made all the relationships as one way.

If your issue is solved, please accept my reply as answer, that way, other community members would easily find the solution when they get same issues.

Regards,
Lydia