Forum Discussion
Microsoft EmployeeShare your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)
Been waiting for this capability and looking forward to it ever since it was announced. My initial plan of use was to break apart some larger complex datasets into 'child' datasets that could then be combined back into the larger parent dataset but still allow other organizational reporting areas to leverage some of the specific content of the child datasets to prevent the need from adding a bunch of tables that would be unnecessary to the application.
Initially this was looking good. However, I ran into a limitation with the model being directquery, namely the limitation of passing more than one million records to an external source.
My setup was two separate Power BI datasets with a one to many relationship (I also tried bidirectional just to see) between a fact table from each. The visual renders fine until a dimension from the second dataset is added to the first. If filters are applied to the second dataset so that the resulting record count is below 1,000,000 records everything is good. Even though the first dataset has far fewer than 1,000,000 records, it would seem the second is trying to pass 1,000,000+ across the relationship and thus erroring out due to the directquery limitation.
This is unfortunately something that would likely frequently be an issue for us when trying to build out a robust enterprise model leveraging datasets from different functional areas to more effectively and efficiently leverage our premium capacity.
I think this can still be useful for some targeted uses but not as much from a large governed data model.
Unless I'm doing something wrong - then I'm all ears!
Another issue I came across was that I can no longer get directquery dataflows to work whereas I could before.
Hi clementskr !
See my blog post - DirectQuery gives weired queries with enormous big DAX...
Go for "Import"!
“Composite” arrived – CAUTION!
Regards
Oliver
Thanks for your reply. I pretty much always use import. However, if HR has a Power BI dataset that they have built with all of the enterprise semantic layers built into measures and likewise Finance has a Power BI dataset (both Imported on their own), and I want to tie the two together with a relationship between the two without rebuilding all of the already approved measures, which is what this feature allows me to do, I'm limited by the direct query max rows limitation of the relationship is of high cardinality. Both sources are imported in themselves but tying them together forces it to a direct query source. Obviously, a single imported dataset of a full enterprise could be made, but that seems like a maintenance and refresh nightmare.
So as far as I can tell, connecting large functional specific datasets to other large functional specific datasets isn't really enabled (depending on what that relationship is and the cardinality of such) because of direct query limitations.
Ok, I was not aware that also there are limits when the direct is to an tabular in memory...
I also had some other issues to connect datasets.
See:
Stacked datasets
I share your sentiment sir!