SQL Server Performance

MDX Question

Discussion in 'Analysis Services/Data Warehousing' started by jwelcome, Jun 11, 2004.

  1. jwelcome New Member

    Hello,

    I have three dimension:

    Date : Year/Month/Week Ending
    Location: Region/District/Location
    Item: Group/Division/Style/Item


    For a given Week Ending, I need to be able to calculate how many items are at a specific location vs the total items in all locations (Location OH / Total OH).

    For instance: I have 10 widgets in Location 1; 20 widgets in Location 2; 20 widgets in Location 3. I want to be able to say as a calculated member that 20% of widgets are Location 1; 40% are in Location 2 and 40% are in Location 3. In order to do this, I need the ability to sum all item numbers across the date.

    Any ideas how to get the total count of items across all locations; w/o maintaining the information in the fact tables? Basically, does someone know the MDX syntax for this calculated member?

    Below is a stab I've made. I know the part that returns the Comp Location works correctly but I am having troubles spanning it across all Locations for that day.

    ----------------------------------------------------------------
    MEMBER [Measures].[Week Total] AS
    'SUM([Item].CurrentMember, ([Measures].[Comp Location Qty], OpeningPeriod([Week Ending], [Date].CurrentMember))'
    ----------------------------------------------------------------

    Thanks in advance, Jeff
  2. Raulie New Member

    Try this

    WITH
    MEMBER [Measures].[Week Total] AS
    'SUM([Item].CurrentMember, ([Measures].[Comp Location Qty], OpeningPeriod([Week Ending], [Date].CurrentMember))'
    SELECT [LOCATION].[LOCATION].MEMBERS ON COLUMNS,
    [MEASRUES].MEMBERS ON ROWS
    FROM MY_CUBE

Share This Page