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 7

By William Pearson

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.



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