Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
KayceVC
Helper II
Helper II

Table relationship causes severe performance drop

I have been working to optimize an existing report in our company. I trimmed the largest table down to around 150k rows by adding a date filter. The total report has 14 tables with 11 of them having relationships. All are imported from our company SQL server. This report performs perfectly until I add a single relationship between my Dates (Calendar) table and a Ticket table. Adding this single relationship reduces any operation to a 13-15 minute wait. All my dates are set to Date format, not datetime. I am honestly at my wits end as to why this one relationship is so detrimental to the report when other relationships to Dates and Tickets did not cause this.  A copy of the relationship model is below.

Model.JPG

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@KayceVC -

 

Regarding the snowflaking - There are 3 tables that could be removed and incorporated into their respective dimension tables:

Months -> Dates (can create a M-to-M relationship with Agreement Revenue)

Staff Teams -> Staff

Customer Teams -> Companies

 

View solution in original post

Anonymous
Not applicable

you have circular relationships betwen tables which is causing poor performance.  tryy to avoid it

View solution in original post

12 REPLIES 12
KayceVC
Helper II
Helper II

Thank you both! Sorry it took me so long to circle back to this, I got assigned tasks with a higher priority and was just able to test these solutions yesterday. I don't know how I overlooked the relationship between Dates and Months as causing the issue for so long, but as soon as I removed it and updated the links and measures, the report performs perfectly again!

Anonymous
Not applicable

you have circular relationships betwen tables which is causing poor performance.  tryy to avoid it

gooranga1
Power Participant
Power Participant

Hi @KayceVC 

 

Is it necessary for that Both relationship from Month to Date table? I find the Both relationship in general more trouble than it's worth and I have only used it in a few specific situations.

 

Try setting it to Single and see what that is like.

Hi @gooranga1 

 

The relationship is currently Single direction from Tickets to Dates, the image may have just been overlapped a bit making it hard to see. 

 

If this is a memory issue, I'm not sure what to do. Unfortunately I have to have this relationship for my data report properly.  I've become highly restrictive in the reports to limit the amount of data in the tables, so this report is fairly small compared to others I have that function with no issues. That's why this is so puzzling. 

hi @KayceVC 

 

The issue is not the Tickets --> Dates relationship but the Dates --> Month relationship.

 

See highlighed tables and relationship.

 

join.PNG

Sorry @gooranga1 , I need to slow down and read closer. I did change this to a single direction and it made no noticable difference to the report. Creating, deleting, saving measures and columns still takes between 13 - 15 minutes. 

what is the actual pbix file size when saved?

14,270KB

Anonymous
Not applicable

A couple of optimization thoughts:

1. Star schema is preferred to snowflake. At query time, less relationships = better performance.

2. Remove any unnecessary columns. This will reduce memory consumption of the model. The most memory-intensive columns are those with many distinct values.

Thank you for the advice, Natel, but these were some of the first things I did in optimizing this report. Unfortunately, the link to this date table is still causing a problem for me.

Anonymous
Not applicable

@KayceVC -

 

Regarding the snowflaking - There are 3 tables that could be removed and incorporated into their respective dimension tables:

Months -> Dates (can create a M-to-M relationship with Agreement Revenue)

Staff Teams -> Staff

Customer Teams -> Companies

 

Anonymous
Not applicable

@KayceVC  - 

It's just speculation, but perhaps you're bumping into some memory limit. Relationships are stored in memory.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.