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 Apr 10, 2006

Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II - Page 3

By William Pearson

Create a Basic Cube

We will create a basic cube, to house our Named Calculations, and to see how we put them to work in the dimensional structure. We will focus on other areas within the overall design and creation process in other articles of the series – our primary objective here is to see how we use the Named Calculations we have created – not to concern ourselves with the myriad other topics contained within general cube design.

1.  Right-click the Cubes folder within the Solution Explorer.

2.  Select New Cube from the context menu that appears, as shown in Illustration 4.

Click for larger image

Illustration 4: Select New Cube to Begin Cube Creation

The Welcome to the Cube Wizard dialog appears, as depicted in Illustration 5.

Click for larger image

Illustration 5: The Welcome to the Cube Wizard Dialog Appears ...

3.  Click Next.

4.  Ensure that Build the cube using a data source checkbox is selected on the Select Build Method dialog that appears next, along with the Auto build checkbox, and with Create attributes and hierarchies selected in the drop-down selector underneath the Auto build setting.

These default settings are shown in Illustration 6.

Click for larger image

Illustration 6: Ensuring the Default Settings on the Select Build Method Dialog

These settings will allow the Wizard to leverage the IntelliCube technology built into Analysis Services 2005, and to detect the relationships between attributes, dimensions, and facts within the data source we have defined. While we will not accept all the judgments that the wizard makes, we can still take advantage of the actions it performs, and modify or eliminate those that are inappropriate or of no use to us.

5.  Click Next.

6.  Ascertain that the Data Source View we have created, Adventure Works DW, is selected within the Available data source views list on the left side of the Select Data Source View dialog, which appears next. (The tables contained within the Data Source View are displayed on the right side of the dialog).

The Select Data Source View dialog appears, as depicted in Illustration 7.

Illustration 7: Select AdventureWorks DW as the Data Source View

7.  Click Next.

The Cube Wizard begins scanning the relational schema, identifying fact and dimensional tables, as indicated on the Detecting Fact and Dimension Tables page that appears next. We quickly receive a message indicating that the Wizard has completed its analysis of the relationships between tables in the Data Source View, and that it proposes its suggestions on the following page, as shown in Illustration 8.

Illustration 8: The Detecting Fact and Dimension Tables Page Indicates Completion ...

8.  Click Next.

The Identify Fact and Dimension Tables dialog next appears. Let’s select a Time dimension table, and narrow the table selection somewhat, for the purposes of our rudimentary cube.

9.  Select the DimTime table in the drop-down Time dimension table selector.

10.  Uncheck the following checkboxes:

Fact Column: DimReseller (leave checked within Dimension column)

Dimension Column: DimCurrency

NOTE: We can also make table selections graphically via the Diagram tab of the Identify Fact and Dimension Tables dialog.

The Identify Fact and Dimension Tables dialog appears, as depicted in Illustration 9.

Illustration 9: The Identify Fact and Dimension Tables Dialog, with Our Modifications

11.  Click Next.

The Select Time Periods dialog appears.

12.  Leaving any unspecified columns blank, select the Time Table Columns values shown in Table 1 for each of the listed Time Property Names.

Time Property Name

Time Table Columns









Table 1: Selections within Respective Time Table Columns in Select Time Periods Dialog

The Select Time Periods dialog appears, with our selections, as shown in Illustration 10.

Illustration 10: The Select Time Periods Dialog, with Our Selections

13.  Click Next.

The Select Measures dialog appears next.

14.  Uncheck the following checkboxes:

  • Revision Number
  • Discount Amount
  • Product Standard Cost
  • Tax Amount
  • Freight

The Select Measures dialog appears, as depicted in Illustration 11.

Illustration 11: The Select Measures Dialog, with Adjusted Selections

15.  Click Next.

The Detecting Hierarchies page appears next, as hierarchy detection begins. We receive rapid indication that dimension tables have been scanned and hierarchies detected, as shown in Illustration 12.

Illustration 12: The Detecting Hierarchies Page, with Indication of Relationships Detected

16.  Click Next.

We move to the Review New Dimensions page, which appears as depicted in Illustration 13.

Illustration 13: The Review New Dimensions Page

17.  Click Next.

The Completing the Wizard page appears next.

18.  Change the default Cube name to the following:

ANSYS045 - Named Calculations

The Completing the Wizard page appears as shown in Illustration 14.

Illustration 14: The Completing the Wizard Page

19.  Click Finish.

The Wizard concludes, and the Cube Designer opens, as depicted in Illustration 15.

Illustration 15: The Cube Designer Opens ...

Here, within the multi-tabbed Cube Designer component of the Business Intelligence Development Studio, we can perform myriad enhancements and extensions to, and operations surrounding, the existing cube. We will be examining these new features in prospective articles, where we define business needs and then meet them with the appropriate functionality(ies). For the purposes of this article, let’s put our new Named Calculations to work, and verify their effectiveness in our basic cube.

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