Data Warehouse Archives - Turning data into powerful insights

12 August 2016Ousama EL HOR No Comments

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

After creating our DimTime table in the 1^st 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:

--
Reserve
Name

2 July 2016Ousama EL HOR No Comments

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.

10 March 2016Ousama EL HOR No Comments

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.

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.

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.