Create a Time Dimension with SSAS (DimTime) - 2 Part

After creating our DimTime table in the 1ages part of this article, we will now make some improvements on our dimension.

From experience, I would recommend using a key PKDate int type in format yyyymmdd.

This type of key will allow you to get better performance.

This coding in the format yyyymmdd should be generalized at all levels of our axis time: year, semester, quarter, month, week ... So I suggest that each level (year, semester, quarter, month, week, day) be composed of three distinct attributes:

  • Code
  • Date
  • Name

Read More Create a Time Dimension with SSAS (DimTime) - 2 Part

Create a Time Dimension with SSAS (DimTime) - 1 Part

The dimension Time (DimTime) plays a very important role in a decision-making system, its presence is obligatory in the tables of facts which characterizes a decision-making system compared to a transactional system.

In the rest of this article, we will create the table DimTime using an SSAS project wizard.

Read More Create a Time Dimension with SSAS (DimTime) - 1 Part

Slowly Changing Dimensions

Ralph introduced the concept of "slowly changing dimension (SCD)" attributes in 1996.

Slowly Changing Dimensions (SCD) - dimensions that evolve slowly over time, rather than changing the time base in the regular calendar. In the data warehouse, it is necessary to follow the changes of the attributes in the dimension in order to make the data historized. In other words, the implementation of an SCD type must allow users to assign the dimension attribute value appropriate for a given date. Example of these dimensions could be: customer, geography, employee.

Read More Slowly Changing Dimensions

STG & ODS

We always talk about STG and ODS in BI archi, so what is the role of these two phases in a BI archi?

- STG (Staging Area): is a set of tables that represent a true copy of the data source and are purged each time the ETL is run: it is a waiting area, a "boarding room" before the ODS phase; it is in these tables that we can find data in odd formats.

- ODS (Operating Data Store): is the place where the transformations, the crossings, ... etc; before feeding the datawarehouse and using as source the STG.

Read More STG & ODS