Thursday, October 4, 2007

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!

4 comments:

Rafael Salas said...

Why you don't place step 3 at the begging? Checking for a duplicate execution should be one, if not the first checks to perform.

Pedro said...

Hy Rafael! Thanks for your visit!
But I you sugest it? I need the step 2 to execute step 3.
What you think I can add ti the package to improve more funtionality?
Regards and thanks!

Rui said...

Congratulations Pedro
You have a superb BI Blog.
In future, when I need to find some kind of information, of course I will return to your blog.
Regards

Rui Norte

Pedro said...

My friend Rui! :-) Thanks for you comment... You could speak in portuguese... but of course in english we can express our feelings/opinions to more people...
Your visit is a pleasure for me! I hope to see you more times here... :-)
Cheers!! Or in portuguese : Um abraço!! lolol

LinkWithin

Related Posts Plugin for WordPress, Blogger...