SASS Mutidimensional import Extremely slow

Question

Hi All,&nbsp;I am connecting to a Multimdimensioal SSAS cube from within Power BI desktop and I am attempting to import a single column from 3 dimensions, one&nbsp;of them is the date&nbsp;dimension,&nbsp;and a single measure from a fact table. The cube is a traditional star schema and contains no M:N relationships, so it is very simplistic.&nbsp;However filtering the date dimension to a particular day and importing the data takes well over 10 minutes, however doing this in Excel or SSMS takes 2 seconds at most. I ran SQL Server Profiler and the MDX that is produced by Power BI is extremely inefficient with crossjoins all over.&nbsp;This makes it pretty much useless for our end users to pull this simple data in to then join and analyse with other data.&nbsp;I raised an issue with MS but they have stated that it is not a break/fix issue so they can't do anything with it. Has anyone any suggestions on how to make this quicker?&nbsp;I have included both MDX scripts below.&nbsp;Thanks&nbsp;&nbsp;

craig_wilson · Answer

Power BI:SELECT&nbsp;&nbsp; &nbsp;{[Measures].[EOD Market Val - GBP]}ON 0,&nbsp;&nbsp; &nbsp;SUBSET(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;NONEMPTY(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;CROSSJOIN(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;CROSSJOIN([Date].[Date].[Date].ALLMEMBERS,[Fund].[Fund Code].[Fund Code].ALLMEMBERS),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;{[Measures].[EOD Market Val - GBP]}),0,4096)PROPERTIES MEMBER_CAPTION,MEMBER_UNIQUE_NAME ON 1FROM&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;SELECT FILTER(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;CROSSJOIN(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;CROSSJOIN([Date].[Date].[Date].ALLMEMBERS,[Fund].[Fund Code].[Fund Code].ALLMEMBERS),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(ISEMPTY(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;OR&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;ISEMPTY("09-06-2016")&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)AND&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(ISEMPTY(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;AND&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;ISEMPTY("09-06-2016")&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;OR&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;NOT(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(ISEMPTY([Date].[Date].CURRENTMEMBER.MEMBER_CAPTION)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;OR&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;ISEMPTY("09-06-2016")&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;AND&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION = "09-06-2016")&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;ON 0&nbsp;&nbsp; &nbsp;FROM&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[CUBE]&nbsp;&nbsp; &nbsp;)CELL PROPERTIES VALUE&nbsp;SSMS:&nbsp;SELECT&nbsp;&nbsp; &nbsp;NON EMPTY { [Measures].[EOD Market Val - GBP] } ON COLUMNS,&nbsp;&nbsp; &nbsp;NON EMPTY { ([Fund].[Fund Code].[Fund Code].ALLMEMBERS * [Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWSFROM&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;SELECT ( { [Date].[Date].&amp;[20160609] } ) ON COLUMNS&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;FROM [CUBE]&nbsp;&nbsp; &nbsp;)WHERE&nbsp;&nbsp; &nbsp;( [Date].[Date].&amp;[20160609] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

gooranga1 · Answer

SSMS:Not sure about the top one but have you tried some extra {} around your dimension attributes? I had a similar issue with an mdx query and it was the way I was selecting the dimension attrbutes.&nbsp;SELECT&nbsp;&nbsp; &nbsp;NON EMPTY { [Measures].[EOD Market Val - GBP] } ON COLUMNS,&nbsp;&nbsp; &nbsp;NON EMPTY {{ ([Fund].[Fund Code].[Fund Code].ALLMEMBERS} * {[Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS ) } }DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWSFROM&nbsp;&nbsp; &nbsp;(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;SELECT ( { [Date].[Date].&amp;[20160609] } ) ON COLUMNS&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;FROM [CUBE]&nbsp;&nbsp; &nbsp;)WHERE&nbsp;&nbsp; &nbsp;( [Date].[Date].&amp;[20160609] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

craig_wilson · Answer

Thanks for the reply.The SSMS one completes very quickly and produces the correct results, however the MDX that Power BI creates automatically, which I pasted above, cannot be changed.&nbsp;The Power BI version&nbsp;is the MDX that takes an extremely long time to run and should produce the exact same results as the SSMS version. However the MDX is so different, which I don't understand. Its the same query they should both be executing.

gooranga1 · Answer

Ah, are you running power bi with the May update? If you are there is a bug that means you can't post mdx into a data source. Very very annoying which is why we won't upgrade until it's fixed. Looks like the when power bi translates from dax to mdx it turns it into a bit of a mess, well that is what I am guessing it does.

https://community.powerbi.com/t5/Desktop/MDX-script-not-being-recognised-in-new-version-of-power-bi/m-p/40636#M15278

https://ideas.powerbi.com/forums/360879-issues/suggestions/14743518-power-bi-desktop-may-update-import-data-from-ssa

craig_wilson · Answer

We are not wanting to post MDX into PowerBI, the users don't know MDX. They want to use Power Query to pull in the fields from the cube they want e.g. import the data. The MDX above was captured from SQL Server Profiler.&nbsp;I agree the DAX to MDX looks awful and performs terribly. MS need to fix this.

Forum Discussion

SASS Mutidimensional import Extremely slow

7 Replies

Related content

Power BI Service extremely slow

ShapeMap visualization extremely small

Workspace loading is extremely slow

Extreme swings in dataset refresh times

Direct Query is extremely slow