To get data from a Multidimensional cube or a Tabular model you need to talk "MDX" (or "DAX"). By using OPENQUERY, you are able to query them via a linked server from within your T-SQL code.
The T-SQL code below shows an example of how you can query a Multidimensional cube by using dynamic T-SQL:
- To query a Multidimensional cube or Tabular model you need to create a linked server first (in the code below this linked server is called "MULTIDIMENSIONAL").
- The MDX query returns data where the column names contain square brackets. You can rename those column names by surrounding them with double quotes and giving them an alias ("[Measures].[Turnover]" AS [Turnover]).
- You can load the data in a temporary table (in this example a table variable is created) so that you can use the results further in your code/process.
- Dynamic T-SQL