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

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.

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.

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.

The Create Named Calculation dialog appears.

ANSYS045_Aging_Bucket_Key

Numeric Aging Bucket Key

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.

The dialog closes.

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

The Create Named Calculation dialog appears.

ANSYS045_Aging_Bucket_Name

Aging Bucket Name

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.

The dialog closes.

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.

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.

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

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

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.

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.