Exporting MDX queries to SQL Relational Databases using SSIS 2005

Businesses that employ Business Intelligence solutions often have related sets of data in different locations. One scenario I have encountered recently is a company that employed both SQL Server Analysis Services 2005 (Multi-dimensional cube) solutions and SQL Server 2005 relational databases. In many instances this company needed to combine data from both sources to deliver reports using SQL Server Reporting Services. This would be simple enough if each data source (cube and database relational tables) provided a complete set of data – however the problem was that the data from the Cube had to be joined to the data from the relational database tables and calculations applied before the combined data could be fed to Reporting Services.

The solution was to find a way data from MDX queries could be exported into relational database tables. The data transformation tool of choice was SQL Server Integration Services (SSIS), however in 2005 version there is no SSIS tool to facilitate this. Here I explain the solution I came up with.

To show how useful this solution can be I will use a complex MDX query, which also helps demonstrate how we will handle the conversion errors that may occur during the process.