Support Parameterization from Analysis Services - Parameter Defaults - Page 2

Practice

Practice: Add Parameter Default Support Objects to the Analysis Services Layer

We will next add parameter default support within the Analysis Services database upon which our sample report is based. To do so, we will access the Cube Designer within the Business Intelligence Development Studio, taking the following steps:

Add Calculated Members in Analysis Services to Support Parameter Defaults within Reporting Services

As we learned in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, as well as in other recent articles within our series, when we create parameterized filters within our dataset(s), Reporting Services automatically creates support objects for self-contained parameterization at the reporting layer. These support objects include Report Parameters, as we have seen, as well as datasets to support each of those Report Parameters. The datasets use the WITH MEMBER keywords to create calculated members at runtime, when the datasets are instantiated in Reporting Services, which are then referenced, by default, in the Value and Label fields within the associated Report Parameter settings page.

Because we took the shortcut approach of having Reporting Services generate the support objects automatically in our creation of the date-related parameters in Reporting Services: Customize Automatically Created Parameter Support Objects we had the Report Parameters in place, ready to then be pointed to the calculated members we created in Analysis Services in Support Parameterization from Analysis Services. In this article, we will save time in like manner, creating support objects in Analysis Services which we will then reference in a dataset, and then tie to the appropriate Report Parameters in Reporting Services, as we shall see. We will continue with the somewhat intuitive date-related parameters we have already established, as we create the calculated members we will need in Analysis Services to support parameter defaults. We could, of course, include additional custom calculated members that are entirely different, should the need arise in our local business environments.

We review the parameter default requirements with the client representatives, reiterating that, while parameterization can certainly be managed using the automatically created objects provided within Reporting Services, several benefits accrue to us in creating the support objects within the Analysis Services layer. Positing the intelligence within the cube will, we assert, mean that the same logic can be carried forward to multiple reports by simply referencing the calculations in their respective data sources, versus performing the calculations for every affected measure, as an early example, in the report (and adding to processing time, etc.). Moreover, we emphasize that maintaining the logic in a single location in the Analysis Services layer means a single point of maintenance: we can modify the logic in one place and rely upon the changes to “ripple through” to all reports that reference that logic, versus having to mechanically modify each individual report. This also ensures consistency of application of the logic to all affected reports: none will be overlooked for prospective modifications in the logic, for instance, when modifications become necessary, nor will different logic be accidentally applied across different reports.

From within the now open Cube Designer, we will begin creating Analysis Services objects, specifically a named set and several calculated members, to support parameter defaults in our practice report. Keep in mind that there are multiple ways to accomplish this effort: we will focus upon the creation of simple calculated members that we can easily pull into our existing report datasets to illustrate a straightforward approach that might be handled in more sophisticated ways.

The Calculations tab opens.

Clicking the New Named Set button adds a new named set to the Script Organizer pane (by default named “[Named Set]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane.

At this stage, let’s review what we already have in place in the report datasets. The focus is to build upon what we have previously put in place, and leverage the existing datasets to simply include, as part of the data they entrain from Analysis Services, the calculated members we employ there, in later steps, to generate our parameter defaults.

The MDX for the dataset, which we added as a part of our steps in aligning parameter support to the respective Analysis Services calculated members in our earlier article, appears as depicted in Illustration 10.

We note that the query simply selects calculated members for parameter caption (what the information consumer sees in the parameter selector at runtime) and parameter value (the qualified MDX value that is passed to Analysis Services to query and retrieve data at runtime, based upon the selection of the information consumer). We can certainly use this logic to propel our existing solution - we need only extend the solution to include support for parameter defaults. To do so we will return to Analysis Services, and create a named set and additional calculated members within our cube calculations, whose mission will be to specify the default for this, and each of the other two, date-related parameters, as we shall see. Once we have created the additional calculated members, we will return to reference them in a separate dataset through which we align the newly created Analysis Services objects with the respective report parameters.

   'TAIL(
      FILTER(
         [Date].[Calendar].[Month].MEMBERS,
            NOT ISEMPTY ([Measures].[Internet Sales Amount])
          ),
      1
   )'

The new [Last Period Sales] calculation appears in the Calculations Expressions pane, as shown in Illustration 11.

The Check Syntax message box appears, indicating that the syntax check was successful, as shown in Illustration 13.

We have successfully added support, via the named set “Last Period Sales,” for what will become a row axis in a Reporting Services dataset, as we shall see in short order. We have, in effect, specified that Analysis Services use the logic within the named set to determine the “most recent period” (including Year, Quarter, and Month) for which our cube contains data. The logic accomplishes this by identifying the most recent Period for which the measure Internet Sales Amount is not empty. We use the MDX Tail() function to accomplish selection of “the last non-empty” period - by employing the MDX Filter() function to filter out any except periods that are “occupied” by an Internet Sales Amount.

Because we are retrieving data at the Month level, the corresponding row set returned by Reporting Services will also include, as we shall see, higher levels within the Date dimension (Calendar hierarchy) - a characteristic of Reporting Services that produces a dataset ideal for many purposes.