Forum Discussion

KayceVC's avatar
KayceVC
Helper II
7 years ago
Solved

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
    Anonymous
    7 years ago

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

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    KayceVC  - 

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

  • gooranga1's avatar
    gooranga1
    Power Participant
    7 years ago

    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's avatar
      KayceVC
      Helper II
      7 years ago

      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's avatar
        gooranga1
        Power Participant
        7 years ago

        hi KayceVC 

         

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

         

        See highlighed tables and relationship.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

  • KayceVC's avatar
    KayceVC
    Helper II
    7 years ago

    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!