Passing unCONSTRAINED Set and Member parameters between reports in Reporting Services

By default SSRS MDX queries get a StrToMember or StrToSet functions with a CONSTRAINED flag. However, many developers do not quite know why it is there or what it actually does. Books-On-Line contains this statements:

StrToMember

  • When the CONSTRAINED flag is used, the member name must be directly resolvable to a qualified or unqualified member name. This flag is used to reduce the risk of injection attacks via the specified string. If a string is provided that is not directly resolvable to a qualified or unqualified member name, the following error appears: “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.”
  • When the CONSTRAINED flag is not used, the specified member can resolve either directly to a member name or can resolve to an MDX expression that resolves to a name.
  • StrToSet

  • When the CONSTRAINED flag is used, the set specification must contain qualified or unqualified member names or a set of tuples containing qualified or unqualified member names enclosed by braces {}. This flag is used to reduce the risk of injection attacks via the specified string. If a string is provided that is not directly resolvable to qualified or unqualified member names, the following error appears: “The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.”
  • When the CONSTRAINED flag is not used, the specified set specification can resolve to a valid Multidimensional Expressions (MDX) expression that returns a set.
  • Therefore, if you have a CONSTRAINED flag you have to specify exact members or sets (e.g. [Date].[Year].[2009], or {[Date].[Year].[2009],[Date].[Year].[2010]}). If you omit the flag, you can pass to the StrToMember an expression, which evaluates to a member (e.g. [Date].[Year].[Year].Members.Item(0)), and to StrToSet an expression, which evaluates to a set (e.g. NONEMPTY([Date].[Year].[Year].Members, [Measures].[Amount]).

    The flexibility which removing CONSTRAINED offers can be quite powerful when passing parameters between reports. In example, we may want to pass a parameter to a drill-through report from two different summary reports, where each of those uses a different subset of dimension members, which in turn can be derived by different set expressions.

    Major drawbacks of using this approach is the severe performance hit it leads to, as well as a possible “MDX injection” vulnerability. Since in most cases we would  be using the passed parameters in a subcube expression or on the slicer axis (WHERE clause), this should not lead to as bad performance as we would get if we would use it inside a calculation. However, when we need to use a parameter directly in a calculated measure, we would be better off avoiding an unCONSTRAINED function. 

     Therefore, we may instead use SetToStr on the summary reports and pass a String parameter to a CONSTRAINED StrToSet function in the drill-through report. This way we are first resolving the set expression once and passing it on to the underlying report as a string. We could do that in a calculated measure returning a String, then passed on as a Field to the drill-through parameter. However, in the rare case where we have many rows travelling from the SSAS server to the SSRS server, this could be slow.

    So, whether we use a StrToSet without CONSTRAINED flag, or a String parameter constructed by a SetToStr function, is dependent on the actual scenario, but it is good to have both options in our arsenal of tools and techniques when we need to implement some non-quite-standard piece of functionality.

    When Not To Write MDX and When Not To Use Analysis Services

    MDX is a great way to achieve some objectives easily and efficiently. However, there are some things better done in other ways. I will first discuss three distinct mistakes, which designers and developers tend to make when working on a typical BI solution.

    1. Leaf-Level Calculations

    This is by far the most common one. Both on the MSDN SQL Server forums, and in practice – developers try building calculations in MDX on the leaf level of some dimensions and usually hit severe performance problems. While it is usually possible to build an MDX expression to achieve the goal, it is usually much simpler and way better for performance to just do the calculation either in the ETL, or in the DSV (either as a Named Calculation, or as a part of a Named Query). This avoids the need for the query engine to perform a large number of calculations every time we request an aggregate.

    2. Mocking Joins

    I have discussed this in a previous post, where I am explaining how we can access a measure group through a dimension, which is not directly related to it (but rather related to it through another measure group and another dimension). Well, instead of doing this, we can simply build a many-to-many relationship between the two and avoid the whole MDX bit.

    3. Business Logic over a large dimension

    MDX is brilliant for implementing business logic. Unless it needs to operate over millions of dimension members every time a calculation is being requested. In example, recently I tried building a bit of business logic, which needed to order a dimension over a measure, and get the member with a largest value for each member of another dimension with hundreds of thousands of members. On top of it there were other calculations doing similar logic and the end result was not quite what was expected. Even though the MDX was really neat and achieved the purpose in 3-4 lines, I moved the logic back to the ETL (which was quite a bit more complex) because of performance. So, in general, I would not advise in favour of using MDX when to retrieve the result, the query engine needs to go through a lot of cells (in my case quite a few million), especially when ordering is required.

    A more fundamental mistake is using Analysis Services in a solution that does not really need it. Two severe and common, in my opinion mistakes are:

    1. Data Dumps

    Why build a cube when the sole purpose of the project is to allow users to access the underlying data? Unfortunately, sometimes Analysis Services is seen as a silver bullet for everything. If the end report contains a massive amount of data and a key requirement is for it to export nicely to CSV, why bother – just export the data to CSV, zip it up and let the users download it. As far as I know, this can be achieved very easily in a number of other ways. Especially considering the amount of effort and skills needed to build and maintain a SSAS solution.

    2. No Aggregations

    Another way SSAS gets misused is when a lot of textual data gets stored in a large number of big dimensions, and those get linked in a “fact table”. I have previously worked on a solution where there were no measure columns in the fact table at all and the cube was used to retrieve information about dimension members of the largest dimension called “Member”, containing 4-5 million customers. The rest were dimensions like “Sign Up Date”, “Date Suspended”, “Country of Birth”, “Age Band”, etc. In the end, the main report consisted of the information about the members. No data was aggregated apart from a simple count. The entire OLAP solution could have been replaced by a SQL query with a WHERE clause and an index.

    I am sure that there are many other cases when SSAS gets misused. A solution utilising SSAS and MDX properly can be very powerful and elegant. However, sometimes because of poor design choices it gets discarded as inadequate. Don’t blame the tools and the technology if your cube is slow – it is most likely a problem with either your design or the way you have developed your solution.

    All Member Properties – Name, Key and Level

    I just tried to find some more information about the All Member in SSAS dimension hierarchies and since it was not readily available, I had to experiment a bit, so I thought I may as well share my findings. For some these may be obvious, but for some they could as well be interesting.

    So in brief, I will explore the Name, Key and Level of an All member in a dimension hierarchy. The one of choice was the Customer dimension and Customer Geography hierarchy in Adventure Works. There is an All member, called All Customers. As expected, .PROPERTIES(“MEMBER_NAME”) gives us “All Customers”:

    WITH
    MEMBER [Measures].[test] AS
      [Customer].[Customer Geography].CurrentMember.PROPERTIES(“MEMBER_NAME”)
    SELECT
    {
      [Measures].[test]
    } ON 0,
    {
      [Customer].[Customer Geography].Members
    } ON 1
    FROM [Adventure Works]

    The first row shows us: All Customers.

    Now, let’s see what its key is:

    WITH
    MEMBER [Measures].[test] AS
      [Customer].[Customer Geography].CurrentMember.PROPERTIES(“KEY”)
    SELECT
    {
      [Measures].[test]
    } ON 0,
    {
      [Customer].[Customer Geography].Members
    } ON 1
    FROM [Adventure Works]

    This gives us 0.

    And its level:

    WITH
    MEMBER [Measures].[test] AS
      [Customer].[Customer Geography].CurrentMember.Level.Name
    SELECT
    {
      [Measures].[test]
    } ON 0,
    {
      [Customer].[Customer Geography].Members
    } ON 1
    FROM [Adventure Works]

    The result this time is: (All).

    So far so good. Now let’s try using these to get only the All member:

    SELECT
    {
      [Customer].[Customer Geography].[All Customers]
    } ON 0
    FROM [Adventure Works]

    This works. Now if we try the Key:

    SELECT
    {
      [Customer].[Customer Geography].&[0]
    } ON 0
    FROM [Adventure Works]

    Interestingly, since the All member is a calculated member and has no physical key, if we try to use the one that SSAS gave us does not actually work – we get nothing on Axis 0.

    Using the level works:

    SELECT
    {
      [Customer].[Customer Geography].[(All)].Item(0)
    } ON 0
    FROM [Adventure Works]

    Also, after experimenting a bit further:

    SELECT
    {
      [Customer].[Customer Geography].[All]
    } ON 0
    FROM [Adventure Works]

    This query also works even though the All member name is [All Customers], not just [All]. However, Analysis Services does recognise [All].

    In summary, the most robust options for referencing the All member in a dimension hierarchy that I have found are:

    1. [Dimension].[Hierarchy].[(All)].Item(0)
    2. [Dimension].[Hierarchy].[All]

    These will always work – regardless of the dimension and hierarchy names.

    Another option is using [Dimension].[Hierarchy].[] – e.g. [Customer].[Customer Hierarchy].[All Customers]

    And, one that does not work – referencing through its alleged key: [Customer].[Customer Hierarchy].&[0]

    Please let me know if there are any better alternatives, or why it would give me a key of 0 for the All member and would not work when actually using this key.

    Average Aggregation in Analysis Services

    In SSAS we do not have a measure Average aggregation type. We do have AverageOfChildren (or Average over time), however it is semi-additive and works only along a Time dimension. Fortunately, we have Sum and Count, and since Average = Sum / Count, we can build our own Average aggregation when we need one. 

    To do that: 

    1. Create a measure using the Sum aggregation type (which is also the default). In our example, let’s call it Amount.
    2. Create a Count of Non-Empty Values (or Count of Rows) measure. In example – [Measure Count].
    3. Create the actual calculation – [Measures].[Amount]/[Measures].[Measure Count]

    We can either create a calculated measure, which performs the MDX calculation above: 

    CREATE MEMBER CURRENTCUBE.[Measures].[Average Amount]
    AS
    [Measures].[Amount]/[Measures].[Measure Count]
    ,NON_EMPTY_BEHAVIOR = {[Measures].[Measure Count]}
    ,VISIBLE=1; 

    , or if we really do not need the Sum base measure, we can set it to be replaced by the calculation with a SCOPE statement: 

    SCOPE([Measures].[Amount]);
      This = [Measures].[Amount]/[Measures].[Measure Count];
    NON_EMPTY_BEHAVIOR(This) = [Measures].[Measure Count];
    END SCOPE; 

    Voila! We have created a measure simulating an Average aggregation type. Then, we can hide the Count helper measure and from user point of view there is no evidence of our effort. 

    Since the count will never be 0, we do not have to say “If not 0, divide, else – do not” and the NON_EMPTY_BEHAVIOR query hint may in fact improve performance, since the calculation will not be performed when the Count measure is NULL (instead of resulting in NULL/NULL=NULL). 

    Mosha has previously blogged about NON_EMPTY_BEHAVIOR and division by zero and I strongly recommend reading his post. 

    Another important consideration, which depends on the business scenario is the type of the Count aggregate. It could be Count of rows (Row Bound) or Count of non-empty values (Column Bound). The difference is whether we want to include or exclude the empty values from our aggregate. Either way, the described technique will work equally well. 

    I realise that this is a well-known approach, but since it is hard to find the solution online I thought it may be interesting for some less-experienced developers.

    OLAP Browser Feature on Connect

    As there has been a considerable stir around a few blogs (including mine) lately in regards to the lack of a good OLAP browser/client in the Microsoft BI space, I just created a feature suggestion on Microsoft Connect. Please support me with some more ideas about how Microsoft can fill this apparent gap in the current BI stack. The link is here:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=523128

    Other posts about this issue:

    http://richardlees.blogspot.com/2009/09/which-cube-browser-for-microsoft-olap.html
    http://richardlees.blogspot.com/2009/10/whats-preventing-excel-from-being.html
    http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!5100.entry
    http://sqlblog.com/blogs/marco_russo/archive/2010/01/05/microsoft-doesn-t-play-the-traditional-bi-client-game.aspx
    http://www.bp-msbi.com/2009/09/on-search-for-perfect-olap-browser.html

    7 Ways to Process Analysis Services Objects

    Being asked a bit too often how we can process Analysis Services databases (or cubes and dimensions) here is a list of 7 different methods:

    1. Through the GUI

    This one is obvious. We can do it through both SSMS and BIDS.

    2. XMLA Script

    To generate the script we can use the hefty Script button in SSMS. Simply configuring the processing settings and then instead of clicking the all too usual OK, we can as well click on the little button in the top left corner of the Process window:

    xmla_script

    Then, we can just execute the generated query.

    3. SSIS Analysis Services Processing Task

    This Control Flow task allows us to configure any settings and then add it to our ETL process. Quite handy.

    image

    4. SQL Server Agent Job

    This one is really an automation of Method #2 – XMLA Script. We can encapsulate it into a job of SQL Server Analysis Services Command type:

    image

    5. .NET Code

    This allows us to process cubes as a part of an application. Nice if we want to let our users process our cubes on-demand. Of course, better left to application developers, but still a good trick to know. Especially if we want to seem all-knowing when it comes to databases of any type. To achieve this objective, we use AMO (Analysis Management Objects). An API can be found here:

    http://technet.microsoft.com/en-us/library/microsoft.analysisservices(SQL.90).aspx

    6. Command Line – ascmd

    The command line utility can do a lot – including processing SSAS objects. For a full readme you can go here:

    http://msdn.microsoft.com/en-us/library/ms365187.aspx

    7. Command Line – PowerShell

    This PowerShell script will perform a Full Process of Adventure Works DW 2008 on localhost:

    [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
    $servername=New-Object Microsoft.AnalysisServices.Server
    $servername.connect(“localhost”)
    $databasename=New-Object Microsoft.AnalysisServices.Database
    $databasename=$servername.Databases.GetByName(“Adventure Works DW 2008”)
    $databasename.Process(“ProcessFull”)

    Using AMO we can do any maintenance tasks through PowerShell, including an object process.

    Probably not a fully exhaustive list, but I hope it helps with giving developers some options when it comes to this trivial and crucial part of the development and deployment process.

    Filtering measures by indirectly related dimensions in MDX

    I have lately started visiting the SQL Server MSDN Forums and trying to answer some questions about Analysis Services and Reporting Services. One of the questions about MDX queries seems to get repeated quite often and I will try to address it in this post, so hopefully more people will get to read this rather than ask about it on MSDN.

    The actual question takes the form of:
    “I have Dimension A and Dimension B, related to Measure 1. Dimension B is also related to Measure 2. How can I (is it possible to) get the values for Measure 1 filtered/sliced by Dimension A. I know it is easy to achieve with a join in SQL, but I do not know how to do it with MDX.

    This suggest the following dimension model:

    One solution would be creating a many-to-many relationship between Dimension A and Measure Group 2. However, we may want to avoid that for some reason and answer the problem with a query.

    We can achieve the desired result in a number of ways but I will discuss the one using NONEMPTY. Others would be using FILTER and EXISTS.

    A sample script is:

    SELECT
    {
    [Measures].[Measure 2]
    } ON 0,
    NON EMPTY
    {
    NONEMPTY( [Dimension B].[Dimension B Hierarchy].Members,
    ([Measures].[Measure 1], [Dimension A].[Dimension A Hierarchy].&[Member_Key]))
    } ON 1
    FROM [Cube]

    What this script does:

    1. Gets all Dimension B members, which have associated cells for Measure 1 and the specific Dimension A member (which we are filtering/slicing by)
    2. Gets the Measure 2 cells for the set of members retrieved in Step 1
    3. Removes members from Step 1, for which cells from Step 2 are empty

    An AdventureWorks example is:

    SELECT
    {
    [Measures].[Internet Order Count]
    } ON 0,
    NON EMPTY
    {
    NONEMPTY( [Product].[Product].Members,
    ([Measures].[Reseller Order Count], [Reseller].[Reseller].&[238]))
    } ON 1
    FROM [Adventure Works]