Forum Discussion
Power ParticipantBest Practice for Import vs. DirectQuery Mode in Power BI Reports with Dimensional and Fact Tables
Power ParticipantThanks for sharing. Regarding what you mentioned here:
lbendlin wrote:2. There is a soft limit on the cardinality of the key column in table relationships. Anything above 50K will start to give you problems both in composite data models but also inside a semantic model. This is mostly caused by the key column values being enumerated verbatim, which can lead to extremely large query texts (not even talking about data here!) and severe performance degradation.
Please correct me if I'm wrong but are you saying this soft limit would apply for both DirectQuery and Import mode?. Furthermore, what woud be the workaround when handling data with such high cardinality?.
I will try to avoid DirectQuery moving forward unless I'm dealing with one of the three scenarios that you listed:
lbendlin wrote:Direct Query mode against an on-premise data source should only be used if that source is
- too large (TB range)
- queried very infrequently and randomly
- fast changing (note I say changing, not inserting)
I noticed you put an emphasis on on-premises data. If the data source was stored on the cloud would you make a distinction here or would the same general rule apply?.
Thanks in advance.
Super Userare you saying this soft limit would apply for both DirectQuery and Import mode?. Furthermore, what woud be the workaround when handling data with such high cardinality?
The soft limit applies to the semantic model (which by definition is direct query only)
There is no easy solution. Let's assume you have a DateTime field as the primary key - you could consider splitting that into two tables, one with dates and one with date fractions. That would still give you 86400 rows in the second table (assuming second level granularity) but you might be able to get away with it.
You may be able to find compromises, but there's no guarantee. I have had cases where I had to abandon a project entirely (a dimension with 1.3M distinct key values, added to a composite model, produced direct query queries larger than 6GB (JUST THE QUERY TEXT). That was simply not sustainable.