Forum Discussion
SASS Mutidimensional import Extremely slow
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.
SELECT
NON EMPTY { [Measures].[EOD Market Val - GBP] } ON COLUMNS,
NON EMPTY {{ ([Fund].[Fund Code].[Fund Code].ALLMEMBERS} * {[Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS ) } }DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
(
SELECT ( { [Date].[Date].&[20160609] } ) ON COLUMNS
FROM [CUBE]
)
WHERE
( [Date].[Date].&[20160609] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
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. The Power BI version 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.
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.
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.
I agree the DAX to MDX looks awful and performs terribly. MS need to fix this.
okay I see.
I suppose the alternative is to re-create your cubes but in a tabular model instead of multi-dimensional.