Analysis Services can deliver data models that allow business users to connect, query and report with relative ease. In SQL Server 2012, these data models can be designed by either using the multidimensional approach (cube) or the tabular approach. Regardless of design approach, the data model is queried by using Multidimensional Expressions (MDX) – except if the tabular data model is configured for DirectQuery mode.
There are many good reasons to deploy data models. In brief, they are deployed to deliver intuitive data access to business users, to accelerate access to potentially extremely large data volumes, to encapsulate business rules and key performance indicators (KPIs), and to deliver data and calculations as a “single version of truth”. The purpose of this article, then, is to describe how Reporting Services can be used to leverage and query data model data – and metadata – to build and deliver reports.
Reporting Services includes a number capabilities to connect to and query an Analysis Services data model. It include a data processing extension, a graphical MDX query designer, extended field properties and a special aggregation function, simply named Aggregate.
The graphical query designer is available in both Report Designer (available in SQL Server Data Tools, formerly known as Business Intelligence Development Studio) and Report Builder. Its purpose is to construct an MDX query that is suitable for Reporting Services to deliver as a dataset (which is by definition a collection of fields). As an MDX query can possibly return a multidimensional set, the designer constrains the query design to one which returns a two-dimensional result. Note that there are requirements that the query include at least one measure, and that all measures are placed on the columns axis.
The query designer has two modes: Design and Query. In Design mode, the report author uses drag-and-drop techniques to introduce measures, KPI metrics, and hierarchy levels into the query pane. This effectively constructs an MDX query under the covers. The query can be filtered by using members from hierarchies, and, with the simple check of a checkbox, the filter can be parameterized, which results in the automatic creation of a report parameter and a (hidden) dataset to retrieve the available values for the parameter. There is also the ability to define calculated members, and you will need an understanding of MDX to work with this capability.
In Query mode, the report author works directly by crafting the MDX query. It is often helpful to start in Design mode to produce a query close to your requirement, and then switch to Query mode to revise the query. Note that once the query is modified in Query mode you cannot revert back to Design mode.
As a result of producing the dataset query by using the query designer, a predefined set of cell properties and dimension properties are automatically added to the query. These are surfaced through extended field properties which can be used in report property expressions. There are 14 extended field properties beyond the commonly used Value property. Extended fields include UniqueName (the fully qualified member name), FormattedValue, and font formatting and colour properties. The latter are ideal for ensuring a report uses the formats, fonts and colors that the data model developer intended. In Report Designer (but not Report Builder), when constructing an expression in the Expression window, Intellisense will provide a list of properties for you to choose from. Note that most extended field properties are only relevant for multidimensional data models.
Lastly, to exploit the superior performance of the Analysis Services storage engine to compute aggregations (as opposed to Reporting Services retrieving and aggregating potential large volumes of granular data), the Aggregate aggregate function retrieves server aggregates. It does this by modifying the dataset query to include additional members that map to, for example, the row groupings in a table. This way, Reporting Services delegates the aggregation task back to Analysis Services, and this results in faster data processing, less network traffic, and accuracy (the report author should never assume how values in a data model are aggregated, as the data modeler can use techniques to override natural aggregation logic).
Reporting from Analysis Services data models by using Reporting Services is fully covered in the Microsoft BI Academy Reporting Services course, written and presented by Peter Myers