The general consensus within current data warehousing and business intelligence communities is that the preferred structure for presenting quantitative and other organizational data to information consumers lies within the dimensional model. The dimensional model has consistently been shown to meet the primary objectives of business intelligence: to present relevant and accurate information representing business operations and events, and to return query results rapidly and accurately. The dimensional model is particularly optimal in its support of “slice and dice” query creation and modification; it supports an environment wherein information consumers can pose quick and easy questions, and achieve rapid results datasets, using various perspectives called dimensions.
In this, Part I of a two-part article, the first of several articles focusing upon dimensional model components in SQL Server Analysis Services 2008 / 2008 R2, we will gain an introduction, with hands-on exposure, to dimensions within a sample cube. Our examination will include:
- An introduction to the Dimensional Model and a discussion of its role in meeting the primary objectives of business intelligence.
- An introduction to Dimensions from a general perspective, including the two primary dimension types within Analysis Services.
- An examination of the Database Dimensions and the Cube Dimensions that exist within our sample UDM, together with a discussion surrounding the differences between the two dimension types.
- Coverage of the general concepts and properties (including what they define and support, and how we can manage them) underpinning Database Dimensions.
- A review of the Properties associated with a Database Dimension, based upon the examination of a representative dimension within our sample UDM.
- A look ahead to Part II of this article, where we explore the Properties associated with a representative Cube Dimension.
Dimensions in Analysis Services
Dimensions form the foundation of the dimensional model. They represent the perspectives of a business or other operation, and reflect the intuitive ways that information consumers need to query and view data. To use an analogy, if we consider the facts or measures contained within our business intelligence system as verbs (or actions / transactions undertaken by the business), then the dimensions might be considered as nouns that take part in, or are otherwise associated with, those verbs. Common examples of dimensions include time (or date), customer, product, geography, lab type, campus, patient, promotions, gender (and other demographics), and so forth. Each dimension is associated with the facts / measures to which it relates via the linkages / joins between the table(s) housing the dimension (the dimension table) and the fact table. As in illustration (found within the Adventure Works sample environment), the link / join between the DimCustomer table and the FactInternetSales table reflects that the Customer dimension participates in sales, order quantities, discounts, freight, and various other measures.
Because we typically support each dimension with an underlying table(s) within a database, we commonly see a complete, discrete list of dimension members within each table. (Within each single member row are also included various attributes that describe, classify, and otherwise act as adjectives to the dimension noun. We will examine dimension attributes in subsequent articles here at BIDN.com.)
We can identify dimensions as a part of business requirements gathering when we discuss the perspectives by which information consumers within the organization wish to be able to analyze, or report upon, the activities, results, and other facets of business operation. As an illustration, we might determine early in our requirements gathering efforts that information consumers need to see Internet Sales by geography, by month, by product, or by a combination of these perspectives, among others. Such perspectives need to be included within our design, within the appropriate dimension tables (or snowflake tables linked to those tables, as appropriate).
We will get some hands-on exposure to dimensions inthe practice session below. Before we get started working within a sample cube clone, we will need to prepare the local environment for the practice session. We will take steps to accomplish this within the section that follows.
Preparation: Create a Sample Basic UDM within Which to Perform the Steps of Our Practice Session
Create Sample Primary Analysis Services UDM for the Practice Exercise
Before getting started with our practice session, we will need a basic sample Analysis Services UDM (containing a cube and other objects) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, where we will create a copy of the sample Analysis Services Project that is available to anyone installing SQL Server 2008 / SQL Server 2008 R2 Analysis Services.
NOTE: The samples do not self-install, and are designed to accompany various SQL Server 2008 application tutorials that appear on the installation media, as well as online at www.microsoft.com and elsewhere.
As stated in the Books Online, there are many code samples and sample databases available for Microsoft SQL Server. These samples are no longer shipped with SQL Server. Instead, you can browse through and download the samples in several different projects on CodePlex.com. There is a samples project portal which directs you to all the other SQL Server sample projects on CodePlex at the following URL (as of this writing):
There has been confusion surrounding the installation of the samples, to say the least. In particular, please review the information concerning the Analysis Services 2008 / 2008 R2 database samples at the following URL (as of this writing):
The focus of this procedure is to make a copy of the sample Analysis Services database, which we will then deploy and use as a rudimentary database (containing a cube, from within which we can perform various practice exercises. The Adventure Works DW database would typically be installed after the initial installation of SQL Server 2008 / SQL Server 2008 R2 and components along with various other samples for use throughout the application suite.
To begin, launch the SQL Server Business Intelligence Development Studio.
1. Click Start.
2. Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.
The equivalent on my PC appears as depicted in Illustration 1.
Illustration 1: Launching SQL Server Business Intelligence Development Studio
We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.
3. Close the Start page, if desired.
4. Select File -à Open from the main menu.
5. Click Project / Solution … from the cascading menu, as shown in Illustration 2.
Illustration 2: Selecting a Project …
The Open Project dialog appears.
6. Browse to the location of the appropriate Adventure Works Analysis Services sample project folders.
If installation of the samples from the CodePlex site (noted above) has been accomplished, multiple versions of the sample Adventure Works Analysis Services Project (one for each of the 2008 and 2008 R2 versions) may appear, (by default, and, of course, subject to be installed in different locations on our individual machines) in the following location:
C:\Program Files\Microsoft SQL Server\100\Tools\Samples
Of the various samples, we will select the Enterprise project in the AdventureWorks 2008R2 Analysis Services Project, as it contains the UDM for a more evolved database and cube. The Standard database will suffice for the vast majority of the steps we take in this article, too, however.
7. Right-click the Enterprise folder, containing the sample Adventure Works solution,project and associated objects.
8. Select Copy from the context menu that appears, as depicted in Illustration 3.
Illustration 3: Copy the Sample Analysis Services Project Folder …
9. Navigate, from within the Open Project dialog box, to the location of preference for storing the folder we have copied, and from which we will create our “clone” project.
10. Upon arrival within the desired folder, right-click the area within the folder.
11.Select Paste from the context menu that appears, as shown in Illustration 4.
Illustration 4: Paste the Copy of the Basic Analysis Services Project to a Selected Location
The project (Enterprise, in the illustration)folderappears, via the Open Project dialog, in the new location, as depicted in Illustration 5.
Illustration 5: Sample Analysis Services Project in Its New Location …
12.Right-click the project folder, within the Open Project dialog.
13.Select Rename from the context menu that appears, as shown in Illustration 6.
Illustration 6: Renaming the Folder …
14. Type the following replacement name into the folder’s activated caption box:
MSBI_Basic Analysis Services Project
15.Click outside the caption box to accept the new name, which then appears as depicted in Illustration 7.
Illustration 7: Newly Named Folder …
16.Click the new folder, to select it.
17.Click the Open button on the Open Project dialog.
The solution file, Adventure Works.sln, appears at this point, along with folders containing associated objects.
18.Select the Adventure Works.sln file, and then click the Open button, as shown in Illustration 8.
Illustration 8: Opening the Adventure Works.sln File
The Adventure Works sample solution opens, and we see the various associated objects appear in Solution Explorer, as depicted in Illustration 9.
Illustration 9: The Cloned Project and Objects, within the Solution Explorer
19.Right-click the Adventure Works DW 2008 project, within the Solution Explorer.
20.Select Rename from the context menu that appears.
21.Type the following replacement name into the project’s activated caption box:
MSBI_Basic A S Project
22.Click outside the caption box to accept the new name.
23.Click Yes on the dialog box that appears next, asking if we would like to change the object name, as well.
24.Rename the cube within the project from Adventure Works.cube to the following:
Basic.cube
25.Click Yes on the dialog box that appears next, once again asking if we would like to change the object name, as well.
The newly renamed objects appear within the Solution Explorer, as shown in Illustration 10.
Illustration 10: The Newly Renamed Objects Appear …
As a final step, we will modify the targeted database, to prevent overwrite of the existing (if it has, indeed, already been deployed in your environment) sample database on the Analysis Server (to preserve it for working directly with the tutorials that come with Analysis Services 2008 and other uses).
26.Right-click the new project, MSBI_Basic A S Project, within the Solution Explorer.
27.Select Properties from the context menu that appears, as depicted in Illustration 11.
Illustration 11: Select Properties for the Project
The MSBI_Basic A S Project Properties Pages dialog opens.
28.Click Deployment, under Configuration Properties, in the tree in the left-hand pane of the dialog.
29.Replace the current text (Adventure Works DW 2008R2), as appropriate, in the Database box (in the lower right half of the dialog, within the Target properties group) with the following:
MSBI_Basic AS DB
30.Adjust the Server name, as appropriate, while within the Target properties group subsection of the Deployment section of the Configuration Properties.
The newly renamed Target Database (and Server, as appropriate) appears within the MSBI_Basic A S Project Properties Pages dialog, as shown in Illustration 12.
Illustration 12: The Newly Renamed Target Properties
31.Click OK to accept our modifications and to dismiss the MSBI_Basic A S Project Properties Pages dialog.
Ascertain Connectivity to the Underlying Relational Data Source
Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running “side-by-side” installations of a combination of MSSQL Server 2000 and / or MSSQL Server 2005, and SQL Server 2008 / SQL Server 2008 R2, but it is important to check even if not). You can do this by performing the steps of the following procedure:
From within the open Analysis Services project, in the SQL Server Business Intelligence Development Studio, take the following steps:
- Double-click Adventure Works.ds, within the Data Sources folder seen in Solution Explorer.
The Data Source Designer opens, defaulted to the General tab, and appears with default settings as depicted in Illustration 13.
Illustration 13: The Data Source Designer with Default Settings …
- Click the Edit button on the Data Source Designer dialog.
The Connection Manager opens, and appears with default settings shown in Illustration 14.
Illustration 14: The Connection Manager with Default Settings …
We note that the default Server name is “Localhost.” While this might prove an adequate setting for a PC with only SQL Server 2008 / SQL Server 2008 R2 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that identifies the correct SQL Server 2008 / SQL Server 2008 R2 instance. (Clicking the Test Connection button at this point will provide confirmation as to whether we need to modify Server name).
- If appropriate, type the correct server / instance name into the Server name box of the Connection Manager. (Mine is TEMPLE001\SQLSERVER2K8R2, as depicted in Illustration 15.)
Illustration 15: The Connection Manager, with Corrected Settings …
- Ensure that AdventureWorksDW2008R2 (or the local equivalent of same) is selected in the box labeled Select or enter a database name in the Connect to a database section of the Connection Manager dialog.
- Ensure that authentication settings are correct for the local environment.
- Click the Test Connection button.
A Connection Manager message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are proper. The message box appears as shown in Illustration 16.
Illustration 16: Testing Positive for Connectivity …
- Click OK to dismiss the message box.
- Click OK to accept changes, as appropriate, and to dismiss the Connection Manager.
The Data Source Designer reappears, with our modified settings, similar to that depicted in Illustration 17.
Illustration 17: The Data Source Designer with Modified Settings …
- Click OK to close the Data Source Designer, and to return to the development environment.
Once we have created the new sample project, and ascertained connectivity, we are ready to deploy it to the Analysis Server by performing the steps of the following procedure (substitute object names for those appropriate to your own environment, when following the procedure).
Deploy the Analysis Services Project
Having ensured alignment of the appropriate deployment server, and having modified the name of the destination Analysis Services database, if necessary, to prevent the write over of the pre-existing sample Analysis Services database, we are ready to deploy the new Analysis Services project.
From inside Business Intelligence Development Studio for the Analysis Services project you wish to deploy:
- Right-click MSBI_Basic A S Project in the Solution Explorer.
- Select Deploy from the context menu that appears, as shown in Illustration 18.
Illustration 18: Initiating Analysis Services Project Deployment...
Deployment begins. Deployment and processing can be monitored from the Deployment Progress viewer, where the steps of processing are logged as they transpire. Once processing finishes, we receive a Deployment Completed Successfully message in the Status bar at the bottom of the viewer, as depicted in Illustration 19.
Illustration 19: "Process Succeeded" Message
Having deployed the new database, we are ready to begin the procedural part of our practice session.
Procedure: Examine Dimension Properties in Analysis Services 2008 R2
In the practice procedures that follow, we will examine the properties, which we have introduced in the foregoing sections of the article. We will examine the properties for a representative database dimension within this section of this,Part I,of the article; we will cover the properties that specifically define and support Cube dimensions within the similar procedural section of Part II. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will perform our overviews of dimensions within our new Analysis Services database, MSBI_Basic AS DB.
Contrast Cube Dimensions with Database Dimensions
Let’s examine both Database dimensions and Cube dimensions in general, mostly to gain an understanding of the differences between the two, before proceeding to our overview of the properties of Database dimensions.
1. Within the Solution Explorer, expand the Dimensions folder (by clicking the “+” sign to its immediate left), if necessary, to expose the Database dimensions for our new sample UDM.
The Database dimensions appear as shown in Illustration 20.
Illustration 20: The Database Dimensions of Our Sample UDM
We notice that 21 Database dimensions appear within the Dimensions folder of the Solution Explorer.
2. Within the Solution Explorer, right-click the Basic cube (expand the Cubes folder as necessary).
3. Click Open on the context menu that appears, as depicted in Illustration 21.
Illustration 21: Opening the Cube Designer …
The tabs of the Cube Designer open.
4. Click the Cube Structure tab, if it has not already appeared by default.
5. Examine the Cube dimensions that appear within the Dimensions pane of the Cube Structure tab.
The Cube dimensions belonging to our Basic cube appear as shown in Illustration 22.
Illustration 22: The Cube Dimensions, Basic Cube
We notice that 21 Cube dimensions appear within the Dimensions pane of the Cube Structure tab. The fact that the membership lists of the Cube dimensions and the Database dimensions can differ, however, leads us to the consideration of a powerful capability that accrues to dimensional model designers that use Analysis Services: we can reuse single Database dimensions for multiple Cube dimensions. (We can also name the dimensions differently within individual cubes, and so forth.) The benefits we enjoy in doing so include simplified dimension management, reduced overall processing time, and usage of less disk space by our deployed models.
An important fact to grasp when considering the differences between Database dimensions and Cube dimensions is that different properties exist for each dimension type, even though the former serves as the basis for the latter. All other dimensions within the model derive from the Database dimension. The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties, as we shall see.
To optimize the numerous properties settings that are available to us within our design and implementation efforts, we need to understand the different settings that are made in each of the Database and Cube dimensions. These settings become confusing to many who are new to Analysis Services, and so we will examine them individually beginning with our next section, where we will take up the few properties involved with Database dimensions. We will examine the properties for Cube dimensions in Part II of this article.
Review Database Dimension Properties
We can modify, or even delete, a Database dimension by using the Dimension Designer within the Business Intelligence Development Studio. Let’s take a look at the properties in place for a Database dimension within our new sample UDM by taking the following steps.
1. Within the Solution Explorer, right-click the Product dimension (expand the Dimensions folder as necessary).
2. Right-click the Product dimension.
3. Click Properties on the context menu that appears, as depicted in Illustration 23.
Illustration 23: Select Properties from the Context Menu …
The Properties pane appears for the Product dimension. (The Properties pane likely appeared already, by default, below the Solution Explorer. The design environment can, of course, be customized in many ways to accommodate your local environment and development needs.)
We can see the two Object Model properties for the Product dimension within the Properties pane. These are, quite simply, Object ID and Object Name. The Properties pane for the Product Database dimension appears as shown in Illustration 24.
Illustration 24: The Properties Pane for the Product Database Dimension
Object Name can be modified within the Solution Explorer by simply using the Rename selection within the context menu that appears when we right-click on most objects in the Solution Explorer.
We will extend our examination of dimensions yet further in the second half of this article, where we will examine Cube dimensions and the more numerous properties that are associated with them.
NOTE: Please consider saving the project we have created to this point for use in the second half of this article, as well as for subsequent related articles within this subseries, so as to avoid the need to repeat the preparation process we have undertaken above.
4. Select File -à Save All to save our work, up to this point, within the originally chosen location, where it can be easily accessed for Part II of this article, as well is for other articles within this subseries.
5. Select File -à Exit to leave the design environment, when ready, and to close the Business Intelligence Development Studio.
Conclusion
In this, Part I of a two-part article, we began a subseries of several articles focusing upon dimensional model components, with an objective of discussing the associated concepts, and of providing hands-on exposure to the properties supporting each. We began with an introduction to the dimensional model and a discussion of its role in meeting the primary objectives of business intelligence. Next, we introduced dimensions in general, including the two primary dimension types within Analysis Services, Database dimensions and Cube dimensions.
As a means of distinguishing the two types of dimensions, we examined both within the design environment. We compared the Database dimension type to the Cube dimension type. We then reviewed the Properties associated with a Database Dimension, based upon the examination of a representative dimension within our sample UDM. Finally, we looked forward to the second half of this article, where we explore the general concepts and properties (including what they define and support, and how we can manage them) underpinning Cube Dimensions.