How To Load Periodic SnapShot Fact Table With SSIS

I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.

However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!

Hopefully you have a method of deriving all possibly combinations of the 5 key columns. Personally I would do this by extracting all possible values from the dimension tabes, unioning them together but where each column creates a new column in the output from the UNION ALL and then use the Aggregate component to produce all of the combinations.

Once you have done that you can use a MERGE JOIN component to join to your dataset and produce nulls/zeros for teh fact values for all of the missing rows.

-Jamie

|||

Thanks Jamie. I will give that a shot.

-Steve

How to load Fact table and dimension using SSIS?

Hi All,

I am just curious to know how I can load data from a data warehouse to an Analysis Service Cube (both to the fact tables and dimensions).

Does any body have some way to achieve this?

I appreciate if any body provide me a good material which describe this scenario.

Sincerely,

--Amde

Amde wrote:

Hi All,

I am just curious to know how I can load data from a data warehouse to an Analysis Service Cube (both to the fact tables and dimensions).

Does any body have some way to achieve this?

I appreciate if any body provide me a good material which describe this scenario.

Sincerely,

--Amde

this link should help: http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

How to load data from a data warehouse to a Cube ?

Hi All,

I am just curious to know how I can load data from a data warehouse to an Analysis Service Cube (both to the fact tables and dimensions). Whenever there is a change in the data warehouse, it should be reflected to the fact table and dimension

Does any body have some way to achieve this?

I appreciate if any body provide me a good material which describe this scenario.

Sincerely,

--Amde

Hi Amde,

You should process your cube, every time that you have news records in your fact table. If you have data every month, for example, then you could use partitions cubes by month.

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

You mean I can load the data automatically using partitions cubes? Because, the thing is we are developing a product that will be delivered to a client. So in this case the user is not supposed to use the analysis service environment to proceess the cube, instead I want to schedule the process so that it will automatically populate the cube with the new records.

Would you please send me a link which talks about this scenario?

Thanks

--Amde

|||

If you open Analysis Services 2005 in management studio and expand the database with the cubes and dimension you want to schedule, you do the following.

Right click on a dimension and choose process. In the dialoge you are presented with you are able to script this command to a file or the clipboard.

After this you expand the SQL Server Agent in SQL Server 2005. Under this you expand job and choose new job(right click). After you choose new step. In this dialoge you paste the code in to the large window att the bottom. Do not forget to change the type of the command to an Analysis Services command.

Repeat this process for each dimension and finally each cube.

When the job is complete you need to add some parameters and command for the job.

You can also build a workflow for processing cubes in SSIS. Perhaps this is easier.

HTH

Thomas Ivarsson

|||

I understand what you are saying. I want to confirm one thing. Let's say we have 6 dimension, we need to create 6 jobs and schedule them at the same time. Am I right saying that?

--Amde