Populating the Cube with Factdata

Important


Now that we have created an Olation model, a PowerExcel database to be exact, the next thing to do is to populate the Cube with factdata.

One of the ways to do this is to create a PowerExcel Slice out of the appropriate Cube and enter the factdata values from there.

For this exercise, we will show how we can populate the Sales cube with factdata and how the dimension and cube attributes we have configured, handles/displays the factdata that we will be inputting. We will be using the PowerExcel Addin to Excel feature to allow us to enter factdata from the Excel environment and push those data back to the source Olation database (the source Olation database being the PowerExcel Olation.PowerXL).

To populate the Cube with Factdata:

1.   Begin by creating a PowerExcel Connection in Excel.
To do this:

• Launch the Excel application and go to the PowerExcel Tab along the Excel ribbon.

• In the PowerExcel Connections control group, click the Connections icon.

• In the Connections dialog that appears, click New.
• In the Name field, enter the <name of the PowerExcel connection>. For this exercise, name the connection as PowerExcel Olation.
  Note: You can provide a different name for the connection but just for easy identification, we decide to name the PowerExcel connection same thing as the source Olation database.
• In the URL field, enter the correct URL. This URL will be the URL of the Olation Server where the source Olation database (PowerExcel Olation) is currently running/opened.
• Click on the Database drop-down and select the correct source Olation database. Following this exercise, select PowerExcel Olation as the source Olation database.

Important


If the source Olation database is opened in the specified Olation Server (URL) then you should see that database displayed when you click the Database drop-down button.

It is important that the source Olation database be opened in the Olation Server for it to be accessible via the PowerExcel Addin to Excel feature.

• Click OK. The PowerExcel connection is now successfully created.

2.   Next, create a PowerExcel Slice.
To do this:

• Still in the PowerExcel Tab of the Excel ribbon, go to the PowerExcel Slice control group and click the New or New PowerExcel Slice icon. The PowerExcel sidebar will appear on the right-hand area of Excel.

• Go to the PowerExcel sidebar, click on the Database drop-down and select the appropriate PowerExcel connection, i.e., PowerExcel Olation.
• Still in the PowerExcel sidebar, click on the Cube drop-down and select the appropriate Cube, i.e., Sales.
  The PowerExcel sidebar will appear as follows:

• You may re-arrange the Dimensions by dragging and dropping them along the Filters, Columns and Rows sections. For this exercise though, we have previously configured during Cube creation how we want the Slice to be arranged when we bring them down to the PowerExcel Slice so we can leave it at the default order.
• Go to the Filters section of the Olation sidebar and change the Filter member for Version to show Actual.
• Go to the Filters section of the Olation sidebar and change the Filter member for Region to show United States.
• Choose PivotTable as the PowerExcel Slice type.
• Choose to insert the PowerExcel Slice into the current workbook starting at $A$1 cell.
• Click the Insert button. The PowerExcel Slice will look as follows:

3.   Now start entering some Sales and Cost of Sales figures for the individual months January to December.

Notes


DO NOT enter factdata on the cells corresponding to quarters and Total Month (Month dimension) and; Gross Profit and Net Income (Account dimension).

4.   Next, press F9 to refresh the PowerExcel Slice. Notice how the columns and rows for aggregates all get populated with data.
These columns and rows are populated with factdata as a result of the hierarchies we have built for the Dimension components.

                                                                                                                                                                 Aggregate Columns

                                                                                                                                                                 Aggregate Rows

5.   Also, observe the Columns for Gross Profit and Net Income. Notice that the Gross Profit and Net Income computation now applies the correct formula logic where Gross Profit should be the difference of ‘Sales less Cost of Sales’; whereas Net Income should be the difference of ‘Gross Profit less Operating Expenses’. This is the result of the Aggregate or Member Weight we have applied to their component children where we applied a ‘-1’ weight to Cost of Sales (child of Gross Profit) and Operating Expenses (child of Net Income).

6.   At this point the Operating Expenses columns are still empty, so to further see how the Aggregate or Member Weight affects the hierarchies, enter some factdata values on the Operating Expenses column.

7.   Once you have all the factdata values entered for the Operating Expenses column (again only enter factdata values for the detail members or leaf-level members which in this case would be OPEX for individual months January to December), press F9. Notice how the Net Income now show the new results.

Tips


You can temporarily create Excel formulas outside the area of the PowerExcel Slice references and PivotTable just to check or test that computations are correct. Then you can just easily delete all those Excel formulas later on and it won’t affect the PowerExcel Slice, or you may keep them if you wish.

As shown in the screenshot below, the Net Income computation indeed shows the correct formula logic where Net Income should be the difference of ‘Gross Profit less Operating Expenses’.

8.   For this exercise, if you have created Excel formulas to check the accuracy of results, delete them. The factdata entered should now be saved back to the Olation database (i.e., PowerExcel Olation).

9.   To check that the data has been indeed saved, close out the current Excel workbook and reopen Excel. Note: You can also just open a new Excel workbook.

10. Next, create a new PowerExcel Slice for the Sales cube (just follow the same procedure for creating a PowerExcel Slice as detailed in step#2). You should now see that all new PowerExcel Slices created from the Sales cube of the PowerExcel Olation database are now populated with factdata.

This concludes our QuickStart to Creating Olation models via the Multidimensional Approach.