none
Power Query to SSAS does several CROSSJOINS.. Why and is it a performance issue? RRS feed

  • Question

  • I have a query where I am pulling data from SSAS from a couple of tables and merging it with another power query table.

    The query execution is slower than what I would like it to be so I decided to look at the SQL profiler and below is what I got. 

    SELECT{[Measures].[All Hrs]}ON 0,NONEMPTY(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    CROSSJOIN(
    [Project].[Project ID].[Project ID].ALLMEMBERS,[Time].[Approved By].[Approved By].ALLMEMBERS),[Time].[Approved Date].[Approved Date].ALLMEMBERS),[Time].[Attendance Type].[Attendance Type].ALLMEMBERS),[Time].[Bill Group].[Bill Group].ALLMEMBERS),[Time].[Billing Indicator].[Billing Indicator].ALLMEMBERS),[Time].[Country].[Country].ALLMEMBERS),[Time].[Created Date].[Created Date].ALLMEMBERS),[Time].[Current Approver].[Current Approver].ALLMEMBERS),[Time].[Employee].[Employee].ALLMEMBERS),[Time].[Employee SAP ID].[Employee SAP ID].ALLMEMBERS),[Time].[Employee Type].[Employee Type].ALLMEMBERS),[Time].[Time Bucket].[Time Bucket].ALLMEMBERS),[Time].[Timesheet Status].[Timesheet Status].ALLMEMBERS),[Time].[Weekend Date].[Weekend Date].ALLMEMBERS),{[Measures].[All Hrs]})PROPERTIES MEMBER_CAPTION,MEMBER_UNIQUE_NAME ON 1 FROM [Model] CELL PROPERTIES VALUE

    Can anyone explain exactly what is happening in the MDX query below.. as in why are there so many cross-joins and will it have an impact on performance.

    Thursday, August 4, 2016 8:35 PM

Answers

  • CROSSJOIN is what the Power Query SSAS connector uses to do its tabular projection across hierarchies. If the number of CROSSJOIN is causing performance issues, it's probably because you're adding too many dimensions attributes to the result set. Here it seems like you're adding 14. This is equivalent to a pivot table where you've added 14 hierarchies and expanded all the nodes. If you really need all 14 columns, then you will probably need to filter your result set down if you want reasonable load time.

    Friday, August 5, 2016 6:40 PM
    Moderator