SSAS Back Engine and Named Queries in the DSV

Hi everybody,

What’s up?

Thought I’d share with you something strange I found while using named queries in the DSV layer in SQL Server Analysis Services.

I have developed quite a complex layer of connected tables in my DSV. As development had evolved (with the customers demand) I found myself turning 3 of my regular tables into named queries. The named queries were basically using the same table twice in an inner join to compare the state of the entity in two different times in the table. After this change, my process time for the cube and the dimensions grew from about 5 minutes to 20 (!)

After a while I found myself adding yet another named query to my DSV. This time, the named query included a few tables inner joined. My process time for the cube and the dimensions grew to over an hour(!) I admit I even stopped the process so I don’t know the total time, but just the cube itself demanded about 45 minutes… needless to say, quite disturbing.

So what’s left to do? Check the SQL query working in the background of the SSAS engine. Seeing the query used is easy – all you need to do is click on the dimension > attribute > sql query or click on the measure group > partition > sql query. After double clicking the "select" statement under the "sql query" and you can copy the query.

Next, you take the query and run it inside a new query in the Management Studio using the relevant database. Run the query with the Estimated Execution Plan which can show you what the engine of the SSAS is actually doing in the background while building the attribute / measure.

I found the engine runs my named queries used a lot of scans and their order wasn't right (it started with the big tables first).    

So what’s the solution? Well, in my project at least, the only immediate solution seemed to be to build actual tables out of the named queries. And my processing time shrunk back to 5 minutes again! J