olap 多维分析_将关系数据库与OLAP多维数据集链接

olap 多维分析

One of the most asked questions is how to link an OLAP cube with a relational database, or in another way how to join the result of an MDX query with a table stored in a relational database.

最受欢迎的问题之一是如何将OLAP多维数据集与关系数据库链接，或者以另一种方式如何将MDX查询的结果与关系数据库中存储的表联接。

In this article, we will illustrate how to link a SQL Server Analysis Services instance with a relational database engine using a linked server, then we will give some examples and problems troubleshooting. Examples are conducted using the AdventureWorksDW2014 sample database.

在本文中，我们将说明如何使用链接的服务器将SQL Server Analysis Services实例与关系数据库引擎链接，然后提供一些示例和疑难解答。 使用AdventureWorksDW2014示例数据库进行示例。

创建链接服务器 (Creating a linked server)

To connect an SSAS instance to the database engine, you need to create a linked server. You can do this from SSMS or using T-SQL. In this section, we will provide a step-by-step guide for both approaches.

要将SSAS实例连接到数据库引擎，您需要创建一个链接服务器。 您可以从SSMS或使用T-SQL执行此操作。 在本节中，我们将提供两种方法的分步指南。

使用SQL Server Management Studio (Using SQL Server Management Studio)

After opening SQL Server Management Studio, in Object Explorer go to Server Objects | Linked Servers as shown in the image below:

打开SQL Server Management Studio之后，在“对象资源管理器”中转到“ 服务器对象 | 链接服务器 ，如下图所示：

Right-click on the Linked Servers folder and click on New Linked Server:

右键单击“ 链接服务器”文件夹，然后单击“ 新建链接服务器” ：

In the New Linked Server dialog, you need to specify the following inputs values:

在“ 新建链接服务器”对话框中，需要指定以下输入值：

• Linked server: the name of the linked server object to be used in SQL Server 链接服务器： SQL Server中要使用的链接服务器对象的名称
• Provider: you have to select “Microsoft OLE DB Provider for Analysis Services <xx.x>”. 提供程序：您必须选择“ Analysis Services <xx.x>的Microsoft OLE DB提供程序”。 (Where <xx.x> is the SQL server version) （其中<xx.x>是SQL Server版本）
• Product name: for analysis services, 产品名称：对于分析服务，必须使用MSOLAP must be used MSOLAP
• Data source: the name of the Analysis Services instance installed 数据源：已安装的Analysis Services实例的名称
• Catalog: the name of the analysis database that contains the OLAP cube 目录：包含OLAP多维数据集的分析数据库的名称

After creating the linked server, you can browse its content from Object Explorer:

创建链接服务器后，可以从对象资源管理器中浏览其内容：

The objects shown under the Analysis Service catalog are the dimensions and measures of the OLAP cube. But these objects are useless since they cannot be queried using a simple SELECT query since you need to pass an MDX expression to retrieve data from the OLAP cube.

Analysis Service目录下显示的对象是OLAP多维数据集的尺寸和度量。 但是这些对象没有用，因为无法使用简单的SELECT查询来查询它们，因为您需要传递MDX表达式以从OLAP多维数据集中检索数据。

使用T-SQL (Using T-SQL)

Another method is to create the linked server using a SQL command. You have to use the master.dbo.sp_addlinkedserver and master.dbo.sp_addlinkedsrvlogin stored procedures as following:

另一种方法是使用SQL命令创建链接服务器。 您必须使用master.dbo.sp_addlinkedserver和master.dbo.sp_addlinkedsrvlogin存储过程，如下所示：

EXEC master.dbo.sp_addlinkedserver @server = N'SSAS_LINKEDSERVER', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'MYLAPTOP\MSSQL', @catalog=N'AdventureWorks2014'

Where @server is the linked server object name, @srvproduct is the product name, @provider is the provider, @datasrc is the data source and @catalog is the initial catalog.

其中@server被链接服务器对象名，@srvproduct是，产品名称，@provider是提供程序，@datasrc是数据源和@catalog是初始目录。

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS_LINKEDSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

The second command is used to add a default login for the linked server; it will use the current user credentials used to connect to the database engine.

第二个命令用于为链接服务器添加默认登录名。 它将使用用于连接数据库引擎的当前用户凭据。

从T-SQL查询执行MDX查询 (Executing MDX query from T-SQL query)

In order to retrieve data from an OLAP cube, you need to write a multidimensional expression (MDX) command which cannot be done using SQL statements. So, you cannot use a select statement from the linked server object to retrieve data from the cube.

为了从OLAP多维数据集中检索数据，您需要编写一个多维表达式（MDX）命令，该命令无法使用SQL语句完成。 因此，您不能使用链接服务器对象中的select语句从多维数据集中检索数据。

To solve this problem, you need to use OPENQUERY() option to send and MDX command to the Analysis Services instance and to bring the query result into the Database Engine.

要解决此问题，您需要使用OPENQUERY（）选项将MDX命令发送到Analysis Services实例，并将查询结果带入数据库引擎。

Another thing worth to mention is that using OPENQUERY() – in general – is more efficient than querying the linked server via T-SQL since it sends the command to be executed on the linked server instead of bringing all data to the Database Engine and then querying this data. To read more information about each approach you can refer to the following article: Querying remote data sources in SQL Server.

值得一提的另一件事是，使用OPENQUERY（）通常比通过T-SQL查询链接服务器更有效，因为它发送要在链接服务器上执行的命令，而不是将所有数据带到数据库引擎，然后查询此数据。 要阅读有关每种方法的更多信息，可以参考以下文章： 在SQL Server中查询远程数据源 。

OPENQUERY() takes two parameters: (1) the linked server object and (2) the statement that we need to execute on this linked server.

OPENQUERY（）具有两个参数：（1）链接服务器对象和（2）我们需要在此链接服务器上执行的语句。

You can store the result of the command passed by OPENQUERY() into a table using the SELECT INTO statement. As an example:

您可以使用SELECT INTO语句将OPENQUERY（）传递的命令结果存储到表中。 举个例子：

SELECT * 
INTO #TBLTEMP
FROM OPENQUERY(SSAS_LINKEDSERVER,'SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works DW2014]')

From the image above, you can see that the column names of the result table are fully qualified and it is indicated whether it belongs to measures or to a dimension.

从上面的图像中，您可以看到结果表的列名是完全限定的，并指明了它是属于度量还是维度。

After that the MDX command result is stored within a table, you can simply integrate it with other relational database objects such as other tables, views, functions…

在将MDX命令结果存储在表中之后，您可以将其与其他关系数据库对象（例如其他表，视图，函数）简单地集成在一起。

如何执行长度大于8000的MDX查询？ (How to execute MDX queries with a length bigger than 8000?)

One of the main limitations of OPENQUERY() is that it can execute a command having a length less or equal to 8000 or it will be truncated. If you need to execute a similar command you can use the EXECUTE() AT method since it can execute a command of type VARCHAR(MAX).

OPENQUERY（）的主要限制之一是它可以执行长度小于或等于8000的命令，否则它将被截断。 如果您需要执行类似的命令，则可以使用EXECUTE（）AT方法，因为它可以执行VARCHAR（MAX）类型的命令。

As shown in the example below, first you have to store the whole command within a variable of type VARCHAR(MAX) then execute it:

如下例所示，首先必须将整个命令存储在VARCHAR（MAX）类型的变量中，然后执行它：

DECLARE @str VARCHAR(MAX)
 
SET @str = 'SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS,NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works DW2014]'
    
EXECUTE (@str) AT SSAS_LINKEDSERVER

You may encounter the following error the first time you try to use this approach:

第一次尝试使用这种方法时，您可能会遇到以下错误：

Server ‘SSAS_LINKEDSERVER’ is not configured for RPC

未为RPC配置服务器“ SSAS_LINKEDSERVER”

This can be solved by setting the RPC and RPC Out properties to true in the Linked Server Properties as shown in the image below:

可以通过在链接服务器属性中将RPC和RPC Out属性设置为true来解决此问题，如下图所示：

For more information and troubleshooting for EXECUTE() AT method, you can refer to the following TechNet Wiki page: SQL Server – Execute At Linked Server.

有关EXECUTE（）AT方法的详细信息和疑难解答，您可以参考下面的TechNet Wiki页面： SQL Server –在链接服务器上执行 。

如何在不知道结果表结构的情况下将数据插入表中？ (How to insert data into a table without knowing the result table structure?)

Another issue is that if we need to insert the result of the EXECUTE() AT method, we need to know the table structure since we are not able to use SELECT INTO statement to create the table based on the query result. We can only use INSERT INTO … EXECUTE which requires that the table already exists.

另一个问题是，如果需要插入EXECUTE（）AT方法的结果，则需要了解表结构，因为我们不能使用SELECT INTO语句根据查询结果创建表。 我们只能使用INSERT INTO…EXECUTE，这要求该表已经存在。

To solve this problem, we must first pass an MDX command that returns a small chunk of data using OPENQUERY() to retrieve the table structure from the OLAP cube. Next, we use the TRUNCATE() statement to clear data, then we use INSERT INTO … EXECUTE to pass the long command.

要解决此问题，我们必须首先传递一个MDX命令，该命令使用OPENQUERY（）返回一小部分数据，以从OLAP多维数据集中检索表结构。 接下来，我们使用TRUNCATE（）语句清除数据，然后使用INSERT INTO…EXECUTE传递long命令。

例 (Example)

--Using OPENQUERY() to retrieve the table structure
SELECT * 
INTO #TBLTEMP
FROM OPENQUERY(SSAS_LINKEDSERVER,' SELECT NON EMPTY { [Measures].[Discount Amount], [Measures].[Order Quantity] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Product].[Product Key].&[606] } ) ON COLUMNS FROM [Adventure Works DW2014])')
    
--Clear data from created table
TRUNCATE TABLE #TBLTEMP
    
--Execute the MDX command 
DECLARE @str VARCHAR(MAX)
    
SET @str = ' SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Product].[Product Key].&[1], [Product].[Product Key].&[2], [Product].[Product Key].&[3], [Product].[Product Key].&[4], [Product].[Product Key].&[5], [Product].[Product Key].&[6], [Product].[Product Key].&[7], [Product].[Product Key].&[8], [Product].[Product Key].&[9], [Product].[Product Key].&[10], [Product].[Product Key].&[11], [Product].[Product Key].&[12], [Product].[Product Key].&[13], [Product].[Product Key].&[14], [Product].[Product Key].&[15], [Product].[Product Key].&[16], [Product].[Product Key].&[17], [Product].[Product Key].&[18], [Product].[Product Key].&[20], [Product].[Product Key].&[19], [Product].[Product Key].&[21], [Product].[Product Key].&[22], [Product].[Product Key].&[23], [Product].[Product Key].&[24], [Product].[Product Key].&[25], [Product].[Product Key].&[26], [Product].[Product Key].&[27], [Product].[Product Key].&[28], [Product].[Product Key].&[29], [Product].[Product Key].&[30], [Product].[Product Key].&[31], [Product].[Product Key].&[32], [Product].[Product Key].&[33], [Product].[Product Key].&[34], [Product].[Product Key].&[35], [Product].[Product Key].&[36], [Product].[Product Key].&[37], [Product].[Product Key].&[39], [Product].[Product Key].&[38], [Product].[Product Key].&[40], [Product].[Product Key].&[41], [Product].[Product Key].&[42], [Product].[Product Key].&[43], [Product].[Product Key].&[44], [Product].[Product Key].&[45], [Product].[Product Key].&[46], [Product].[Product Key].&[47], [Product].[Product Key].&[48], [Product].[Product Key].&[49], [Product].[Product Key].&[50], [Product].[Product Key].&[606], [Product].[Product Key].&[605], [Product].[Product Key].&[604], [Product].[Product Key].&[603], [Product].[Product Key].&[602], [Product].[Product Key].&[601], [Product].[Product Key].&[600], [Product].[Product Key].&[599], [Product].[Product Key].&[597], [Product].[Product Key].&[598], [Product].[Product Key].&[596], [Product].[Product Key].&[595] } ) ON COLUMNS FROM [Adventure Works DW2014])'
    
INSERT INTO #TBLTEMP
EXECUTE (@str) AT SSAS_LINKEDSERVER
    
--Retrieving data
SELECT * FROM #TBLTEMP

其他SSAS陈述 (Other SSAS statements)

One last thing worth to mention is that you can use the methods we illustrated in this article to execute other statements supported by SSAS such as XMLA and DMX queries.

最后值得一提的是，您可以使用本文中介绍的方法来执行SSAS支持的其他语句，例如XMLA和DMX查询。

结论 (Conclusion)

In this article, we described how to link the result of an MDX query executed over an OLAP cube with data stored within a relational database. We mentioned two methods of executing MDX queries from T-SQL queries and how to store result within a relational database.

在本文中，我们描述了如何将通过OLAP多维数据集执行的MDX查询的结果与关系数据库中存储的数据进行链接。 我们提到了两种从T-SQL查询执行MDX查询的方法，以及如何在关系数据库中存储结果。

翻译自: https://www.sqlshack.com/linking-relational-databases-with-olap-cube/

olap 多维分析