While I was playing around with the new release (August 2018) of Power BI Desktop I noticed there was an undocumented change: similar to the OData improvements I blogged about here, there is a new option in the AnalysisServices.Database() and AnalysisServices.Databases() M functions that turns on a newer version of the MDX generation layer used by the Power Query engine. Like the OData improvements it is an option called Implementation=”2.0”, used like this:
[sourcecode language=’text’ padlinenumbers=’true’ highlight=’5′]
AnalysisServices.Databases(
“localhost”,
[
TypedMeasureColumns=true,
Implementation=”2.0″
]
)
[/sourcecode]
…and also, as with the OData improvements, you will need to manually edit any existing M queries to take advantage of this.
In fact first heard about this option in a comment on this blog post back in January, but as I was told by the dev team that it hadn’t been tested properly I didn’t blog about it. However as it is now set by default in the M code generated by the Power Query Editor, I guess it’s ready for production use. I’m told it should improve the performance of M queries that import data from Analysis Services – and I would be very interested to hear from anyone who tests this about how much of an improvement they see.
I’ve done a little bit of testing myself and I can see there are indeed some differences in the MDX generated. For example, I created an M query that connected to the Adventure Works DW database and returned all combinations of customer and day name where the Internet Sales Amount measure is greater than 100. In the old version of the MDX generation layer (ie without Implementation=”2.0” set) the following MDX query is generated:
[sourcecode language=’text’ ]
SELECT
{
[Measures].[Internet Sales Amount]
}ON 0,
SUBSET(
NONEMPTY(
CROSSJOIN(
[Date].[Day Name].[Day Name].ALLMEMBERS,
[Customer].[Customer].[Customer].ALLMEMBERS
),
{
[Measures].[Internet Sales Amount]
}
),
4096
)
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1
FROM(
SELECT
FILTER(
CROSSJOIN(
[Date].[Day Name].[Day Name].ALLMEMBERS,
[Customer].[Customer].[Customer].ALLMEMBERS
),
(
NOT((
ISEMPTY(
[Measures].[Internet Sales Amount]
)
OR
ISEMPTY(
100
)
))
AND
([Measures].[Internet Sales Amount] > 100)
)
)ON 0
FROM [Adventure Works]
)CELL PROPERTIES VALUE
[/sourcecode]
However with the Implementation=”2.0” option set, the following query is generated:
[sourcecode language=’text’ ]
SELECT
{
[Measures].[Internet Sales Amount]
}ON 0,
SUBSET(
NONEMPTY(
FILTER(
CROSSJOIN(
[Date].[Day Name].[Day Name].ALLMEMBERS,
[Customer].[Customer].[Customer].ALLMEMBERS
),
(
NOT(ISEMPTY(
[Measures].[Internet Sales Amount]
))
AND
([Measures].[Internet Sales Amount] > 100)
)
),
{
[Measures].[Internet Sales Amount]
}
),
4096
)
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1
FROM [Adventure Works] CELL PROPERTIES VALUE
[/sourcecode]
The difference between the two is that the first query uses a subselect to do the filtering whereas the second does not; subselects in MDX are not necessarily bad, but I generally try to avoid using them unless I need to. There may be other differences in the way the MDX is generated in the new version but I haven’t had a chance to do any detailed testing.
One thought on “Analysis Services Import Performance Improvements In The August 2018 Release Of Power BI”