Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube - Page 5August 8, 2005 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.
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.
23. Click the Advanced tab in the Properties pane. 24. Select Key in the Order By box, as depicted in Illustration 20.
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.
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.
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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
