About the Series …
This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article see Usage-Based Optimization in Analysis Services 2005, another article within this series.
Introduction
In Dimensional Model Components: Dimensions Part I, we noted that 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. We discussed several reasons why this is the case, including the fact that the dimensional model, through its use of various perspectives called dimensions, meets the primary objectives of business intelligence in numerous ways, including its capacity to support:
- the presentation of relevant and accurate information representing business operations and events;
- the rapid and accurate return of query results;
- “slice and dice” query creation and modification;
- an environment wherein information consumers can pose quick and easy questions, and achieve rapid results datasets.
In this, the second half of our two-part examination of dimensions (which itself represents the first of several articles focusing upon dimensional model component structures), we will gain further introduction, with hands-on exposure, to dimensions within a sample cube. Our examination will include:
- A brief review of Dimensions from a conceptual perspective, including the two primary dimension types within Analysis Services that we discovered in Part I;
- Coverage of the general concepts surrounding Cube Dimensions;
- An examination of the properties (including what they define and support, and how we can manage them) underpinning Cube Dimensions.
- A look ahead to other articles of this subseries, where we explore the attribute, hierarchy, and other components of the dimensional model as implemented by Analysis Services.
Dimensions in Analysis Services: Cube Dimensions
We learned, in Dimensional Model Components: Dimensions Part I, that 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. We noted that we might consider dimensions as nouns that take part in, or are otherwise associated with, the verbs (or actions / transactions undertaken by the business) that are represented by the facts or measures contained within our business intelligence system. We cited common examples, such as time (or date), customer, product, lab type, campus, patient, gender (and other demographics), and discussed how 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(s) ) and the fact table.
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, comparing the Database dimension type to the Cube dimension type. We then focused upon Database Dimensions, reviewing the Properties associated with a representative Database Dimension within our sample UDM.
In this lesson we will concentrate on the other of the two main dimension types we introduced in Part I, the Cube dimension. As we have already discovered, the multidimensional structure of a cube is defined by the dimensions that it contains. The Database dimensions within a given UDM can often be used to define a cube that we create therein, but the Cube dimensions concept affords us the capability to dictate that the dimensions available within such a cube can be a subset of the Database dimensions, and that the structure of the cube can therefore be limited to those dimensions that we define as being visible or used within the cube. Cube dimensions exist at a lower object level than the Database dimensions and the cube itself.
Preparation: Locate and Open the Sample Basic UDM Created in Pt I
In Part I, we created a sample basic UDM within which to perform the steps of the practice sessions we set out to undertake in the various articles of this subseries. Once we ascertained the new practice database appeared to be in place, and once we had renamed it to ANSYS065_Basic AS DB, we began our examination of dimension properties. We will perform our examination of Cube dimensions within the same practice environment, which we will access using the following steps within the SQL Server Business Intelligence Development Studio, as we did within Part I.
NOTE: Please access the UDM which we prepared in Part I before proceeding with this article. If you have not completed the preparation to which I refer in the previous article, or if you cannot locate / access the Analysis Services database with which we worked there, please consider taking the preparation steps provided in Part I before continuing, and saving the objects with which you work, so as to avoid the need to repeat the preparation process we have already undertaken for subsequent related articles within this subseries.
1. Click Start.
2. Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.
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 Analysis Services Database … from the cascading menu, as depicted in Illustration 1.
Illustration 1: Opening the Analysis Services Database …
The Connect to Database dialog appears.
6. Ensuring that the Connect to existing database radio button is selected, type the Analysis Server name into the Server input box atop the dialog.
7. Using the selector just beneath, labeled Database, select ANSYS065_Basic AS DB, as shown in Illustration 2.
Illustration 2: Selecting the New Basic Analysis Services Database …
8. Leaving other settings on the dialog at default, click OK.
SQL Server Business Intelligence Development Studio briefly reads the database from the Analysis Server, and then we see the Solution Explorer populated with the database objects. We will review the dimension types, and then focus upon Cube dimensions using the Cube Designer from within our sample UDM.
