Performance of calculated members in SSAS

Question

We are using SSAS in MS SQL Server 2008 R2 to store a cube with company data. We use this cube as a data source for a pivot grid in a web app and to generate a "market penetration" report.

Users are able to upload lists of company IDs. The IDs are stored in a database.

When the user wants to generate a "market penetration" report, he or she has to specify a list that acts as "selection" and a list that acts as a "market".

After submitting, the IDs of both lists end up as calculated members in the cube, using MDX statements like this:

CREATE MEMBER [Prospects].[Measures].[selectids] AS COALESCEEMPTY(SUM( {
  [Dim Bedrijf].[BronId].&[1234], [Dim Bedrijf].[BronId].&[1235],
  ...., [Dim Bedrijf].[BronId].&[3456]}, [Measures].[Fact Bedrijven Count] ),0)

After creating these members, more are created for calculating the indicators in the report (using members and dimensions already defined in the cube). Finally, there is a select statement that returns the necessary data.

This works fine when the number of IDs in either list is smaller than 100k. If the number gets bigger we start getting timeout errors. The timeout is set to 1 hour (the report is generated in a win service separate from the web app).

I'm still new to SSAS so I was wondering if we are just running into the boundaries of the system, or if there is any way to make things more efficient? We need this to be able to handle more then 1M records so we are wondering if this is the way to go.

Hopefully I am clear but if I'm not please let me know.

FrankPl FrankPl · Accepted Answer · 2013-10-10T16:19:10

You are running into one of the performance issues of Analysis Services: Excessive use of calculated members, especially if you reference really many single members, slows down query time. I would suggest you create another attribute on the [Dim Bedrijf] dimension that groups those members that you have in your calculation. This would avoid the need for the calculated member altogether, as you could just select the appropriate attribute member and the base measure [Measures].[Fact Bedrijven Count]. Of course this would mean a bit more effort on the ETL/relational modeling side. But it would reward you with a really good performance at query run time.

Performance of calculated members in SSAS

1 Answers