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!
Thanks for your answer. We got a F128 and already use max memory per query which is 10 GB
I will check the Excel updates and see if we can do a rollback. But we are using Power BI Service so in that case I guess it is impossible to do a rollback since we can't control the updates in the service?
Regards
Fredrik
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;