Mastering Enterprise BI: Working with Measure Groups - Page 2

Working with Measures and Measure Groups

Overview and Discussion

We will create an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in the past, the pre-assembled Analysis Services Project that makes its home within the Studio assists us in organizing and managing the numerous objects that we will need to support our efforts to create and deploy our Analysis Services database.

We will leverage the Cube Wizard in this article to quickly design and create a cube, allowing us to focus on the subject matter of the article with minimal peripheral distraction. 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 at which it is pointed. 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

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.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Hands-On Procedure

We will begin by creating a new project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

Preparation

Create a New Analysis Services Project

We begin our preparation within Business Intelligence Development Studio, where we will create a new Microsoft SQL Server 2005 Analysis Services (SSAS) Project. As we have noted in other articles, those of us who have worked within earlier versions of Reporting Services, or within the Visual Studio development environment under other circumstances, will recognize the general look and feel of the Studio. Under this new style of development for Analysis Services, to which much of the documentation refers as "project mode," we create an Analysis Services Project, which houses a set of Analysis Services objects. The object set typically includes Data Sources, Data Source Views, Dimensions, Cubes, and other components.

Using an Analysis Services Project template affords us many benefits, such as helping us to organize and manage the items that are required to build, debug, and deploy an Analysis Services database. The development of an Analysis Services cube, which is our objective within this session, as well as one of the most common tasks in Analysis Services, can be accelerated though the use of the Analysis Services 2005 Cube Wizard, which both guides and simplifies the process, as is the way of wizards. IntelliCube technology, which can detect the relationships between attributes, dimensions, and facts (as we have already noted), makes the wizard more effective than its counterpart in Analysis Services 2000.

While we will not go into the processes and structures that underlie our immediate topic, working with Measure Groups, let's make a mental note that all objects that we create and store within a given project are defined as files (using an XML representation) that eventually are deployed to an Analysis Services database.

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.

The New Project dialog appears.

ANSYS048 Measure Groups

The Solution Name changes to match the project Name by default.

The relevant section of the New Project dialog appears similar to that depicted in Illustration 5.

The New Project dialog closes. Visual Studio creates the project, which appears within solution ANSYS048 Measure Groups, in the Solution Explorer as shown in Illustration 6.

Solution Explorer presents a tree view of the objects contained in the solution, the primary management unit within the Business Intelligence Development Studio, which can contain multiple projects. Individual projects, such as the one we have created, themselves contain folders for the objects that can be defined for projects of a similar type. It is, after all, the purpose of an Analysis Services Project to group related files containing the XML code behind the objects in a given Analysis Services database.

As we can see in the present instance, the Analysis Services Project template, upon which our ANSYS048 Measure Groups project was based, contains the following folders:

• Data Sources
• Data Source Views
• Cubes
• Dimensions
• Mining Structures
• Roles
• Assemblies
• Miscellaneous

Anytime we create a new Analysis Services Project, the Solution Explorer and the Properties window are visible and docked, as we see them in the present case. The following windows are hidden and docked, initially, and appear on the right, bottom, or left side of the development environment, depending upon where they are docked:

• Server Explorer
• Toolbox
• Task List
• Error List

Viewing any of the hidden windows is as simple as positioning the pointer over it – its mouseover behavior is to reappear. We can also click the Auto Hide button (depicted for the Solution Explorer in Illustration 7) to hide or unhide a window.

Finally, we can always open a closed window by selecting it from the View menu atop the development environment, as shown in Illustration 8.

Having created a new Analysis Services Project, we are ready to define a Data Source and Data Source View.