How to handle multiple aggregations for multiple KPIs in fact table

Hi,

I work for a call center company that provides services to multiple clients. Recently we had a request to generate a report that allows users to see the KPIs for each client (we called them accounts). The report looks like this:

KPI       Date     7 Days Before     Month to Date     Previous Month
--------------------------------------------------------------------------------
KPI1     9.73%        13.72%              12.00%               12.42%
KPI2     221.67       241.13                232.16                260.02
KPI3     70.61%       69.09%               69.73%             68.53%

Some accounts can have only two KPIs other can have more than 10, so to handle this request we design the following model:



We decided to store all base elements in the fact table. We store all measurements per account on a daily basis. The KPI dimension has the formulas that can be use to calculate the KPIs for each account. We use these formulas in the front end application. Because we're performing all the calculation in the final application, the performance is really slow, in addition we have a date filter that allows the user change the date when this happens all values in the report are recalculated for that specific time.

Maybe you could check the model and suggest us how could we improve the design and add the calculations in the fact table instead of doing it in the front end. We have think about creating a fact table with the following structure:

Fact ID
Accunt ID
KPI ID --> Removing the bridge table and join directly to the KPI dimension
row_date
measure --> this will be the actual KPI for the date in row_date
measure 7 days --> calculate the value for 7 days before
measure month to date
measure previous month --> although here the granularity is different

But, it seems that we are having different granularities. Is there any way to do this without creating a fact table for each aggregation (daily, seven days before, month to date, previous month).

Thank you in advance for your help.

Kind Regards,

Karla

You will have your fact table with the daily attributes
Account Id,
KPI ID
Day ID
Measure
Measure 7 days
Measure Month to Date

Another fact table :

Account Id
KPI ID
Month ID (FK From Month dimension)
Measure Month

You have just a little redesign your Day dimension ( just focusing on main date attributes )

Day ID
Date
Month ID
Month name
Month number
Year
…

The Month dimension table is the following:
Month ID
Month name
Month number
Year
…

This Month dimension table is a so called conformed rollup dimension table where the attributes are subset of dimension table DAY. The common attributes share the same structure and content.
Month ID is  sequence number starting with the first month in the system.

In your report (or analysis) you will use these 2 tables.
The first 3 KPI’s are from the first fact table and the 4. is coming from the second table ( account ID is the same, KPI  defined, Month ID= Month ID from the Day dimension according the given date – 1 ( previous month)

This is the so called drill across when you are analyzing facts from more than one fact table.

I'm not addressing this specific situation, but rather the basic scenario, which is fairly common.

Uses will often request reports which contain a multitude of KPI's that may or may not be somewhat related. It is natural to try to think there may be a way to model around it, but most of the time it involves abstracting the data to the point of incomprehension or a model so convoluted it is difficult to use for day-to-day analysis.

Sometimes it is a good idea and step back and ask yourself what will happen when the report changes.

When you look at these kind of reports, basically what they demand is the ability to incorporate multiple queries on a single page. It really is a reporting tool issue than a modeling one.