Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube - Page 7
August 8, 2005
12. Expand the Sales cube, by clicking the "+" sign to its immediate left.
13. Expand the Partitions folder that appears underneath the expanded Sales cube.
14. Right-click the sole partition, named Sales, which appears within the expanded folder.
15. Select Edit from the context menu that appears, as depicted in Illustration 28.
Click for larger image
The first page of the Partition Wizard appears, as shown in Illustration 29.
Click for larger image
16. Without making any changes, click Next.
17. Click Next again, at the Select the data slice (optional) page, as depicted in Illustration 30.
18. Click Next, once again, on the Specify the partition type page that appears, as shown in Illustration 31.
We arrive at the Finish the Partition Wizard page.
19. Click the radio button to the left of Design the aggregations later on the dialog.
20. Click Advanced, as depicted in Illustration 32.
The Advanced Settings dialog opens.
21. Type (or cut and paste) the following syntax into the filter statement box:
"time_by_day"."the_date" > #05/31/1997# AND "time_by_day"."the_date" < #01/01/1998#
The Advanced Settings dialog appears as shown in Illustration 33, with the syntax we have added.
Now let's set up a drillthrough capability that will help us quickly evaluate the effectiveness of our efforts in the Verification section that follows.
22. Click the Drillthrough Options button in the bottom section of the Advanced Settings dialog.
The Partition Drillthrough Options dialog opens.
23. Within the selection checklist, ensure that only the checkboxes presented in Table 1 below are checked:
Table 1: Select Settings for the Detail Drillthrough View ...
Our intent here is simply to establish the display of dated transactions to give us a feel that transactions within reasonable date ranges are, indeed, underneath the totals we see in the age buckets. The columns selected here could obviously be varied to accomplish other specific needs just as easily.
The Partition Drillthrough Options dialog (partial view of the selection checklist) appears as depicted in Illustration 34, with the syntax we have added.
24. Click OK to accept changes and close the Partition Drillthrough Options dialog.
25. Click OK once again, this time to accept and close the Advanced Settings dialog with our input.
26. Click the Finish button on the Finish the Partition Wizard page, to which we return next.
27. Click OK on the Fact Table Row Count message box that appears next, as shown in Illustration 35.
The Fact Table Row Count message box closes. Now all that remains is to process the cube, and to verify the operation of our new aging capability using the Cube Browser.