Procedure: Examine Dimension Properties in Analysis Services 2005
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, ANSYS065_Basic
AS DB.
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
3.
Illustration
3: 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
4.
Illustration
4: 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 contrast the dimension types,
and then focus upon Database dimensions using the Cube Designer
from within our new sample UDM.
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.
(We will overview properties for Cube dimensions in Part II of
this article.)
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 depicted in Illustration 5.
Illustration 5: The
Database Dimensions of Our Sample UDM
We notice
that eight 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 shown in Illustration 6.
Illustration
6: 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 depicted in Illustration 7.
Illustration
7: The Cube Dimensions, Basic Cube
We notice
that ten Cube dimensions appear within the Dimensions pane
of the Cube Structure tab. The difference in number between the Cube
dimensions and the Database dimensions we saw earlier
reflects a powerful capability that accrues to dimensional model
designers that use Analysis Services 2005 and beyond: we can reuse
single Database dimensions for multiple Cube dimensions. 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.
The Basic
cube contains more dimensions than the Analysis Services database
because three separate Cube dimensions that relate to time / date (and
which are based on
different time-related facts in the fact table) share the Time Database dimension as their
bases. These three Cube dimensions represent Role-playing dimensions
within our cube, which allow information consumers to dimension the cube from
three separate sales-related date perspectives: the date a given product was
ordered, the due date that was applicable for fulfillment of the order,
and the date that the order was actually shipped.
NOTE: We explore Role-playing dimensions in other
articles of my Introduction to MSSQL Server Analysis Services series at Database Journal.
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.
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.
» 
Comment and Contribute
