Mastering Enterprise BI: Time Intelligence Pt. I - Page 3
February 12, 2007
Ascertain Connectivity of the Relational Data Source
Lets ensure that the data source within our project is in working order. (We will be creating a data connection to the new database, as well, in a step within our practice session.) Many of us will be running side-by-side installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone (the default for the Adventure Works DW project samples connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default).
If you do not know how to ascertain connectivity of the relational data source, please perform the steps of the following procedure in the References section of my articles index:
Create a Cube with No Underlying Data Source
To save preparation time, we will be working within our copy of the sample Analysis Services Project. We will be creating a new cube, however, within which we will get some hands-on exposure to working with a Time dimension. Having established the presence of a working Data Source within our Analysis Services Project, our next steps surround the creation of very basic cube, within which we will perform our practice session.
Although our intent is to continue our development efforts with the metadata without an open connection, most of us probably find it more common to build our cubes and other objects within the Unified Dimension Model (UDM) based upon an actual underlying database. We have ensured the presence of working Data Source for steps that we will perform subsequent to the design of our Time dimension.
Create a Basic Cube Up to Insertion of a Time Dimension
We will create a basic cube, to house a representative Measure and a few dimensions. We will focus upon other areas within the overall design and creation process in other articles of the series our primary objective here is to gain some familiarity with a Time dimension not to concern ourselves with the myriad other options and topics contained within general cube design.
1. Right-click the Cubes folder within the Solution Explorer.
2. Select New Cube ... from the context menu that appears, as shown in Illustration 7.
The Welcome to the Cube Wizard dialog appears, as depicted in Illustration 8.
3. Click Next.
4. Click the Build the cube without using a data source radio button on the Select Build Method page that appears next.
5. Click the checkbox just underneath the radio button we just checked, labeled Use a cube template.
6. Ensure that Adventure Works Standard Edition is selected in the template selector list.
The Select Build Method page appears as shown in Illustration 9.
These settings tell the Wizard that we have chosen to create our cube using a template, but without basing it upon an actual database. Using a template allows us to design a cube based upon the structure, including dimensions and measures, of an already existing cube, saving significant time and effort.
7. Click Next.
8. In the next page, Define New Measures, click the left corner box in the header row of the section labeled Select measures from template (in the upper half of the page), as depicted in Illustration 10.
This clears selection of the measures from the cube we have selected as a template. By default, all measures are selected.
9. Select Internet Sales Amount (the top measure in the section labeled Select measures from template) by clicking the checkbox to its left.
10. Click Next.
The Review Shared Dimensions page appears next. Here we see available shared dimensions that exist within the UDM, but which have yet to be selected as cube dimensions. We will not include these shared dimensions in our cube.
The Review Shared Dimensions page appears as shown in Illustration 11.
11. Click Next.
12. On the next page, Define New Dimensions, click the left corner box in the header row of the section labeled Select dimensions from template (in the upper half of the page), as depicted in Illustration 12.
This clears selection of the dimensions (all of which were initially selected by default) from the template cube we have selected as a template.
13. Select Customers (the second dimension from the top in the section labeled Select dimension from template) by clicking the checkbox to its left.
14. In like manner, select Products, two rows below (the fourth row from the top).
15. Similarly, select Time (the tenth row from the top).
While the measure and two of the dimensions we have selected are merely to give our cube some substance, the Time dimension we have selected above is the key focus of this session. (We will assume, for our immediate purposes, that the default attributes and settings for each dimension will be acceptable.)
The Define New Dimensions page appears, with our selections, as shown in Illustration 13.
Our next steps will take us into the focus portion of our practice exercise: working with a Time dimension in Analysis Services 2005.