MDX cross-Cube query--lookupcube

About querying data from multiple cubes in SSAS

Premise: The cube involved is in the same database, dimension sharing (best)

The ability to provide an MDX statement to query data from multiple cubes at the same time in SSAS is the LookupCube function, assuming that the two cubes are in the same database, shared dimensions (not shared dimensions can be checked and not tested)

The syntax of the LookupCube function is as follows: LookupCube (Cube_name, numeric_expression) LookupCube (Cube_name, string_expression) where cube_ Name refers to a query's cube numeric_expression refers to a numeric expression, which is a string

About the function on MSDN this explains that if a numeric expression is specified, the LookupCube function evaluates the specified numeric expression within the specified cube and returns the resulting value. If a string expression is specified, the LookupCube function evaluates the specified string expression within the specified cube and returns the resulting string. The LookupCube function processes the cube in the same database as the source cube where it executes the MDX query that contains the LookupCube function. Important: Because the context of the current query does not extend to the cube that will be queried, you must provide any necessary current members within a numeric or string expression.

One of the last hints is very important, it shows that we can not query the context of some of the key information related to the LookupCube function to deal with, but to the outside of the function to be processed before the function to query the cell value.

What do you mean, here's an example to illustrate:

If there is a "product" dimension in the cube, measuring "unit price" and "sales", we now have to query the total sales for the product.

In the same cube, we can query as follows:

 
with Member [Measures].[sales]
As
([measures]. [price], [product]. [name]. CurrentMember) * ([measures]. [quantity], [product]. [name]. CurrentMember)
Select {[measures]. [price], [measures]. [quantity], [measures]. [sales]} on 0
,
{[product]. [name]. [name], [product]. [name]} on 1
From [Sales]

WithMember [Measures]. [Sales] AS ([measures].[ Price], [Product]. [Name].currentmember] * ([measures].[ Quantity], [Product]. [name].currentmember) Select {[measures].[ Price],[measures]. [Quantity],[measures]. [Sales]} On 0,{[product].[ Name]. [Name],[Products]. [Name]} On 1from [Sales]of course there are some places where the query is superfluous, here just to illustrate the problem! In the example above, CurrentMember refers to the execution context of the loop, so if it appears in Numeric_Expression or string_expression, it will not execute correctly

The following query is incorrect:

 
with Member [Measures].[sales]
As
Lookupcube ("[profile]" ([measures]. [price], [product]. [name]. CurrentMember) * ([measures]. [number], [product]. [name]. CurrentMember) ")
Select {[measures]. [price], [measures]. [quantity], [measures]. [sales]} on 0
,
{[product]. [name]. [name], [product]. [name]} on 1
From [Sales]

With Member [Measures]. [Sales] as LookupCube ("[Profit]" "([measures].[ Price], [Product]. [Name].currentmember] * ([measures].[ Number], [Product]. [Name].currentmember)] Select {[measures].[ Price],[measures]. [Quantity],[measures]. [Sales]} On 0,{[product].[ Name]. [Name],[Products]. [Name]} On 1from [Sales]

Now assume [Measures]. [Price],[measures]. [number] is defined in a multi-bit dataset [profit], while dimensions and remaining measures are in [Sales]

The above query will not get the correct result data because LookupCube will not be able to interpret the execution context CurrentMember

So what is the correct query? The currentmember should be interpreted before lookupcube execution, and then the function will be executed as follows:

 
with Member [Measures].[sales]
as lookupcube("[profit]",
"([measures]. [price], + [product]. [name]. CurrentMember. Uniquename +")*
([measures]. [number], "+ [product]. [name]. CurrentMember. Uniquename +") ")
Select {[measures]. [price], [measures]. [quantity], [measures]. [sales]} on 0
,
{[product]. [name]. [name], [product]. [name]} on 1
From [Sales]

With Member [Measures]. [Sales] as LookupCube ("[Profit]", "([measures].[ Price], "+ [Product]. [Name].currentmember.uniquename+] * ([measures].[ Number], "+ [Product]. [name].currentmember.uniquename+] ") Select {[measures].[ Price],[measures]. [Quantity],[measures]. [Sales]} On 0,{[product].[ Name]. [Name],[Products]. [Name]} On 1from [Sales]

At this point, each cycle of the CurrentMember will be interpreted by the external after the assembly into a string to LookupCube to execute.

The above query, if there is a corresponding "[friend]" in [profit]. [Name] "Hierarchy. will see the correct result data

CurrentMember execution Context here provides a good solution to deal with, but for the other classes will be slightly more complex, here is not to introduce! such as: where axis processing, dimension node traversal ...

At the same time here is just a simple demand, as for more complex cross-cube query will be combined with the relevant string function to complete, this part has time to summarize!

Another: LookupCube currently only found in the MSAs in the function, in Mondrian, Essbase have not found (at least I did not find), do not know whether there is a similar function function.

Slob

MDX cross-Cube query--lookupcube