Existence of varying granularity across KPIs

[kajaldas007]

I am designing the dimensional model for around 70 KPIs. Different numeric parameters (measures) across all these KPIs come at different granularity levels. Let me provide some examples:

1. For KPI# 1, there are 3 parameters; and all three come with the grain (shift - machine - product)
2. For KPI# 2, there are 5 parameters; and all five come with the grain (day - machine group - product)
3. Targets for some KPIs are monthly per machine and for some other KPIs yearly per machine.
4. Rolling forecasts are sometimes quarterly at the factory level and sometimes at a lower granularity.
etc...

The obvious approach is to have many versions of same dimension at different granularity. For example, in case of time dimension, I can have one time dimension with Shift as lowest granularity, another with day as lowest granularity, another with month as lowest granularity, likewise; and same approach for other dimensions.

However, in reality, when I consider all the KPIs and granularity requirements, the total number of dimensions becomes very high and there are many fact tables, some of which may even contain only couple of measures. Is there a smarter way to look at this scenario?

Thanks & Regards.
Kajal Kumar Das

[ngalemmo]

You draw dimensionality from the sources, not the targets.

KPI's can be very complex, often requiring queries from different facts, aggregations, allocations and a host of other manipulations.

If the problem you are having has to do with designing a DW to address these KPI's, you use the KPI's at a high level to identify the sources you need to support the calculations. Once a DW has been built, and the sources are loaded at its most detailed level, then you deal with working out the queries (actually, one exercise to validate the DW design is to show how the tables can be queried to generate the KPI's, so you certainly perform a gap analysis to identify deficiencies in the design prior to building it).

Worry about atomic level data only. Do not try to build aggregates to satisfy specific KPI's. Those can come later.

If all you are trying to do is build a mart from an existing DW, don't expect to be able to put all the KPI's into one star or cube. As you have stated, the dimensionality is all over the place. KPI dashboards often contain a large number of queries to place different KPI's on the screen.

[kajaldas007]

Thanks for sharing your views.

Worry about atomic level data only. Do not try to build aggregates to satisfy specific KPI's. Those can come later.

I am aligned with the idea of working with most atomic data. Fact tables are being designed at the most granular level. However, even in doing that, I observed that measures (at the business process level) are available at different granular level. For example, production volume data is measured at the factory on shift level (lowest time grain), but, rejected items are measured at the end of each day. There are similar issues on other dimensions as well. In that case, is it all right if I go ahead with two time dimensions: one at shift grain and and one at day grain? There will be two facts as well (one at the shift grain and one at the day grain). The shift dim will also contain the attributes of day, month, etc. (conformed), so that drill across and comparison between facts can be done. Please share your views.

You draw dimensionality from the sources, not the targets.

Agreed. However, I also need to store the planned values and target values for visual comparison on the dashboard. Although, these values are not "facts", while storing these information, I also need to associate the context - i.e. the dimensions, but at a higher grain (e.g. month, factory, material group, etc.). Please share your views.

[ngalemmo]

Different grains is commonplace. When dealing with atomic level facts, you have three options: separate fact tables (path of least resistance), allocating higher level measures to lower level facts (workable but a potential political minefield), incorporating multigrain facts into a single table (multigrain is great for baked goods, but a disaster for fact tables).

As far as dashboards go, it is not unusual to have very complex logic behind them (in spite of what dashboard vendors would like you to think). Each element may have its own query process to support it. Most integrated tools allow you to use predefined 'reports' (i.e. queries) as sources, allowing you to pre-calculate measures to improve performance.

[Vishy]

Kajal,
I don't know if you are still following this, I just completed a project which had 700 KPI's and that was a banking solution. I had few KPI's like reconciliations which were at day level, few KPIs were at month level like number of product sold and few at other time levels.

I used only one time dimension though facts were different.
For daily fact we were storing that day's value so -- a date
For monthly fact we were storing last day of the month so again - a date
For quarter facts we were storing last day of that qtr -- a date

so we were able to do it all using a single time dimension.
does it help ??

[Sponsored content]