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

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 codification in yyyymmdd format should be generalized to all levels of our time axis: year, semester, quarter, month, week ... I therefore suggest that each level (year, semester, quarter, month, week, day) be made up of three distinct attributes:

  • Code
  • Date
  • Name

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

 

Create a Time dimension with SSAS (DimTime) - 1st 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) - 1st part

 

Archis BI

In this article, I am going to give you some BI archis that I have met in my various experiences and which use the ODS and STG phases:

process_informatique_decisionnel

Source -> STG-> ODS-> DWH

This is the arch I often use, the STG contains the data of the source without any formatting or filter, it is the exact copy of the source data and ODS contains the formatted data.

Read More Archis BI

 

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 which represent a true copy of the data source and which 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 bizarre formats.

- ODS (Operating Data Store): is the place where the transformations, the crossings,… etc; before the power supply of the data warehouse and which uses the STG as a source.

Read More STG & ODS