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 2

By William Pearson

Introducing Named Calculations (Continued...)

Overview and Discussion

We will return to the Business Intelligence Development Studio, which, as we stated in Part I, provides the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005.

Having created an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop our current cube and its supporting objects, we have created a Data Source and Data Source View (containing our Named Calculations examples). We are ready to leverage the Cube Wizard next, to quickly design and create a cube, allowing us to focus on the subject matter of the article with minimal peripheral distraction. As we have noted elsewhere, the Cube Wizard not only helps us simplify the design and creation of our cubes, as it did within Analysis Services 2000: the Analysis Services 2005 Cube Wizard is more powerful, leveraging IntelliCube technology to examine and classify many of the attributes of our data. Analysis Services can determine, for example, prospective fact tables, dimensions, hierarchies, levels and other structural members of our cubes from a given database schema to which it is pointed. We further pointed out that, regardless of whether we make a habit of using the wizard in our cube development efforts, it certainly provides a way to rapidly generate a cube, if only to eliminate part of the repetitive work involved to create a "starting point" model, which we can then "prune and groom" to more precisely meet the business requirements of our employers and customers.

Considerations and Comments

As we stated prior to beginning Part I, for purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed. For more information on installing the samples, please see the Considerations and Comments section in Part I.

NOTE: It is necessary to have completed Part I to participate in this practice session.

Hands-On Procedure (continued ...)

We will pick up where we left off in Part I, having created three Named Calculations. Recall that the first Named Calculation we created, ANSYS045_Gender Description, was a simple example that was based upon a simple CASE statement. The second and third Named Calculations, ANSYS045_Aging_Bucket_Key and ANSYS045_Aging_Bucket_Name, were also based upon a CASE statement (albeit a slightly more complicated one). The first Named Calculation simply provided the word, "Male" or "Female," based upon the "M" or "F" provided in the source database, which might be used to support a more user-friendly Gender description within the intended cube. (We will not work further with this simple example in our current session). The purpose of the second pair of Named Calculations was to support the population of data, via its transaction dates, into "aging buckets", with one being designed to supply a numeric key to Analysis Services within the cube, and the other to supply a character-based description of the "aging bucket" (ANSYS045_Aging_Bucket_Key and ANSYS045_Aging_Bucket_Name, respectively).

Procedure (continued...)

1.  Restart the SQL Server Business Intelligence Development Studio.

We again briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

2.  Close the Start page, if desired.

3.  Select File --> Open from the main menu.

4.  Click Project / Solution ... from the cascading menu, as shown in Illustration 1.

Illustration 1: Selecting a Project ...

The Open Project dialog appears.

5.  Browse to the location where we left the ANSYS045 Named Calculations solution at the conclusion of Part I.

NOTE: It is necessary to have completed Part I to participate in this practice session.

6.  Select the ANSYS045 Named Calculations.sln file within the appropriate folder, as depicted in Illustration 2.

Illustration 2: Selecting the Part I Solution File ...

7.  Click Open.

The ANSYS045 Named Calculations solution opens within the Designer, and we again see the Data Source View that we created in the last session. (If we closed the Data Source View last session, right click it in the Solution Explorer, and select Open from the context menu that appears, to open it.)

The Designer environment appears as depicted in Illustration 3.

Illustration 3: We Return to the Design Environment

We are now ready to build a basic cube, to provide a vehicle for seeing our Named Calculations in action.

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