Create a New Linked Cube

To create a New Linked Cube in PowerOLAP^® requires a few quick steps, the first of which is to configure a connection from PowerOLAP^® to the Microsoft Analysis Services environment, as shown following:

1.   The first step is to create a new database, or open an existing PowerOLAP database to incorporate the Analysis Services Cube(s).

For this sample exercise let us simply create a new database. The figure below shows the New Database dialog box with a database created for the dynamically linked Cubes.

After creating a new database, you can continue to the next steps.

2.   Inside PowerOLAP main application window, go to the Tools tab of the PowerOLAP ribbon, then in the PowerOLAP Services control group select New Linked Cube command.

You will next be prompted for the Cube Provider:

3.   Select a provider (e.g. MSOLAP) from the drop-down menu. Click Next.

The next dialog box concerns the Server name:

4.   Copy/paste or type in the <Server name>, enter the <username> and <password> if necessary and click Next.

PowerOLAP finds and connects to the Server; available databases on the Server are shown in the ensuing dialog box.

5.   Select a Database (as above, FoodMart 2000), and type in <username> and <password> if necessary, then click Next.

The Cubes available on the database are shown in the ensuing Cube dialog box (drop-down shown below). You can access one Cube at a time as a PowerOLAP Linked Cube.

6.   Select a Cube (e.g. Budget cube) then click Next.
The Select Dimensions and Select Leaf Levels dialog boxes appear next.

The Select Dimensions dialog box lets you select specific Dimensions that you want to bring into your PowerOLAP cube. This is done by enabling the checkboxes of the Dimensions that are to be included. By default, all the Dimensions are enabled. Simply uncheck all those Dimensions that you do not want to be returned in your PowerOLAP cube.

Notes


The Measures dimension is automatically included in the Dimensions that will be sent to the PowerOLAP cube. In other words, the Measures dimension will always be included in the Dimensions that will be returned in the PowerOLAP cube.

The Select Leaf Levels dialog box, on the other hand, lets you pick further specific Leaf level Members, associated with the Dimensions that you previously selected, to include in the PowerOLAP cube. By default, the checkboxes are disabled. Enable only those Detail members that you want to be incorporated into your PowerOLAP cube.

7.   After selecting the Dimensions and Leaf level members to be included, click Next.
The PowerOLAP Services Options dialog box appears.

Enumerated below are the options available under the Configuration screen above:

• Create fully qualified names for all members
  - There are instances when Dimension members appear several times on different levels of the hierarchical structure of a certain Dimension. One way to avoid duplication of Member names in a specific Dimension when requesting data from an MS Analysis Services or OLE DB for OLAP compliant source cubes is to use fully qualified member names.
  When this option is selected, all the Dimensions and Dimension members will be brought into PowerOLAP cube as fully qualified members.

• Create fully qualified names for date members
  - When this option is selected, only those Members of a Date dimension type will be brought in as fully qualified members into PowerOLAP. This option is enabled by default.

• Access Data via PowerOLAP Formulas
  - When this option is enabled, the OLEDBRead and OLEDBReadWrite radio buttons will be available for selection. This option is likewise enabled by default.

• OLEDBRead
  - When this option is enabled, it will read data from the data source, but the data is not editable—thus it is ”Read-Only”.

• OLEDBReadWrite
  - This option also brings in data by reading the data source, but it also makes the data editable, so it is possible to write back to the source if the cell is a Detail, i.e., leaf level cell.

You can now proceed to select the options.

8.   Thus, set up the Dimension Options.

9.   If you enable the Access Data via PowerOLAP Formulas option, select Read or ReadWrite.

10. Next, click Finish to complete the Linked Cube creation.
(For this example ReadWrite option is enabled)

The progress bar will appear onscreen until the process is complete. When finished, the following message box appears.

11. Click OK.

You are returned to the PowerOLAP main application window.

Seeing the results in PowerOLAP^®

To see the result of the Linked cube we have just performed, do the following:

1.   To check what Dimensions have been created, in the PowerOLAP ribbon select Model tab and Dimension command.

2.   To see the Linked Cube itself (which in this case is Budget cube), go to the PowerOLAP ribbon and select Model tab and Cube command.

3.   To determine what a Slice looks like, from the PowerOLAP ribbon select Slice tab and New Slice command then pick Budget as the cube.

The image below shows an example of a slice that resulted from a PowerOLAP Services Linked Cube.

All the Fact Data in an MS Analysis Services Cube has also been delivered to the PowerOLAP Cube, and appears in the Slice.

To update the Fact Data in Cube, all you need to do is press F9 (whether you are in PowerOLAP or an Excel worksheet created from a Slice).

Concerning ”Write-back”: provided the Cube was created using the ReadWrite capability and the cell into which you type data is a Detail (i.e., leaf) member cell and the Cube is enabled for ”write-back” (and you have the requisite rights to do so), you can press F9 to send that data point to the MS Analysis Services Cube.