Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 18, 2003

Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II - Page 6

By William Pearson

The selected measure fields appear in the Measures folder (initial caps is an automatic conversion feature, as we have noted in earlier lessons). Let's adjust a couple of setpoint for our new measure.

8.      Select the new Amount measure by clicking it.

9.      Click the Advanced properties tab.

10.  Click the Data Type property.

11.  Select Currency from the dropdown selector.

12.  Click the Display Format property.

13.  Select Currency from the dropdown selector.

The new measure, together with the property settings we have put in place, appears as shown below.

Illustration 20: The Newly Added Amount Measure, with Advanced Property Settings

We will add dimensions next.

14.  Add the following dimensions to the Dimensions folder, by dragging each from Revenue_fact_Query onto the folder, in the following order:

  • time_id
  • store_id
  • product_id

The selected dimension key fields appear in the Dimensions folder (sans underscores, and with initial caps, once again) as shown below:

Illustration 21: The Dimensions Folder with Newly Added Dimensions

We will process the core cube at this stage.

Processing the Cube

Let's process our new Revenue cube, and then take a look at the results via the Preview pane on the Data tab.

1.      Select Tools from the main menu.

2.      Click Process Cube, as shown below.

Illustration 22: Select Tools -> Process Cube

NOTE: We might have alternatively chosen to right-click the cube, and to have selected Process Cube from the context menu. We could also have chosen the Process Cube button on the toolbar.

The Save the Cube dialog appears as shown below.

Illustration 23: The Save the Cube Dialog

3.      Click Yes.

The Cube: New Name dialog appears.

4.      Name the cube Fin_Rptg_Rev, as shown in Illustration 24.

Illustration 24: The Cube: New Name Dialog

5.      Click OK.

A message box (shown below) appears, warning that the cube has no aggregates designed, and asking if we want to design aggregations prior to processing the cube.

Illustration 25: Warning Message Box: No Aggregates Designed

6.      Click No.

The Process a Cube dialog appears, as shown in Illustration 26. The Full Process option is selected by default, since this is the first time the cube has been proposed for processing.

Illustration 26: The Process a Cube Dialog

7.      Click OK.

Cube processing begins, as evidenced in the status display of the Process dialog that appears. The Process dialog displays status events as the cube processes, and then displays a "Processing Completed Successfully" statement in green at the bottom of the dialog. The dialog appears as shown below at completion of processing.

Illustration 27: The Process Dialog, Displaying Status

8.      Click Close.

9.      Click the Data tab.

After a "Retrieving Data" message briefly appears, we see the actual data presented in the Preview pane, as shown below.

Illustration 28: Actual Data in the Preview Pane (Partial View)

If we switch out the Calendar.Time dimension with the others appearing in the upper pane of the data tab, we see that the dimensions, currently displaying as Product Id and Store Id, are represented by numbers (the ID fields in the fact table) within the various views we can access. This comes as no surprise, as we have used only the ID keys to create the dimensions, much as we did in the previous lesson and others, to limit the initial cube build to a central fact table.

As we learned in Part I, we must process the cube before browsing actual data anytime we are submitting a new cube for processing, alter a previously processed cube's structure, or undertake material data changes in the source data.

10.  Select File --> Exit to leave the Cube Editor for now, and to return to the Analysis Manager console.

MS SQL Archives

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