具有多个数据库的SSAS多维数据集 [英] SSAS cube with multiple DB

问题描述

我有3个结构相同但数据不同的数据库，因为它们来自不同的客户端.

I have 3 databases with the same structure, but different data, since they are from different clients.

现在，我有一个现有的SSAS项目.其数据源视图，多维数据集和维度只能使用或访问一个数据库.

Now, I have an existing SSAS project. Its Data Source Views, Cubes and Dimensions can only use or access one DB.

• 我想要的是能够使用具有相同结构的多个数据库，并使用它们创建一个多维数据集.
• 每个客户端还必须能够使用多维数据集，但它们只能看到自己的数据.

这些可能吗?您能提供一些见解和一些有用的参考吗?

Are these possible? Can you please provide insights and some useful references?

推荐答案

简单解决方案 解决此问题的最简单方法是仅拥有三个Analysis Services数据库.安装将很容易，您将只有三个结构相同的数据库，无需管理多维数据集内的安全性，只需访问多维数据集即可.它易于管理，并且难以出错，从而使用户可以访问他们不应该看到的数据.而且，由于不允许任何人访问其他公司的数据，因此不需要一个通用的多维数据集.

Easy Solution The easiest way to solve this would be to just have three Analysis Services databases. Setup would be easy, you would have just three structurally identical databases, and no need to manage security within the cubes, only access to the cube. It is easy to manage, and difficult to make errors allowing users to get access to data they should not see. And as nobody should be allowed to access data form other companies, there is no need for one common cube.

只需使用不同的Analysis Services数据库名称部署项目三次即可. 然后更改已部署数据库的数据源对象，使其指向不同的关系数据库.

Just deploy your project three times using a different Analysis Services database name. Then change the data source object of the deployed databases to point to the different relational databases.

第一步，在Business Intelligence Development Studio中，右键单击解决方案资源管理器中的项目节点，选择底部条目(属性")，然后选择部署".在这里，您可以输入用于部署解决方案的服务器以及数据库名称.关闭对话框后，再次右键单击项目节点，然后选择部署".使用三个不同的数据库名称重复此步骤.

For the first step, in Business Intelligence Development Studio, right click on the project node in Solution Explorer, select the bottom entry ("Properties"), and then select "Deployment". Here, you can enter the server to deploy the solution to, as well as the database name. After closing the dialog, right click on the project node again, and select Deploy. Repeat this step, using three different database names.

然后，连接到SQL Server Management Studio中的Analysis Services服务器，打开每个数据库，然后编辑每个数据库的数据源对象以指向其关系数据库. 之后，重新处理Analysis Services数据库.

Then, connect to your Analysis Services server in SQL Server Management Studio, open each database, and edit the data source object of each database to point to its relational database. After that, re-process the Analysis Services database.

或者，您也可以在BIDS中执行所有操作，即. e.在更改要部署和部署的目标数据库之间，在此更改数据源，以及在部署之后，可能会重新处理Analysis Services数据库.

Alternatively, you can also do everything in BIDS, i. e. between changing the target database for deployment and deploying, change the data source there, and after deployment, possibly, re-process the Analysis Services database.

如果您假设需要多次更改和部署多维数据集定义，则可能可以使用可以在项目属性对话框中使用配置管理器"按钮进行编辑的配置.您将具有三种配置，每个目标Analysis Services数据库一个.您可以在每个部署的工具栏的下拉列表中选择一种配置，而无需一次又一次地编辑属性.

If you assume you will need to change and deploy the cube definition several times, you probably could make use of configurations which you can edit in the project properties dialog using the "Configuration Manager" button. You would have three configurations, one for each target Analysis Services database. You could select one of the configurations in the dropdown list in the toolbar for each deployment without the need to edit properties again and again.

如果您需要经常执行此操作，我认为通过XMLA或AMO或在PowerShell中自动执行更改数据库并重新处理多维数据集的步骤并不难.但是要实现这一点，将是另一个问题.

If you need to do this often, I think it would not be difficult to automate the steps to change the database and reprocess the cube, either via XMLA, or via AMO, or in PowerShell. But to implement this this would be another question.

更复杂的解决方案

如果您确实希望将所有内容都放在一个多维数据集中，则必须在数据源视图中具有来自不同源的表的并集.如果所有三个关系数据库都在同一个SQL Server实例上，则可以将其定义为数据源视图中的命名查询，也可以定义为其中一个数据库中的视图，甚至可以将其定义为单独关系中的视图或表数据库.您可以以NameOfDB.Schema.Tablename的形式访问在SQL Server的同一实例中运行的另一个数据库中的表或视图.

If you really want to have everything in one cube, then you will have to have a union of the tables from the different sources in the data source view. If all three relational databases are on the same SQL Server instance, you can define this either as a named query in the data source view, or as a view in one of the databases, maybe even better as a view or table in a separate relational database. You can access a table or view from another database running in the same instance of SQL Server in the form NameOfDB.Schema.Tablename.

如果这些数据库位于不同的实例上，则可以使用链接的服务器.

In case these databases are on different instances, you could use linked servers.

当然，您将必须管理这些不同数据库中的密钥，以便同一维条目具有相同的密钥，而不同维条目具有不同的密钥.而且，您将必须在多维数据集中设置安全性，以使任何用户都无法看到不该被看到的数据.

And of course, you will have to manage the keys in these different databases so that the same dimension entry has the same key, and different dimension entries have different keys. And you will have to set up security in the cube so that no user can see data that is not meant to be seen.

虽然您可以在Analysis Services中为不同的表或Analysis Services中的不同查询使用不同的数据源对象，但每个对象仅使用一个，实际上，这是一条发送到该源的SQL语句.维度需要基于一个数据源视图对象，例如一个命名查询，视图或表.对于事实表，您可以使用分区来解决此问题，而对于维度则不能.

While you could use different data source objects in Analysis Services for different tables or named queries in Analysis Services, each of these only uses one, as actually, this is one SQL statement that is sent to this source. And dimensions need to be based on one data source view object like one named query, view, or table. For fact tables, you could get around this using partitions, but not for dimensions.

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