Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 8, 2005

Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube

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 adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Current Service Pack updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, within which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

About the Mastering Enterprise BI Articles ...

Having implemented, and developed within, most of the major enterprise BI applications for over ten 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 this article, we will look at a common business need, the aging of values. Aging is typically a process by which the enterprise determines the length of time that has transpired since a transaction (usually financial) has taken place within an account. Examples of common subject areas for agings in the business environment include accounts receivable, accounts payable and inventory, among many less common uses.

Regardless of the specific type of aging that we need to enact, we can apply the principles we will examine in this article to reach a solution that works within the OLAP environment. Moreover, and more to the point of our Mastering Enterprise BI series, we can replicate the functionality provided in many accounting and financial applications, as well as a plethora of "pre-fab" reporting solutions on the market, within the integrated Microsoft BI solution.

In this article, we will:

  • Discuss general aging concepts, and their pervasiveness in the business environment;
  • Prepare for the exercise by creating a clone of the FoodMart Analysis Services database, within which we will age customer transactions, simulating accounts receivable inside the sample Sales cube;
  • Enact Source Table Filters (dimension and cube) to limit our cube to a specified transaction date range.
  • Create an Aged Periods shared dimension within the sample cube to provide "buckets" for date-based transactional data;
  • Establish drillthrough capability for use in our verification process later.
  • Verify adequacy of our solution by demonstrating the use of the new capabilities from the perspective of the Cube Browser in Analysis Manager;
  • Look forward to a subsequent article where we create aging buckets through an alternative method;
  • Generally discuss the use of our new structures in the ultimate reporting application. We will also look forward to a subsequent article where we employ the aging buckets that we create in this article within Reporting Services.


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM