关注SSAS数据库架构? [英] Concern about SSAS DATABASE Architecture ?

问题描述

大家好，

首先，我想描述一下我的数据仓库设置，然后是我的关注.这是我的数据库( OLAP/OLTP )结构

我们在(ABC_SQL1)服务器上有一个名为"ABC_ProductSales" 的OLTP数据库，其中包含有关产品和销售信息的所有详细信息，这是面向客户的数据库，将进行更新经常发生这种情况.

在(ABC_SQL2)服务器上，我们有一个名为"ABC_ProductSalesDW" 的OLAP数据库，在该数据库中，我们具有与产品和销售信息相关的所有维度和事实表.

我们有两个多维数据集 Product_Info 和 Product_Sales_Info 部署在 ABC_SQL2 服务器上，并且在部署时它将它放到服务器上，然后它创建了一个名为"ABC_ProductCubes" 的新OLAP数据库.我们可以通过SSMS连接分析服务来查看它.

我们已经创建了SSIS包以

1)将数据转换为业务逻辑后，将所有数据从 OLTP(ABC_ProductSales)加载到 OLAP(ABC_ProductSalesDW)数据库.
2)处理OLAP数据库"ABC_ProductCubes" ，我们在其中部署了两个多维数据集.

我真的很担心这是数据仓库的必需设计，在该仓库中，我们必须维护两个单独的数据库( ABC_ProductSalesDW / ABC_ProductCubes )，而不是OLTP( ABC_ProductSales )?

OLAP(ABC_ProductCubes)数据库是否包含实际的产品和销售数据?因为我们只能在运行 ETL 以更新 ABC_ProductSalesDW 数据库后处理多维数据集时才能看到其数据?它不会直接进入该数据库.

这是否意味着如果从 OLTP到OLAP传输数据需要2个小时，那么处理Cube数据库将花费相同的时间.

还想知道什么数据驻留在多维数据集数据库中 ABC_ProductCubes 吗?

预先感谢.

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.

推荐答案

使数据仓库中的体系结构保持简单，这意味着首先您可以准备，可以在阶段级为维度和事实表准备数据，在这里完全对数据进行非规范化并填充SCD维度.因此，它将使您的DW数据无误.

之后，仅将数据从登台加载到DW，然后在成功加载后将数据从登台事实中刷新.

现在，多维数据集将指向您的DW以从中选择数据，并将其从DW填充到新数据库中.

如果您将数据保留在DW中，那么无论何时由于任何原因导致多维数据集数据库损坏，都可以随时启动系统，则可以选择完整的数据并用它填充多维数据集数据库.

现在，Things成为一种方法，您可以使用它在事实表和维度表中使用SSIS加载数据.我要在半小时内加载数百万条记录.您必须使用有效的策略来设计这样的ETL，这样才能节省您的时间.

如果您对度量值组进行分区并使用SSIS分析服务处理任务来处理最新的分区，则下一个多维数据集的处理时间将得到改善.


如果您需要其他详细信息，请告诉我.

问候
穆宾

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

这篇关于关注SSAS数据库架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！