SSAS - AVG Calculation but when used parent/child dimension use SUM

Question

I have this structure of DWH

DimRegion

ID  IDParent Level Region       
1 |  NULL    |  0   | World             
2 |   1      |  1   |America
3 |   1      |  1   |Europe
4 |   2      |  2   |USA
5 |   2      |  2   |Canada
6 |   2      |  2   |Mexico
7 |   3      |  2   |Italy
8 |   3      |  2   |France

FactSales

ID  IDDimRegion IDDimDate  Sales 
1 | 7          | 20150101        | 20
2 | 7          | 20150201        | 30
3 | 7          | 20150301        | 40
4 | 4          | 20150101        | 50
5 | 4          | 20150201        | 30
6 | 4          | 20150301        | 40
7 | 5          | 20150101        | 60
8 | 5          | 20150201        | 30
9 | 5          | 20150301        | 60
10| 8          | 20150101        | 10
11| 8          | 20150201        | 10
12  8          | 20150301        | 10

etc....

All values from Fact table are connected only to values on level 2 in DimRegion table.

I created cube [Sales Cube] in SSAS with:

parent/child dimension DimRegion
dimension DimDate
Measures with SUM aggregation [Sales SUM]

I made calculation [Measures].[AVG Into higher regions] which wrap the measure [Sales SUM] and do aggregation AVG into higher level of regions.

CREATE 
  MEMBER CURRENTCUBE.[Measures].[AVG Into higher regions] AS 
    CoalesceEmpty
    (
      Avg
      (
        Descendants
        (
          [DimRegion].[IDParent].Children,
         ,LEAVES
        )
       ,[Measures].[Sales SUM]
      )
     ,[Measures].[Sales SUM]
    ) 
   ,VISIBLE = 1 ;

but it is very slow when i use this calculation with dimension date and IDDimDate member in MDX query, so i don't know if there is something incorrect in calculation or in this MDX query because it is so slow.

SELECT 
NON EMPTY { [Measures].[AVG Into higher regions] } ON COLUMNS, 
NON EMPTY 
{ (
DESCENDANTS([DimRegion].[IDParent].[Level 02].ALLMEMBERS) * 
[DimDate].[IDDimDate].[IDDimDate].ALLMEMBERS ) } ON ROWS 
FROM [Sales Cube]

In my tes Data Warehouse i have about 200 rows in fact table and pre-generated 20 years in Date dimension it's 7300 rows so probably it can be caused somehow by Date dimension. But the final fact table will have about 1 million rows so it is bad that on so less rows it is so slow.

Thank you for your help

what is the point of CoalesceEmpty ? if you leave that out of the measure does the measure still work? — whytheq
I have the CoalesceEmpty there because the lowest value in parent/child hierarchy has (null) without that so this make that the (null) value is replaced with value from Sales SUM measure — Bushwacka

whytheq whytheq · Accepted Answer · 2016-08-26T09:27:09

NULL is the friend of olap calculations, in terms of creating sparse calculations that then function in block mode - rather than cell-by-cell calculations.

You inclusion of CoalesceEmpty looks to me like NULL will never be encountered - so this will be slow.

Without that function do you get the wrong answer?

CREATE 
  MEMBER CURRENTCUBE.[Measures].[AVG Into higher regions] AS 
      Avg
      (
        Descendants
        (
          [DimRegion].[IDParent].Children,
         ,LEAVES
        )
       ,[Measures].[Sales SUM]
      )
   ,VISIBLE = 1 ;

SSAS - AVG Calculation but when used parent/child dimension use SUM

2 Answers