Measure Selection in SSRS and SSAS

Pivoting around the measure improves report usability by adding an additional dimension that can be utilised in reports. A common example of this is a trend report that allows the user to select the measure that they wish to see. There are a few ways to accomplish this in your solution in this post looks at some of them. The methods examined are as a static parameter within the report definition, as a flexible parameter within the report definition, and finally, as a utility dimension.

In this example, we will look at pivoting a monthly trend report around two measures (actual and budget). The output for our reports will look like a screenshot below. Notice that the measure is selectable by the user.

In these reports we have created a measure parameter (named @measure_name) with two possible values as follows;

Parameter Value Parameter Label
=“[Measures].[Native Amt ACT]” Native Amt ACT
=“[Measures].[Native Amt BGT]” Native Amt BGT

Note that the value of the parameter should be included as an expression(otherwise an error will occur).

Static Parameters

Static Parameters transform pivot the data in the data set result. All possible measures I return by the query, and the dataset determines the pivotal value based on a formula. Therefore, in the base query looks like the one below, notice that both [Native Amt ACT] and [Native Amt BGT] are returned;

with
member measures.month_key as
[Dates].[Fin Years].currentmember.member_key
select
{
measures.month_key
,[Measures].[Native Amt ACT]
, [Measures].[Native Amt BGT]
} on 0,
strtomember(@year).children
on 1
from ….

Our data set includes an additional, added field that determines what measure to show (as an iif a function)

=iif(Parameters!measure_name.Label=“Native Amt ACT”
, Fields!Native_Amt_ACT.Value
,Fields!Native_Amt_BGT.Value
   )

In order to have the data set fields visible in the expression editor of the query must be saved and then reopened. Of course our report pivots around the Measure_Val amount.

Flexible Parameters

Flexible parameters are transformed within the query, that is, the parameter is defined as a member (measure) within the query. That is, at query now converts the parameter value @measure_name to a measure which is defined in the query.

with
member measures.month_key as
[Dates].[Fin Years].currentmember.member_key
member measures.measure_val as
strtomember(@measure_name)
select
{
measures.month_key
, Measures.measure_val
} on 0,
strtomember(@year).children
on 1
from
The @measure_name parameter must be included in the query parameters (as below);

Utility Dimensions

Utility dimensions are probably best known for their use in time intelligence and the by-passing the SSAS wizard for time intelligence (see here). Generally, unsophisticated users find individual measures relatively easy to use in their reports or pivots because it allows them to select a measure without thinking about the ‘time dimension’. You can simply stack measures and create calculations by referring to individual measures rather than the tuples created by a measure and a dimension combination.

A measure utility dimension can be created in the following manner;

1. Add a table to the database (or view with data) to produce the desired characteristics (ie, those measure names that we want to allow users to select). As an advanced option, we can also include additional properties to allow the MDX query to return a subset of dimension members.

2. Add the table to the DSV

3. Create a dimension based on the table so that the measure_name field appear as captions for members. There may be a logical case for setting the ‘IsAggregatable’ property to false and set a default member however, there is no need to do so because the user will select the member (or measure value) that they require and the default value for the Measure_Val measure is null.

4. Add the measure (Measure_Val) to cube and scope the intersects for selected [Reporting Measures] definitions.

create
member  currentcube.Measures.[Measure_Val] as null
, visible=0;
scope(Measures.[ Measure_Val]);
scope([Reporting Measures].[Measure Name].&[Actual TM]);
 this = [Measures].[Native Amt ACT];
end scope;
scope( [Reporting Measures].[Measure Name].&[Budget TM]);
 this=[Measures].[Native Amt BGT];
end scope;
end scope;

Now in the report is created, the [Reporting Measures] dimension can be used as a parameter in the report. In fact, we can specify the member values as the code for the ‘measure’ parameter selection. In this case, our @measure_name would have the available value set to query, on the query would return the members of the [Reporting Measures] dimension

with

member [measures].[measure_label] as

[Reporting Measures].[Measure Name].member_caption

member [measures].[measure_value] as
[Reporting Measures].[Measure Name].member_unique_name
select
{
[measures].[measure_label]
, [measures].[measure_value]
} on 0,
[Reporting Measures].[Measure Name].[Measure Name] on 1
From …

The report data set uses the [reporting measures] dimension is a parameter in the query is changed to use the dimension as below. Note that in this situation the Measures.Measure_Val member is a measure within the cube and not a definition within the MDX.

with
member measures.month_key as
[Dates].[Fin Years].currentmember.member_key
select
{
measures.month_key
, Measures.measure_val
} on 0,
strtomember(@year).children
on 1
from
where
( strtomember(@measure_name)
, …
)

The method of creating utility dimension is quite flexible for the SSRS query designer because all values and dimensions can be selected from the within the SSRS query designer without the need to write MDX.

Conclusion

There are a number of options for allowing the user to select measures their reports. The method chosen may depend on the uses ability right custom MDX and the need to duplicate measure selection throughout the reports (or the reporting environment). Additionally the method chosen may depend on the rate of change for the ‘reporting measure’ selection . When utility dimension is used, production versions the cube can easily hide the measure [measure_value] and [Reporting Measure] dimension in this may remove confusion the cube is browsed ad hoc manner.

Advertisements

PASS Day 1 – MS Announces Hekaton, PolyBase

Today at PASS Microsoft announces three initiatives for SQL Server. These are;

  • Project Hekaton
  • Support for BIG Data
  • and the integration of self-service BI in office

For those that are actively engaged in Business Intelligence the integration of the xVelocity engine and PowerView into Excel is not surprising, it’s been on the road map for some time and has previously been available through CTP (Excel 2013). There is also the shift to less pervasive BI through additional functionality that is offered in Excel (for example chart prompting and data learning for lists). I think that a lot of analysts or users that are involved in data manipulation will also enjoy using PowerView visualisations and the ability to interact with data in real time will improve data competencies. Clearly Microsoft is delivering on its goal of bringing ‘BI to the masses’ and its flagship is Excel (with inbuilt PowerView for visualisation). Most Importantly the Excel 2013 PowerView SUPPORTS visualisations against OLAP databases so most companies investment in OLAP technologies can be utilised with PowerView in Excel.  The update to the OLAP SSAS engine will occur at some date in the future

For those that are engaged with SQL server, Project Hekaton is an in memory improvement to SQL server objects. The ability to hold these objects in memory will give massive improvements in performance and is targeted towards OLTP environments. The impressive thing about Hekaton is that the technology is embedded into the SQL Server engine and so will become part of core functionality. Additionally the in-memory component (load) can be customised to the database (and workload) so that there is complete control over the workload. In-fact, the engine suggests objects (tables and stored procedures) that should have in memory storage applied. There is no doubt that this will have a major impact on the performance of OLTP systems and reduce reliance on hardware based solutions (albeit the engine consumes CPU).

Finally it has been announced that, in a move to support big data Microsoft it will support its own HADOOP type instance through HDInsight. This is not really surprising given the industries direction at big data (it seems that all vendors have some big data solution). What is interesting from a Microsoft point of view is that the big data component of the engine will support SQL queries. This technology (PolyBase) allows the PDW (Parallel Data Warehouse) relational and big data components to be combined in a single SQL Query.

One noticeable exclusion from today’s keynote was the lack of clarity (or any announcement) around Microsoft’s mobile BI strategy. At last year’s summit, it was announced it would support mobile BI and demonstrated it on an IPad. A similar announcement was not given today, however there are sessions in the summit that specifically address a mobile solution (from MS personnel) and so will be interesting to see this content.