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 Mar 13, 2006

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

By William Pearson

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.



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