Blog Purpose

Dear Friends,

Yesterday, an anonymous user post in this blog and advised me to take some care about the information that I publish…
He thought that I was publishing confidential information in the examples published… he was wrong but I did not advise anyone and I agree with him, but he could have itself identified… even because I fell he works near me…
Some people does not understand the purpose of this blog, and doesn't know the importance that it has for me. No matter how little this blog is, but If it helps at least one person, the existence is justified.
And to prove it, I’m in the top ten of answerers in the SSIS, SSAS, SSRS and SharePoint MSDN Forums! Side by side with the bigger “gurus” in this subjects and authors of my preferred BI Books!!

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1 (SSAS)

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1 (SSIS)

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1 (SSRS)

I help the others not for be in this top, but to help others as well as they help me to find the right solution for my doubts… this is a community, and somebody does not know what it is!
I’m proud to be part of this community and to have some knowledge to share…

In my free time I will continue here and in MSDN Forums sharing my ideas!

Kind regards and good work

SSIS - MSDN Arkiboys doubt

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

SSIS - MSDN Gemma doubt

Dear friends,

I'm writing this post to try to help Gemma (from MSDN forums get the solution for her problem.





http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871831&SiteID=1
regards!

SSAS - Slowly Changing Values

Dear Friends,
This post could be very controversy… I hope to receive comments from you…

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

The Solution
Using the Transform Slowly Changing Dimension, I think the performance would be worst (but maybe I’m wrong), and is more useful when you need the Data in the IS. So, inside of one of the best books I have in home, MDX Solutions (http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html), I found the solution for my problem in the page 84 (Chapter3)




The data inside my FactTable that has the values to create historical data in MDX:


I hope the get comments to it!
Thanks and regards!

SSAS - Get ReferenceDate

Dear Friends,
In this post I will describe on of the last problems that I was involved.

The Problem
I have millions of rows in a FactTable with fields ID, Date and Amount… (I have more fields in this table, but for this post is irrelevant).
In AS I will have these rows aggregated by Time, so, for each day I have an amount. For each row, I need to create a calculated member for each day with the value amount of a reference date. The formula is:

Reference Date = Last Date of previous year of [My Field Date] of each row in FactTable.

The Solution
I posted my problem in MSDN forums, and I got an answer from Bryan C. Smith but didn’t resolve my problem.
He gave me an example of Adventure Works, but did not work in my project.

So, I was looking for several MDX functions, and after a lot of failed attempts I founded the solution!

In this statement, I'm using the ClosingPeriod and Ancestor MDX functions. The ancestor to get the last year, and the ClosingPeriod to get the last value of that year in the hierarchy day.

I hope this post help you!
Regards!

SSIS - DataFlow Performance I

Dear Friends,
Today I will show you the images of the dataflow inside the SEQ Container (SEQ IMPORT TO FactTable CashFlows).
This dataflow import data from SQL Server 2000 and insert it in a SQL Server 2005. Each of this datasource are from different tables. If your SSIS server does not have sufficient RAM, maybe could be better to separate this dataflow in 2 or more.




Give me your 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!

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!