Design Question

Hi,

Iam having problem designing a proper solution for the current architecture, we have for a web application.

I would like to make use of Analysis Services, but not sure how to....

These are the typical course of events, which happen :

1) Data is uploaded into a maintenance database

Here during the data upload, a lot of calculations take place and some tables which contain the result of calculations are altered/populated. Usually, this takes hours to take place because of the number of rows being updated, deleted and added.(Usually in millions)

Scenario:

previous data is deleted (not all, only the required)

the tables corresponding to the uploaded data are modified(around 20 tables)

calculations are perfomed on the uploaded data

the tables corresponding to the calculations are updated.

2) The maintenance database is replicated to the production database.

This process is a pain in the neck and hence would like to use to analysis services.But Iam not sure of how to do it. In which direction should I proceed?Do I need a datawarehouse to perform the complex calculations ? Do I need to maintain separate databases, one for the calculations and one for production.

Thank you

Prash

Do you need to replicate back the calculations to the production database in order to support you webb application or are people running queries for pure analytic needs?

This can be reformulated as "are you running reports/analytic applications on your production database?

If so, I would recommend you to build a data warehouse/datamart.

What SSAS2005 can help you with is to aggregate information fast from the records in your data warehouse/dm. It can also help you with complex calculations because MDX(the query language that this product use) is stronger and requires less code than using TSQL/Stored procedures.

HTH

Thomas Ivarsson

|||

Thanks for your reply.

Do you need to replicate back the calculations to the production database in order to support you webb application or are people running queries for pure analytic needs?

Since all the calculations are done and stored in specific tables for the Reports, I just need to copy the database to production. No further calculations are involved.

Pardon me for my questions (Iam a beginner). Say, I have a dataware house which is built using the traditional snowflake/star schema model. Then I would assume that these steps need to be taken.

a) Built the relational database, such that no calculations are done

b) Built the dataware house/datamart and then perform the calculations(complex calculations) using the SSAS2005 by deploying the necessary cubes

c)Then, I could use reporting services to connect to the cubes for generating the reports.

If this is the procedure, then how do I connect my dataware house and the relational database(Integration services ?). Can you throw some light on the procedure, I need to follow.

Once again, thank you for your help.

-Prash

|||

Please see my comments:

a) Built the relational database, such that no calculations are done

You build a staging area which are tables that are only used for pumping data from your production system. The connection ,between your source system and the staging area, and the pumping of data is done by SSIS. You empty all the tables in staging area every time that you extract new source data. From the staging area you then build SSIS packages to move, clean and(or aggregate data.

b) Built the dataware house/datamart and then perform the calculations(complex calculations) using the SSAS2005 by deploying the necessary cubes

You can make some simple calculations in the data wareshouse for performance reasons. The rest is correct.

c)Then, I could use reporting services to connect to the cubes for generating the reports.

Correct. The only problem with reporting services is that it do not have the flexibility of a OLAP-client. Excel 2007 will support all functionality in SSAS2005. Reporting Services is best for standard reports.

If this is the procedure, then how do I connect my dataware house and the relational database(Integration services ?). Can you throw some light on the procedure, I need to follow.

Yes. You will use SSIS for this. Have a look at http://www.msftdwtoolkit.com/ and especially the book" The Microsoft Data Warehouse Toolkit".

HTH

Thomas

|||

Thank you. That's enough for me to get started. Can you suggest me any tutorials for building a dataware house depending on the existing relational database.

Cheers

Prash