对多个相同的数据库使用数据仓库 [英] use datawarehouse against multiple identical database

问题描述

我目前正在研究一个使用ERP数据库中的SSIS 2008 r2构建数据集市的项目.

这些数据集市必须在具有相同结构(相同表/字段)的每个数据库中使用.

问题在于源表名称根据所连接的数据库而变化.但是表结构(元数据)在这些环境之间不会改变.

例如
数据库1 项目表名是 company_name1 $ item
数据库2 项目表名是 company_name2 $ item

我正在寻找一种使datamart可以用于两个数据库的解决方案.

我做了一些研究；我发现我的解决方案可以像图片中解释的那样
！[在此处输入图片描述] [1]

正如您在图中所示，有以下步骤来构建数据集市:

解决方案图图像

-要构建数据集市，您将提取信息并进行处理
他们通过ETL获得所需的数据集市.此过程需要
应用于所有数据库




-此过程可以通过erp数据库中名为
的表来完成 对象此表包含有关数据库的所有信息
表，但最有用的字段是表ID，表名称和公司
名称

对象表图像

现在，我想使用此表ID(名称公司)来选择适当的表，而不必根据表名选择数据，但是对象表和其他表之间没有链接，因此您需要创建某种代码或能够创建链接的东西.
*简而言之，当我想使用数据库包时，我插入的是公司名称，该软件包将运行以填充我的数据集市.*

如一些建议，我应该使用这种类型的sql过程

I''m currently working on a project of building a data mart using SSIS 2008 r2 from ERP database.

These datamarts will have to be used in every database that has the same structure (same tables/fields).

The problem is that the source table name changes based on the database connected to. But the table structure (metadata) does not change between those environments.

For example,
Database 1 item table name is company_name1$item
Database 2 item table name is company_name2$item

I''m looking for a solution that makes the datamart can be used for both databases.

I did some research; I found that my solution can be like explained in the picture
![enter image description here][1]

As you in the diagram picture, there are steps to follow to build the datamart:

solution diagram image

- To build a datamart, you will to extract the informations and process
them through ETL to have the desired datamart. This procedure needs
to be applied to all databases




- This procedure can be done by a table in the erp database called
object This table has all kind of informations about the database
tables but the most useful fields are table id,table name and company
name

object table image

Now, I want to use this table id, Name Company to select the proper table without having to selected data based on table name but there no linking between object table and other tables, so you kind of need to create a some kind of code or something to be able to create the link.
*In a nut shell, when I want to use package for a databse , I have insert is the company name and the package will be run to fill my datamart.*

As some suggested, I should use a sql procedure of this type

USE YourDataBaseName;
  CREATE PROCEDURE GetMyData
        @aCompany NVARCHAR(50) = NULL,
        @tbl_ID INT = 0
    AS
        SET NOCOUNT ON;
        DECLARE @tableName NVARCHAR(50)
        DECLARE @aQuery NVARCHAR(4000) 
     
        SELECT @tableName = [Name]
        FROM [object]
        WHERE [Company Name] = @aCompany AND [ID] = @tbl_ID
     
        SET @aQuery = 'SELECT * FROM ' + @tableName
        EXEC(@aQuery)
    GO

另一个有趣的是，说SSIS允许在源和目标中使用变量名.我只需要提供改变价值的机制即可.也许那是一个foreach枚举器，其中包含数据流或在运行时设置的参数.

两种建议的解决方案都很棒，但仍不清楚如何做到这一点，我希望现在我已经解释了我的问题，我将得到一个更具体的答案

Other one which was also interesting, said that SSIS allows for the use of a variable name in your source and destinations. I just simply need to provide the mechanism for making that value change. Perhaps that''s a foreach enumerator containing the data flow or a parameter set at run-time.

Both suggested solution are great but still not clear on how to do that , I hope now that I have explained my question, I would get a more specific answer

推荐答案

item
数据库2 项目表名是 company_name2

item
Database 2 item table name is company_name2

项目

我正在寻找一种使datamart可以用于两个数据库的解决方案.

我做了一些研究；我发现我的解决方案可以像图片中解释的那样
！[在此处输入图片描述] [1]

正如您在图中所示，有以下步骤来构建数据集市:

解决方案图图像

-要构建数据集市，您将提取信息并进行处理
他们通过ETL获得所需的数据集市.此过程需要
应用于所有数据库




-此过程可以通过erp数据库中名为
的表来完成 对象此表包含有关数据库的所有信息
表，但最有用的字段是表ID，表名称和公司
名称

对象表图像

现在，我想使用此表ID(名称公司)来选择适当的表，而不必根据表名选择数据，但是对象表和其他表之间没有链接，因此您需要创建某种代码或能够创建链接的东西.
*简而言之，当我想使用数据库包时，我插入的是公司名称，该软件包将运行以填充我的数据集市.*

如一些建议，我应该使用这种类型的sql过程

item

I''m looking for a solution that makes the datamart can be used for both databases.

I did some research; I found that my solution can be like explained in the picture
![enter image description here][1]

As you in the diagram picture, there are steps to follow to build the datamart:

solution diagram image

- To build a datamart, you will to extract the informations and process
them through ETL to have the desired datamart. This procedure needs
to be applied to all databases




- This procedure can be done by a table in the erp database called
object This table has all kind of informations about the database
tables but the most useful fields are table id,table name and company
name

object table image

Now, I want to use this table id, Name Company to select the proper table without having to selected data based on table name but there no linking between object table and other tables, so you kind of need to create a some kind of code or something to be able to create the link.
*In a nut shell, when I want to use package for a databse , I have insert is the company name and the package will be run to fill my datamart.*

As some suggested, I should use a sql procedure of this type

USE YourDataBaseName;
  CREATE PROCEDURE GetMyData
        @aCompany NVARCHAR(50) = NULL,
        @tbl_ID INT = 0
    AS
        SET NOCOUNT ON;
        DECLARE @tableName NVARCHAR(50)
        DECLARE @aQuery NVARCHAR(4000) 
     
        SELECT @tableName = [Name]
        FROM [object]
        WHERE [Company Name] = @aCompany AND [ID] = @tbl_ID
     
        SET @aQuery = 'SELECT * FROM ' + @tableName
        EXEC(@aQuery)
    GO

另一个有趣的是，说SSIS允许在源和目标中使用变量名.我只需要提供改变价值的机制即可.也许那是一个foreach枚举器，其中包含数据流或在运行时设置的参数.

两种建议的解决方案都很棒，但仍不清楚如何做到这一点，我希望现在我已经解释了我的问题，我会得到一个更具体的答案

Other one which was also interesting, said that SSIS allows for the use of a variable name in your source and destinations. I just simply need to provide the mechanism for making that value change. Perhaps that''s a foreach enumerator containing the data flow or a parameter set at run-time.

Both suggested solution are great but still not clear on how to do that , I hope now that I have explained my question, I would get a more specific answer

我认为您将需要视图返回所需的数据，但在所有数据库中都称为相同的视图.为了帮助组织，您应该将视图放置在dbo之外的其他模式中.

唯一的问题是粗鲁的操作.您打算更新表格吗?

I think you are going to need views that return the data you want but are called the same in all database. To help organisation you should put the views in a schema other than dbo.

The only problem with this is crud operations. Are you planning to update the tables?

这篇关于对多个相同的数据库使用数据仓库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！