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.

With Set [Not Active Departments] AS

' {[MyDepartment].&[5],

[MyDepartment].&[6],

[MyDepartment].&[7],

[MyDepartment].&[8]}'

Set [Filtered Departments] AS

'{Except([MyDepartment].Members,{[ Not Active Departments]})}'

Select

{

{[Time Period].[Time Period].&[Actual],

[Time Period].[Time Period].[Period to Date],

[Time Period].[Time Period].[Year To Date]}*

{[Version].[Time].&[1],

[Version].[Time].[Forecast],

[Version].[Time].[Budget],

[Version].[Time].[Actuals Vs LY %]}

} On Columns ,

{

{{[Measures].[Sales RV]}*

{{[Channel].[Channel].[Retail],

[Channel].[Channel].[Direct],

[Channel].[Channel].[All Channel]}

},

{

[Measures].[Store Stock RV]}*{

[Channel].[Channel].[All Channel]

}}

}

On Rows

From [MyCube]

WHERE ( [Current Financial Week].Item(0).PrevMember,

[Area].&[1])

Using a complex MDX query also demonstrates that you can use the power of a multi-dimensional cube querying to handle complex business logic and still extract data into a relational database where you can combine the processed data with data in other database tables! Writing the same query in SQL would be a very time consuming task.

Running this MDX query in SQL Server Management Studio (SSMS) gives me this:

This MDX query queries for Previous Week Sales by Sales Channel (Retail (Shops) or Direct (Internet)), Previous Week Sales Total and Previous Week Stock Totals by Version (Actual, Budget, Forecast, Actual Vs Last Year %) over various Time Periods (Period (Months) to date and Year to date) for Area 1, excluding Departments 5, 6, 7 and 8. [SJ1]

Think of writing that in SQL!

The next step is getting this data into a relational database table. Start a new SSIS Project and drag the Data Flow Task onto the Control Flow design area. Name it From MDX to SQL.

Right Click the Data Flow Task and select Edit to go to the Data Flow designer. Drag an OLE DB Source to the design area, and name it MDX Source.

Right Click and select Edit which brings up the OLE DB Source Editor. Click New next to the OLE DB Connection Manager drop down and in the Configure Dialogue click New to bring up the Connection Manager dialogue.

Change the Provider to Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0, your Analysis Services Server name, and in the Initial Catalog drop dropdown select your Analysis Services database. Now very important – Click the Connection Properties tab:

You need to tell the Connection Manager to return the query in tabular format, so in the Extended Properties text box enter Format=Tabular

Click OK until you return to the OLE DB Source Editor. In the Data access mode drop down box select SQL Command, and copy and paste the whole MDX Query into the SQL command text box.

Now click the Columns menu item, and you will get a warning box, one for each column where SSIS will complain about not being able to map the data type and saying it will use DT_WSTR data type instead. This is fine, it appears SSIS is unable to automatically derive data types from an MDX query. We will fix this later. Click OK to see that SSIS has converted the MDX query into columns. We can now use this to design the table that will receive the data in the relational database. Notice also that the columns containing the row descriptions are suffixed with [MEMBER CAPTION].

When creating your database table you may be tempted to design columns with the expected data types – although this may work, I advise against it. This is because the MDX query will often return MDX arithmetic exceptions in the way it displays them in SSMS – e.g.NaN, 1.#IND, 1.#INF.This will causeconversion into say decimal or integer to fail. We will handle this problem later so for the time being create a table with a VARCHAR (MAX) column for each Output column from the MDX query. You can also give the database table columns simpler names rather than those the MDX Query creates which are in MDX syntax.

CREATE TABLE [dbo].[MDXtoSQL](

[MEASURE] [varchar](max)NULL,

[CHANNEL] [varchar](max)NULL,

[Actual Actual] [varchar](max)NULL,

[Actuals Forecast] [varchar](max)NULL,

[Actuals Budget] [varchar](max)NULL,

[Actuals Actual Vs LY %] [varchar](max)NULL,

[Period To Date Actual] [varchar](max)NULL,

[Period To Date Forecast] [varchar](max)NULL,

[Period To Date Budget] [varchar](max)NULL,

[Period To Date Actual Vs LY %] [varchar](max)NULL,

[Year To Date Actual] [varchar](max)NULL,

[Year To Date Forecast] [varchar](max)NULL,

[Year To Date Budget] [varchar](max)NULL,

[Year To Date Actual Vs LY %] [varchar](max)NULL

) ON [PRIMARY]

Click the Error Output menu item. Here you need to decide how to deal with any errors. With most MDX queries you will not have to worry about this, but if you want to you can tell SSIS to ignore failures which I recommend. Click OK and return to the Data Flow designer. Don’t worry about the warning symbol that appears on the OLE DB Source.

Next is to carry out the necessary data conversion between MDX data types and SQL data types. Drag the Data Conversion Task onto the Data Flow designer. Drag a Green connector (Flow Path) to it from the OLE DB Source.

Edit the Data Conversion task. In the Transformation Editor you will see the MDX Query columns in the upper pane. Select them all and change the Output Alias values for each column to match the column names of your database table.

Important – Change the Data Type for each column to string[DT_STR] length 255. Click OK to exit the Transformation Editor.

Drag an OLE DB Destination item onto the Data Flow task designer. Name it SQL Destination and drag a Green connector to it from the Data Conversion task. Right click the OLE DB Destination item and select Edit. Select your relational database server and database in the Connection Manager, leave the Data access mode to Table or View, and select the table you just cre

In SSIS create a variable that holds the Exponential table name and then follow the same design process we did with the MDX Exceptions, but this time in the first Execute SQL Task select the exponential column from your exponential table.

Recall that we have extracted the data to VARCHAR data types in the database, so create a database function that converts VARCHARs to DECIMAL and looks for the values preceding the exponential notation if it exists in the column:

CREATE FUNCTION [dbo].[fnOLAPConvertVARtoDEC]

(

@Actual VARCHAR(MAX)

)

RETURNS DECIMAL(20,4)

AS

BEGIN

DECLARE @ResultVar DECIMAL(20,4)

SET @ResultVar = CASE

WHEN CHARINDEX('E',@Actual)> 0 THEN

ISNULL(CONVERT(DECIMAL(20,4),LEFT(@Actual,CHARINDEX('E',@Actual)-1)),0)

ELSE

ISNULL(CONVERT(DECIMAL(20,4),(@Actual)),0)

END

RETURN @ResultVar

END

You can now use this function in the SSIS package in the script task to replace the exponential values with their decimal value. Remember to pass the exponential table variable to the script in addition to the other 2 variables. In the script you can assign the following to the p_sql variable we used earlier

p_sql = "UPDATE [dbo].[" & tableName & "] " & _

"SET [" & ColumnName & "] = " & _

"dbo.fnOLAPConvertVARtoDEC([" & ColumnName & "])* a.[MultiplierVal] " & _

"FROM [dbo].[" & tableName & "] " & _

" LEFT JOIN ( " & _

" SELECT Expotential, [Multiplier] as MultiplierVal " & _

" from [dbo].[mdx_Expotential_Errors] " & _

" WHERE [Expotential] = '" & MDXExpotential & "') AS a " & _

" ON RIGHT([" & ColumnName & "],3) = a.Expotential " & _

" WHERE RIGHT([" & ColumnName & "],3) = '" & MDXExpotential & "'"

Now that you have clean data you can create another table with the data types you need and feed the clean data into it while converting the values to your chosen data types.

I hope this article has shown you the power SSIS has when it comes to transforming data from an MDX query into SQL tables. One modification you may consider is building the MDX query in a Script, passing it to a variable which is then passed to the Execute SQL Task command. The advantage with this method is you can pass parameters to the scripted MDX query and for example loop through multiple departments in SSIS. Your database table will then contain the combined data from multiple departments using the same MDX query.

ated. Click Mappings and because you renamed the columns in the Data Conversion Task to match the database table, SSIS should automatically map the columns correctly. Click OK.

Save and execute the Package. When execution completes, check your database table and you will see the extracted data.

We now need to clean this data. The Conversion from MDX to SQL table data creates 2 types of errors – MDX arithmetic exceptions which I have explained above and Exponential Values. Where the output is a value too large or small to be shown in decimal notation, it is written in exponential format e.g. -3.6355174668697212E-4. We need to present these values in their decimal format in the database.

Let’s start with the MDX arithmetic exceptions. Create a table in your database called mdx_Exceptions with a column that contains an MDX arithmetic exception in each row. In SSIS we will loop through the table mdx_Exceptions rows and replace the MDX arithmetic exceptions with a chosen value – in this case 0 – in other implementations you may decide to do something else with these exceptions e.g. remove the row.

In SSIS drop An Execute SQL Task item onto the Control Flow designer and call it Get MDX Exceptions. Drag a Green process flow from the Data Flow Task to the new SQL Task item. Edit the SQL Task. Set the Connection to your database. Set the ResultSet to Full Result Set, and enter the SQLStatement as

SELECT * FROM mdx_Exceptions.

Click the Result Set menu item and click Add, change the Result Name to 0, and in the Variable Name column, select the drop down box to add a new variable. The new variable will hold the MDX arithmetic exceptions so make it Value Type Object and call it MDXAritmeticExceptions. Click OK.

Now drop a Foreach Loop Container onto the designer and drag a green process flow from Get MDX Exceptions. Call the Foreach Loop Container For Each MDX Exception and edit it. Set its Collection to a Foreach ADO Enumerator, and select MDXAritmeticExceptions as the ADO object source variable. For the Variable Mappings create a new variable called MDXException of Value Type String, and make sure it’s Index value is 0 (i.e. the first column of the object source variable). Click OK.

Drop another Execute SQL Task Item inside the Foreach Loop container and call it Get Columns. As the For Loop container loops through the MDX Exceptions we want it to search and replace in each column of each row of the database table holding the extracted data. Edit the Execute SQL Task Item. Set the ResultSet to Full Result Set, connect to your database and enter the following query in the SQLStatement text area:

SELECT [name] AS [Column name]

FROM syscolumns

where id = (SELECT id

from sysobjects

where type = 'U'

AND [NAME] = 'MDXtoSQL')

AND [name] NOT IN('MEASURE','CHANNEL')

This will return the columns of the database table we are cleansing. Notice I have excluded the columns I know are text values and will not have any MDX arithmetic exceptions. Select the Result Set menu in the editor to assign the columns to a variable called MDXColumns of Value type Object. Don’t forget to change the Result Name to 0.

Drop another Foreach Loop Container inside the first Foreach Loop container and call it Remove MDX Exception. Connect the Get Columns Execute SQL Task to the new Foreach Loop Container which you should edit setting its Collection to a Foreach ADO Enumerator, and select MDXColumns as the ADO object source variable. In its Variable Mappings create a new variable called columnName of Value Type String, and make sure it’s Index value is 0.

Drop a Script Task into the second Foreach Loop container and name it Remove MDX Exceptions. Edit it to pass the MDXException and columnName variables to the script.

In the Script Designer enter the following code to remove any Exceptions:

Imports System

Imports System.Data

Imports System.Math

ImportsMicrosoft.SqlServer.Dts.Runtime

ImportsSystem.Data.SqlClient

Public Class ScriptMain

Public Sub Main()

Dim ColumnName As String = CStr(Dts.Variables("columnName").Value)

Dim MDXException As String = CStr(Dts.Variables("MDXException").Value)

Dim p_cnn As SqlConnection = New SqlConnection("[Your Data Source]")

Dim p_cmd As SqlCommand = New SqlCommand()

Dim p_sql As String

p_sql = "UPDATE [Your Database].[dbo].[MDXtoSQL] " & _

"SET [" & ColumnName & "] = " & _

"REPLACE([" & ColumnName & "], '" & MDXException & "','0')"

p_cnn.Open()

p_cmd.Connection = p_cnn

p_cmd.CommandType = CommandType.Text

p_cmd.CommandText = p_sql

p_cmd.ExecuteNonQuery()

'Release resources for next loop

p_cnn.Close()

p_cnn.Dispose()

p_cmd.Dispose()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Close the Script Designer and Task and execute the SSIS package

Removing Exponential Values can be done in the same way. The easiest way to handle these values is to multiply the value before the exponential notation by a multiplier which is determined by the exponential numeric value. To do this create a database table to hold the exponential notation and its respective multiplier