none
MDX /use of exists function for Multiple Measuregroup or equivalent.

    Question

  • I would like to use exists function or any similar to use two or more measure group with different dimenstionalities

    Currently i am using a function for a single measuregroup which works fine, but now the requirment is change to use two measuregroup instead

    exists([color].[nfl color].[color]*[location].[location].members, ,"Sales")

    but my requirment is i need to find the following for both "sales" and "inventory" measure group  it is more like  if it does not exists in sales pick the record from  inventory measure group .

    i am hoping for something like


    exists([color].[nfl color].[color]*[location].[location].members, ,"Sales" or "Inventory")

    or something like  exists([color].[nfl color].[color]*[location].[location].members, ,{"Sales" * "Inventory"})


    apparently this work but the performance is way slow ,

    exists([color].[nfl color].[color] ,{Measuregroupmeasures.("Sales") * MeasuregroupMeasures("inventory")} )

    *

    exists([location].[location].[members] ,{Measuregroupmeasures.("Sales") * MeasuregroupMeasures("inventory")} )

    please advise how could i use make a better way of using two measuregroups


    any help in this matter will be appreciated

    Tuesday, April 07, 2009 3:53 AM

Answers

  • Hi,

    can you be precise here, is the requirement to use logical AND function, logical OR function or sequence of testing (like Coalesce() function)? You made several contradictory requests and each implementation will give you different results.

    Here are some of possible combinations:



    NonEmpty( [color].[nfl color].[color] * [location].[location].members,
    { Measure.CountOfRecordsInSales, Measure.CountOfRecordsInInventory } ) Exists( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), , "Inventory" ) Exists( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory"), , "Sales" ) NonEmpty( NonEmpty( [color].[nfl color].[color] * [location].[location].members, { Measure.CountOfRecordsInSales } ), { Measure.CountOfRecordsInInventory } ) NonEmpty( NonEmpty( [color].[nfl color].[color] * [location].[location].members, { Measure.CountOfRecordsInInventory } ), { Measure.CountOfRecordsInSales } ) Intersect( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") ) Union( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") )


    You can try them and pick the one that suits you, or you can be precise about what exactly do you need (see my first paragraph).

    Hope it helps or gives you some idea,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Tuesday, April 07, 2009 10:10 AM
    Answerer
  • Hold on,

    there are several things we should discuss here. First, you query, as a basis for everything.

    Whatever measures you put on columns, if the whole row is empty, that row will drop from results if you use NON EMPTY on rows (which you do). However, if one of the hierarchy on rows is not present in both measure groups and default option for ignoring unrelated dimensions is active, then you'd get results for all rows. So, is this the case or not? Are both of the dimensions on rows present (linked) in both measure groups you use on columns? If not, there's an option for that on measure groups and it says IgnoreUnrelatedDimensions, which can be set to False.

    Next, if they are linked to both measure groups, using NON EMPTY should filter empty rows and leave you only rows with data (in whichever measure, from whichever measure group on columns). That, however, may not be so if you made additional calculations. For example, your first and second calc measures are never null. Which means you'll get results for them and those rows will appear in final result. Is that what's bothering you? That's why you're trying to optimize it using Exists() on measure group or similar?

    One of the ways to handle this is to introduce a count measure per each measure group. And then use NonEmpty() on that measures. The queries will look simple. From what you wrote I can see you need OR logic on measure groups. NonEmpty() using both count measures in second set should give you exactly that. See my example above, the first one.

    I don't see why Union() wouldn't work though. It should give you your 153 rows. Sets inside are of the same dimensionality. Have you tried it? Using that you wouldn't need to introduce new count measures, so you can test it right away.

    We can continue,

    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by ns100 Tuesday, April 07, 2009 4:32 PM
    Tuesday, April 07, 2009 1:03 PM
    Answerer
  • Union seem to have resolved the issue.

    Thank you very much

    Union( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"),
           Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") )
    • Marked as answer by ns100 Tuesday, April 07, 2009 4:32 PM
    Tuesday, April 07, 2009 4:32 PM

All replies

  • Hi,

    can you be precise here, is the requirement to use logical AND function, logical OR function or sequence of testing (like Coalesce() function)? You made several contradictory requests and each implementation will give you different results.

    Here are some of possible combinations:



    NonEmpty( [color].[nfl color].[color] * [location].[location].members,
    { Measure.CountOfRecordsInSales, Measure.CountOfRecordsInInventory } ) Exists( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), , "Inventory" ) Exists( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory"), , "Sales" ) NonEmpty( NonEmpty( [color].[nfl color].[color] * [location].[location].members, { Measure.CountOfRecordsInSales } ), { Measure.CountOfRecordsInInventory } ) NonEmpty( NonEmpty( [color].[nfl color].[color] * [location].[location].members, { Measure.CountOfRecordsInInventory } ), { Measure.CountOfRecordsInSales } ) Intersect( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") ) Union( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") )


    You can try them and pick the one that suits you, or you can be precise about what exactly do you need (see my first paragraph).

    Hope it helps or gives you some idea,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Tuesday, April 07, 2009 10:10 AM
    Answerer
  • Hi Tomislav

    This is basicaly what i am trying to do.  In the query i am trying to query few measures from both measure groups on coloumns.

    However i want to  see the columns data from either or i,e if the data from inventory is present then use from inventory, if not use the dat from sales.

    the current query which i use from one measure group works fine but does when i try to add other measure group

    it is basically

    color blue for location 101 , look for the data in measure group Inventory for what ever available coloumns on 0 , if the data is not available in inventory then look for the data in measuregroup sales  


    The query i am using currently works for one measure group but does not work with both measure group the error which i basically get is measure hierarchy is being used.

    the query which i am using for measure group is

    WITH

     

     

    MEMBER

     

     

    [Measures].[ColorKey] AS [Color].[Color].CURRENTMEMBER.MemberValue

    --MEMBER [Measures].[ColorKey] AS [Color].[Color].CURRENTMEMBER.Properties('Member_Value')

    -- use Fields!ColorKey.Value for Color nbr, and Fields!Color.Value for color description

    MEMBER

     

     

    [Measures].[ReplColor] AS

     

     

    Count(Exists([Color].[Color].CurrentMember, [Replenishment].[Replenishment].&[Y], 'Sales'))

    SELECT

     

     

    --NON EMPTY

    {

    [Measures].[ColorKey]

    ,[Measures].[ReplColor]

    --------------------

    ,[Measures].[WTD Net Sales Units]

    ,[Measures].[WTD Sales Units Sell Off %]

    ,[Measures].[WTD Net Sales Dollars]

    ,[Measures].[WTD Sales $ Sell Off %]

    ,[Measures].[WTD Average Unit Retail $]

    ,[Measures].[WTD Receipt Units]

    ,[Measures].[WTD Receipt Dollars]

    --------------------

    --5WK

    ,[Measures].[5WK Net Sales Units]

    ,[Measures].[5WK Net Sales Dollars]

    ,[Measures].[5WK Sales Units Sell Off %]

    ,[Measures].[5WK Average Unit Retail $]

    --------------------

    ,[Measures].[PTD Net Sales Units]

    ,[Measures].[PTD Sales Units Sell Off %]

    ,[Measures].[PTD Net Sales Dollars]

    ,[Measures].[PTD Sales $ Sell Off %]

    ,[Measures].[PTD Average Unit Retail $]

    ,[Measures].[PTD Receipt Units]

    ,[Measures].[PTD Receipt Dollars]

    ,[Measures].[PTD Total MD Rate]

    ,[Measures].[PTD Perm MD Rate]

    ,[Measures].[PTD POS MD Rate]

    ,[Measures].[PTD Net Total MD $]

    ,[Measures].[PTD Net POS MD Dollars]

    ,[Measures].[PTD Net Perm MD Dollars]

    --------------------

    ,[Measures].[STD Net Sales Units]

    ,[Measures].[STD Sales Units Sell Off %]

    ,[Measures].[STD Net Sales Dollars]

    ,[Measures].[STD Sales $ Sell Off %]

    ,[Measures].[STD Average Unit Retail $]

    ,[Measures].[STD Receipt Units]

    ,[Measures].[STD Receipt Dollars]

    ,[Measures].[STD Total MD Rate]

    ,[Measures].[STD Perm MD Rate]

    ,[Measures].[STD POS MD Rate]

    ,[Measures].[STD Net Total MD $]

    ,[Measures].[STD Net POS MD Dollars]

    ,[Measures].[STD Net Perm MD Dollars]

    --------------------

    ,[Measures].[YTD Net Sales Units]

    ,[Measures].[YTD Sales Units Sell Off %]

    ,[Measures].[YTD Net Sales Dollars]

    ,[Measures].[YTD Sales $ Sell Off %]

    ,[Measures].[YTD Average Unit Retail $]

    ,[Measures].[YTD Receipt Units]

    ,[Measures].[YTD Receipt Dollars]

    ,[Measures].[YTD Total MD Rate]

    ,[Measures].[YTD Perm MD Rate]

    ,[Measures].[YTD POS MD Rate]

    ,[Measures].[YTD Net Total MD $]

    ,[Measures].[YTD Net POS MD Dollars]

    ,[Measures].[YTD Net Perm MD Dollars]

    -------------------

    ,[Measures].[OH Units]

    ,[Measures].[OH Dollars]

    ,[Measures].[On Order Units]

    ,[Measures].[On Order Dollars]

    ,[Measures].[OH+OO Units]

    ,[Measures].[OH+OO Dollars]

    }

     

    ON COLUMNS

    ,

     

    NON EMPTY

    {

     

     

    Exists( [Color].[NRF Color Group].[Color] * [Location].[Location].children, , "Sales")

    }

    ON

     

     

    ROWS

    FROM

     

     

    (SELECT [Location].[Location].[All] ON 0 FROM [Item])

    where

     

     

    (

    {[Product].[Markstyle].&[12]&[444]&[101]&[13534]}

    ,[Vendor].[Vendor].&[444]&[101]

    ,[Replenishment].[Replenishment].[All]

    ,[Markdown Status].[Markdown Status].&[10]

    ,[Location].[Loc Category].&[1]

    ,[Location].[Plan Flg].&[Y]

    )



    THank you


    Tuesday, April 07, 2009 11:06 AM
  • i tried few queries

    Union and Intersect would not work as they do not have common number of coloumn  in between both measure group , so the results displays are 0.


    Both of this queries display same number of rows , but i am not sure because it is just trying to find the common records in between both measures.  which is exactly to opposite i am looking for.
     I am looking for records that exists in measuregroup (sales) and NOT IN   Measuregroup (Inventory)

    Exists( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"), ,
            "Inventory" )

    Exists( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory"), ,
            "Sales" )

    Because sales has 149 records and inventory and 89 , the result sets are 84 records.

    I would proably expect more then 149 records , because sales has 149 records and if the records in sales but are in inventory are in count of 4 which. so the result set is what exactly should be close to 153. which are exactly of  what i am getting are 89 records.

    the other queries of count i could run because  i do not have count measure in my measure group. so i may need to work towards adding it.

    • Edited by ns100 Tuesday, April 07, 2009 12:13 PM edited for clarification
    Tuesday, April 07, 2009 11:50 AM
  • Hold on,

    there are several things we should discuss here. First, you query, as a basis for everything.

    Whatever measures you put on columns, if the whole row is empty, that row will drop from results if you use NON EMPTY on rows (which you do). However, if one of the hierarchy on rows is not present in both measure groups and default option for ignoring unrelated dimensions is active, then you'd get results for all rows. So, is this the case or not? Are both of the dimensions on rows present (linked) in both measure groups you use on columns? If not, there's an option for that on measure groups and it says IgnoreUnrelatedDimensions, which can be set to False.

    Next, if they are linked to both measure groups, using NON EMPTY should filter empty rows and leave you only rows with data (in whichever measure, from whichever measure group on columns). That, however, may not be so if you made additional calculations. For example, your first and second calc measures are never null. Which means you'll get results for them and those rows will appear in final result. Is that what's bothering you? That's why you're trying to optimize it using Exists() on measure group or similar?

    One of the ways to handle this is to introduce a count measure per each measure group. And then use NonEmpty() on that measures. The queries will look simple. From what you wrote I can see you need OR logic on measure groups. NonEmpty() using both count measures in second set should give you exactly that. See my example above, the first one.

    I don't see why Union() wouldn't work though. It should give you your 153 rows. Sets inside are of the same dimensionality. Have you tried it? Using that you wouldn't need to introduce new count measures, so you can test it right away.

    We can continue,

    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by ns100 Tuesday, April 07, 2009 4:32 PM
    Tuesday, April 07, 2009 1:03 PM
    Answerer

  • That is true that  both of the dimensions on rows present are (linked) with both measure groups ,

    Not necessary that all the colums in the query would have link with Color and Location. In Some case the Data is only relavent to location and not color and those records are required to be displayed in report. That is absolutely not a concern of display.

    In some case you have records for both sales and inventory which has no color but it does have locations , which should be displayed.

    The reason i was using Exists function was too filter out the dimension crossjoin for the data from measuregroup.


    I have used both of Union and Non empty function which you suggested , but both of patterns does not display any records. infact i realized i had the count of inventory measure and count of sales measure. 

    The basic sql would Union command , but apparently in Mdx it does not show any results. 




    Thank you



     

    WITH

    MEMBER

    [Measures].[ColorKey] AS [Color].[Color].CURRENTMEMBER.MemberValue

    --MEMBER [Measures].[ColorKey] AS [Color].[Color].CURRENTMEMBER.Properties('Member_Value')

    -- use Fields!ColorKey.Value for Color nbr, and Fields!Color.Value for color description

    MEMBER

    [Measures].[ReplColor] AS

     

    Count(Exists([Color].[Color].CurrentMember, [Replenishment].[Replenishment].&[Y], 'Sales'))

    SELECT

    --NON EMPTY

    {

    [Measures].[ColorKey]

    ,[Measures].[ReplColor]

    --------------------

    ,[Measures].[WTD Net Sales Units]

    ,[Measures].[WTD Sales Units Sell Off %]

    ,[Measures].[WTD Net Sales Dollars]

    ,[Measures].[WTD Sales $ Sell Off %]

    ,[Measures].[WTD Average Unit Retail $]

    ,[Measures].[WTD Receipt Units]

    ,[Measures].[WTD Receipt Dollars]

    --------------------

    --5WK

    ,[Measures].[5WK Net Sales Units]

    ,[Measures].[5WK Net Sales Dollars]

    ,[Measures].[5WK Sales Units Sell Off %]

    ,[Measures].[5WK Average Unit Retail $]

    --------------------

    ,[Measures].[PTD Net Sales Units]

    ,[Measures].[PTD Sales Units Sell Off %]

    ,[Measures].[PTD Net Sales Dollars]

    ,[Measures].[PTD Sales $ Sell Off %]

    ,[Measures].[PTD Average Unit Retail $]

    ,[Measures].[PTD Receipt Units]

    ,[Measures].[PTD Receipt Dollars]

    ,[Measures].[PTD Total MD Rate]

    ,[Measures].[PTD Perm MD Rate]

    ,[Measures].[PTD POS MD Rate]

    ,[Measures].[PTD Net Total MD $]

    ,[Measures].[PTD Net POS MD Dollars]

    ,[Measures].[PTD Net Perm MD Dollars]

    --------------------

    ,[Measures].[STD Net Sales Units]

    ,[Measures].[STD Sales Units Sell Off %]

    ,[Measures].[STD Net Sales Dollars]

    ,[Measures].[STD Sales $ Sell Off %]

    ,[Measures].[STD Average Unit Retail $]

    ,[Measures].[STD Receipt Units]

    ,[Measures].[STD Receipt Dollars]

    ,[Measures].[STD Total MD Rate]

    ,[Measures].[STD Perm MD Rate]

    ,[Measures].[STD POS MD Rate]

    ,[Measures].[STD Net Total MD $]

    ,[Measures].[STD Net POS MD Dollars]

    ,[Measures].[STD Net Perm MD Dollars]

    --------------------

    ,[Measures].[YTD Net Sales Units]

    ,[Measures].[YTD Sales Units Sell Off %]

    ,[Measures].[YTD Net Sales Dollars]

    ,[Measures].[YTD Sales $ Sell Off %]

    ,[Measures].[YTD Average Unit Retail $]

    ,[Measures].[YTD Receipt Units]

    ,[Measures].[YTD Receipt Dollars]

    ,[Measures].[YTD Total MD Rate]

    ,[Measures].[YTD Perm MD Rate]

    ,[Measures].[YTD POS MD Rate]

    ,[Measures].[YTD Net Total MD $]

    ,[Measures].[YTD Net POS MD Dollars]

    ,[Measures].[YTD Net Perm MD Dollars]

    -------------------

    ,[Measures].[OH Units]

    ,[Measures].[OH Dollars]

    ,[Measures].[On Order Units]

    ,[Measures].[On Order Dollars]

    ,[Measures].[OH+OO Units]

    ,[Measures].[OH+OO Dollars]

    }

    ON COLUMNS

    ,

    NON EMPTY

    {

    NonEmpty

    ( [color].[nfl color].[color] * [location].[location].members,

    { [Measures].[Inventory Count], [Measures].[Sales Count] } )

    --Exists(Exists( [Color].[NRF Color Group].[Color] * [Location].[Location].children, , "inventory"),,"sales")

     

    -- Exists( [Color].[NRF Color Group].[Color] * [Location].[Location].children, , "sales")

     

    -- Exists( [Color].[NRF Color Group].[Color] * [Location].[Location].children, , "inventory")

     

     

    --exists([Color].[NRF Color Group].[Color] * [Location].[Location].children)

     

     

    --INTERSECT( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"),

     

    -- Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") )

     

    --Union( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"),

     

    --Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") )

     

     

     

    }

    ON

    ROWS

    FROM

    (SELECT [Location].[Location].[All] ON 0 FROM [Item])

    where

    (

    {[Product].[Markstyle].&[12]&[444]&[101]&[13534]}

    ,[Vendor].[Vendor].&[444]&[101]

    ,[Replenishment].[Replenishment].[All]

    ,[Markdown Status].[Markdown Status].&[10]

    ,[Location].[Loc Category].&[1]

    ,[Location].[Plan Flg].&[Y]

    )

     













     

    Tuesday, April 07, 2009 2:20 PM
  • I didn't quite follow your explaination why do you do it in the first place using Exists function: "The reason i was using Exists function was too filter out the dimension crossjoin for the data from measuregroup." Can you elaborate a bit more?

    I made a similar example on Adventure Works 2008 database. It works in my case. Normally, you would get Cartesian product on rows (because of calc measures 1 and 2 never being null). However, if you apply NonEmpty() on it, the row count reduces from 61 to 55, eliminating all empty rows, empty for both measure group measures, and we have the same number of rows as we would have without the first two measures and using NON EMPTY. Since they are never null, we had to take care about getting the same number of rows on axis, using NonEmpty.

     

    WITH
    MEMBER [Measures].[ColorKey] AS
        [Product].[Color].CURRENTMEMBER.MemberValue
    MEMBER [Measures].[ReplColor] AS 
        Count(Exists([Product].[Color].CurrentMember,
                     [Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop],
                     'Reseller Orders')) 
    SELECT
    {
        [Measures].[ColorKey],
        [Measures].[ReplColor],
        MEASUREGROUPMEASURES( 'Internet Orders' ) +
        MEASUREGROUPMEASURES( 'Reseller Orders' )
    } on 0,
    NON EMPTY
    {
        NonEmpty(
                 { [Product].[Color].[Color].MEMBERS * 
                   [Sales Territory].[Sales Territory].[Country].MEMBERS } ,
                 MEASUREGROUPMEASURES( 'Internet Orders' ) +
                 MEASUREGROUPMEASURES( 'Reseller Orders' )
                ) 
    } on 1
    FROM
        ( SELECT [Sales Territory].[Sales Territory].[All Sales Territories] ON 0
          FROM [Adventure Works]
        )

     

     

     

    Another approach is to act in measures themselves. If we add some more logic to them, we will make them null for certain scenarios. That way we don't have to take care about results on axis. See here.



     

    WITH
    MEMBER [Measures].[ColorKey] AS
        iif(
            NonEmpty(
                     ([Product].[Color].CurrentMember,
                      [Sales Territory].[Sales Territory].CurrentMember),
                     MEASUREGROUPMEASURES( 'Internet Orders' ) +
                     MEASUREGROUPMEASURES( 'Reseller Orders' )).Count = 0,
            null,
            [Product].[Color].CURRENTMEMBER.MemberValue
           )
    MEMBER [Measures].[ReplColor] AS 
        iif(
            NonEmpty(
                     ([Product].[Color].CurrentMember,
                      [Sales Territory].[Sales Territory].CurrentMember),
                     MEASUREGROUPMEASURES( 'Internet Orders' ) +
                     MEASUREGROUPMEASURES( 'Reseller Orders' )).Count = 0,
            null,
            Count(Exists([Product].[Color].CurrentMember,
                         [Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop],
                         'Reseller Orders')) 
           ) 
    SELECT
    {
        [Measures].[ColorKey],
        [Measures].[ReplColor],
        MEASUREGROUPMEASURES( 'Internet Orders' ) +
        MEASUREGROUPMEASURES( 'Reseller Orders' )
    } on 0,
    NON EMPTY
    {
        [Product].[Color].[Color].MEMBERS * 
        [Sales Territory].[Sales Territory].[Country].MEMBERS
    } on 1
    FROM
        ( SELECT [Sales Territory].[Sales Territory].[All Sales Territories] ON 0
          FROM [Adventure Works]
        )

     

     

     

    This MDX also give 55 rows. Calc measure gets result only if the row itself has result. How about it?

    Am I going the right way in this matter or not? About the goal.

    Regards,



    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Tuesday, April 07, 2009 3:32 PM
    Answerer
  • Union seem to have resolved the issue.

    Thank you very much

    Union( Exists( [color].[nfl color].[color] * [location].[location].members, ,"Sales"),
           Exists( [color].[nfl color].[color] * [location].[location].members, ,"Inventory") )
    • Marked as answer by ns100 Tuesday, April 07, 2009 4:32 PM
    Tuesday, April 07, 2009 4:32 PM