Formatting Linked Server MDX Query Results Using CTEs

By Dan Meyers

In my previous post I described how to export MDX query results in SSIS without having to use a linked server. Even though a linked server is not required and probably not advised the majority of the time, there might be times when it is actually the best solution to your problem.

One of the biggest headaches with this approach it that the data types of all of the fields returned are either nText or nVarchar. This means that you have to deal with converting and formatting the results before you can use them in a report. There are a couple of ways to go about this.

CTEs (Common Table Expressions) are the most elegant technique to employ here. Plus, they are a bit more readable than their SQL Server 2000 equivalent, Derived Tables. CTEs are only available in SQL Server 2005 and later. If you are using SQL Server 2000 then you have to use a derived table in place of the CTE.

Below is an example of using a CTE to rename and format the results of an MDX query executed against an Analysis Services cube via a linked server:

WITH MDXResults (FiscalYear, FiscalSemester, FiscalQuarter, SalesAmount, GrossProfit)
AS
(
SELECT *
FROM OPENQUERY(SSAS_AW2008,
'SELECT
{[Measures].[Sales Amount], [Measures].[Gross Profit]} ON COLUMNS,
{[Date].[Fiscal].[Fiscal Quarter].Members} ON ROWS
FROM
[Adventure Works]')
)
SELECT
CONVERT(VARCHAR(255),FiscalYear) AS FiscalYear,
CONVERT(VARCHAR(255),FiscalSemester) AS FiscalSemester,
CONVERT(VARCHAR(255),FiscalQuarter) AS FiscalQuarter,
CONVERT(FLOAT,SalesAmount) AS SalesAmount,
CONVERT(FLOAT, GrossProfit) AS GrossProfit
FROM
MDXResults



If you are using SQL Server 2000 then you have to use a derived table instead. Below is an example using the same MDX query that is used in the example above:

SELECT
CONVERT(VARCHAR(255),FiscalYear) AS FiscalYear,
CONVERT(VARCHAR(255),FiscalSemester) AS FiscalSemester,
CONVERT(VARCHAR(255),FiscalQuarter) AS FiscalQuarter,
CONVERT(FLOAT,SalesAmount) AS SalesAmount,
CONVERT(FLOAT, GrossProfit) AS GrossProfit
FROM
(
SELECT *
FROM OPENQUERY(SSAS_AW2008,
'SELECT
{[Measures].[Sales Amount], [Measures].[Gross Profit]} ON COLUMNS,
{[Date].[Fiscal].[Fiscal Quarter].Members} ON ROWS
FROM
[Adventure Works]')
) AS MDXResults (FiscalYear, FiscalSemester, FiscalQuarter, SalesAmount, GrossProfit)