SASS Mutidimensional import Extremely slow

[craig_wilson]

Hi All,

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 of them is the date dimension, 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.

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.

This makes it pretty much useless for our end users to pull this simple data in to then join and analyse with other data.

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?

I have included both MDX scripts below.

Thanks

[gooranga1]

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

[craig_wilson]

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.

[gooranga1]

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/...

https://ideas.powerbi.com/forums/360879-issues/suggestions/14743518-power-bi-desktop-may-update-impo...

[craig_wilson]

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.

[gooranga1]

okay I see.

I suppose the alternative is to re-create your cubes but in a tabular model instead of multi-dimensional.

[craig_wilson]

Porting to Tabular is not an option for us unfortunately

[craig_wilson]

Power BI:

SELECT
    {[Measures].[EOD Market Val - GBP]}ON 0,
    SUBSET(
        NONEMPTY(
            CROSSJOIN(
                CROSSJOIN([Date].[Date].[Date].ALLMEMBERS,[Fund].[Fund Code].[Fund Code].ALLMEMBERS),
                [Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS),
            {[Measures].[EOD Market Val - GBP]}),0,4096)PROPERTIES MEMBER_CAPTION,MEMBER_UNIQUE_NAME ON 1
FROM
    (
        SELECT FILTER(
            CROSSJOIN(
                CROSSJOIN([Date].[Date].[Date].ALLMEMBERS,[Fund].[Fund Code].[Fund Code].ALLMEMBERS),
                [Instrument].[Instrument Parent Type].[Instrument Parent Type].ALLMEMBERS),
                (
                    (
                        (ISEMPTY(
                            [Date].[Date].CURRENTMEMBER.MEMBER_CAPTION)
                            OR
                            ISEMPTY("09-06-2016")
                        )AND
                        (ISEMPTY(
                            [Date].[Date].CURRENTMEMBER.MEMBER_CAPTION)
                            AND
                            ISEMPTY("09-06-2016")
                        )
                    )
                    OR
                    (
                        NOT(
                            (ISEMPTY([Date].[Date].CURRENTMEMBER.MEMBER_CAPTION)
                            OR
                            ISEMPTY("09-06-2016")
                        )
                    )
                    AND
                    (
                        [Date].[Date].CURRENTMEMBER.MEMBER_CAPTION = "09-06-2016")
                    )
                )
            )
        ON 0
    FROM
        [CUBE]
    )CELL PROPERTIES VALUE

SSMS:

 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