Dealing with large result sets in SQL Server Analysis Services

Question

I have a Database that contains data about articles,structures and manufacturers. Meaning an article is linked to 1 manufacturer and to N structure-nodes (think as article-classification-nodes).

Querying articles using T-SQL with a lot of conditions is currently too slow to be usable for an e-shop, even with good hardware and properly indexed tables. (Should be below 1 sec). Now I wonder if it would make sense to access this data through an OLAP Cube. I already developed one to get aggregations, like: How many articles of manufacturer X exist below node Y recursively?

These aggregations are pretty fast, now I wonder if it makes sense to also retrieve whole article-result sets through Cubes. Meaning: Give me every single article ID of manufacturer X that exist below node Y recursively. Because the result sets can be quite large, the query takes even longer..

Therefore my question, is there a way to deal with large result sets in SSAS, or is this totally the wrong direction I am taking?

Answer 1

You can definitely deal with large sets of data and make them perform decently in SSAS by leveraging Aggregations. Of course, if you're going over the wire, that's still a lot of data to move, so keep that in mind. Your query will return quickly; the results will take a while to transfer.

The real power of SSAS is being able to be targeted in your approach. Instead of saying "Give me everything," we can start out at a high level, drill down, find the level we want, and continue drilling down, down, down until you get to the data that you really want.