How do I create a Period Dimension?

This blog covers the period dimension. It looks a lot like the date dimension discussed earlier (and indispensable in every data warehouse), but its function is a little different. I describe the power of this table with some examples from AdventureWorks.

What is a period dimension?

In star schemas the level of detail, also known as the “Grain”, of a fact is usually set at date: the lowest level of time that is stored (time of day is possible too, but it is not relevant now). Say a fact has a grain at week level. You will then try to link it to the date dimension. You can do this by converting the week number to a DatumID (SELECT 201001 * 100 + 01). This is by far the simplest way. For management reporting this works fine for transaction facts that are additive. The sum of the revenue of all orders over the days in a month is, after all, the revenue of that month. But what do you do when you must report facts that you cannot easily add up? In other words, the totals of month 1 + 2 + 3 are not the totals of quarter 1. Think of “Number of working people per week”. This is a fact you cannot add up. Still, you want to show this information in reports per quarter, month, year, week, etc., and preferably in a trend report too. The solution is a period dimension in combination with a snapshot fact. This article gives the advantages, how it works and an AdventureWorks example. The scripts to build a Period dimension are attached to this article.

Structure of the Period dimension

A Period dimension holds one record for every period, and you can use it in, for example, your Kimball data warehouse model. Every period is part of a period type. A period type can be a month or a week. In my case the period dimension holds 102 records for the year 2010. For weeks there are 52 records (or 53), for year one record, for month 12 records, etc.

The following columns are important too:

PeriodeID: unique key Jaar: the year the period falls in PeriodeNummer: year + period number BeginDatumID: first day of the period EindDatumID: last day of the period PeriodeGrootheidID: type of the period VorigJaar1PeriodeID, PeriodeID of the period one year back VorigePeriode01PeriodeID: PeriodeID of the previous period VorigePeriode02PeriodeID: PeriodeID of two periods back VorigePeriode03PeriodeID: PeriodeID of three periods back VorigePeriode04PeriodeID: PeriodeID of four periods back VorigePeriode05PeriodeID: PeriodeID of five periods back VorigePeriode06PeriodeID: PeriodeID of six periods back

PeriodeNummer is useful to link the result of a period fact later to another fact that is aggregated by week, for example, because PeriodeNummer is the same as WeekNummer. The Vorige IDs are very useful for comparisons with the previous period later on. You can extend this with more fields (function attached) with, for example, this SQL:

1
UPDATE DimPeriode
2
SET VorigePeriode01PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -1) ,
3
VorigePeriode02PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -2) ,
4
VorigePeriode03PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -3) ,
5
VorigePeriode04PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -4) ,
6
VorigePeriode05PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -5) ,
7
VorigePeriode06PeriodeID = dbo.GetParallelPeriodID(PeriodeID, -6);

Key structure of the period dimension

The primary key of the table is PeriodeID. It is unique. The key follows a fixed logic: Year + Period type + Period number.

Example Week 12-2010: 2010112 Example Month 8-2010: 2010208

There is a record for every period type and every period. These are the period types:

• Week
• Month based on Calendar
• Quarter based on Calendar
• Period based on 444 Week split
• Month based on 445 Week split
• Quarter based on 445 Week split
• Year based on Calendar
• Year based on Week split
• Day

Practical example of the period dimension

You can combine the period dimension with an organisation structure. This gives you a combination of all periods and all organisation units. You can then attach values to these combinations, for example KPIs.

Business questions you can answer:

• Number of employees who work at our company. The user must be able to change with a parameter how he wants to view the data. He must be able to view it per week, per month, per quarter and per year. You will now have to determine the measure “number of employees” for every period type that people want.
• Ranking: how does my department compare to other departments (divide the revenue KPI by the number of FTEs)
• Compare revenue figures with a period one year back: you no longer have to struggle through large tables and use SUM functions; you store the revenue figures in all possible combinations in a KPI table that uses DimPeriode
• Show trend lines: trend lines often perform badly with SQL (without OLAP). Several Group By and SUM functions hurt the performance. With a KPI table in combination with DimPeriode you have less of this problem.

For this mechanism to work correctly, you need fairly complex SQL to fill the new facts. This is not always the case. To show how the table works and how powerful it is, I give some SQL examples on AdventureWorksDWH.

Question: number of customers who placed an internet order, split into weeks, months and year.

1
SELECT DP.PeriodeID ,
2
SUM(FIS.IsOrder) AS AantalBestellingen
3
FROM ( SELECT CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) + 60000 AS OrderDatumID ,
4
1 AS IsOrder
5
FROM FACTINTERNETSALES AS FIS
6
INNER JOIN DIMTIME AS DT ON FIS.ORDERDATEKEY = DT.TIMEKEY
7
WHERE CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) > 20040000
8
) AS FIS --Hier is de truuk, bepaal in welke periodes een bestelling valt!
9
INNER JOIN DimPeriode AS DP ON FIS.OrderDatumID BETWEEN DP.BeginDatumID
10
AND DP.EindDatumID
11
INNER JOIN DimPeriodeGrootheid AS DPG ON DP.PeriodeGrootheidID = DPG.PeriodeGrootheidID
12
GROUP BY DP.PeriodeID
13
ORDER BY PeriodeID;

What you see in the images are all periods in 2010 with the number of customers who placed an internet order. I picked out two of them to explain how it works.

You can put the result of this SQL in a new table, for example FactPeriodeKPI. A Measure in FactPeriodeKPI then becomes AantalInternetBestellingen. This way you can store all kinds of KPIs / Measures.

Question: number of unique customers per sales territory, over time

This is a fun one. It is a typical KPI that is not additive. You cannot add up the number of customers in a week to see them per month. So you must calculate these values per period. The trick is to look at which customers have an order per date. If a customer has two orders on one day, that does not matter for the count: it is about the number of unique customers per day. Then you link this to the periods by checking whether a date falls in a period. On top of that you do a COUNT DISTINCT to get the number of unique customers. The fun really starts when a customer structure also has hierarchies. A customer is only a customer at the highest level (for example the group), and the customer numbers below it must not be counted as unique. I leave that out of scope for now. On to the SQL:

1
SELECT DP.PeriodeID ,
2
DPG.Naam ,
3
DP.PeriodeNummer ,
4
COUNT(DISTINCT FIS.CUSTOMERKEY) AS AantalUniekeKlanten
5
FROM (
6

7
-- Aantal Unieke klanten per orderdatum
8
SELECT DISTINCT
9
CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) + 60000 AS OrderDatumID ,
10
DC.CUSTOMERKEY
11
FROM FACTINTERNETSALES AS FIS
12
INNER JOIN DIMTIME AS DT ON FIS.ORDERDATEKEY = DT.TIMEKEY
13
INNER JOIN DIMCUSTOMER AS DC ON FIS.CUSTOMERKEY = DC.CUSTOMERKEY
14
WHERE CONVERT(VARCHAR, DT.FULLDATEALTERNATEKEY, 112) > 20040000
15
) AS FIS --Hier is de truuk, bepaal in welke periodes een bestelling valt!
16
INNER JOIN DimPeriode AS DP ON FIS.OrderDatumID BETWEEN DP.BeginDatumID
17
AND DP.EindDatumID
18
INNER JOIN DimPeriodeGrootheid AS DPG ON DP.PeriodeGrootheidID = DPG.PeriodeGrootheidID
19
GROUP BY DP.PeriodeID ,
20
DP.PeriodeNummer ,
21
DPG.Naam
22
ORDER BY PeriodeID;

The result above shows the power of DimPeriode. As you can see, there are a number of unique customers in the first three months. Below that you see the quarter result. The quarter result is the number of unique customers in the first quarter. This is not the sum of the first three values, because then you would get 5392 customers.

The last one I cover is the number of lost customers compared to period -1, -2, -4, -4. I will add this to this article soon, when I have more time.

2767_Script_Voor_DimPeriode