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 - Page 5

By William Pearson

The Date appears in the tree, underneath the Transaction Date level. We wish to insert the new level above the Transaction Date level, and so we will re-arrange it in the next step.

16.  Select The_Date in the tree.

17.  Drag The_Date to a position on top of the Transaction Date level

18.  Drop the Transaction Date level, to "swap places" with the Transaction Date level.

The_Date appears in the tree, now as the first level, as depicted in Illustration 18.


Illustration 18: Moving the New Level to the "Level One" Position ...

19.  Click The_Date in the Dimension tree to select it, if necessary.

20.  Replace The_Date in the Name box of the Properties panel with the following:

Aged Period

21.  Replace the current text in the Member Key Column box, "time_by_day"."the_date", with the following:

IIF(DateDiff('d', "time_by_day"."the_date", '12/31/1997')< 30, 29, 
IIF(DateDiff('d', "time_by_day"."the_date", '12/31/1997')>= 30 AND DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')<= 60, 60, IIF(DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')>= 61 AND DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')<= 90, 90, IIF(DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')>= 91 AND DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')<= 120, 120, 999))))

NOTE: The "SQL Server equivalent," wherein we would leverage the CASE statement to achieve the same results, would be approximated by the following:

CASE WHEN DATEDIFF(Day, "dbo"."time_by_day"."the_date", '12/31/1997') < 30 
THEN 29 WHEN DATEDIFF(Day, "dbo"."time_by_day"."the_date", '12/31/1997') 
BETWEEN 30 AND 60 THEN 60 WHEN 
DATEDIFF(Day,"dbo"."dbo"."time_by_day"."the_date", '12/31/1997') BETWEEN 
61 AND 90 THEN 90 WHEN DATEDIFF(Day,"dbo"."time_by_day"."the_date", 
'12/31/1997') BETWEEN 91 AND 120 THEN 120 ELSE 999 END

In the above, we are simply setting up a key for each bucket. The key could be anything intuitive – I chose numbers that would support sorting, but the keys could certainly be defined as anything that makes sense for the environment in which the cube resides.

22.  Replace the current text in the Member Name Column box, "time_by_day"."the_date", with the following:

IIf(DateDiff('d', "time_by_day"."the_date", '12/31/1997')< 30, '< 30', 
IIF(DateDiff('d', "time_by_day"."the_date", '12/31/1997')>= 30 AND DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')<= 60, '30 - 60', IIF(DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')>= 61 AND DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')<= 90, '61 - 90', IIF(DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')>= 91 AND DateDiff('d', 
"time_by_day"."the_date", '12/31/1997')<= 120, '91 - 120', '> 120'))))

NOTE: The "SQL Server equivalent," wherein we would leverage the CASE statement to achieve the same results, would be similar to the following:

CASE WHEN DATEDIFF(Day, "dbo"."vW_MSAS_Inventory"."Received Date", 
'01/31/2005') < 30 THEN '< 30' WHEN 
DATEDIFF(Day,"dbo"."vW_MSAS_Inventory"."Received Date", '01/31/2005') 
BETWEEN 30 AND 60 THEN '30 - 60' WHEN 
DATEDIFF(Day,"dbo"."vW_MSAS_Inventory"."Received Date", '01/31/2005') 
BETWEEN 61 AND 90 THEN '61 - 90' WHEN 
DATEDIFF(Day,"dbo"."vW_MSAS_Inventory"."Received Date", '01/31/2005') 
BETWEEN 91 AND 120 THEN '91 - 120' ELSE '> 120' END

Our changes appear in the Properties pane, as partially shown in Illustration 19.


Illustration 19: Changes in the Dimension Editor (Partial View)

23.  Click the Advanced tab in the Properties pane.

24.  Select Key in the Order By box, as depicted in Illustration 20.


Illustration 20: Select Key in the Order By Box ...

25.  Click the Aged Period dimension atop the tree to select it.

26.  Click the Advanced tab, in the Properties pane, as we did with the Time dimension earlier.

27.  Type (or cut and paste) the following into the Source Table Filter box within the Advanced Properties tab:

"time_by_day"."the_date" >#05/31/1997# AND "time_by_day"."the_date" <#01/01/1998#

Our addition to the Source Table Filter box for the Aged Period dimension appears just as it did for the Time dimension earlier.

Let's process the Aged Period dimension, saving our changes as part of the process.

28.  Right-click the Aged Period dimension, atop the tree pane.

29.  Select Process Dimension ... from the context menu that appears, as shown in Illustration 21.


Illustration 21: Select Process Dimension from the Context Menu

30.  Click Yes on the dialog that next asks if we wish to save the dimension.

The Process a Dimension dialog appears next, defaulted to the Rebuild the dimension structure processing method.

31.  Click OK to rebuild the dimension structure.

Processing commences and completes rapidly, indicated, once again, by the green Processing completed successfully message at the bottom of the Process viewer.

32.  Click Close to dismiss the Process viewer.

33.  Click the Data tab in the Dimension Editor.

34.  Expand the All Aged Period level that appears.

The new Aged Period dimension appears on the Data tab, reflecting the aging buckets we have created, as depicted in Illustration 22.


Illustration 22: The Aging Buckets Appear on the Data Tab of the Dimension Editor

We note that, within each of the bucket members of the Age level, the corresponding transaction dates are captured. This provides a quick "reasonableness" test that the buckets are aggregating data properly.

35.  Select File --> Exit from the main menu to close the Dimension Editor.

We return to Analysis Manager.



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