none
DAX CrossJoin between heavy facts

    Question

  • Dear Gurus,

    I am using SSAS Tabular and creating SSRS reports on top of it. My datasets are pure DAX code, no MDX.

    My issue is that I am unable to find out how to make my query fast when the dataset involves many facts.

    Currently I am using the following query:

    EVALUATE
    ADDCOLUMNS(
    FILTER(
    	SUMMARIZE(
    		CROSSJOIN(
    			FactFallRegistration,
    			FactPermissions
    		)
    		, Resident[Firstname]
    		, Resident[Lastname]
    		, Resident[NationalNumber]
    		, Room[RoomDescription]
    		, Room[RoomTypeEN]
    		, CareProfile[CareProfileCode]
    	), --Summarize
    	FactPermissions[# Active Permissions] > 0
    )--Filter
    
    , "ResidentFirstname", Resident[Firstname]
    , "ResidentLastName", Resident[Lastname]
    , "ResidentNationalNumber", Resident[NationalNumber]
    , "ResidentRoomDescription", Room[RoomDescription]
    , "ResidentRoomTypeEN", Room[RoomTypeEN]
    , "RizivCareProfile", CareProfile[CareProfileCode]
    , "# Falls", FactFallRegistration[# Falls]		
    )--ADDCOLUMNS

    The following model represents my cube:

    The starting point is the Fact Falls, all attributes I need to read are linked to the factFalls except DimCareProfile, and in order to get a care profile a measure called # Active Permissions must be positive for one resident on one day.

    Currently I am using a plain crossjoin embedded in a Filter that excludes the null/0 permissions; factPermissions is significant in term of size, hence the query as-is (without SSRS filters) takes 2:30minutes to execute.

    How can I tune this query to propagate the context from falls to the careprofile without a crossjoin ?

    What is the querying best practice when multiple facts are to be used in the same query (2 facts or more)?

    Thanks so much for any insights

    Miloud


    • Edited by MyLoudWords Thursday, May 15, 2014 11:29 AM corrections
    Thursday, May 15, 2014 11:27 AM

Answers

  • Assuming that [Falls #] is a measure, why you don't do that? I don't see the point of doing the Crossjoin.

    EVALUATE
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                FactPermissions,
                Resident[Firstname],
                Resident[Lastname],
                Resident[NationalNumber],
                Room[RoomDescription],
                Room[RoomTypeEN],
                CareProfile[CareProfileCode]
            ),
            FactPermissions[# Active Permissions] > 0
        ),
        "ResidentFirstname", Resident[Firstname],
        "ResidentLastName", Resident[Lastname],
        "ResidentNationalNumber", Resident[NationalNumber],
        "ResidentRoomDescription", Room[RoomDescription],
        "ResidentRoomTypeEN", Room[RoomTypeEN],
        "RizivCareProfile", CareProfile[CareProfileCode],
        "# Falls", [# Falls]
    )


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by MyLoudWords Tuesday, May 20, 2014 2:42 PM
    Friday, May 16, 2014 12:23 PM

All replies

  • Assuming that [Falls #] is a measure, why you don't do that? I don't see the point of doing the Crossjoin.

    EVALUATE
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                FactPermissions,
                Resident[Firstname],
                Resident[Lastname],
                Resident[NationalNumber],
                Room[RoomDescription],
                Room[RoomTypeEN],
                CareProfile[CareProfileCode]
            ),
            FactPermissions[# Active Permissions] > 0
        ),
        "ResidentFirstname", Resident[Firstname],
        "ResidentLastName", Resident[Lastname],
        "ResidentNationalNumber", Resident[NationalNumber],
        "ResidentRoomDescription", Room[RoomDescription],
        "ResidentRoomTypeEN", Room[RoomTypeEN],
        "RizivCareProfile", CareProfile[CareProfileCode],
        "# Falls", [# Falls]
    )


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by MyLoudWords Tuesday, May 20, 2014 2:42 PM
    Friday, May 16, 2014 12:23 PM
  • Marco thanks for the tip !

    But I am a bit surprised because the query still takes a very long time :-/

    I don't get it, if I am at a certain row in Fact Fall, if then I want to get the CareProfile, going through the Fact Permissions should be ultra fast since the link (Resident + Date) is known...

    Or is my query construct non accurate ?

    Marco moreover, let's imagine the starting point is Fact Falls, how could you still get the value of the CareProfile ? I have troubles to understand how can I navigate from Fact Falls to RizivCareProfile

     Edit:

    I made some progress, now I have a super fast query. The idea i followed is as below:

    EVALUATE
    CALCULATETABLE(
    	FILTER(
    		ADDCOLUMNS(
    			SUMMARIZE(
    				GENERATE(
    					Filter(
    						FactFallRegistration, 
    						[# Falls Permitted Long Stay] = 1
    					)
    					, CareProfile
    				)
    				, Resident[Firstname]
    				, Resident[Lastname]
    				, Resident[NationalNumber]
    				, OperationalUnit[OperationalUnitNumber]
    				, 'Date'[BK_Calendar] , CareProfile[CareProfileCode]
    				, "CareProfile", CareProfile[CareProfileCode]
    			) -- summarize
    		, "# Falls", [# Falls Permitted Long Stay]
    		)
    		,[# Falls Permitted Long Stay] > 0
    	)
    	, Resident[NationalNumber] = "XXX"
    )
    ORDER BY 
    	'Date'[BK_Calendar]

    I embedded the many to many relationship in a measure to have a cleaner approach.

    Now this data set gives me something like:

    

    Now what I am unable to achieve is to get something like:

    Indeed, I need to group on resident operational unit...etc then give the total number of falls and the last value of CareProfile (in the 1st table above it's C)

    I tried using the LastNonBlank but no luck, how can I do a sort of tail or whatever DAX oriented call to get the last version of the careprofile ?

    Thanks very much

    • Edited by MyLoudWords Monday, May 19, 2014 3:46 PM add
    Monday, May 19, 2014 12:16 PM
  • If you want to get the first/last row, you should look at TOPN.

    However, it seems you want to group rows (so use SUMMARIZE), not just getting the last row.

    Am I wrong?


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Monday, May 19, 2014 9:36 PM
  • If you want to get the first/last row, you should look at TOPN.

    However, it seems you want to group rows (so use SUMMARIZE), not just getting the last row.

    Am I wrong?


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    I will look into TOPN to see how I can use it. Actually no it is a group by except for the CareProfile column, where I must take the last one as shown in the first figure, the last profile value is C hence I need to return that one

    Thanks so much for TOPN didn't know it !

    Monday, May 19, 2014 9:39 PM
  • Marco I can't find my way out of this with TOPN :-/

    You see, in the example I give above I filter on one unique person with:

    Resident[NationalNumber] = "XXX"

    Although my grouping will need to run on all my dataset, I do not see how I can integrate TOPN in it, to only return the last value of CareProfile for every person

    Thanks for any insights

    Tuesday, May 20, 2014 6:55 AM
  • You can execute a TOPN( 1, ...) for each person - use GENERATE to iterate the persons. Something like
    GENERATE ( Resident, TOPN ( 1, ... ) )

    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Tuesday, May 20, 2014 7:50 AM
  • Marco I have marked your earliest post as the answer because it actually answers my initial question, then I created a new posted with my new problem to avoid confusion :)

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cbc65c4e-5d67-4418-989e-f8a3e86337c7/dax-query-how-to-retrieve-the-last-member-in-a-dimension?forum=sqlanalysisservices

    Tuesday, May 20, 2014 2:43 PM