Review
the Generated Schema, and the Table Supporting the Date Dimension, within MSSQL
Server
One of
the objectives of our practice session was to demonstrate the ease with which
we can generate a schema (and, within our focus upon the Time dimension,
a populated Date table for its support) that we design “from the top down”
within Analysis Services. As a part of preparation, we created a target
database within which to generate the schema that we designed. At this point,
we will return to SQL Server Management Studio, to verify the creation
of the tables involved, including the populated Date table, by taking
the following steps:
1.
Click the Start
button on the PC.
2.
Select Microsoft
SQL Server 2005 within the Program group of the menu.
3.
Click SQL
Server Management Studio, as we did in our preparatory steps earlier.
The Connect to
Server dialog appears, after the brief Management Studio splash
screen.
4.
Select Database
Engine in the Server type selector, as we did before.
5.
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector, once again.
6.
Supply the
required authentication information.
7.
Click the Connect
button to connect with the specified SQL Server Database Engine
server, as we did in accessing the Management Studio earlier.
The SQL Server
Management Studio opens.
8.
In the Object
Explorer pane, expand the Databases folder appearing underneath the Database
server within which we are working, once again.
The Databases
folder opens, exposing the detected databases. We see the DBJ_Schema_Target
database appear among those shown, as shown in Illustration 44.
Illustration 44: The DBJ_Schema_Target
Database, among Other Databases within the Object Explorer
9.
Using the “+”
sign to its immediate left, expand the DBJ_Schema_Target database.
10.
In like
manner, expand the Tables folder that appears within the expanded
DBJ_Schema_Target database, as necessary.
The
tables within the folder appear, as depicted in Illustration 45.
Illustration 45: The
Tables of the DBJ_Schema_Target Database Appear ...
We see that the target
database contains each of the tables that we defined within the BI
Development Studio. Let's take a look at the Date table, both from
a structural viewpoint (as an example of the schema generated), and also
because we expect it to be completely populated, based upon our settings within
the Schema Generation Wizard in the procedures above.
11.
Right-click
the table DBJ_Adventure Works DW.Date_1 within the Tables folder
of the expanded DBJ_Schema_Target database.
12.
Select Modify
from the context menu that appears, as shown in Illustration 46.
Illustration
46: Select Modify to Examine Table Structure ...
A tab named Table -
DBJ_Adventure Works DW.Date_1 opens, displaying Column Names, Column
Properties, and other information about the table. A portion of the tab
appears as depicted in Illustration 47.
Illustration 47: Details
for the New Date Table (Partial View)
Here, we might make
modifications to this mechanically generated table, in scenarios where the
local environment requires different settings, data types, and the like. We
still would have enjoyed the benefit of rapid construction of the table, even
if we faced a need to make modifications to some of the columns, etc.
13.
Right-click
the table DBJ_Adventure Works DW.Date_1 within the Tables folder
of the expanded DBJ_Schema_Target database, once again.
14.
Select Open
Table from the context menu that appears, as shown in Illustration 48.
Illustration
48: Select Open Table to View the Populated Table ...
Another tab named Table
- DBJ_Adventure Works DW.Date_1 opens, this time to the left of the tab
that appeared previously, and displaying the table itself, together with
the data that it contains. The new tab appears as partially depicted in Illustration 49.
Illustration
49: Data View of the New Date Table (Partial View)
The data view confirms
that the table has, indeed, been populated. We can easily scroll down the tab
to ascertain that the date ranges we have requested have been generated. We
can modify the table as needed to “tune” it further to meet our immediate needs
- or perhaps even use it as a basis for a Time dimension within a
totally unrelated data warehouse or mart - having enjoyed the benefits, in the
meantime, of full design and generation from a graphical user interface. These
options alone present a compelling motivator for use of the Schema
Generation Wizard in Analysis Services 2005.
15.
Further
inspect the properties and data of the new Date dimension table, as
desired.
16.
Select File
-> Exit, when ready, to leave the SQL
Server Management Studio as shown in Illustration 50.
Illustration 50: Exiting the SQL
Server Management Studio
17.
Return to the BI
Development Studio.
18.
Further
inspect newly created Date (and other) dimensions, as well as the
related Data Source View, together with their properties and other
settings. Perform additional browses, as desired.
19.
Select File
-> Save All from the main menu, to save our
work through this point, as shown in Illustration 51.
Illustration 51: Saving All Work
from Our Session
20.
Select File
-> Exit, when ready, to leave the Business
Intelligence Development Studio.
Conclusion
In
this article, we explored various unique
characteristics that distinguish a Time dimension from other dimensions
within our cube models, as well as the special attention that has been given to
the Time dimension, in general, within the design of enterprise business
intelligence applications. We then narrowed our focus to the topic of this and
the subsequent article: how this support has been enhanced in Analysis
Services 2005;
We next performed
preparation steps for the hands-on practice session that was to follow,
creating an Analysis Services Project within which to perform the anticipated
steps, and creating a target database within SQL Server Management Studio
for the schema generation procedure we would also perform. We then
concluded preparation by ascertaining connectivity of the relational data
source, along with other preparatory procedures, within the new Analysis
Services 2005 Project.
We
next began the creation of a rudimentary cube via the “top down” approach (with
no underlying data source in place), containing a Date dimension,
upon which to base our general examination of a Time dimension. Once
this was completed, we examined the structure
of the new Time dimension within the design environment, discussing its
various characteristics and settings. We then generated the underlying
relational schema for the new cube model,
including the generation of a Time table design, as well
as its subsequent population, from within the cube model that it was designed
to support. Finally,
we reviewed the new Date dimension within the Dimension Designer,
and then examined the generated schema, focusing upon the populated table supporting
the Date dimension, within SQL Server Management Studio.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
» 
Comment and Contribute
