Forum Discussion
Direct Query to Multiple Datasets - Peformance
I applaud you for your decision to cut out unnecessary steps in your process.
The single biggest consideration you need to have is cardinality. Specifically the cardinality of the fields that you use to link the datasets together. That cardinality must be kept as low as possible. Low 100s preferred. 10K still somewhat possible. As soon as you hit 1M you are doomed. Truly doomed. If you want to get a really nice shock you can look at the generated DAX query in DAX Studio or others. I have seen DAX queries of 6GB (yes, Gigabytes) every.time.a.user.interacts.with.the.report - just because I hadn't checked the cardinality (which turned out to be 1.3M)
I applaud you for your decision to cut out unnecessary steps in your process.
The single biggest consideration you need to have is cardinality. Specifically the cardinality of the fields that you use to link the datasets together. That cardinality must be kept as low as possible. Low 100s preferred. 10K still somewhat possible. As soon as you hit 1M you are doomed. Truly doomed. If you want to get a really nice shock you can look at the generated DAX query in DAX Studio or others. I have seen DAX queries of 6GB (yes, Gigabytes) every.time.a.user.interacts.with.the.report - just because I hadn't checked the cardinality (which turned out to be 1.3M)
Thanks for the reply!
So often the datasets will be linked via a core masterdata table, which would be roughly 4500 rows, and is the one side of the one to many. However, the many side will go into the millions depending on the dataset - is this going to prove to be a barrier to this approach?
It will be as soon as you have a measure that filters the many side. I recommend you watch the queries very closely, especially for slow loading visuals. The issue will be obvious once you see ginormous enumerations.
Thanks for your help. Would a bridge table be a solution potentially? So i could bridge the many side with the same masterdata (~4500 rows), turning it into a 1 to many, which ultimately then filters the million+ rows but within its own dataset?