Search code examples
sql-serverssasmdx

SSAS Measure average related to range values

I have Sales data provided weekly and Lookup data provided quarterly. In the SSAS data cube I have pre-calculated average of sales data for each period of time and what I need to do is to get related record from LookupTable for next calculations, where: LookupTable.Min < Sales Average < LookupTable.Max

Example:

Sales = 297 + 33 + 311 = 641

SalesAverage = 213.66

LookupRecordShrinkageIndicator = Min < SalesAverage < Max = 0 < 213.66 < 9000 = 0.007

CREATE TABLE dbo.SalesData
(
    Id int,
    Sales decimal(18, 2)    )


CREATE TABLE dbo.LookupTable
(
    Id int,
    Min int,
    Max int,
    Shrinkage decimal(10, 5),
    Wages decimal(10, 5),
    Waste decimal(10, 5)
 )

INSERT [dbo].[SalesData] ([Id], [Sales]) VALUES (1, 297)
INSERT [dbo].[SalesData] ([Id], [Sales]) VALUES (2, 33)
INSERT [dbo].[SalesData] ([Id], [Sales]) VALUES (3, 311)

INSERT [dbo].[LookupTable] ([Id], [Min], [Max], [Shrinkage], [Wages], [Waste]) VALUES (1, 0, 9000, 0.00700, 0.12700, 0.00300)
INSERT [dbo].[LookupTable] ([Id], [Min], [Max], [Shrinkage], [Wages], [Waste]) VALUES (2, 9000, 9250, 0.00700, 0.12700, 0.00300)
INSERT [dbo].[LookupTable] ([Id], [Min], [Max], [Shrinkage], [Wages], [Waste]) VALUES (3, 9250, 9500, 0.00700, 0.12300, 0.00300)

I need to create calculated member based on sales average which contains indicators from lookup table for next calculations.

Solution

  • To solve this issue I had to use my LookupTable as dimension and as measures, let's see how I did this.

    1. Create dimension based on LookupTable:

    2. Add Lookup measures do the cube and add Lookup dimension to the cube as well. Cube design view

    3. Create Fact relationship between Lookup dimension and Lookup measures group

    That's all:

    Let's see mdx example:

    SELECT 
{
    FILTER([Lookup Table].[Id].AllMembers ,  [Measures].[Min] <= 213 AND [Measures].[Max] > 213 )
}
ON COLUMNS,
{
    [Measures].[Shrinkage - Lookup Table], [Measures].[Wages - Lookup Table], [Measures].[Waste - Lookup Table]

} ON ROWS
FROM
[MyCube]

    And result:

    MDX Query result

    I hope this example will be useful