First Datawarehousing project-need some help with SSIS,SSAS,SSRS

[jdcosta]

Hey guys,

As you may have understood from the title, my company has been giving responsibility of bringing up the DW using SQL server 2005.

It;s turning out to be a little more complicated than i thought and i need some help to expedite my project.

What I'm done with so far

(a) Importing all data from the various transaction databases into the staging area

(b) Last phase of deduplicating data(I had hoped SSIS would make it easier or at least help in automation..but i have finally ended up manually importing and de-duplicating data)

What i need help with is



(A) preparing with the next phase..SSAS..do i really need to use this tool, i have the dimensions setup and data imported Once the data is de-duplicated ...is it ready for reporting?? i.e. SSRS

(B)So far data cleansing has only been removing duplicates..is there anything else i should look for?

(C) whats the best way to prepare for the reporting phase.

Could you please give me your feedback along these lines, in addition any comments/tips/advice from those who have projects under their belt would help me ..in my first one.

Thanks, i really need some advice...

 

[RiverGuy]

(A) Don't even attempt to put your data into a cube structure like SSAS until you have your star/snowflake schema complete. You mentioned you might not have all of your data cleansed, so do that first. My advice is to design your relational data warehouse database so that it can stand on its own even without the cubes.

(B) Other things to look for include verifying with all affected business areas that the labels and attributes for your dimensions convey what the different business areas need to do their analysis. Also, figure out a way to handle exceptions which will come through in your ETL process. For example, if some records from your source system cannot be loaded, you need a plan to reconcile those records. ETL will be one of the most time consuming parts of your process and shouldn't be neglected.

(C) The first thing to do to prepare for the reporting phase is to find out what your business users want and need as far as reporting is concerned. After that is complete, your business needs to select the appropriate reporting tool, which may be SSRS. If you select SSRS for example, you need to make sure your IT infrastructure department has the means and availability to manage the SSRS server. Meanwhile, you could prepare some proof-of-concepts or rough drafts for some selected reports to make sure your data warehouse design fits the reporting solution as per your business needs.

 

[Hans63]

(B) Apart from deduplicating, you should consider to do some conforming. Make sure all codes are the same. Like all Gender fields should be M/F, etc.

Add some chekcs on the number of records (if possible)

 

[chamilz]

In addition, you may need to figure out how the business wants to track the changes to the attributes in dimensions. (Slowly changing dimensions)

Which method business prefers to use; TYPE: 1, TYPE: 2 or TYPE: 3.

Depending on the business requirements, it is required to design the relational data warehouse DB and ETL processes to capture these changes.

Tracking the history of attributes in dimensions gives a true picture of the past business activities. In this case, TYPE: 2 or 3 can be applied.

You could read more here..

http://msdn2.microsoft.com/en-us/library/aa902672(SQL.80).aspx

 

[johnherman]

Kimball has a nice book on Microsoft Data Warehousing which might assist you.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright