Standardize Your MDX Parameter Queries

By Dan Meyers

One thing that I have found useful when writing a lot of Reporting Services reports that have parameters and use MDX is to create some standard calculated members in my cube for the Label and Value properties of the parameters. Just like any other calculated measures that get built into the cube script, you get the advantage of reusing them instead of doing them over and over again in the WITH clause of your query.  The code below is dynamic and is not specific to a particular dimension or anything so it will work with whatever you put on ROWS in your data set query for your report parameters.

Below is the MDX for the calculated members and a sample query.

Insert this code into your cube script (at the bottom)

CREATE MEMBER CURRENTCUBE.[Measures].[ParameterValue] AS
    Axis(1).Item(0).Item(0).Dimension.CurrentMember.UNIQUE_NAME,
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[ParameterCaption] AS 
    String(Axis(1).Item(0).Item(0).Dimension.CurrentMember.Level.Ordinal * 1 , ' ' ) + Axis(1).Item(0).Item(0).Dimension.CurrentMember.Member_Caption,
VISIBLE = 1;

Sample Query
SELECT
      {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON 0,
      {[Date].[Calendar].ALLMEMBERS} ON 1
FROM
      [Adventure Works]

.

MDX Tips from BI Conference

By Dan Meyers

Below is a link to a video and the accompanying slide deck for an MDX session that I sat in on at the Microsoft BI Conference in New Orleans. 

 http://www.msteched.com/2010/NorthAmerica/BIE11-INT

He presents a different way to do your time period calculations using a Utility dimension in SSAS.  I think it’s interesting how he has created a hierarchy within the dimension. 

Other MDX samples include moving dates and how to handle time zone differences.

There are some zip files embedded in some of the slides that will give you an XMLA script to create the sample on your laptop or the demo server.

Cheers,

Dan Meyers
Senior Business Intelligence Consultant

URL Encoding in Reporting Services

Handling Special Characters Using HttpUtility.URLEncode()

By Dan Meyers

Reporting Services allows you to create hyperlinks in your reports using the Go To URL action. This is a very useful feature that provides you with the ability to make almost anything on the report a hyperlink. When creating links to other reports, this action type provides many advantages over using the Go To Report action type. For example, you can embed some javascript into the link so that the report opens up in a new window that is a specific size in a specific location on the screen. You cannot do this using the Go To Report action type.

Of course there are disadvantages too. When using the Go To URL action type you have to manually handle any special characters in you URL string. This is something that the Go To Report action type handles for you when it comes to the parameter values passed to the report. In my opinion, the advantages outweigh the disadvantages. So I always use the Go To URL action type and manually handle the special characters using the HttpUtility.URLEncode() function.

Before we can use the the HttpUtility.URLEncode() function we have to first add a reference to System.Web assembly.

Go to Report –> Properties –> References. Click [Add].

Scroll down a select ‘System.Web’ in the list and click [OK].

You should now see that the reference has been added.

Now that we have added the reference to System.Web, we now have to create a function in the report’s Code window that uses the URLEncode() function. Click on ‘Code’ in the left panel and enter the code below.

Public Function URLEncode (ByVal inURL As String) As String
Dim outURL As String
outURL = System.Web.HttpUtility.UrlEncode(inURL).ToString
Return outURL
End Function

We can now use our new report function in an expression anywhere in our report.

As you can see in the screenshot below, the function will convert any special charters and encode the URL. In this example, I am passing in a string for a MDX member for Barnes & Noble. You can see the before and after values below.

A Common Mistake
One common mistake that many make when taking this approach is that they try to use the HttpUtility.URLEncode() function directly from an expression. The expression will basically ignore the reference and not work. You have to use the function in the report’s Code window and then reference the report’s function in the expression for it to work.

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)

Exporting MDX Query Data with SSIS

Query from the data flow without having to use a linked server or a script task

By Dan Meyers

I was recently working on a Scorecard application that displays KPI information from multiple data sources which included a few Analysis Services cubes. This required me to extract some KPI values from the cubes and place them into a SQL Server table (they did not have PerformancePoint up and going yet). I wanted to do this using a data flow in a SSIS package but ran into an issue that I thought had been fixed.

I was attempting to export the results of my MDX queries to a SQL Server table using a normal data flow task when I encountered one of Microsoft’s very un-helpful error messages. The OLE DB source adapter that runs the MDX query against the cube was failing and reporting the error message below.

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

After doing some searching, I was surprised to find out how many people are having issues trying to accomplish this. Even more surprising was the fact that Microsoft has not been very proactive in providing developers with a more direct method of doing this that does not produce errors out-of-the-box. This drove me crazy for hours before I figured it out. The annoying part of it was that in the UI in SSIS you can successfully preview the results on your query and even get the column definitions back but it would not run when you executed the package or data flow task.

Now, there are a couple of alternative approaches that you could use here. The two most popular ones that I have seen mentioned on the web are: to use a SQL Server Linked Server and a SQL Server data source to pass through your MDX query to the SSAS server and return a tabular dataset; or to substitute the OLE DB data source adapter with a Script Component and ADOMD.NET to create a dataset from the results returned.

The linked server approach seems to work just fine but is still a “work around” and not advisable. The Script Component requires a bit of scripting and is more cumbersome to implement.

Instead of using either of those alternatives, I decided to try a little trick that I remembered seeing in BOL when reading about the specific properties supported by XMLA.

Open up your Analysis Services data source and click the 'All' button on the left to go into the properties.



In the text field provided for the 'Extended Properties' property enter 'Format=Tabular' and save your changes.



You will get a warning, 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.

To fix this, all you have to do is convert your data types using a Derived Columns task. You may have to get a little creative when converting some of the numeric values coming out of your MDX queries from text to the appropriate SQL Server data type if they are in expressed using scientific notation, for example. Maybe I will blog about that another time.

Hopefully this will save you some headaches and allow you to query a cube from the data flow without having to use a linked server or a script task.