Export Cube/ Export Fact Data

 

 

 

Export Factdata

The Export Cube or Export Factdata feature allows users to export the Fact Data or the data values for an entire Cube or specific portions of a Cube.

[Note: this feature exports only Fact Data for the selected Cube; the Cube’s Meta Data must be exported using the Export Meta Data feature, previously described.]

In PowerOLAP users can save an Export Cube structure via the Task Scheduler.

To Create an Export Cube Task/Export Fact data:

1.   In the PowerOLAP ribbon, go to Data tab then in the Export control group, select Factdata icon or Export Data command.
The Export Cube Tasks dialog box appears.

2.   Create a new task by clicking the Add button.
The Select Cube dialog box will be displayed.

3.   Enter a <task name>.
It is advised to give the task a name that is descriptive of what it does.
For this example name the export task as Margin-Actual Unit Cost and Price.

4.   Select the source Cube or cube where export data are going to come from using the Cube drop down button. The Cube drop-down menu lists all Cubes in the current database to which you have privilege.
For this exercise, select the Margin cube.

5.   Specify an <Export File Name> by clicking the Lookup button.
(In the example, the export file was named as Margin-Actual Unit Cost and Price.

6.   Select the appropriate format for your export file (For this example, choose default i.e., PowerOLAP Import).

7.   Select a delimiter (For this example, choose default i.e, Tab).

8.   Click Next.    
The Options dialog box will be displayed.

Note the three checkboxes: You can choose to Skip Zeros, Skip Aggregates and Skip Calculated Values by maintaining the default setting. Maintaining these defaults causes only Detail-level values that are not calculated to be exported, enhancing the export procedure performance.

 Notes

If you export Cube Fact Data without Aggregates and Calculated Values, and then proceed to import the file (as shown later in this section) into a Cube with the same Hierarchy structure and Formulas, then all values will appear as in the “original export Cube”. The fact that zeros are not exported (then imported) will not matter—PowerOLAP® puts a zero value in Dimension member intersection points where there is no information (“null”).

 Tip

Enable options by either clicking on the radio button or checking the checkbox.

9.   Click Next button.
The Destination Members dialog box will be displayed.
This is where source Members to be exported are defined.

The Dimensions list box contains the Dimensions for the selected Cube. At this point, you can select a Dimension from the list— in which case, once you select a specific Dimension, all Members for that Dimension will appear in the Members list box on the right.

If you wish to export all the Dimensions and their corresponding members, click Next. Otherwise, specify in this dialog box only the specific members that you want to export.

In the example we only need to export data for Actual  Unit Cost and Unit Price for the Margin cube.

Define specific members to export as follows:

10. In the Dimension list select the dimension/s from which you only want to export specific members. The Members and Subset tab on the right hand pane will display a list of the members and subsets that exist for the selected dimension.

By default, the All radio button is active. Select individual Members by checking the box next to each Member name (the Selected radio button becomes active) to export Fact Data only for those Members.

For this exercise, select Version dimension and pick the member Actual by ticking the checkbox.
Next, select Margin Account dimension and pick the members Unit Price and Unit Cost:

11. Click Next button.

A summary of the options selected will be displayed.

12. Click Finish button.
This completely creates your Export Cube Task.

 Notes

The new task is created and will be added in the Export Cube Tasks dialog box. This task is saved within the PowerOLAP database and can be scheduled via the PowerOLAP® Scheduler. It could also be manually executed (internal to PowerOLAP) by clicking the Execute button.  The Export Cube Tasks dialog box also provides facility to edit and delete any defined task.

Security privileges are maintained in the Fact Data export process. If a user does not have Read access to specific data points, then the values for those data points will not be exported. (Refer to the Section Applying Database Security.)

Generate an Export File for Export Cube Task

Create an export file as follows:

1.   From the Export Cube Tasks dialog, select a task.
(I.e., Margin-Actual Unit Cost and Price)

2.   Click on the Execute button.
This will generate the corresponding export file. You should receive a message prompt if your task completed successfully.

3.   Click OK to close the message prompt and Done to close the Export Cube Task dialog.

 Notes

Remember that an export Fact Data—both for the Cubes and Slices (shown next)—supports numeric as well as string values.

 

Export Cube Fact Data Script

The following figure shows the first few lines of the TXT file that represents the Fact Data from the Margin cube. The first few lines indicate the Dimensions that make up the Margin cube: Version, Year, Region, Product, Month and Margin Account. The succeeding lines are numeric values which occurs at the intersection of those Members.

 

A full explanation of the Cube Fact Data export script appears in Advanced Reference Materials Section.