Share via

Step 2: Updating the OLAP Cube

  • Article

After you've extended the staging tables and created the database connection, the second step is adding the pay period dimension to your Portfolio Analyzer OLAP cube. This topic shows you how to update the OLAP cube manually; once you are familiar with the steps involved, you can use Decision Support Objects (DSO) to update and regenerate the virtual cube programmatically.

If you are creating a new dimension, it will not be overwritten when the OLAP cube is rebuilt using this procedure. However, if you are modifying an existing dimension, you would have to do this programmatically with the UserOLAPUpdate function in the download. Otherwise, the dimension will be overwritten each time the cube is rebuilt.

Note  UserOLAPUpdate in the sample provided in the download subdirectory PayPeriodDimension is a template only and does not include functions that actually update the cube. The samples in the subdirectories TaskEarnedValue and TaskIssuesRisks provide working examples of UserOLAPUpdate for the other OLAP extensions in the solution starter.

The following steps describe the update process using the SQL Server Analysis Manager. To open SQL Server Analysis Manager, click Start, point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Analysis Manager.

To add the new tables to the virtual cube:

  1. In SQL Server Analysis Manager, open your Analysis Server, which is listed under the Analysis Servers tree.

    Click your server name, click the name of your OLAP cube, and then click Cubes to expand the tree that contains the two Fact cubes (MSP_ASSN_FACT and MSP_RES_AVAIL_FACT) and the virtual cube (MSP_PORTFOLIO_ANALYZER).

  2. Right-click the MSP_ASSN_FACT cube, and then click Edit.

    Note  If you get an error message such as Unable to connect to the 'OLAP DS' data source, you need to set the correct permission. Expand the Data Sources folder, right-click on the OLAP DS data source, and then click Edit. Enter the correct logon information in the item Enter information to log on to the server. Click Test Connection; if the connection succeeds, click OK and try to edit the MSP_ASSN_FACT cube again.

  3. Right-click Dimensions, and then click New Dimension to start the Dimension Wizard.

  4. In the Dimension Wizard, click Next, select Snowflake Schema: Multiple, related dimension tables, and then click Next.

  5. In the Available tables pane, scroll down and double-click both the PayPeriodLink table and the PayPeriodName table to add them to the Selected tables pane, and then click Next.

    Note  The PayPeriodLink and PayPeriodName tables are at the end of the list of tables but above the list of views in the Available tables pane in the Dimension Wizard dialog box.

  6. Verify that the PP_ID fields in both tables are joined, and then click Next.

  7. Double-click PP_NAME to move the PP_NAME column from the Available columns pane to the Dimension levels pane, and then click Next.

  8. Click Next on the Specify the member key columns page, click Next again without selecting any advanced options, and then type a Dimension name (for example, type Pay Period) to allow the wizard to finish.

    Leave the Share this dimension with other cubes check box selected, and then click Finish.

Now that you've created the new Pay Period dimension, you'll need to link it to the fact table. You can recognize the MSP_CUBE_ASSN_FACT table by its yellow color in the Cube Editor in the Analysis Manager; the dimension tables dbo.PayPeriodLink and dbo.PayPeriodName are blue. If you do not see these three tables when you click Dimensions, scroll down and drag the tables to the top of the right pane.

To link the Pay Period dimension to the MSP_CUBE_ASSN_FACT table and create the cube:

  1. Click Dimensions under the MSP_ASSN_FACT table in the left pane to display the fact tables and dimension tables in the right pane.

  2. Click the TIME_ID column in the MSP_CUBE_ASSN_FACT table and drag it to the TIME_ID column in the dbo.PayPeriodLink table to create the link.

  3. Click Save.

  4. Right-click MSP_ASSN_FACT, and click Process Cube.

  5. In the message box, you will see the message, The cube does not have any aggregations designed. Do you want to design the aggregations before you process the cube?. Click Yes.

    The Storage Design Wizard starts automatically.

    If the Storage Design Wizard does not start, on the Tools menu, click Design Storage.

  6. Click Next to begin; if you have previously created aggregations, click Next to replace the existing aggregations. Click Next to select the multidimensional OLAP (MOLAP) type of data storage, and then click Start.

  7. After all the aggregations have been designed, click Next.

  8. Click Finish to process and create the cube. Processing can take several minutes or longer, depending on the amount of data and number of aggregations.

  9. When you see the message Processing completed successfully in the Process dialog box, click Close. Close the Cube Editor and go back to the Analysis Manager main window.

You are now finished with Step 2 in adding a dimension to the cube. You have added the new staging tables to the virtual cube, linked the Pay Period dimension to the MSP_CUBE_ASSN_FACT table, and created the cube.

The next step is Step 3: Adding the New Dimension to the virtual cube.