HomeMS SQL

Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I

William Pearson
By William Pearson

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, 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.



Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:




  • Microsoft SQL Server 2005 Database Engine



  • Microsoft SQL Server 2005 Analysis Services



  • Microsoft SQL Server 2005 Integration Services



  • Business Intelligence Development Studio



  • Microsoft SQL Server 2005 sample databases



  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.



To successfully replicate the steps of the article, you also need to have:




  • Membership within one of the following:



    • the Administrators local group on the Analysis Services computer


    • the Server role in the instance of Analysis Services



  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, as appropriate.



Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 (“MSSQL Server”), MSSQL Server 2005 Analysis Services (“Analysis Services”), MSSQL Server 2005 Reporting Services (“Reporting Services”) and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.


About the Mastering Enterprise BI Articles …


Having implemented, and developed within, most of the major enterprise BI applications for over for fourteen years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I have become convinced that the components of the Microsoft integrated BI solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of Cognos (and other) enterprise BI to the Microsoft solution. In addition to converting formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.


The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel those found in the more “mature” enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost. The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the dominant enterprise BI vendors, to date, represents a serious “undersell” of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves – and, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the Microsoft solution will commoditize Business Intelligence.


For more information about the Mastering Enterprise BI articles, see the section entitled “About the Mastering Enterprise BI Articles” in my article Relative Time Periods in an Analysis Services Cube, Part I.


Introduction


In past articles of this series, I presented ways of “extending” the data source tables underlying our Analysis Services 2000 cubes through the use of SQL expressions within the Member Key and Member Name columns (in the case of dimension structures), and in the Source column (in the case of measures). In some scenarios (such as one that I presented in my article Mastering Enterprise BI: Create Aging “Buckets” in a Cube, where I proposed the use of an IIF / CASE scenario to build the necessary dimensional structure into a sample cube to support aging buckets), we got a glimpse of how, although the approach might work to help us deliver desired results in our business environments, the use of SQL expressions within these rather limited selectors might become cumbersome in many situations. Among many overall improvements and added conveniences in the design arena, Analysis Services 2005 offers us far more flexibility in this area, as well


As I stated in Introduction to MSSQL Server Analysis Services: Introducing Data Source Views, the advent of the Data Source View represents a significant design and development enhancement within Analysis Services. Forming a central, unified view of the metadata within our Analysis Services Project, the Data Source View contains the logical model of the schema used by database objects, including cubes, dimensions, and so forth. A Data Source View can be built to represent one or more Data Sources, allowing us to integrate data from multiple data stores within a single cube, or even dimension. The Data Source View serves as an abstract layer: the Analysis Services database objects are not bound directly to the underlying physical objects within the supporting data stores, but are bound, instead, to the logical objects within the Data Source View.


As we also noted in Introducing Data Source Views , as well as in other articles, one of many advantages that the Data Source View layer offers is that it can contain logical objects, such as queries, relationships, and calculated columns, that do not exist within (and, indeed, are entirely separate from) the underlying data sources. This factor alone offers a great deal of power in scenarios where, for whatever reason, we cannot create these objects within the data sources upon which we are constructing our Analysis Services Projects. A great example of such a logical object is the Named Calculation.


In this two-part article, we will examine Named Calculations, and get hands-on exposure to the process of adding them to a basic cube we construct within the new Business Intelligence Development Studio. We will overview the creation of Named Calculations, and discuss ways in which they can offer flexibility in general cube design and development. As a part of our examination of the steps, we will:



  • Prepare Analysis Services, and our environment, by creating an Analysis Services Project to house our development steps, and to serve as a platform for the design of a quick cube model, within which to perform subsequent procedures in our session (Part I);
  • Create a Data Source containing the information Analysis Services needs to connect to a database (Part I).
  • Create a Data Source View containing schema information (Part I);
  • Add examples of Named Calculations within the Data Source View (Part I);
  • Create a Cube containing data from our sample relational tables (Part II);
  • Create a Dimension based upon two of our Named Calculations to support “aging buckets” (Part II);
  • Deploy our Analysis Services Solution (Part II);
  • Browse the Cube, focusing on the new aging dimension structures (Part II).

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.
Previous article
SQL Server 2005 Integration Services – Migration Tasks
Next article
Calculate Easter for Any Year

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.