Concern about SSAS DATABASE Architecture ?

Question

0.00/5 (No votes)

See more:

Hello All ,

First of all I would like to describe my datawarehouse setup followed by my concern . So here is my databases(OLAP/OLTP) stucture

We have one OLTP database called as "ABC_ProductSales" on (ABC_SQL1) server where we have all the details regarding product and there sales information and this is customer facing database and update would happened on this frequently.

We have one OLAP database called as "ABC_ProductSalesDW" on (ABC_SQL2) server where we have all the dimension and fact tables related to product and sales information.

We have two cubes Product_Info and Product_Sales_Info deployed on ABC_SQL2 server and when we deployed it on to the server then It had created new OLAP database called as "ABC_ProductCubes".We can see it by connecting analysis services through SSMS.

We have created SSIS packages to

1) Load all the data from OLTP(ABC_ProductSales) to OLAP (ABC_ProductSalesDW) database after converting data for business logic.

2) Process OLAP database "ABC_ProductCubes" where we have two cubes deployed.

I am really concern Is this a needed design for datawarehouse where we have to maintain two separate databases(ABC_ProductSalesDW / ABC_ProductCubes) other than OLTP (ABC_ProductSales)?

Does OLAP(ABC_ProductCubes) database contain actual Product and sales data ? Because we are only able to see data from cube when we process it after running ETL to update ABC_ProductSalesDW database ? It does not go directly into that database.

Does it mean that If it takes 2 hours to transfer data from OLTP to OLAP then It will take same time to process the Cube database.

Also want to know what data resides in cube database ABC_ProductCubes ?

Thanks in advance.

Posted 11-Apr-12 21:43pm

RDBurmon

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mubin M. Shaikh · Accepted Answer · 2013-09-04T03:12:00

Hi,

Keep architecture simple in data warehouse, means first you can prepare you can prepare data on staging level for your dimensions and fact tables, complete denormalization of your data and population of your SCD dimension here itself. so it will keep your DW data error free.

AFter that just load your data from staging to DW and then after successful loading flush your data from staging fact.

Now cube will point to Your DW to pick data from it and it will populate data into new database from your DW.

If you preserve data in DW then it will be helpful to bring your system up anytime whenever your cube database get corrupt due to any reason, you can pick complete data and populate cube database with it.

Now Things comes method which you use to load data using SSIS in fact table and dimension table. i am loading millions of records within half an hour.you have to design ETL such like this using effective strategy, so your time will be saved.

Next Cube processing time will be improved if you do partitioning of your measure group and process latest partion using SSIS analysis service processing task.

please let me know if you need any other detail.

Regards
Mubin