Forum Discussion
Calc table Performance issues in composite models
The single most important factor is the cardinality of the join columns. Examine some of your measures in DAX Studio and you will find that the queries include enumerations of the values in the join column. If the cardinality is more than a couple thousand then you very quickly end up in situations where the query is slow not because it is complex, but because it is big, physically big. You can have scenarios where the query text is multiple gigabytes in size, and these gigabytes have to travel through the network for every single query.
Keep the link cardinality as low as possible.
Thank you @ibendlim for your thoughts. I will dig into DaxStudio for your comments on cardinality. I was working late Saturday evening on this issue - and responsiveness at least return a result. The one visual maintained it's normal 8-10 seconds, but the problematic one resutling in errors was almost instaneous. I simplified one of my measures. However, today - Sunday afternoon, I am back to the slow responsiveness.
I am wondering when I do a refresh of the local (chained file) - the refresh seems to take an extended period of time (even longer than a refresh of the remote (base file). Yesterday afternoon, the refresh of the local file was within 30 seconds.
I am also attempting to understand the aggregations features. I am using import mode from dataflows, and seem to get hung up on creating a live query connection from a dataflow. Unfortunately, I have multiple transformations within the dataflow, pulling live from the source file would be of low value (transitioning these transformations to a live basis would be disasterouly slow). All my reading/video watching suggest aggregatations require a live connection - that is converted to dual mode. I am not finding how to do that with dataflow imports.
Thank you again for your thoughts - I will definitely dig into the dax studio suggestion.