Role Playing Dimensions - DAX vs MDX (Part 1)

A couple of weeks ago I was doing some work on an internal reporting cube. One of the measures required represents an ‘order backlog’, that is orders that have been received but haven’t yet been provisioned in our systems.

The Problem

The fact table looks something like this:

A row will appear in the fact table after the order has been closed, with the provisioned date being set to NULL until it has been provisioned. The provisioned date is updated once the order has progressed in the system.

There are a few measures that I want to report on:

• Won (£) - very simple SUM of the Amount column
• Provisioned (£) - the amount that was provisioned in a given month
• Accumulated Backlog (£) - The total amount, for each month, of orders not yet provisioned

For example, in January 2013:

• Won (£): 550
• Provisioned (£): 150
• Backlog Change (£): 400 (Won - Provisioned)
• Accumulated Backlog (£): 1,250 (400 + 850 from December)

The problem comes when wanting to show the Accumulated Backlog amount by the ClosedDate.

The Solution DAX and PowerPivot

Accomplishing the end result with PowerPivot turned out to be very straightforward and in my opinion much easier to implement than the MDX solution.

The first thing I did was load my tables into PowerPivot, create the relationships between them and I have also created a Calendar hierarchy in my Date table.

Take note that there are two relationships between the Sales table and the Date table, the active relationship is on the ClosedDate column in Sales, inactive relationship is on ProvisionedDate.

The reason for this is because I want all my reports to be based on the ClosedDate of a sales order.

Calculating Won (£) is very simple, as it is based on the ClosedDate all we need is a simple SUM:

Won (£) := SUM(Sales[Amount])

To calculate Provisioned (£) involves using the CALCULATE and USERELATIONSHIP functions, this formula below, tells PowerPivot to calculate the [Won (£)] field using the relationship between Sales and Date based on the ProvisionedDate

Provisioned (£) := CALCULATE ( [Won (£)], USERELATIONSHIP ( ‘Date’[Date], Sales[ProvisionedDate] ), NOT ( ISBLANK ( Sales[ProvisionedDate] ) ) )

Instead of using [Won (£)] in the above formula I could have very easily used SUM(Sales[Amount]), but I thought that using [Won (£)] reads better, because the provisioned amount is the amount of “Won” orders that had been provisioned.

To calculate Backlog Change (£) is then simply:

Backlog Change (£):=[Won (£)]-[Provisioned (£)]

And finally the Accumulated Backlog (£), this formula will calculate the running SUM of the [Backlog Change (£)] field for all dates, in the given context.

Accumulated Backlog (£) := CALCULATE ( [Backlog Change (£)], FILTER ( ALL ( ‘Date’ ), ‘Date’[Date] <= MAX ( ‘Date’[Date] ) ) )

The resulting pivot table is exactly what I was expecting

With the basics implemented, I could go on to add some other interesting calculations, for example: age of oldest unprovisioned order or average time to provision order.

In the next post I will show how I implemented the above using SQL Server Analysis Services and MDX.