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.
.png)
2. Create a new task by clicking
the Add button.
The Select Cube dialog box will be displayed.
.png)
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.
.png)
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.
.png)
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.
.png)
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:
.png)
.png)
11. Click Next button.
A summary of the options selected will be displayed.
.png)
12. Click Finish button.
This completely creates your Export Cube Task.
.png)
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.
.png)
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.
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.
.png)
A full explanation of the Cube Fact Data export script appears in Advanced Reference Materials Section.