New Composite model and Tuning query limits

[smpa01]

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' rows

Is there any way around this ?

Many thanks in advance.

[smpa01 · Accepted solution]

@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

[amitchandak]

@smpa01 , seems like these measure query some row level data, that exceeds 10M rows.