Having trouble creating a model for a time boxed KPI

Question

So I am a bit of a noob when it comes to dimensional modelleling so hopefully there is a straightforward way to accomplish what I trying to do.

The situation is that we have certain accounts that have contractual agreements to purchase a certain amount over a given time frame. We've got a sales cube and what is desired is a report that shows where each account is in their overall agreement (i.e. 34% of total agreement met) and where they are on a period to period basis (i.e. if they agreed to buy 1000 units over the next year how are they tracking during each sales period).

Agreements have a start period, a duration and a a total unit count commitment. I've got my model to the point where I can see sales as well as the commitment target for the year in which it starts but now I am stuck on how to show performance against target period by period as well as over the duration of the agreement. Agreement details are not in the sales cube but are linked Excel tables.

My first though is to create a table for the combinatorial matrix off account, period, and period target. I'm pretty sure that would work but everytime a new account or agreeemnt is added that is a fair amount of data management to get right.

Is their a better way to accomplish this sort of thing with DAX or am I modelling it incorrectly?

Any help for the noob is appreciated!

Answer 1

The simplest solution would be to take the annual agreement amount and allocate it across periods like you mentioned.  Here's an example to demonstrate:

In the Agreement fact, my example allocates to the month level.  With that, you can use the first of the month as the date key for each month's allocation of the agreement.  And the date dimension is shared with the Sales Fact.  This way, you can slice by year & month from the shared date dimension and aggregate UnitCommitments and Sales.  As well as calculate variance and YTD values to compare.

The result looks like this:

And if you want to look at the example I built, it's here.

Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Thanks Brent, That has gotten me quite a bit further.

One final thing and I think I'll have enough to put this together:

Given your model (which I've now largely copied) is there a calculation that would result in an SumAgreementSales measure? I.e. those sales that fall within the agreement timeframe. Each account's agreement starts at a different time and they may have prior sales, but the KPI will rate them against sales once the agreement starts.

I've by trying to create that measure using CALCULATE([SumSales], DATESINPERIOD(...)) but haven't gotten it yet.

Again thanks for the help!

Answer 3

Glad that helped.

To be able to slice by Agreement, here are a couple of options.

1: You could put the Agreement ID  directly in the FactlessAgreement table.  This would be a degenerate dimension embedded directly into the fact (although, since this is columnar, you could get away with using a name or whatever you'd like to slice by).  And then add that field to rows on the pivot to slice both commitments and sales.  For this to work correctly, you'll have to modify your Sales measure to take into account the current filters on the FactlessAgreement table like this:

SumSales_M2MAgreement:=CALCULATE([SumSales],FactlessAgreement)

2: A cleaner dimensional approach, and the one I'd recommend, is to abstract the Agreement out into it's own object, along with the summary level attributes (StartDate, EndDate, Total Commitments, etc.).  This new dimension would be included in the intersection of FactlessAgreement like this:

With that, and with the modified Sales measure, you will be able to slice by the AgreementName like the pivot on the right below (also included the actual linked table for reference):

This is a many-to-many pattern that is common when correlating multiple events / facts.  Marco Russo & Alberto Ferrari did a great white paper with lots of examples if you're interested in digging deeper in that space.  Check it out on their site here.

Let me know if that helps.  And I updated the file in my Skydrive Public folder if you want to take a look.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com