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 Jun 21, 2004

Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation - Page 6

By William Pearson

The Step 1 of 3 dialog is a great example of how the wizard makes design straightforward and rapid - assuming planning (based upon a solid understanding of the business requirements of the information consumers) has taken place before we embark upon cube design. We simply select from a list the data source fields that we wish to present, and how we wish to summarize each of those fields, in an efficient and easy-to-use screen.

In this step, it is important that we decide which of our source data fields it makes sense to use as data fields. Data fields contain values (that is, they are measures) that we want to summarize, such as store costs for which information consumers have a need for totals. The wizard requires that we select at least one field to be a data field.

When the dialog initially appears, the wizard has several boxes checked already. These are selected by the well-meaning (but not necessarily correct) wizard, based upon its conclusion that these fields appear to contain measure-like data. It "proposes" them, as a result, for selection in this step. It is critical to verify whether the wizard's proposals are correct, and to make any changes to fit our business requirements. The fields that we leave unchecked in this step will comprise the set of available dimension fields in Step 2, from which we will select and organize those we need to design our dimension hierarchy structures

18.  Fill out the Step 1 0f 3 dialog, ensuring that only the settings in Table 1 below exist (clearing any unwanted checkboxes).

Source Field

Summarize By

Data Field Name

store_sales

Sum

Store Sales

store_cost

Sum

Store Costs

unit_sales

Sum

Store Unit Sales


Table 1: Initial Measures List with Suggested New Names

In the above settings, we made minor modifications to the field names, as we might to fit terminology that exists in current reports, and so forth.

The OLAP Cube Wizard - Step 1 of 3 dialog now partially appears, with all relevant selections displayed, as shown in Illustration 12.


Illustration 12: Step 1 of 3 Dialog (Partial View) with Our Selections

19.  Click Next.

The OLAP Cube Wizard - Step 2 of 3 dialog appears.

In this step, we organize the descriptive data into dimensions, each of which can be used as a field in any report we generate from our cube. The organization of the fields in levels of detail that we design at this stage should allow information consumers to select the level of detail to view, starting with high-level summaries, drilling to details and zooming back to summaries as appropriate to meet their reporting needs.

The wizard requires that we designate at least one dimension for a cube. We can designate fields that provide isolated facts, and do not belong in any particular hierarchy, such as the Store Type in our example, as dimensions with a single level. Rather obviously, our cube will be more useful for reports if we design some of the fields, as levels, to "roll up" to higher levels and dimensions.

To create a level within a dimension, drag each field from the Source Fields list onto an existing dimension or level in the Dimensions box, as shown in the following steps. To rename a selection, simply right-click and select Rename from the shortcut menu that appears. (The "click label and wait" routine also enables the direct typing of changes.)

20.  Move the selections shown below in Table 2 from the Source fields ("Table Name" in Table 2) list on the left to the appropriate position in the Dimensions list on the right. (To correctly place the dimensions / levels under the dimensions, use the "template" guide that automatically adjusts itself to remain at the bottom of the existing Dimension list, for each new dimension created.)

21.  Rename each "Table Name" selection, with the suggested "New Name" below it, as shown in Table 2. (See Illustration 13, below Table 2, to clarify any confusion as to placement).

Dimension

Level1

Level2

Level3

Table Name

product_category

product_category

product_subcategory

product_name

New Name

Product

Category

Subcategory

Product Name

Table Name

sales_country

sales_country

sales_state_province

sales_city

New Name

Sales

Sales Country

Sales State

Sales City

Table Name

store_country

store_country

store_state

store_city

New Name

Store

Store Country

Store State

Store City

Table Name

the_year

the_year

quarter

the_month

New Name

Time

Year

Quarter

Month


Table 2: Initial Dimensions List with Suggested New Names

The OLAP Cube Wizard - Step 2 of 3 dialog now resembles that partially illustrated in Illustration 13.


Illustration 13: Partial View of Step 2 of 3 Dialog with Our Selections

22.  Click Next.

The OLAP Cube Wizard - Step 3 of 3 dialog appears.

23.  Select the radio button with the caption Save a cube file containing all data for the cube by clicking it, if necessary.

24.  Select a convenient location in which to save the cube file. (I left mine at default.)

25.  Click Save, as required.

The OLAP Cube Wizard - Step 3 of 3 dialog now appears similar to Illustration 14 below.


Illustration 14: Step 3 of 3 Dialog with Cube Type Selection and File Name / Location



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


















Thanks for your registration, follow us on our social networks to keep up-to-date