Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
May 14, 2007
About the Series ...
This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (Analysis Services), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article see Usage-Based Optimization in Analysis Services 2005, another article within this series.
An important consideration, when designing a Business Intelligence system within any environment, is where to put the intelligence among the various layers within the system. I have stated many times in the past, in both articles and presentations, that multi-layered reporting solutions require multi-layered architects. Nowhere is this more apropos than within the design and implementation of the integrated Microsoft business intelligence solution. A typical implementation for me includes the following:
(It is not uncommon to find other reporting applications within the Reporting layer, or to encounter non-Microsoft RDBMS underneath the OLAP layer, particularly in the initial phases of conversions to the integrated Microsoft business intelligence solution. Conversions of this sort are becoming very common, and staging of the components that occupy the primary layers is approached in many ways.)
In the search for a qualified Business Intelligence Architect, particularly in what is becoming the common backdrop of an integrated solution combining end-to-end relational, OLAP and reporting strata, corporate decision-makers and implementation teams need to rely upon architects that understand all the layers. The practice of seeking a reporting guru, OLAP architect, or data warehouse designer to meet business needs often leads to a suboptimal solution, because the stratum specialist, often working in a vacuum, tends to build functionality into the level he / she knows, and not necessarily into the level that best serves the integrated mechanism. When we add the inherent inability of the placement industry (for organizations that cannot or will not do their own research), to grasp even the requirements of the discrete layers, the odds of anything but a suboptimal outcome become small, indeed, when the enterprise relies upon layer specialists.
Many of my articles give examples of considerations of this sort, just a few of which include:
Regardless of whether the enterprise implements the entire solution using in-house talent or seeks skill / other resource augmentation from consultants, it is critical to seek a multi-dimensional perspective in the planning and design stages of the implementation, at a minimum. Much of the time, money and aggravation that are the natural issue of a haphazard approach is unwarranted, and can haunt the enterprise for a long time. For a general summary of my opinions surrounding the importance of thinking "multi-dimensionally" within the design and implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.
Conditional Formatting in the Analysis Services Layer
The Reporting / Presentation layer of the integrated Microsoft business intelligence solution is quite often the only point of interaction for organizational information consumers. It is here that we often find it desirable to manifest the effects of conditional formatting. Conditional formatting typically includes the dynamic modification of the visual appearances of report items, such as size, fonts, color or background color to draw attention to a given condition (such as the assignment of a red background color to any value representing an inordinately high expense total for a division); to generally class / group values based upon conditions (for example, the operating income of all units meeting their expressed goals is presented in green numerals, with the values for those falling short of the mark exhibiting red numerals), and so forth. Conditional formatting can serve many purposes, ranging from simple highlighting of conditions of this sort to more sophisticated functions that go well beyond mere color and font changes.
Because a reporting tool like MSSQL Server Reporting Services makes conditional formatting easy to put into place, and because it is within the deployed reports that the resulting data presentations are manifest, we often assume that conditional formatting is most naturally to be handled within the Reporting layer. Complications arise, however, when we have multiple values to which such formatting is applied, or when our conditional formatting expressions become complicated and resource intensive. Say the expression performs a somewhat lengthy comparison process between values under consideration and multiple possible values, to support the classification of the values within buckets, to each of which we assign a specific formatting attribute. It is easy to see how report processing time can be adversely affected when the conditional testing and resulting formatting are applied to, say, every measure value in a given column of the report.
Analysis Services allows us to apply conditional formatting at the cube level, where the values can be calculated and stored as a part of structure of the cube, in many respects. This not only might mean far more efficient report processing (where, for example, the expression in the report that dictates the conditional formatting is substituted by an expression that does a straightforward pull of the result(s) of the conditional test from the cube (such as a color description). In such a scenario, the outcomes of the test have already been determined, so the testing, specification of format properties, and so forth, do not need to be performed in the report itself at runtime, with the reporting application then assigning the desired attribute for each report value individually. In addition to the often obvious performance enhancement that results, the logic can be stored (and consistently maintained and enforced), from a single location within the integrated solution, instead of hardcoded into every report for which the same conditional formatting logic is applicable.
In this session, we will examine the process of embedding the logic to support conditional formatting within a cube for a calculated measure. While we will be examining a relatively simple set of circumstances in the interest of time and space, we should keep in mind that the concepts involved (the fact that we do, indeed, have options, for placing support of such capabilities among the multiple layers of the architecture) can become extremely important in enterprise-level design and implementation. Perhaps one of the most important considerations I hope to make plain is that, as I have mentioned many times, determining the placement of components among the layers requires far more than expertise in Analysis Services or Reporting Services alone, an excellent grasp of SQL or MDX, an outstanding capability to generate relational reports from MSSQL Server, in general, or mastery of any of the other discrete layers of the integrated solution. Multi-level awareness and integrated expertise - is required to optimize a multi-level solution.