Forum Discussion
Memory issue with Excel Pivot table connected to Semantic Models
Hi!
I have now downgraded the Excel version on my computer and can confirm that the problem is the Excel version. Version 16.0.18429.20132 was installed on my computer and now I have downgraded it to version 16.0.18227.20082.
Our files that exceeded the 10 GB memory limit now only consume 50 MB.
The MDX code generated in version 16.0.18429.20132 of Excel looked like this:
SELECT
NON EMPTY
Hierarchize
(
DrillDownMember
(
DrillDownMember
(
CrossJoin
(
{
[Product].[Article].[All],
[Product].[Article].[Article].ALLMEMBERS
},
{([Supplier].[Production Market].[All],[Size].[Size].[All])}
),
[Product].[Article].[Article].ALLMEMBERS,
[Supplier].[Production Market]
),
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size]
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{
[Time DIS].[Period DIS].&[Aug, 2025],
[Time DIS].[Period DIS].&[Dec, 2025],
[Time DIS].[Period DIS].&[Jul, 2025],
[Time DIS].[Period DIS].&[Jun, 2025],
[Time DIS].[Period DIS].&[May, 2025],
[Time DIS].[Period DIS].&[Nov, 2025],
[Time DIS].[Period DIS].&[Oct, 2025],
[Time DIS].[Period DIS].&[Sep, 2025]
} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - Casual W],
[Product].[Major Product Group].&[6 - Trousers],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
And the MDX code for the same Pivot table but in version 16.0.18227.20082 of Excel looks like this:
SELECT
NON EMPTY
Hierarchize
(
CrossJoin
(
{[Product].[Article].[Article].ALLMEMBERS},
{
(
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size].[Size].ALLMEMBERS
)
}
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{
[Time DIS].[Period DIS].&[Aug, 2025],
[Time DIS].[Period DIS].&[Dec, 2025],
[Time DIS].[Period DIS].&[Jul, 2025],
[Time DIS].[Period DIS].&[Jun, 2025],
[Time DIS].[Period DIS].&[May, 2025],
[Time DIS].[Period DIS].&[Nov, 2025],
[Time DIS].[Period DIS].&[Oct, 2025],
[Time DIS].[Period DIS].&[Sep, 2025]
} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - Casual W],
[Product].[Major Product Group].&[6 - Trousers],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
Hi!
I've done some more testing and here's some more information.
I've set up a simple pivot that goes through to see how much memory it requires. The pivot I set up generates this MDX code:
SELECT
NON EMPTY
Hierarchize
(
DrillDownMember
(
DrillDownMember
(
CrossJoin
(
{
[Product].[Article].[All],
[Product].[Article].[Article].ALLMEMBERS
},
{([Supplier].[Production Market].[All],[Size].[Size].[All])}
),
[Product].[Article].[Article].ALLMEMBERS,
[Supplier].[Production Market]
),
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size]
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{[Time DIS].[Period DIS].&[Sep, 2025]} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - W],
[Product].[Major Product Group].&[6 - T],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
And this query uses 3 GB of memory.
The model is a total of 600 MB
The dimensions used are:
Product containing 230,000 rows
Supplier containing 2,400 rows
The facts table, FPURORD, used contains 1.1 million rows
The measure used looks like this: SUM('FPURORD'[POUNITS])
I seem like a lot of memory usage for this small model and simple pivot table