Introduction to OLAP Pivot to PO Feature
PowerOLAP’s OLAPPivot to PO feature (or OLAPPivot to PowerOLAP) is a light ETL (Extract, Transform, Load) tool which is a great tool for building PowerOLAP models and prototypes easily and quickly. Moreover, it has a potential to drive an ETL process for an application. This functionality leverages peoples’ skills and familiarity in an Excel environment as it allows users who are more knowledgeable in Excel to pull external data into Excel, and use Excel to “Stage Data” or as a workspace to design the desired output of the model through use of PivotTables and very easily and quickly generate the PowerOLAP cubes from those PivotTables.
How to use build PowerOLAP Model using OLAPPivot to PO
For this example, we will be extracting our data from an external source which is a SQL Server database, specifically from the UsingOLATION SQL database.
Notes
This manual uses an example SQL Server database called UsingOLATION.
To begin:
1. Launch Excel.
.png)
2. From the Excel ribbon, go to Data
tab, select Get Data command icon, From Database and select
From SQL Server Database option.
The following window that appears will require you to specify the name
of the SQL Server you are trying to access:
.png)
3. In the Server text box, enter the <name of the SQL Server instance> you want to connect to.
4. Click OK.
The first time you try to connect to the corresponding SQL Server instance,
you will be required to enter a valid user name and password. Once you
have successfully entered a valid authentication for this specific SQL
Server, the next time you perform a Get Data command, you will no longer
be required to re-enter the credentials. Instead, you will be automatically
directed to the Navigator window which displays the available databases
from the corresponding server.
Note:
• If this is the first time you are connecting
to the SQL Server using the Get Data command, go to step #5.
• If this is not the first time, go to step #9 .
5. If this is the first time you will perform a ‘Get Data’ command from the specified SQL Server, you will see this window after clicking the OK button. Leave default options in this Windows section.
.png)
6. Go to the Database section and enter a valid user and password to enable connection to the external source, which in this case is the SQL Server. In this example, a valid <SQL username> and <password> was entered .
.png)
7. Click Connect. The following prompt will appear .
.png)
8. Click OK. The Navigator Window appears .
.png)
9. In the Navigator window, locate the database of which data you want to extract to Excel. In this case, we will be extracting data from the UsingOLATION SQL database.
10. Expand the corresponding database, and then click
on the table that you wish to bring to Excel. E.g., Factdata table.
Notice that this will display a preview of the data within the table selected.
.png)
11. Click Load.
This will bring down the Factdata SQL table into Excel worksheet in a table
form. Next is to stage this data into a PivotTable to enable us to pick
the specific fields and data that we will need or would want to build
a PowerOLAP Cube from.
.png)
12. From the Excel ribbon, go to the Design tab and click Summarize PivotTable option.
.png)
13. In the Create PivotTable dialog that appears,
specify the table or range that you want to create a PivotTable from.
By default, it picks the whole table range .
.png)
14. For this example, accept the default settings and click OK.
.png)
15. Now, a list of all the fields or columns that comprises
the Factdata SQL table will appear (see PivotTable fields on the
right pane boxed in the succeeding image).
In a real life scenario, you could be dealing with a hundred fields, so
through PivotTable, you can easily manage what data you really need to
work with and specify which specific fields you will actually want to
use. Next step is to define which fields you want to include in your PivotTable.
.png)
16. Check the fields you want to include in your dimension. In this case, select all five fields.
17. Re-arrange the fields as you want them to appear
by dragging and dropping them under the Filters, Columns, Rows and Values
sections (see bottom section of the boxed pane in the preceding image).
Arrange as follows:
Filters: dim_Actua_Vs_Budget
dim_Accounts
Columns: dim_Months
Rows: dim_Regions
Values: Amount
When you have arranged the PivotTable structure, as you would like it to appear in your PowerOLAP model, you can now bring in the structure and data into a PowerOLAP database .
18. In the Filter Dimensions, you may select a specific Member to show, e.g., you may want to show only Actual data. You can do so, by clicking on the corresponding drop-down button and selecting the specific member to display.
For this example however, we will want the Filter fields to display data for all members of the dim_actua_VS_Budget and dim_Accounts fields .
19. Click on the Filter Field dim_Actua_Vs_Budget
and select All then click OK.
In this specific example, All is selected by default so we may skip this
step.
.png)
20. Do the same for the Filter Field dim_Accounts
where you select All then click OK.
Again, in this example All is selected by default so we may skip this step
.
21. Select the PivotTable by clicking on it (any area that is covered by the PivotTable).
.png)
22. From the Excel ribbon, click the Pivot to PO
tab and click the Pivot to PO command icon.
Next you will be asked in which PO database you want to bring the meta
data structure and the fact data into .
.png)
23. Browse for a location of the database where you want
to generate your new PowerOLAP cube.
For this example, let’s push the data into a new PowerOLAP database, which
we will call Book1.
24. Click OK.
Next you will be prompted with the message whether you want the fact data
to be Set or Accumulated.
• Set Option - This will replace or overwrite the existing values in the PO cube with the new values from the external source.
• Accumulate Option - This will add the new value from the source to the existing values in the PO cubes.
.png)
25. For this example, pick ‘Set’ option by clicking Yes. Notice that it automatically generated a new sheet called ‘POHierrachy’ which contains the Metadata import commands that will be used by the PowerOLAP engine to push the metadata structures, such as Dimensions, Members, Hierarchy Structure and even Aggregate Weights to the target PowerOLAP database.
As a result, this will create an olp (PowerOLAP database) where the PivotTable is built into as a cube.
.png)
26. Next check the cube created by opening the PowerOLAP
application and opening the database that was just created, which in this
case is Book1.olp.
Since this is a new PowerOLAP database, we know that we only have one cube
built in here so far; which is the one we created out of the PivotTable.
For this example, change the filter dimensions to show the following members.
27. Create a slice view out of that cube. By default,
the cube will take after the name of your PivotTable, hence in the example
the cube name is PivotTable1 (see encircled in the preceding image).
However, you have the option to rename your PivotTable, by going to the
Analyze tab of the Excel ribbon and typing the new name on the PivotTable
Name textbox .
.png)
28. Going back to PowerOLAP cube, change the slice configuration
to show the following:
Dim_Accounts: Sales
Dim_Actua_VS_Budget: Actual
.png)
Notice that the newly built cube has a similar dimensional structure to the PivotTable from which it was created from. However, since hierarchy management is very difficult to do from an Excel PivotTable, the hierarchy generated may not appear flawless in PowerOLAP. On the other hand, since we’ve got all the metadata and fact data built into PowerOLAP, we can now easily re-organize the default order of the Dimension members in a slice view, add additional hierarchies, build subsets, apply formulas and a lot more other additions and optimizations.
29. So for example, we would want to create a new hierarchy for dim_Regions, from PowerOLAP, go to Model tab, Dimensions, select the dim_Regions dimension and select the Edit button. This will bring up the Dimension Hierarchy dialog, expand the dim_Regions hierarchy .
.png)
30. For instance, we want to add a new hierarchy for regions falling under North America, click the Create New Member icon and type in the North America on the Members pane on the left. You may re-arrange the order of the members by dragging and dropping them below and atop other members in the Members pane on the left hand side of the dialog. For this example, drag and drop North America at the bottom of the Member list.
31. Next, drag and drop North America to the Hierarchy pane on the right, as a child of dim_Regions. And drag and drop the members Canada, USA and Mexico as children of North America and click OK (green checkmark button). Again, click OK on the Dimension dialog.
.png)
32. Now go back and refresh your slice by pressing F9.
Notice that the new hierarchy now appears on the slice, you can expand
the parent North America to show its children by double-clicking on the
plus sign.
.png)
In a real life scenario, over time, your external data source can be updated. And most likely, you will want to be able to access those updates for your analytical work or for reporting purposes. This is one other advantage of Pivot to PO function as it facilitates an easy method for bringing down or pushing those updates down to your PowerOLAP database.
As an example let us edit our external data source, which is the factdata SQL table from the UsingOLATION SQL database, and show how to push those updates to the PowerOLAP cube.
33. Open the SQL Server Management Studio, access the UsingOLATION SQL database and open a view of the Factdata SQL table.
For example, let us change the January Sales for the region Argentina and would like to get that update in the PowerOLAP cube. For exercise purposes, let us change it to a big number so we can easily spot the changes between SQL database, Excel and PowerOLAP.
.png)
34. Type 88888888 on the intersection corresponding to Actual Sales for January by region Argentina.
35. Next, move to Excel and find the same intersection
that you have updated in your PivotTable. Note: Make sure that you have
the correct field selections displayed on the Pivot table. In this case
make sure that Filter fields have the following selections:
dim_Actua_VS_Budget:
Actual
dim_Accounts: Sales
36. From the Excel ribbon, go to Data tab, and
in the Connections control group, select Refresh button.
You will now see the new data come into your Excel report.
.png)
37. Now, push the updates to your PowerOLAP cube by clicking the Pivot To PO tab in Excel ribbon and clicking Data to PO command icon.
.png)
38. A dialog will appear next which will allow you to select which PowerOLAP database you want the data updates to be pushed into. In this case, select the same db we created earlier, Book1.olp.
.png)
39. Click OK. Next, it will bring up a message prompt which will ask you whether you want to ‘Set’ or ‘Accumulate’ data .
.png)
40. For this example, select ‘Set’ option by clicking
Yes.
You will see the following message prompt appear.
.png)
41. Click OK.
As a result, the new value will now come through to the PowerOLAP cube.
So whenever you want to update your PowerOLAP cube with the latest data, you can do this easily and quickly through the use of the OLAPPivot to PO feature in Excel.
42. To check that the new value has indeed come through to PowerOLAP cube, go to the PowerOLAP application, open the corresponding cube and make sure that you have the following slice configuration as the succeeding image. Click the Recalculate command button or F9.
Notice that the data update is now reflected in the PowerOLAP cube (as encircled in the picture below).
.png)
This manual concludes how to use the Pivot to PO feature.