Forum Discussion
Community ChampionNew Composite model and Tuning query limits
I have tried my hands on new composite model (https://www.youtube.com/watch?v=g-nRxDVt3To&lc=z23bsxzgzyrfud0pl04t1aokgilrytqdo5vik10towetbk0h00410). This is so crazy good for optmization and model reduction, I can't belive what Microsoft has unleashed throught this. This is super awesome !!!
I have tried this on a fairly large SSAS cube with the largest dataset being >10M+ rows. Once I converted an existing model to a composite model, the size reduced by >=95%, however some of the measures are not working as the measure calculation are reaching the query limits.
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rowsIs there any way around this ?
Many thanks in advance.
amitchandak thanks for looking into this. The composite model works as a direct query and hence, all the limitations of direct query applies to it, including the possibilty of the background query of a DAX measure being evaluated for >=1M+ combinations. This is where the measure will throw an error. Despite Composite model being super cool, they are hard to maintain unless each and every measure is optimized. In my case, I had a measure with SWITCH which was throwing me the error. The moment I optimized the measure, the error went away. For a composite model to work flawlessly, it challenges the developer to write measure in a super optimized away which in turn helps the developer to take caution and apply optimization from the initial stage. I plan to work with Composite model this year to see what other challenges it throw at me, tackling which would make me become better at DAX
2 Replies
- amitchandak
Super User
smpa01 , seems like these measure query some row level data, that exceeds 10M rows.
smpa01amitchandak thanks for looking into this. The composite model works as a direct query and hence, all the limitations of direct query applies to it, including the possibilty of the background query of a DAX measure being evaluated for >=1M+ combinations. This is where the measure will throw an error. Despite Composite model being super cool, they are hard to maintain unless each and every measure is optimized. In my case, I had a measure with SWITCH which was throwing me the error. The moment I optimized the measure, the error went away. For a composite model to work flawlessly, it challenges the developer to write measure in a super optimized away which in turn helps the developer to take caution and apply optimization from the initial stage. I plan to work with Composite model this year to see what other challenges it throw at me, tackling which would make me become better at DAX
