BI CaseStudy - Slowly Changing Dimension (Type2)

The Problem
Store historic data in a relation ManyToMany between two Dimensions.

The Scenario
For each day I need to import via ETL about 5.000 rows of financial movements into a FactTable.
This data must be viewed by several dimensions: Entidade, Carteira, Mesa, Folder and FolderGroup. Each dimension has several variables that is related only by the dimension, and these variables must me store along time. So I need to store historic data for each dimension and In this project there is two important areas:
1. Entidade, Carteira, Mesa and Folder is a typically a Slowly Changing Dimension (Type2) and these dimension are structured as a snowflake. (Yes… I know Kimball doesn’t recommend the use of snowflakes, but I think for this case the data could be better understandable and the data model will be more easy to maintain in the future)
2. FolderGroup is a ManyToMany relation between Dimension Folder and Dimension Group and a Bridge to link the both.

Now, I’ll show the current structure and then I’ll explain my problem.
As I told you before, my problem is that I cannot create a Slowly Changing Dimension (Type 2) when there is a relation ManyToMany between Dimensions.
In my project I have the typically SCD (type2) that is working good and I fell data model very “clean” and understandable.

Sample Data from Slowly Changing Dimension (Type 2)
I divided Entidade on Entidade and EntidadeHistoric because the attributes never change and by this way I’ll avoid redundant data in the dimension. If for any unexpected reason I need to change ENT_Name or ENT_RelatorioKVaR, it will be a typically SCD (Type1) and I’ll do a classic update to the dimension row. The other reason of this division is that I only need to save historic data for hENT_RacioCooke. (I have other fields to store but for this example I’ll omit it)

The value 9020 is the Foreign Key to the Time Table and correspond to the date 10-10-2003.
So, from this picture we can see that for Lisbon, the field hENT_RacioCooke, between date 8888 and 9020 is 0,5 and between 9021 until now is 0,1.

Sample data for my Snowflake schema
I decided to create the relations below as Snowflake, because I felt that could be a better way to maintain the data in the future.
The business keys from each level (ENT_ID, CRT_ID, MESA_ID and FOLDER_ID) are in a string format to be coherent with the source data of ETL and to be more comprehensive for the admin users that will be analyzing data directly to OLAP and OLTP databases.

Based on the images posted before, I will show you how the SCD (Type 2) is working on my project.
I decided not include all the structure and focus only in the essential data to understand my pending problem.

As you can see in the above picture, this is my SCD (type 2). I’ll test if this structure is working good creating the follow SQL query:

And the result is:

Imagine you need to add a new field to the table Dados with the formula : D_MeasureX_ENT=D_MarketValue*hENT_RacioCooke.
For the same D_MarketValue value (10.000) in the days 9019 and 9023, the new field D_MeasureX_ENT is different because the RacioCooke between 8888 and 9020 was 0,5 and between 9021 until now is 0,1.

OK.. now that you understood my project, I will start describing my real problem. All I write above could be changed and discussed with us, but for now is working perfectly and for me is urgent resolve my current problem… but I accept suggestions for it!

In the project I have to group folders in groups and to stores the historic as I did before for Entidade, Carteira, Mesa and Folder. I remember you that each historic depends on each dimension, and I need to group 3 or 4 folders into a group and this group has a specific RacioCooke as Entidade, Carteira, Mesa and Folder. And the problem is that a folder in fact Dados could be in more than one group.
While in the SCD described before there is a relation OneToMany, and you could store your surrogateKey (Foreign Key) in the Fact Dados, in this case you couldn’t because there is a relation ManyToMany. I tried to use the structure below, but when I tested, didn’t work because it would create duplicate records in my fact Dados.

The Big Question

What is the hGRP_RacioCooke for the row 2131 in Fact DADOS for GRP01 (Investment) and for GRP02 (Negotiation)?

The Solution
I already found the solution and I will explain that later.

Kind Regards

BI CaseStudy - SSIS Package v2

Dear Friends,
In a previous post I described the first version of my current project main SSIS packages.
http://pedrocgd.blogspot.com/2007/06/ssis-packages-structures-v1.html

Today, I will describe how the main package changed. I‘ll probably do some changes to this version, depending on your feedback.
The majority of this objects are sequence containers objects and each of them has several steps inside.




This package will be executed in 95% of the cases for each day and it will import data of several data sources like SQL Server and Sybase. Sometimes the package could be executed not only for one date but between two dates to correct an unexpected error or for other unexpected reason.

1. SEQ VALIDATE Surrogate Keys
Step to validate my surrogate keys. (This step will be explained in a future post)

2. SEQ GET Configuration
Get the start and end date from the database table Configuration. These dates could be manual (stored in DB) or automatic (getting last date from main fact table Dados)

3. SEQ VALIDATE LastRunDate DADOS
Step to avoid inserting duplicate rows from an existent day in FactTable Dados

4. SEQ VALIDATE LastRunDate CASHFLOWS
Step to avoid inserting duplicate rows from an existent day in FactTable CashFlows

5. SEQ IMPORT SpotRates
Step to import currency rates based on Euro.

6. SEQ TRUNCATE Tables (Temporary)
Step to truncate tables. I will improve this step soon.

7. SEQ IMPORT TO FactTable CASHFLOWS
Step to Import data into FactTable CashFlows, FactTable BondsCouponLiq e BondsAmortLiq

8. SEQ GET IDENTITY Initial Values
Step to GET initial dimension identity values.
http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

9. SEQ IMPORT TO FactTable DADOS
Step to import data into FactTable Dados and populate dimensions CounterParty and Instruments. Inside the sequence container there is several dataflows to import data into FactDados for each instrument like Equities, Bonds, Swaps, CapsFloor, Credit Swaps…

10. SEQ UPDATE FactTable DADOS
Step to update several fields like basel weight and risk into the rows inserted in the previous sequence container

11. SHRINK Database dbRentabilidade
Step to compact space in the database

12. SEND MAIL with SSIS Report
Step to send an e-mail with SSIS Report.

So, what improvements you think that could be done? Feel free to send feedback!!
Regards!

BI CaseStudy - Packages Structures v1

Dear Friends,
Here I show you my version 1.0 of SSIS in my current project. You can see from the images the 3 packages that I will use. Inside each sequence container (SEQ) you have some complex tasks, but for now is to early to describy all for you.

1. Package


2. Package


3. Package


Regards,

BI CaseStudy - Current and proposed system

Dear Friends,
In the previous posts, I was talking about the project but this images can show you a better understanding to it...
I'm alone developing this project since the step of the requirements until the final step in sharepoint and proClarity. So, I don't have a lot of time, but I'll always be trying to show here some information. I made this documents in Visio to show to the customer:

Current System


Proposed System


Regards!

BI - Introduction to the project

I'm currently working in a BI project, therefore almost the posts will be focus on it.

The project will be use the Microsoft tools for Business Intelligence as SQL2005 (SSIS, SSAS and SSRS) and Sharepoint 2007.

The image below, show you the appearance of the SSIS controlflow (completed 80%):



the next posts, I will describe some of the problems that I had and the solutions to solve it.
Regards!