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

Illustration 28: Select Edit from the Context Menu that Appears

The first page of the Partition Wizard appears, as shown in Illustration 29.

Click for larger image

Illustration 29: The Partition Wizard Initializes ...

16.  Without making any changes, click Next.

17.  Click Next again, at the Select the data slice (optional) page, as depicted in Illustration 30.


Illustration 30: Click Next on the Select the Data Slice (Optional) Page

18.  Click Next, once again, on the Specify the partition type page that appears, as shown in Illustration 31.


Illustration 31: Click Next on the Specify the Partition Type Page

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.


Illustration 32: Click Advanced on the Finish the Partition Wizard Dialog

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.


Illustration 33: Our Filter Statement in the Advanced Settings Dialog

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:

Column

Table

"product_id"

"sales"fact_1997"

"customer_id"

"sales"fact_1997"

"store_sales"

"sales"fact_1997"

"the_date"

"time_by_day"


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.


Illustration 34: Partial View of the Selection Checklist

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.


Illustration 35: Click OK on the Fact Table Row Count Message Box

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers