Hirereachy is used more than Once in the cross Join Function

Question

I have MDX query which throws an error - "The portalID hierarchy is used more than once in the cross Join Function"

The query fails at the where clause

WHERE 
            (-{[SG Location].[PortalID].[30],
            [SG Location].[PortalID].[46]},
            [SG Location].[PortalID].[4],
            [SG Program].[Campaign].&[*Cymbalta_Lilly (111)], 
            [Prints], 
            [SG Location].[IsTest].[0]
            )

In the where clause above following condition (Exclude few portals) is appended dynamically

-{[SG Location].[PortalID].[30],
 [SG Location].[PortalID].[46]}

Please help! I am new to MDX queries. Any useful link related to MDX queries - about where clause specifically - is really appreciated.

Below is the complete MDX query

            WITH 
            MEMBER [SG Date].[Day].[TotPrintCount] AS 
            SUM(
                    {
                        [SG Date].[Day].&[2018-06-15], 
                        [SG Date].[Day].&[2018-06-16], 
                        [SG Date].[Day].&[2018-06-17], 
                        [SG Date].[Day].&[2018-06-18]
                    },
                        [Measures].[Prints]
                )    
            MEMBER [SG Date].[Day].[TotCustRev] AS 
            SUM(
                    {
                        [SG Date].[Day].&[2018-06-15], 
                        [SG Date].[Day].&[2018-06-16], 
                        [SG Date].[Day].&[2018-06-17], 
                        [SG Date].[Day].&[2018-06-18]
                    },
                        [Measures].[CustRev]
                )    
            MEMBER [SG Date].[Day].[TotLDMRev] AS 
            SUM(
                    {
                        [SG Date].[Day].&[2018-06-15], 
                        [SG Date].[Day].&[2018-06-16], 
                        [SG Date].[Day].&[2018-06-17], 
                        [SG Date].[Day].&[2018-06-18]
                    },
                        [Measures].[LDMRev]
                )    
            MEMBER [Measures].[RevRatio] AS [Measures].[LDMRev] / [Measures].[Transactions] * 1000    
            MEMBER [Measures].[MatchRatio] AS SUM([Measures].[PaidPrints]) / SUM([Measures].[Transactions]) * 100 
            SELECT { 
                    [SG Date].[Day].[TotPrintCount], 
                    [SG Date].[Day].&[2018-06-15], 
                    [SG Date].[Day].&[2018-06-16], 
                    [SG Date].[Day].&[2018-06-17], 
                    [SG Date].[Day].&[2018-06-18], 
                    [SG Date].[Day].[TotCustRev], 
                    [SG Date].[Day].[TotLDMRev]} 
            ON COLUMNS, NON EMPTY 
            { 
                { [SG Location].[Hierarchy].[Portal].ALLMEMBERS } * { [SG Program].[Hierarchy].ALLMEMBERS } 
            } ON ROWS FROM [ScriptGuide] 

            WHERE 
                (-{[SG Location].[PortalID].[30],
                [SG Location].[PortalID].[46]},
                [SG Location].[PortalID].[4],
                [SG Program].[Campaign].&[*Cymbalta_Lilly (111)], 
                [Prints], 
                [SG Location].[IsTest].[0]
                )

CodeFader CodeFader · Accepted Answer · 2018-11-20T17:38:34

What you SHOULD do is not use the hierarchy more than once. But from your statement, that it's "added dynamically" I'm assuming it's being done by a different module and it's a problem for the separate modules to coordinate their hierarchy filters. In that case - create a sub-query like so:

WITH 
    (...)
SELECT 
    (...)
FROM (
    SELECT (-{[SG Location].[PortalID].[30], [SG Location].[PortalID].[46]}) on 0 FROM [ScriptGuide] 
)
WHERE 
(
    [SG Location].[PortalID].[4],
    [SG Program].[Campaign].&[*Cymbalta_Lilly (111)], 
    [Prints], 
    [SG Location].[IsTest].[0]
)

What we're doing here is relegating the default PortalID exclusions to a subquery under FROM. That query gets executed first and thus the main query does not see it as conflicting.

If it's a global (or global-ish at least) PortalID removal filter - it makes a lot of sense to push it out into the subquery.

Hirereachy is used more than Once in the cross Join Function

1 Answers