Reporting Services Optional Multi-Valued Parameters with Analaysis Services as the source

Creating multi-valued parameters with reports is a common requirement and it can be easily done with Reporting Services, making Allow multiple values property enabled with the report. However, if you need to make it as optional, allowing user to continue with the report without selecting any item in the parameter, you will face an issue because Reporting Services does not allow user to continue without selecting an item if the parameter is multi-valued parameter. Not only that, you cannot enable Allow null value property too.

If your source for the parameter is SQL Server database, workaround is, adding a NULL value to the dataset manually (or as a part of the query) and use it for handling optional requirement. If you google, you can find the way of implementing this workaround in many forums and sites. What if the source for the parameter is Analysis Services, then this workaround does not work as it is, it needs some extra work.

Here is the way I implemented, it may not be the perfect method but it worked fine for me.

Assume that I need to load Products for a parameter and make it as multi-valued parameter, my MDX for that will be something like below;

WITH 
MEMBER [Measures].[Product Name]
 AS [Product].[Product].CurrentMember.Name
MEMBER [Measures].[Product Unique Name]
 AS [Product].[Product].CurrentMember.UniqueName
SELECT
 { [Measures].[Product Name], [Measures].[Product Unique Name] } ON 0
 , {[Product].[Product].[Product].Members } ON 1
FROM [Adventure Works];

However, I cannot make the parameter as optional because of the limit, hence I need a member that represents NULL. For that, I need to introduce query-scoped calculated member and add it to the resultset;

WITH 
MEMBER [Measures].[Product Name]
 AS [Product].[Product].CurrentMember.Name
MEMBER [Measures].[Product Unique Name]
 AS [Product].[Product].CurrentMember.UniqueName
MEMBER [Product].[Product].[All Products].[(NULL)]
 AS Aggregate([Product].[Product].&[(NULL)])
SELECT
 { [Measures].[Product Name], [Measures].[Product Unique Name] } ON 0
 , {[Product].[Product].[Product].AllMembers } ON 1
FROM [Adventure Works];

Now just like parameters loaded from SQL Server databases, I have a (NULL) item in the parameter for handling the situation. If you get an issue with sorting the result that MDX produces, alternative way for that is, executing the MDX using a TSQL Stored Procedure connecting to Analysis Services via a Linked Server object.

CREATE PROC dbo.GetProductsFromSSAS
AS
BEGIN

 DECLARE @Query nvarchar(max)
 
 SET @Query = N'
  SELECT 
   "[Measures].[Product Name]"
   , "[Measures].[Product Unique Name]"
  FROM 
   OpenQuery(OLAPDatabase, ''
     WITH 
     MEMBER [Measures].[Product Name]
      AS [Product].[Product].CurrentMember.Name
     MEMBER [Measures].[Product Unique Name]
      AS [Product].[Product].CurrentMember.UniqueName
     MEMBER [Product].[Product].[All Products].[(NULL)]
      AS Aggregate([Product].[Product].&[(NULL)])
     SELECT
      { [Measures].[Product Name], [Measures].[Product Unique Name] } ON 0
      , {[Product].[Product].[Product].AllMembers } ON 1
     FROM [Adventure Works];
    '')
  ORDER BY 1;'
 
 EXEC SP_EXECUTESQL @Query;
 
END
GO

Is MDX query slow? Here are four guidelines for improving performance

The key reason for implementing OLAP databases is performance related to data retrieval and what if the wrote query does not offer expected performance? In a way, troubleshooting MDX query is not as easy as troubleshooting TSQL, if you are not much familiar with the concepts related to MDX statements. However, you can keep following guidelines in mind and do the needful if you experience slow performance with written MDX statement.

Guidelines are based on the time spent on Query Processor and Storage Engine. Duration can be determined using following events with Profiler;

• Query Subcube event - Duration column - for time spent on extracting data from the storage.
• Serialize Results End event - Duration column - for time spent on manipulating data after taken from the storage.

Here are four guidelines;

1. If you see more time on Query  Serialize Results End event - Duration (Processing) than Subcube event - Duration (Storage), you need to optimize the MDX statement reducing added calculation and complexity.
2. If you see more time on Query  Subcube event - Duration (Storage) than Serialize Results End event - Duration  (Processing), it shows that it needs partitioning, user-defined hierarchies and attribute relationship among used attributes defined.
3. If you see more time on Query  Subcube event - Duration (Storage) than Serialize Results End event - Duration  (Processing), and data is retrieval rarely from aggregation, it needs aggregation added based on usage.
4. If you see more time on Query  Subcube event - Duration (Storage) than Serialize Results End event - Duration  (Processing) and data is retieved from the cache, you need to check the resource usage of the server and if required, scale up by adding memory, CPU, etc.

Await for next post on how to use the Profiler for capturing these for troubleshooting.

Analysis Services: Cannot see applied color for calculated measures

Color expression section of Calculations tab of Cube Designer allows us to set Fore Color and Back Color for the Calculated Measures/Members based on an expression. Once it is set properly, we should be able to see colored values for the measure created based on the expression evaluation. What if you do not see it with your MDX statement?

For example, see the below Calculated Measure called Profit, which has a Color Expression set.

And when you browse it with Browser tab, you do not see the color;

And when you get it using MDX, still it does not show the color;

But when it is opened with Microsoft Excel, it CAN BE SEEN.

What is the reason, since it can be seen with Excel, means expression set is correct and it should be worked.

The reason for MDX statement is, it needs CELL PROPERTIES to be added to the statement in order to see colors with management studio. Not only with management studio, even with other applications. Here is the way;

MDX and Reporting Services: How to remove duplicates from a parameter?

What if you get duplicate values from a dimension that is going to be used for loading a Reporting Services parameter? Generally, you do not get duplicates from a dimension because we do not configure the dimension like that. For an example, we do not expect duplicate names from Product dimension. But there can be an exception that shows duplicate names but unique keys, something like below;

Id	Name	...
1	Product A	...
2	Product B	...
3	Product A	...
4	Product C	...

If Id of this has been used as the key and name has been used as the name of the key, even though we write a MDX query like below, we still get duplicates as they have unique keys.

SELECT 
  {  } ON COLUMNS, 
  { Distinct([Product].[Product].MEMBERS) } ON ROWS 
FROM [Cube];

Best way is, fix this in database level without taking it to reporting level. But if we cannot do anything at database level (assume that no permission, have to use as it is) and still should not show duplicates with the parameter, what is the best way?

I have solved this in two ways. One is, execute the MDX from the database engine, connecting with OLAP database using a linked server. In this case, TSQL SELECT statement will be used and source for the parameter will be database engine, not OLAP database.

SELECT
 DISTINCT [Measures.ProductName]
FROM OpenQuery(LinkedServerForOLAP,
 'WITH 
 MEMBER [Measures.ProductName] AS [Product].[Product].CurrentMember.Name
 SELECT 
    { [Measures.ProductName] } ON COLUMNS, 
    { Distinct([Product].[Product].MEMBERS) } ON ROWS 
  FROM [Cube]')

Other method is, write VB.Net code inside the report for removing duplicates. This requires a hidden parameter that holds data coming from the OLAP database and another new parameter for showing values. New parameter has to be loaded from a function written in the Code segment that accesses the hidden parameter for getting values, programatically removes duplicates and returns distinct values.

Here is the second way;

First create a hidden parameter. Make sure it is set as hidden.

Then set Available Values and Default Values with the data set created based on OLAP database.

First part is done. Then implement the following code in Code segment.

Function GetDistinctLabels(parameter As Parameter) As String()


 Dim labels As Object() = parameter.Label
 System.Array.Sort(labels)

  Dim x As Integer = 0

 For i As Integer = 0 to labels.Length - 1

  If i > 0 And labels(i).Equals(labels(i - 1)) Then
   
   Continue For
  End If
  labels(x) = labels(i)
  x += 1
 Next

  Dim distinctlabels As [String]() = New [String](x - 1) {}
 System.Array.Copy(labels, 0, distinctlabels, 0, x)

  Return distinctlabels

End Function

After that, load the main parameter that is used for showing values as below;

Set the Available Values using Specify values and call the function written for loading distinct values passing parameter name. Do it for both Label and Value.

Set Default Values using same technique if required.

That is all. I have used both methods and both worked fine. However if keys of these labels have to be passed to another dataset loading more data, additional work is required for maintaining keys of the parameter.

MDX Top function

If we need get Top n records from a relational database table, we simply use SELECT TOP (n).... But how do you do the same with Multi-dimensional database like Analysis Services databases? MDX is the language we used for querying Analysis Services databases, or Cubes. MDX function called TopCount  is the known function for similar operation but it is believed that it has to be used with a numeric expression and the result of TopCount is based on values of the numeric expression used.

In a way, it is true. But what if we need to get a set of dimension members using Top functionality without using a numeric expression. To elaborate, if I want to get 5 products from Products table, I can write something like below using TSQL;

USE AdventureWorks2014;
GO

SELECT TOP (5) Name
FROM Production.Product;

If I want to do the same with Product Dimension in a cube, how can it be done? Can I use TopCount without using a numeric expression? Yes, it is possible. The numeric expression for TopCount function is optional, hence it works like below;

SELECT 
 {} ON 0
 , {TopCount([Product].[Product Categories].[Product].Members, 3)
  } ON 1
FROM [Adventure Works];

In addition to that, Head  function can be used too.

SELECT 
 {} ON 0
 , {Head([Product].[Product Categories].[Product].Members, 3)
  } ON 1
FROM [Adventure Works];

SSAS: How to find User-Defined Member Properties of a dimension?

User-Defined Member Properties are attribute relationships added to specific named level in a dimension. Once defined, they can be access using either PROPERTIES key word or Properties function. Here is and example of accessing a user-defined properties.

-- from MSDN
SELECT 
   CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members, 
             [Measures].[Sales Amount]) ON COLUMNS,
   NON EMPTY Product.Product.MEMBERS
   DIMENSION PROPERTIES 
              Product.Product.[List Price],
              Product.Product.[Dealer Price]  ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January]) 

However, if you do not know all user-defined properties added and need to get a list, you can simply use Analysis Services Schema Rowsets for querying out information such as objects, state, sessions, connections, etc. There are many schema rowset under OLEDB for OLAP Schema Rowsets and the one we have to use is MDSCHEMA_PROPERTIES. Here is the query for getting all user-defined properties of a level in a dimension.

SELECT *
FROM $SYSTEM.MDSCHEMA_PROPERTIES
WHERE [DIMENSION_UNIQUE_NAME] = '[Dim Customer]'
 AND [HIERARCHY_UNIQUE_NAME] = '[Dim Customer].[Customer]'
 AND [LEVEL_UNIQUE_NAME] = '[Dim Customer].[Customer].[Customer]'
 AND [PROPERTY_TYPE] = 1;

Read more on Analysis Services Schema Rowsets: https://msdn.microsoft.com/en-us/library/ms126233.aspx
Read more on OLE DB for OLAP Schema Rowsets: https://msdn.microsoft.com/en-us/library/ms126079.aspx

MDX: Getting name of the user connected

Are you looking for a way of adding the name of the account used for the session to the resultset? Here is the way of adding it;

Analysis Services (SSAS): Calculating running totals for a given period using MDX

Calculating running totals against a numeric is not an easy task with both relational databases using T-SQL and multidimensional databases using MDX. In a way, calculation on relational databases is little bit easier using new window functions than using multi-dimensional queries. But it does not mean that the calculation using MDX is much more complex and difficult to write. However it depends on the familiarity. Comparatively we do not write MDX statements as much as we write T-SQLs. That is the reason for tagging “complex” on MDX, that is the reason for spending much time even on a simple query than the time spend with T-SQL for similar implementations.

I had to write a similar query today for calculating running totals mixing with YTD and previous year values. As usual I searched for best practices, there were many posts on this based on either calendar year or financial year but could not find a specific one for calculating running totals for a given period. Wrote it, and thought to share it. Here is the MDX, it is written on AdventureWorksDW. You may find it interesting and helpful.

1. WITH MEMBER [Measures].[Running Total]
2. AS
3. (
4.     AGGREGATE([Date].[Calendar].[Month].&[2006]&[10]
5.             :[Date].[Calendar].CurrentMember
6.             , [Measures].[Sales Amount])
7. )
8. SELECT
9.     {[Measures].[Sales Amount]
10.     , [Measures].[Running Total]} ON 0
11.     , [Date].[Calendar].[Month].Members ON 1
12. FROM (SELECT {[Date].[Calendar].[Month].&[2006]&[10]
13.             :[Date].[Calendar].[Month].&[2007]&[11]} ON 0
14.         FROM [Adventure Works])

MDX Error with OLEDB: The Parameter is incorrect

If you have read my previous post, you know why I went for “OLE DB - Microsoft OLE DB Provider for Analysis Services 10.0” instead of “Microsoft SQL Server Analysis Services - .NET Framework Data Provider for Microsoft Analysis Services” for querying data from a cube into SSRS reports. Unfortunately I have to give up the OLE DB Data Source Type because it does not support for parameterizing the MDX query. When a parameter is added, I get following error:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset ‘DataSet1’.
The following system error occurred: The parameter is incorrect..
Error Code = 0x80070057, External Code = 0x00000000:.

As usual, I googled and found that it has been reported on January 2007 and has identified as a bug in .NET’s System.Data.OleDb. The thread has been updated again on April 2008, and seems not fixed. You can read the thread here.

Anyway, since it is related to .NET, I am going to start searching on .NET updates and fixes on this. I will share if I can find something and solve this issue with it.

MDX Error: First Axis of the query contains dimension other than Measure Dimension…

If you try to set some other dimension elements as the first axis other than measure dimension, you will receive an error like below:

I got this error with Reporting Services when I try to query the OLAP database since I had referred some other dimension in the first axis. How do we fix this:

All we have to do is, change the Data Source type for OLE DB and select the provider as Microsoft OLE DB Provider for Analysis Services 10.0. You may see some limitations with this :(.