Dimensional Model Components: Dimensions Part I
January 24, 2008
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.
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 component structures, we will gain an introduction, with hands-on exposure, to dimensions within a sample cube. Our examination will include:
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 of this subseries and elsewhere within my Introduction to MSSQL Server Analysis Services, and other Database Journal, series.)
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 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 in the 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, located in the References section of my articles index:
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 MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:
Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for those appropriate to your own environment, when following the procedure):
Ascertain that the New Practice Database is in Place and Rename It
We are now ready to ascertain that the new Analysis Services database is in place, as well as to rename it to keep it safe from writeover in future exercises within the series. (You can, of course, always delete the practice database if you do not choose to keep it for future reference, etc.) To do this, we will work from within the Microsoft SQL Server Management Studio.
1. Start Microsoft SQL Server Management Studio.
2. Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.
3. Type / select the server name (server name / instance, if appropriate) in the Server name selector.
4. Supply authentication information, as required in your own environment.
The Connect to Server dialog appears similar to that depicted in Illustration 1.
5. Click the Connect button to connect with the specified Analysis Server.
The Microsoft SQL Server Management Studio opens.
6. In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the + sign to its immediate left), appearing underneath the Analysis Server within which we are working.
The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.
7. Right-click the DBJ_Basic AS DB database, and select Rename from the context menu that appears next, as shown in Illustration 2.
8. Rename the Analysis Services database as follows:
ANSYS065_Basic AS DB
9. Exit SQL Server Management Studio when ready.
Having ensured that the new database is in place, and having renamed it, we are ready to begin the procedural part of our practice session.