Adding new data to an SSAS 2005 Cube

[Utopiah]

Actual situation :
Our business application alimenting the
Datawarehouse (on SQL Server 2005) alimenting the
MS OLAP Cube (on SSAS 2005)
all with SSIS scripts made with MS BI Studio.

Users are manipulating the data with ProClarity and finally MS Excel.

The whole system is working well and being used daily.


Objective :
Being able to analyze with additional data available from another DWH or Excel files.


Potential solutions :

1) adding data directly into the cube
[ul]
[li]+advantages+ direct and easier to set in place (I guess)[/li]
[li]-disadvantages- every update from the source DWH will remove added data[/li]
[li]?unknown? how to write data directly in the cube[/li]
[/ul]

2) adding data into the source DWH
[ul]
[li]+advantages+ ?[/li]
[li]-disadvantages- ?[/li]
[li]?unknown? rewriting the alimentation process of the cube ?[/li]
[/ul]

3) ?

Note : the Excel file could be uploaded daily on a Sharepoint server and plugged through with a "dataflow task/Excel flow" component, that's not the main difficulty I guess (after cleaning the data)

 

[MDXer]

Option 2 is really the best option. By Loading the dat into your DW you enure that the Cube an the DW will foot. You also gauntee that your existing Dimensional structure is enforced and the proper keys values are ued, as this should be part of your ETL.

Option 3
Add a partition that will load data from your new datasource. By using partitions you will not affect the data loaded into other partitions. However your data must be in the same structure and utilize the same keys values or it will not work.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Utopiah]

Thanks a lot for your precise answer.

Im not sure I fully understood the 3rd option. when you say "partition" you mean an extra cube to fusion with the main one ?

Also, once I have the SSIS dtproj project file and access to the server (DWH and cube), what am I supposed to do technically ? Rewrite the SSIS project and publish it on the server with appropriate connection to the test datasources ?

 

[MDXer]

A partition is a subobject of a measure group. Just as one would physically partition a database table, you can partition a cube. Partitions allow you more control of data loading. You can point partitions to different datasources that are identical in structure. Easiest way to think of it is if you had a table that contained 2006 data and 2007 data you could have a partition that pointed to the 2006 data and one to the 2007 data, then when you processed 2007 the 2006 data is not reloaded.

Now partitioning at the year is not common typically month or weekly partitons are used.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck