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!

SSAS - Problem&Solution - Get Diference between current and previous row for valid dates

Dear Friends,
I posted a question in Microsoft Forums, and I describe here the solution founded

Problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1720487&SiteID=1

Solution:

I created a view in database filtering only the valid dates in the table Time.

The datasource view in Analysis Services:

Exploring Data in TIME dimension:

Exploring DATA in CASHFLOW Dimension:

And the named calculation to calculate the difference between current and previous:

And in the brower of the cube:

Please let me know if you think that could be a better solution!

Regards!

SSIS - Populating Dimension

Dear Friends,

I was looking for several ways to populate a dimension inside a dataflow and is not so simple find the right solution to that...

It does make sense populating dimensions only in case of the input database has redundance in their tables, or either, not normalized.

The database relation needed to this example is:



and the transformations inside the dataflow are:



Now, I will describe with more detail each of the dataflow transforms.

First step is to create a global variable to store the value of the identity key. I’m manually creating the identity key in my dimension table, so, I need to know the value on each row of the dataflow. (This value will be incremented in the script component transform)

Add a Execute SQL Task to the control flow to get the last identity key:



Add a LookUp transform, to LookUp for InstrumentName in the dimension table "Intrumento"

Add a MultiCast transform for the rows with new InstrumentNames founded.

Add an Aggregate transform to get the distinct InstrumentNames founded.

Add a Script Component transform to increment the INST_IDENTITY global variable



Script Component explanation:
PreExecute Method
Receives the global variable INST_IDENTITY with the last Identity value for the table Instrumento
Initialize the local variable counter, that will be incremented for each row.

Input0_ProcessInputRow Method
Increment counter variable and save it for each row in Row.INSTIDENTITY output column

PostExecute Method
Refresh the global variavle INST_IDENTITY with the counter value.
Add another MultiCast to allow you to Sort the new rows with Identity column created in the step before, and to sort these new records (InstrumentNames) on the Database.
The mappings for the OLEDB Destination that must be created, are:



Add a Merge Join, and configure as a LEFT JOIN like this:



Finally, you have all the rows with the respective Identity value.
I hope to receive some feedback from you!
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!