Table relationship causes severe performance drop

[KayceVC]

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.

[Anonymous · Accepted solution]

@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

[KayceVC]

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 · Accepted solution]

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

[gooranga1]

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.

[KayceVC]

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. 

[gooranga1]

hi @KayceVC 

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

See highlighed tables and relationship.

[KayceVC]

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. 

[gooranga1]

what is the actual pbix file size when saved?

[KayceVC]

14,270KB

[Anonymous]

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.

[KayceVC]

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 · Accepted solution]

@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]

@KayceVC  - 

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