Positing the Intelligence: Conditional Formatting in the Analysis Services Layer

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.

Introduction

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:

  • MSSQL
    Server:
      The
    RDBMS layer, consisting usually of both relational / OLTP data sources
    and warehouse(s) / mart(s);

  • Analysis
    Services:
     
    The OLAP layer, consisting of multiple OLAP cubes;

  • Reporting
    Services:
     
    The Reporting layer, within which both relational and OLAP reports (as
    well as combinations of these types) are authored, managed and delivered.

(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:

  • Housing
    functions and calculations at the MSSQL Server (or any other
    enterprise-level RDBMS) Database layer (be it relational, star-schema,
    or other), which are leveraged in the cubes or reporting system;

  • Building
    structures within the Analysis Services OLAP layer to provide
    picklist, conditional formatting and other support to the Reporting layer
    of the system;

  • Building
    virtually anything we need in the way of calculated fields, parameterization
    support, conditional formatting, and more at the Reporting Layer
    This can be seductively easy within the flexible vacuum of the Reporting
    Services
    design environment, but can often be the worst place to house the
    associated structures from an optimization perspective.

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles