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

March 13, 2006

A More Involved Named Calculation: Support for Aging "Buckets" with CASE

Let's look at a slightly more involved example – and a way to meet a need for generation of aging "buckets" similar to the ones we created (using limited Analysis Services 2000 options) within our cube in Mastering Enterprise BI: Create Aging "Buckets" in a Cube, the article to which we referred earlier.

In the article, as some of us may recall, we examined the use of a dimension whose members were based upon conditional logic to attain our objectives. I stated that this was but one of several approaches I have used to support aging stratification / grouping structures within Analysis Services (having also accomplished aging with calculated measures, as well as via hybrid approaches where I performed aging of transactions at an intermediate view level within the RDBMS, etc.). We noted that, in the same manner that derived measures can be based upon conditional logic (as an example, I noted that I often used CASE statements in derived measures for this purpose, when the RDBMS under consideration was MSSQL Server), we could use conditional logic to create the members of a dimensional level outfitted for the purpose of supporting aging groups.

NOTE: For more information on the derived measures in general (from an Analysis Services 2000 perspective), see my article Derived Measures vs. Calculated Measures, also within this Database Journal series.

I stated that the approach to take in creating aging "buckets," whether through the method we examined in the article, or through alternatives we suggested there and in later articles, depends upon many factors, chief of which might be optimization considerations. Our intent in that article was, just as our intent in this article is, not to focus upon optimization per se, as this is an area that can vary greatly from environment to environment. Then, as now, we were presenting an approach, an option for achieving aging groupings. The option is conceptually very similar to our approach via the capabilities we were afforded in Analysis Services 2000 – the Named Calculations afforded in Analysis Services 2005 simply make it easier to accomplish – even with more elaborate conditional logic. Of course, it still remains to the reader to determine whether the support structure itself (a dimension structure based upon conditional logic) is appropriate, or if another method is a better "fit" within their own setting.

In our previous approaches, we cited an example where we were to add aging capabilities to a cube that a client already had in place. In essence, they wished to age "accounts receivable," based upon customer transaction dates, over a given time frame within their current year. We will replicate the substance of the work we performed in the practice example that follows, using our practice data source instead of the sample FoodMart data source (an MS Office Access database) we used in the Analysis Services 2000 example.

Let's assume, again, that the consumers have told us that they wish to age the customer accounts into the following relatively common aging buckets:

  • < 30 days

  • 30 – 60 days

  • 61 – 90 days

  • 91-120 days

  • > 120 days

Limitations of the underlying sample data require us to ignore the fact that there is no "invoice" date. Indeed, the simple business scenario presented within the underlying relational database gives us little more than a "cash basis" depiction of business: the sales transactions, at least the ones that are collected from the AdventureWorks OLTP into the AdventureWorks DW database that we are using as the source for our cube, do not indicate important information that would be necessary to generate conventional accounts receivable agings or other attributes. (In the real world, we would likely be creating accounts receivable agings based upon transactional data within the transactional database (OLTP), and not with data warehouse / mart data anyway). We will keep things simple and use the underlying data source as we have aligned it to support our cube.

From the perspective of the data source (AdventureWorks DW), the transaction date (we will use the Ship Date, as it at least approximates a date that revenue would become "earned and measurable," and thus support an "account receivable") that we will use resides within the DimTime table, joined to the FactResellerSales table as depicted in Illustration 31.


Illustration 31: Source of the "Transaction Date"

Because the data source provides no easily discernable, subsequent "collection" date for the "receivables" generated on given transaction dates (again, it appears that sales are "cash basis" in this simple data), we will need to make some assumptions. We will assume, for purposes of our exercise, that none of the transactions with ship dates over the last six months have had matching receipts upon them. This will give us some aging strata to support the illustration of our point. (Again, the idea here is to demonstrate a method for creating aging buckets, not to model an accounts receivable system.)

A quick query of the tables concerned indicates that the current (in our scenario, the "most recent") year for available data is 2004, with transactions extending through the early days of June. Within our practice scenario, we will age transactions that have shipment dates between January 1, 2004 and June 30, 2004, making the assumptions we have already discussed. We will take the following steps to create Named Calculations as an approach to providing both keys and names for the members of an aging dimension within our cube.

1.  Right-click the DimTime table, in the Tables pane.

2.  Select New Named Calculation ...from the context menu that appears, as shown in Illustration 32.


Illustration 32: Select New Named Calculation ... from the Tables Pane

The Create Named Calculation dialog appears.

3.  Type the following into the Column Name box, atop the dialog:

ANSYS045_Aging_Bucket_Key

4.  Input the following into the Description box:

Numeric Aging Bucket Key

5.  Type (or cut and paste) the following into the Expression box, atop the dialog:


CASE 
   WHEN DATEDIFF(Day, DimTime.FullDateAlternateKey, '12/31/2003') 
      < 30 THEN 29 
   WHEN DATEDIFF(Day,  DimTime.FulldateAlternateKey, '12/31/2003') 
      BETWEEN 30 AND 60 THEN 60 
   WHEN DATEDIFF(Day, DimTime.FulldateAlternateKey, '12/31/2003') 
      BETWEEN 61 AND 90 THEN 90 
   WHEN DATEDIFF(Day, DimTime.FulldateAlternateKey, '12/31/2003') 
      BETWEEN 91 AND 120 THEN 120 ELSE 999 END

The Create Named Calculation dialog appears, with our input, as depicted in Illustration 33.


Illustration 33: The Create Named Calculation Dialog with Our Input

6.  Click OK to create the new Named Calculation.

The dialog closes.

Next, we will create a Named Calculation to provide member names for our aging "buckets."

7.  Right-click the DimTime table, this time within the Diagram pane.

8.  Select New Named Calculation ... from the context menu that appears, as shown in Illustration 34.


Illustration 34: Select New Named Calculation from the Diagram Pane ...

The Create Named Calculation dialog appears.

9.  Type the following into the Column Name box, atop the dialog:

ANSYS045_Aging_Bucket_Name

10.  Input the following into the Description box:

Aging Bucket Name

11.  Type (or cut and paste) the following into the Expression box, atop the dialog:


CASE 
   WHEN DATEDIFF(Day, DimTime.FullDateAlternateKey, '12/31/2003') 
      < 30 THEN '< 30' 
   WHEN DATEDIFF(Day,  DimTime.FulldateAlternateKey, '12/31/2003') 
      BETWEEN 30 AND 60 THEN '30 - 60' 
   WHEN DATEDIFF(Day, DimTime.FulldateAlternateKey, '12/31/2003') 
      BETWEEN 61 AND 90 THEN '61 - 90' 
   WHEN DATEDIFF(Day, DimTime.FulldateAlternateKey, '12/31/2003') 
      BETWEEN 91 AND 120 THEN '91 - 120'  ELSE '> 120' END

The Create Named Calculation dialog appears, with our input, as depicted in Illustration 35.


Illustration 35: The Create Named Calculation Dialog with Our Input

12.  Click OK to create the new Named Calculation.

The dialog closes.

13.  Expand the DimTime table within the Tables pane, by clicking the "+" sign to its immediate left.

14.  Scroll down as required to see the two new Named Calculations appear toward the bottom of the tree, as shown in Illustration 36.


Illustration 36: The Named Calculations Appear in the Tables Pane

Here, as well as in other places, we can right-click the Named Calculation and modify it, should the need arise. Let's verify, as we did with the first Named Calculation we created in the last section, the effectiveness of the SQL expressions we have used.

15.  Right-click the DimEmployee table within the Tables pane.

16.  Select Explore Data from the context menu that appears, as depicted in Illustration 37.


Illustration 37: Click Select Explore Data from the Context Menu ....

The Load Sample Data message box briefly appears, as we saw earlier, and then a new tab, named Explore DimEmployee Table, appears within BI Development Studio. Within this tab, we can see our new Named Calculations.

17.  On the Explore DimEmployee Table tab, scroll to the rightmost two columns, ANSYS045_Aging_Bucket_Key and ANSYS045_Aging_Bucket_Name.

A sample of the two new Named Calculations appears partially depicted in Illustration 38.


Illustration 38: The New Named Calculations Appear within the Explore Tab

We can once again gain confidence that the Named Calculations are performing their intended objectives, by contrasting representative member fields with the associated fields in the FullDateAlternateKey column near the far left of the table. (We assigned any "out of scope" dates a key of "999" as an example. We might have made this key any value – preferably something that might lend itself to filtering in browses and reports, as we mentioned earlier, from our cube.)

18.  Right-click the Explore DimEmployee Table tab.

19.  Select Close from the context menu, as we did before, to dismiss the Explore DimEmployee Table tab.

We are returned to the Data Source View Designer. We will continue our work in the subsequent article, within which we will create a basic cube structure, and then focus on the use of Named Calculations that we have created within this session, to support the dimensional structure of our cube.

20.  Select File --> Save All from the main menu, to save all work for Part II, as shown in Illustration 39.


Illustration 39: Saving Our Work for Part II ...

21.  Select File --> Exit to leave the Business Intelligence Development Studio, when desired.

Conclusion

In this, the first half of a two-part article, we began an examination of Named Calculations. We prepared Analysis Services, as well as our environment, for the steps in Part I and Part II, creating an Analysis Services project within which to create Named Calculations, and from which to put them to work within a basic cube. We created a Data Source containing the information Analysis Services needed to connect to our source database (the AdventureWorks DW sample), and then created a Data Source View.

Having completed preparation for the focus of our session, we got some hands-on exposure to the design and creation of Named Calculations within the Data Source View. This positioned us to put the Named Calculations to work in Part II, where we will create a dimensional structure for our "aging buckets," which we will support with our new Named Calculations. We will then deploy and browse our cube, to verify the effectiveness of our solution.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers