Out of memory in matrix with hierarchy when adding a measure

[jprdynamicapps]

I have a strange problem with a matrix.  It has 4 levels of hiearchy and a bunch of aggregated fields.

If I add a measure to it (even something like "measure=3"), it runs forever and runs out of memory.

If I remove the lowest level of my hierarchy, I can add the measures without any problem.

I would think that there is some kind of loop related to the hierarchy.

Any ideas on what might be going on?

FYI, all 4 levels of hierarchy are text from dim tables.  The fields are all from a couple of fact tables.

Thanks.

-jpr

[parry2k]

@jprdynamicapps it might be that performance of your measure is not good. what kind of calculationg you are doing in measure?

[jprdynamicapps]

@parry2k, thanks for the reply.  Even just adding a measure like "measure = 3" causes the problem.

Removing it or the lowest level of my hierarchy solves it.

Thanks.

John

[parry2k]

@jprdynamicapps  very odd, how big is the dataset?

[jprdynamicapps]

@parry2k , pretty small and performs very well.

No other measures on the matrix either.

I'm stumped.  Any ideas on troubleshooting?

I was able to replicate in DAX Studio.  Code below.

FPPR and FPMS are fact tables.

Dim1-Dim4 are dim tables

Dim1text, etc. are text files from those dim tables.

Relationships are single direction from dim tables to fact tables.

Removing Measure 1 (which is just Measure 1=3) fixes the issue.

Removing Dim4 fixes the issue.

Otherwise, I get out of memory.

DEFINE VAR __ValueFilterDM5 = 
  FILTER(
    KEEPFILTERS(
      SUMMARIZECOLUMNS(
        'dim Dim1'[Dim1text],
        'dim Dim2'[Dim2text],
        'dim Dim3'[Dim3text],
        'dim Dim4'[Dim4text],
        "Measure_1", 'fact FPPR'[Measure 1],
        "SumField1", CALCULATE(SUM('fact FPPR'[Field1])),
        "AvgField2", CALCULATE(AVERAGE('fact FPPR'[Field2])),
        "AvgField3", CALCULATE(AVERAGE('fact FPPR'[Field3])),
        "SumField4", CALCULATE(SUM('fact FPMS'[Field4])),
        "SumField5", CALCULATE(SUM('fact FPPR'[Field5])),
        "SumField6", CALCULATE(SUM('fact FPPR'[Field6])),
        "v_Rowsin_FPPR", IGNORE('fact FPPR'[#Rowsin FPPR])
      )
    ),
    [v_Rowsin_FPPR] > 0
  )

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('dim Dim1text'[Dim1text], "IsGrandTotalRowTotal"),
      __ValueFilterDM5,
      "Measure_1", 'fact FPPR'[Measure 1],
      "SumField1", CALCULATE(SUM('fact FPPR'[Field1])),
      "AvgField2", CALCULATE(AVERAGE('fact FPPR'[Field2])),
      "AverageField3", CALCULATE(AVERAGE('fact FPPR'[Field3])),
      "SumField4", CALCULATE(SUM('fact FPMS'[Field4])),
      "SumField5", CALCULATE(SUM('fact FPPR'[Field5])),
      "SumField6", CALCULATE(SUM('fact FPPR'[Field6]))
    ),
    [IsGrandTotalRowTotal],
    0,
    'dim Dim1text'[Dim1text],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'dim Dim1text'[Dim1text]

[parry2k]

@jprdynamicapps can you share relationship diagram?

[jprdynamicapps]

@parry2k ,

I was able to replicate the problem with one fact table and 4 dimensions with a single direction 1:many relationships between the fact table and each dimension.

Since that eliminated most of the variables, I tried other things.  It turns out that the whole thing works if I turn off a filter that is set to #RowsinFPPR >0.  #RowsinFPPR = Countrows('fact FPPR').  This is what I'm using to ensure that only valid data from my fact table is included.

Any idea why this would be a problem?

Thanks.

[parry2k]

@jprdynamicapps so I guess you are using this as visual level filter? right? Not sure if you need to do this since if your measure returns blank, it will not show up in the visual. 

[jprdynamicapps]

@parry2k, Yes, it was on the visual.  You're correct that it's not needed on the matrix, just the slicers, so I'm applying it just to them now.  So, my issue is resolved, but I'm curious if you have an idea of why that would be a problem.

Thanks.

[Anonymous]

Hi @jprdynamicapps ,

Please check whether the solution in the following threads can help you.

Hierarchy Filters Breaking when Matrix has Measure as a Value

Matrix out of memory with new measure

Best Regards

Rena

[jprdynamicapps]

@Anonymous Thanks, Rena.  I had seen both of those and neither seem to apply.

John

[jprdynamicapps]

@parry2k I will.  There's a lot going on in it.

I'm creating a version that just has those tables to see if it still happens and to share it as a simpler issue.