SSAS Standard Edition - Getting Last Value from Fact Table at Transaction Grain

Question

Background

Hi! I'm trying to work with a non-additive measure in SSAS 2008R2 Standard Edition knowing that semi-/non-additive measures don't work right out of the box in this edition.

I'm trying to calculate a last price invoiced from a fact table that looks something like this (sorry if this isn't the right way to create tables in these posts!):

| Invoice No | Invoice Line | DateId   | ProductId | Unit Price |
|------------|--------------|----------|-----------|------------|
| 1          | 1            | 20160901 | 2         | 10         |
| 4          | 2            | 20160901 | 2         | 10         |

Unit Price is defined as a Last Child measure. Invoice No & Invoice Line are used to generate a Degenerate Dimension. Following Chris Webb's blog post, Last Ever Non Empty – a new, fast MDX approach, I can grab the last unit price across dates with no problem.

Problem

However, when one item has two records in the fact table for the same day--like the table above--the Unit Prices of each record still get aggregated when browsing the cube using the Date Dimension. Rather than show $10 on 2016-09-01, my cube is returning $10 + $10 = $20.

Solutions?

This post describes what sounds like the same problem and solves it by adding Hours / Seconds / Milliseconds to the Date dimension. Are there any other ways to handle this situation without modifying the Date dimension?

I'm a complete novice with MDX, but my hope is that it can help. Is there an MDX calculation that can somehow retrieve the unit price from the current Date member that has a [Invoice No] record rather than perform an aggregation at the [ALL] level?

I could change the grain of my fact table to be sales by day and handle all the calculations in TSQL where I feel much more comfortable, but that goes counter to what I've read from Ralph Kimball about Dimensional Modeling and trying to keep the fact table at the lowest possible grain.

I could also handle this in the underlying SQL tables during the ETL process using ROW_NUMBER() and then creating a MIN or MAX measure in SSAS.

Finally, I could calculate the Unit Price as an average by dividing Extended Price / Quantity, but it would be great to retrieve the actual price of the last invoice on a day if possible

Thank you!

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-04-21T12:49:01

If you want to get last ever price:

Select 
Tail(
    NonEmpty(
        {
            [DateId].[DateId].[DateId].Members *
            [Invoice Line].[Invoice Line].[Invoice Line].Members *
            [Invoice No ].[Invoice No ].[Invoice No ].Members *
            [ProductId].[ProductId].[ProductId].Members *
            [Unit Price].[Unit Price].[Unit Price].Members
        },
        [Measures].[Invoice Count]  
    ),
    1
) on 1,
[Measures].[Invoice Count] on 0
From [YourCube]

If you need the same per customer, you may want to use the Generate() function:

Select 
Generate(
    [CustomerId].[CustomerId].[CustomerId].Members,
    Tail(
        NonEmpty(
            {
                [CustomerId].[CustomerId].CurrentMember *
                [DateId].[DateId].[DateId].Members *
                [Invoice Line].[Invoice Line].[Invoice Line].Members *
                [Invoice No ].[Invoice No ].[Invoice No ].Members *
                [ProductId].[ProductId].[ProductId].Members *
                [Unit Price].[Unit Price].[Unit Price].Members
            },
            [Measures].[Invoice Count]
        ),
        1
    )
) on 1,
[Measures].[Invoice Count] on 0
From [YourCube]

In sake of performance I'd recommend to add a measure YYYYMMDDPP-style with the MAX aggregation. Where YYYYMMDD is a date code and PP is a price value. For example:

| Invoice No | Invoice Line | DateId   | ProductId | Unit Price | DatePrice  |
|------------|--------------|----------|-----------|------------|------------| 
| 1          | 1            | 20160901 | 2         | 10         | 2016090110 | 
| 4          | 2            | 20160901 | 2         | 10         | 2016090110 |

And it will return a max value. In your case it's 2016090110. In order to get only price you'll need an extra calculated measure:

With

Member [Measures].[Last Price] as
Right([Measures].[MaxDatePrice],2)

Select [Measures].[Last Price] on 0
From [BI Fake]

You may want to expend PP size. It depends on your Price range.

Edit: Since your static size of price is 7, you need the following steps:

Create a DatePrice field in YYYYMMDDPPPPPPP format:

 [PriceDate] =
      year([DateId]) * 10000000000 +
      month([DateId]) * 100000000 +
      day([DateId]) * 10000000
      + [Unit Price]

Create a calculated measure:

Cint(Right([Measures].[MaxDatePrice],7))

Cint will convert 0000010 into 10.

SSAS Standard Edition - Getting Last Value from Fact Table at Transaction Grain

Background

Problem

Solutions?

1 Answers