Posted - 04/12/2012 : 02:30:01
| Hello All ,
First of all I would like to describe my data warehouse setup followed by my concern . So here is my databases(OLAP/OLTP) structure.
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.