0

I have a bunch of calculated measures which I understand are executed only at query time. Unlike the derived measures which are pre-aggregated during cube processing time and thereby giving a better query performance the calculated measures aren't pre-aggregated and hence have the potential for a bad performance.

Are there any tips or tricks to decrease the query time of calculated measures in SSAS cubes? I already created partitions on my measure groups and this helped in reducing the cube perofrmance time. But now I want to go a step further and reduce the query time for the calculated measures.

I hear something about aggregations and named sets that can help with this. But couldn't find anything online with a god tutorial on how to do this.

Updated:

Here are the calculated measures I'm currently using in my cube

[Total Activities] [Measures].[Day1] + [Measures].[Day2] + [Measures].[Day3]

[Total Days Engaged] IIF([Measures].[Day1] > 0 , 1, 0) + IIF([Measures].[Day2] > 0 , 1, 0) + IIF([Measures].[Day3] > 0 , 1, 0)

[Avg Days Engaged Per Users] IIF([Measures].[USER COUNT] = 0, 0 , [Measures].[Total Days Engaged]/[Measures].[USER COUNT])

[Avg Requests Per User] IIF([Measures].[USER COUNT] = 0, 0 ,[Measures].[Total Activities]/[Measures].[USER COUNT])

[Daily Engaged Users] SUM([Dim User].[Id].[Id], IIF([Measures].[Total Days Engaged] > 2, 1, 0))

user330612
  • 2,189
  • 7
  • 33
  • 64
  • 2
    Just by googling "Analyis Services Performance Guide" I quickly found the following documents: http://download.microsoft.com/download/6/5/6/6567C845-FC8D-4D62-920F-C027A349C889/SSASPerfGuide2008R2.pdf http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=17303 http://msdn.microsoft.com/en-us/library/dn749781.aspx – FrankPl Oct 28 '14 at 19:18
  • might be good to include some of the calculated measures in your question as I suspect performance tuning will vary depending on which functions you are using. Certain combinations of functions push the engine into something called cell-by-cell mode (bad) whereas other combinations create a plan in block mode (good) – whytheq Oct 28 '14 at 19:21
  • updated the question with my measures! – user330612 Oct 28 '14 at 19:37
  • Are you really having performance issues, or are you just assuming you might have/get them? – FrankPl Oct 28 '14 at 20:22
  • Is the `Total Days Engaged` measure dependent on other dimensions than User? Is it a physical measure? – FrankPl Oct 28 '14 at 20:23
  • Yes I do have performance issues with my calculated measures. The queries are taking 2-5 minutes for the daily engaged measure. I'm not sure what you mean by physical measure. But it can't be calculated at the fact table level since that table is per user per browser per device. Browser , device and user are my dimensions. – user330612 Oct 28 '14 at 21:01
  • I would have expected this measure to possibly have a problem, as it is the only one from your list that does aggregations in its calculation. All the others are relying on other measures that are already aggregated, and hence should not be a problem. To optimize this measure is very difficult without seeing the cube definition including the measures and dimensions involved. By the way, by "physical measure" I meant a non-calculated "normally aggregated" measure. – FrankPl Oct 29 '14 at 20:09
  • you mention `god tutorial` in question ... god's tutorials do exist here: http://sqlblog.com/blogs/mosha/default.aspx – whytheq Oct 30 '14 at 00:04

0 Answers0