Image1.JPG (46707 bytes)
Microsoft SQL Server OLAP Services 7.0


OLAP Manager Tutorial

This tutorial will guide you in using a multidimensional data model to analyze your business data. As you go through the tutorial, you'll perform all the operations necessary for designing and processing a cube with the OLAP Manager. For information about fully exploiting the power of OLAP Services, see the online documentation.

With the sample cube you will build, you'll set up the connections you need to gain access to data, define your cube, and position yourself to analyze your data in the most efficient way.


Basic Tutorial

Advanced Tutorial


Top

OLAP MANAGER BASIC TUTORIAL




Setting Up Your System Data Source Connection

Before you begin working with the OLAP Manager, you must first set connections to the source of your data in the ODBC Data Source Administrator.

v    To set up your system Data Source Name (DSN)

  1. From the Windows Start menu, point to Settings, choose Control Panel, and then double-click ODBC Data Sources.
  2. On the System DSN tab, click Add.
  3. Choose Microsoft Access Driver (*.mdb), and then click Finish.
  4. Enter "Tutorial" in the Data Source Name box, and then click Select under Database.

  5. ODBC Setup dialog


  6. In the Select Database dialog box, browse to the C:\Program Files\OLAP Services\Samples folder and select FoodMart.mdb. Click OK.
  7. Click OK in the ODBC Microsoft Access Setup dialog box.
  8. Click OK in the ODBC Data Source Administrator dialog box.

 

Top

Starting the OLAP Manager

The OLAP Manager is a snap-in program that runs on Microsoft Management Console (MMC).

v    To start the OLAP Manager

Top

Setting up Your Database and Data Source

Now you are ready to start working within the OLAP Manager. Before you design your cube, you must set up a database structure and connect to the data source you set up earlier in the ODBC Data Source Administrator.

v     To set up your database structure

  1. In the OLAP Manager tree view, expand OLAP servers.
  2. Click your server's name.  A connection with the OLAP Server will be established.
  3. Now right-click your server's name and select New Database from the shortcut menu.
  4. In the Database dialog box, type the name "Tutorial" in the Database name box, and then click OK.
  5. In the OLAP Manager tree view, expand the server, and then expand the Tutorial database you just created.

 

Your new Tutorial database contains three folders. As you work through this exercise, you will populate these three folders:


02.gif (15881 bytes)

Next, you will set up a connection to the sample data in the Tutorial data source. You’ll use this sample for all of the exercises in this tutorial.

Setting up a data source within the OLAP Manager connects your database to the system DSN you set up in the ODBC Data Source Administrator. All your data will be coming from this source as you build your cube.

 

v     To set up your data source

  1. In the OLAP Manager tree view, expand the Library folder, right-click the Data Sources folder, and then select New Data Source from the shortcut menu.
  2. In the Data Link Properties dialog box, Provider tab, select Microsoft OLE DB Provider for ODBC Drivers from the list.

  3. Data Link Properties dialog box

     

     

  4. Click the Connection tab, then from the Use data source name drop-down list, select Tutorial.
  5. Click Test Connection to be sure everything works. A message should appear in the Microsoft Data Link dialog box, stating that your connection was successful. Click OK.

  6. 04.gif (7017 bytes)

  7. Click OK to close the Data Link Properties dialog box.
Top

Building Your Cube

Scenario:    You are a database administrator working for the Food Mart corporation. Food Mart is a large grocery chain with sales in all 50 of the United States. The marketing department wants to analyze all of the sales promotions made during the 1997 calendar year. Using data that is stored in the company's data warehouse, you will build a multidimensional data structure (a cube) to enable fast response times when the marketing analysts query the database.


A cube consists of measures (or quantitative data such as sales or costs) and dimensions (or descriptive business data such as geographical regions, time, or customer demographics). In this step, you will use the Cube wizard to build the cube for the marketing department to use for its analysis of sales promotions.

v     To open the Cube wizard

 

v     To add measures to the cube

  1. In the Welcome screen of the Cube wizard, click Next.
  2. In the Select a fact table for your cube step, expand the Tutorial data source, click sales_fact_1997.  You can view the data in the sales_fact_1997 table by clicking Browse data.
  3. Click Next.
  4. To define the measures for your cube, under Fact table numeric columns, double-click store_sales. Repeat this procedure for the store_cost and unit_sales columns, and then click Next.

 

v    To build your Time dimension

  1. In the Select the dimensions for your cube step of the wizard, click New Dimension.

    05.gif (11918 bytes)


  2. In the Dimension wizard, select A single dimension table (flat or star schema), and then click Next.
  3. In the Select the dimension table step, expand Tutorial, click time_by_day. You can view the data in the time_by_day table by clicking Browse data.
  4. Click Next.
  5. In the Select the dimension type step, choose Time dimension, and then click Next.

  6. 06.gif (27766 bytes)

  7. To define the levels for your dimension, click the Select time levels drop-down list, choose Year, Quarter, Month, and then click Next.
  8. In the last step of the wizard, type "Time" for the name of your new dimension, and then click Finish to return to the Cube wizard.

NOTE: You can designate whether you would like this dimension to be shared or private using the Share this dimension with other cubes check box located on the lower left corner of the screen. Leave the box checked.

  1. You should see the Time dimension in the Cube dimensions list.
  2.  

v    To build your Product dimension

  1. Click New Dimension again.
  2. In the Dimension wizard, click Multiple dimension tables (snowflake schema) and then click Next.
  3. In the Select the dimension tables step, expand Tutorial and then double-click product and product_class to add them to Selected tables. Click Next.
  4. The two tables you selected in the previous step and the existing join between them are displayed in the Create and edit joins step of the Dimension wizard. Click Next.

  5. 07.gif (11342 bytes)

  6. To define the levels for your dimension, under Available columns, double-click the product_category, product_subcategory, and brand_name columns, in that order. After you double-click each column, its name will appear under Dimension levels. Click Next when you have selected all three columns.
  7. In the last step of the wizard, type "Product" in the Dimension name box, and leave the Share this dimension with other cubes box checked. Click Finish.
  8. You should see the Product dimension in the Cube dimensions list.

 

v    To build your Store dimension

  1. Click New Dimension.
  2. In the Dimension wizard, select A single dimension table (flat or star schema), and then click Next.
  3. In the Select the dimension table step, expand Tutorial, click store, and then click Next.
  4. In the Select the dimension type step, click Next.
  5. To define the levels for your dimension, under Available columns, double-click the store_country, store_state, store_city, and store_name columns, in that order. After you double-click each column, its name will appear under Dimension levels. Click Next when you have selected all four columns.
  6. In the last step of the wizard, type "Store" in the Dimension name box, and leave the Share this dimension with other cubes box checked. Click Finish.
  7. You should see the Store dimension in the Cube dimensions list.

 

v    To build your Promotion dimension

  1. Click New Dimension.
  2. In the Dimension wizard, select A single dimension table (flat or star schema), and then click Next.
  3. In the Select the dimension table step, expand Tutorial, click promotion, and then click Next.
  4. In the Select the dimension type step, click Next.
  5. To define the levels for your dimension, under Available columns, double-click the media_type and promotion_name columns in that order, and then click Next.
  6. In the last step of the wizard, type "Promotion" in the Dimension name box, and leave the Share this dimension with other cubes box checked. Click Finish.
  7. You should see the Promotion dimension in the Cube dimensions list.

 

v    To finish building your cube

  1. Click Next.
  2. Name your cube "Sales" and then click Finish.

 

v     To edit your cube in the Cube editor

In the Schema pane of the Cube editor, you can see the fact table (with yellow title bar) and the joined dimension tables (blue title bars). In the Cube editor tree view, you can preview the structure of your cube in a hierarchical tree. You can edit the cube's properties by clicking the Properties button.

08.gif (19783 bytes)


At this time, you decide you need another dimension that gives you data on customer demographics. You can easily build this dimension right here in the Cube editor.

NOTE: Dimensions built in the Cube editor are private dimensions; that is, they can be used only with the cube you are working on and cannot be shared with other cubes.

  1. In the Cube editor, from the Insert menu, choose Tables.
  2. In the Select Table dialog box, expand the Tutorial data source, double-click the customer table, and then click Close.
  3. To define the new dimension, double-click the state_province column in the customer table.
  4. In the Map the Column dialog box, select Dimension, and then click OK.

  5. 09.gif (2944 bytes)

  6. Select the State Province dimension in the tree view.
  7. From the Edit menu, choose Rename.
  8. Type "Customer", and then press ENTER.
  9. Drag the city column from the customer table in the Schema pane to the newly renamed Customer dimension in the Cube editor tree view.
  10. Expand Customer to see the two levels you have created for the dimension.
Top

Adding Roles to Your Cube

Cube roles define which users or user groups can access and query the data in a cube. Now that your cube is fully designed, you will add a role to your cube. In this example you will create a role called "Marketing."

NOTE: The database administrator does not need access permission to query the data using the OLAP Manager browser tools. Roles must be set up for cube access using client-side query tools.

 

v     To open the Cube Roles dialog box

 

v     To create the Sales role

  1. In the Cube Roles dialog box, click New Role.
  2. In the Create a Database Role dialog box, type "Marketing" in the Role name box.
  3. In the Groups and users box, type your user name, and then click OK.

  4. 10.gif (4786 bytes)

  5. In the Cube Roles dialog box, the "Marketing" role should appear in the Cube access list. Click OK.
  6. In the Cube editor, choose Save from the File menu.
Top

Designing Storage and Processing Your Cube

OLAP Services allows you to setup aggregations, which greatly improve the efficiency and response time of queries. Use the Storage Design wizard to specify the way that OLAP Services stores these aggregations to optimize query processing performance for your cube.

v     To start the Storage Design wizard

  1. In the Cube editor, from the Tools menu, choose Design Storage.
  2. In the Storage Design wizard Welcome step, click Next.
  3. Select MOLAP as your data storage type, and then click Next.
  4. Under Aggregation Options, select Performance gain reaches. In the text box, type "40."

    You are instructing OLAP Services to give a performance boost of up to 40 percent, regardless of how much disk space this requires. Administrators can use this tuning ability to balance the need for query performance against the disk space required to store aggregation data.

  5. Click Start.
  6. You can watch the Performance vs. Size graph in the lower right corner of your screen as OLAP Services designs the aggregations. Here you can see how increasing performance gain requires additional disk space utilization. When the process of designing aggregations is complete, click Next.

  7. 11.gif (8492 bytes)

  8. Under What do you want to do?, select Process now, and then click Finish.

  9. NOTE: Processing the aggregations may take some time.

  10. A window appears in which you can see your cube being processed. When processing is complete, a message appears, stating "Processing Completed Successfully." Click Close to return to the Cube editor.
  11. From the File menu, choose Exit to close the Cube editor and return to the OLAP Manager tree view.
Top

Viewing Metadata for Your Cube

OLAP Services allows you to view detailed information about a cube's configuration, or metadata. This information appears in the right pane of the OLAP Manager.

v     To view metadata for the Sales cube

  1. In the OLAP Manager tree view, expand the Cubes folder.
  2. Select the Sales cube.
  3. In the right pane of the OLAP Manager, click Metadata.

11a.gif (16723 bytes)

Top

Browsing Your Cube's Data

Now you're ready to browse the data in the Sales cube. Using the Cube browser, you can look at data in different ways, you can filter the amount of dimension data that is visible, and you can drill down to see greater detail (or drill up to see less).

v     To open the Cube browser

  1. In the OLAP Manager tree view, right-click the Sales cube, and then choose Browse Data from the shortcut menu.

The Cube browser appears, displaying a grid made up of one dimension and the measures of your cube. The additional four dimensions appear at the top of the browser.

12.gif (7173 bytes)


v     To replace a dimension in the grid

  1. To replace one dimension in the grid with another, drag the dimension from the top box and drop it directly on top of the column you wish to exchange it with. Make sure the pointer appears as a double-ended arrow during this process.
  2. Using this technique, select the Product dimension and drag it to the grid, dropping it directly on top of MeasuresLevel. The Product dimension and MeasuresLevel will switch positions in the cube browser.

  3. 13.gif (16141 bytes)

    NOTE: If you want to add a dimension to the grid, rather than replace one with another, drag and drop the dimension on the middle of the grid and not on top of another dimension.

     

v     To filter your data by time

  1. Click the down arrow for the Time dimension.
  2. Expand All Time, then 1997, and then choose Quarter 1. As you'll see, the data in the grid is filtered to reflect figures for only that one quarter.

14.gif (9952 bytes)


v     To drill down


Congratulations!

You have completed the OLAP Manager Basic Tutorial.

You have gone through all the necessary steps to build a basic OLAP cube and make it available to your Marketing department. You can continue with the Advanced Tutorial to learn more about the power and functionality of the OLAP Manager.

 

Top

OLAP MANAGER ADVANCED TUTORIAL


Creating a Partition for Your Cube

NOTE:    User-defined partitions are available with Microsoft SQL Server OLAP Services, Enterprise Edition. If you are using the Standard Edition, please continue to the next section.

Scenario:     Because sales have been doing so well over the last few years, Food Mart's management has decided to expand operations into Canada and Mexico during 1998. In order to closely track performance during the expansion, the part of the Sales cube that deals with 1998 will be optimized for very fast query performance. You do this by creating a partition. Partitions help you separate one logical cube into separate physical partitions so that you can enhance flexibility in data storage, location of data sources, or query performance.

v     To start the Partition wizard

  1. In the OLAP manager tree view, open the Cubes folder under the Tutorial database.
  2. Open the Sales cube.
  3. Right-click the Partitions folder, and then select New Partition from the shortcut menu.

 

v     To build the partition

  1. In the Welcome step of the Partition wizard, click Next.
  2. In the Specify the data source and the fact table step, click Change.
  3. In the Choose a fact table dialog, expand the Tutorial data source, choose sales_fact_1998, then click OK.
  4. Click Next.
  5. In the Select the data slice step, select the Time dimension in the Dimensions box.
  6. In the Members tree view, expand the All Time level, select 1998, then click Next.

  7. 16.gif (13861 bytes)


  8. In the Finish the Partition wizard step, in the Partition name box, type "Sales 98".
  9. Under What do you want to do?, select Copy the aggregation design from an existing partition.
  10. Select the Process the partition when finished check box.

  11. 17.gif (15244 bytes)

  12. Click Finish.

  13. NOTE: Processing the partition may take some time.

  14. A window appears in which you can see your partition being processed. When processing is complete, a message appears, stating "Processing completed successfully".
  15. Close this window to return to the OLAP Manager tree view.
Top

Creating Member Properties and Virtual Dimensions

Member properties allow you to define additional information about the levels in a dimension.  In this example you will create a member property that associates each store name with the manager of that store.  Using this member property, you will then create a virtual dimension.  The virtual dimension will display the store manager information when you view the cube's data.

 

v    To create a Member Property

  1. In the OLAP Manager tree view, expand the Library folder.
  2. Expand the Shared Dimensions folder.
  3. Right-click the Store dimension, and then select Edit from the shortcut menu.
  4. In the Dimension editor, expand the Store Name level. You will see the Member properties folder for the level.
  5. In the Schema pane, drag the store_manager column from the store table to the Member properties folder for Store Name.

17a.gif (13280 bytes)

  1. Select Save from the File menu.
  2. Select Exit from the File menu.
  3.  

v    To create a virtual dimension

  1. In the OLAP Manager tree view, right-click the Virtual Dimensions folder, and then select New Virtual Dimension from the shortcut menu.
  2. In the Welcome step of the wizard, click Next.
  3. In the Select the member property for your virtual dimension step, expand the Store dimension.
  4. Click the Store Name.Store Manager member property.

17b.gif (10106 bytes)

  1. Click Next.
  2. In the Finish the Virtual Dimension Wizard step, in the Virtual Dimension Name box, type "Store Manager".
  3. Click Finish.
  4.  

    NOTE:  Virtual dimensions can be added to a cube using either the Select dimensions step of the Cube wizard or the Dimension manager in the Cube editor.

Top

Creating and Processing a Virtual Cube

Scenario:     One of the key areas where costs for Food Mart can be streamlined is in its warehouse operations. In this exercise you will create a data structure that permits analysis of warehouse costs (from the Warehouse cube) against store sales (from the Store cube). To perform this operation, you will create a virtual cube.


A virtual cube is a superset of selected cubes in your database. It is similar to a view between relational tables. A virtual cube allows you to create a broader view of your multidimensional data without using additional physical storage space on your drive. A virtual cube allows users querying the database to see the data from different cubes in the same place, providing more powerful analysis capabilities.

v     To start the Virtual Cube wizard

NOTE: For this part of the tutorial you must use the Food Mart database.

  1. In the OLAP Manager tree view, expand the Food Mart database.
  2. Right-click the Virtual Cubes folder, and then select New Virtual Cube from the shortcut menu.

 

v     To build the virtual cube

  1. In the Welcome step of the Virtual Cube wizard, click Next.
  2. In the Select the cubes to include in the virtual cube step, double-click the Sales cube in the Available cubes box on the left. It will move to the Virtual cube includes box on the right.
  3. Repeat this procedure for the Warehouse cube, and then click Next.

  4. Select the cubes to include in the virtual cube step

  5. In the Select the measures for the virtual cube step, double-click the Store Sales measure in the Available measures box on the left. It will move to the Selected measures box on the right.
  6. Repeat this procedure for the Warehouse Cost measure, and then click Next.
  7. In the Select the dimensions for the virtual cube step, double-click the Time dimension in the Available dimensions box on the left. It will move to the Selected dimensions box on the right.
  8. Repeat this procedure for the Store and Warehouse dimensions, and then click Next.
  9. In the Finish the Virtual Cube wizard step, in the Virtual cube name box, type in "Cost versus Sales". Click Finish.
  10. Under When do you want to process the virtual cube?, choose Process now.
  11. A window appears in which you can see your virtual cube being processed. When processing is complete, a message appears, stating "Processing completed successfully."
  12. Close this window to return to the OLAP Manager tree view.

The virtual cube is now available to be queried by Food Mart analysts.

Top

Browsing the Virtual Cube's Data

Now you're ready to browse the data in the Cost versus Sales virtual cube.

v     To browse the data in your virtual cube

  1. In the OLAP Manager tree view, under the FoodMart database, expand the Virtual Cubes folder.
  2. Right-click the Cost versus Sales virtual cube, and then select Browse Data. The Cube browser appears, displaying a grid made up of one dimension and the measures of your cube. The additional dimensions appear in the top of the browser.
  3. Click Close to return to the OLAP Manager tree view.

  4. 19.gif (6658 bytes)

Top

Removing the Tutorial Database


Follow these instructions to remove the Tutorial database from your machine.

  1. Right-click the Tutorial database.
  2. Select Delete from the shortcut menu.
  3. Click Yes in the Delete dialog box.
Top

Learn More About the OLAP Manager

There are additional areas of the OLAP Manager that you can explore. Refer to the online Help for more information on the following topics:

 

Top


© 1998 Microsoft Corporation. All rights reserved. Legal Notices.