SSAS: Formulate across cubes and/or databases
Hi folks,
is it possible to calculate values across different cubes located in the same or even in different SSAS-databases? I tried with just putting the cubes' name in front of the MDX and was syntactically successful, but the result was unexpectedly empty, so I assume that I have made a mistake.
Thanks for any help.
Kind regards
Markus
3 replies
-
Hi Markus,
SSAS MDX does provide a cube LOOKUP() function that allows you to lookup values and strings from another cube in the same database. However, as the MDX documentation says, it performs poorly and cube design should be reviewed if you need to use it frequently:
https://docs.microsoft.com/en-us/sql/mdx/lookupcube-mdx?view=sql-server-2017
Note that this is an MDX code construct, not something that Pyramid performs.
-
After such a long time, I have to ask the same question again - with a more general background:
Is it possible to create calculations across different models and/or databases?
For example I have access to an Azure Analysis Services Model for the Enterprise reporting (here I can find the sales values like turnover) and I have created a Pyramid-Model in "SSBI-Mode" containing the FTE and Headcount values of the company. And now I would calculate a Value like Turnover per Headcount or Turnover per FTE.
Is that possible?
Kind regards
Markus -
Hi Markus,
The answer remains the same I'm afraid. It is not currently possible to create calculations across Pyramid Models.
Of course, you can always query your databases, use the queries as data sources and build a new Pyramid model that combines the Turnover, Headcount, FTE etc., then apply the calculations required.
Hope that helps,
Ian