Different complexity of data extraction from cube(SSAS 2015) via excel 2013

Question

When user connect to SSAS cubes 2015(cube size is approx 15-16 GB) from excel 2013. they browse the cube data by selecting the different combination of dimensions attributes and facts measures. This combination in excel to fetch the cube data can be simple or complex. I have a requirement from client where I have to come up with the different types of these combination to fetch data which can be described as Simple, Moderate and Complex. [For example(I have to come up with something like this): 1. fetching data for less than 5 measures within the same measure group against less than 10 dimension is Simple Extraction. 2. Fetching data for more than 5 and less than 10 measures against more than 10 dimesions within the same measure group is moderateExtraction. 3. fetching ata for more than 10 measure across multiple measure groups against more than 15 multiple dimensions attributes considered as Complex Extraction]

Do anyone has any experience /knowledge/idea on working on such type of request. Any comment / idea / suggestion will be a great help.

Answer 1

I faced a similar issue quite a long time back. The situation was we made an in house cube browser (something along the lines of what opens up when we select the browse option in SSMS). The MDX generator for this was also written in-house, so we used to add in functionality when we faced difficult requirements. But with off the shelf tools, you cannot push the MDX they emit. And as the users select more and more combinations the query will not be optimal at all. Therefore I suggest that you let them use excel for the basic or moderate level at most. For the complex level you should provide them custom MDX, where based on your business knowledge, you can filter out irrelevant combinations. Plus you can paginate your query.