Importing and Exporting

Quite often when you use PowerOLAP®, you will need to get data into your database from another system. In addition, there may also be a need to export the data from your model to be used by someone else. As you may already know, one of the key benefits of PowerOLAP® is its ability to facilitate a bi-directional exchange of data between a relational database and your multidimensional PowerOLAP database, using the OLAP Exchange® component. Although this is the optimal situation, there will be cases where the data you need to analyze is in a non-ODBC-compliant system (OLAP Exchange® requires ODBC compliancy). In these cases, PowerOLAP's capabilities to import/export text files may come into play.

The instances when you may need to import data to, or export data from a PowerOLAP database include:

Modifying your own database's Meta Data, Formulas, and Fact Data by using an import script, and

Sharing models with other users by exporting Meta Data and Fact Data. Typically, an export procedure would involve another user creating a new database; importing Meta Data files to create the database structure; then importing Fact Data files to populate the database.

PowerOLAP® allows you to import Meta Data or Fact Data in the form of flat, delimited text files. Conversely, you can also export the Meta Data or Fact Data (in the form of Cubes and Slices) from your PowerOLAP multidimensional database into a text file that can in turn be imported into another system.

In fact, this is the procedure we will follow in this exercise: first you will export a portion of the Meta Data of your Avon Trading Company database; then export a particular Cube or Slice (whose data you want to hypothetically share, for example, with another user). You will then learn how to import these text files, even though we will not actually import into the Avon Trading Company.

(Remember that to populate unpopulated portions of a database, the Cube Transfer function, discussed in the topic Transfer/Clear Cube, is perhaps the appropriate function.)

A detailed explanation of all Meta Data and Fact Data Import Commands is included in the section at the end of this manual: Advanced Reference Materials.

From the main menu, select Data, Export, Metadata.
The Export Metadata dialog box is displayed:

Enter an <<export file name and path>> in the File name text box;
or press Browse button and select a place to save your file in the correct directory.
As an example, you can name the file AvonMargin.

Click on a Cube in the Cubes list box on the left to export Meta Data from that Cube only (e.g., Margin).

You can export Meta Data from several Cubes or from all Cubes in a database, using Ctrl-click or Shift-click.

If the Mark Dimension check box is selected, then all the Dimensions for the selected Cubes will be selected by default.
Otherwise, select specific Dimensions to export from the Dimensions list box by clicking in the check boxes next to Dimension name(s).

Notice the 3 checkboxes in the Cubes section of the Export Meta Data dialog: Mark Dimensions, Formulas and Mark Slices. These options are enabled by default. If these checkboxes are checked, PowerOLAP will automatically select all the dimensions, slices and formulas that belong to the cube selected and include them in the Meta data export.

Select whether to export Aliases and Formulas.
These boxes are checked by default. If you do not wish to include either in the Meta Data export, un-check the appropriate box.

Select a Tab, Comma, Period or Other as the file delimiter. This provides a choice of how to parse out the pieces of information in the export text file e.g., if the Tab radio button is checked, the text file will separate each piece of information by a tab.

The following figure shows the first few lines of the TXT file that represents the Meta Data from the Margin cube. Indeed, they are just the initial lines describing the Product dimension (indicated by "D+"). Something else we can tell about the Product dimensionthere are neither Alias Groups nor Property Groups in the Dimension (if there were, this would be indicated below the Alias Group and Property Group headers). Beginning with the "M+" indicator, the Members for the Dimension are listed.

You can scroll down to see further how the script appears, all the way to where the Cube's formulas are shown.

The Export Cube feature allows you to export the Fact Data 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, go to Data menu, select Export then select Fact Data.

Under Export Cube Tasks dialog box, click the Add button.
Select Cube dialog box will be displayed.

Select a 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.

Type a File Name.
(Note that the exported Cube fact data will be in a text format)

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.

Note: 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").

Click Next button.
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.

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.

Select a Member from the Dimension selected by checking the checkbox. (In the given example Actual was selected.)

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.)

Note 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 Current Year Model cube. The first few lines indicate that Members from the Actual Vs Budget, Accounts, Regions and Months dimension will appear in succession. As you can see, this is followed by a numeric value, which occurs at the intersection of those Members.

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

Fact Data can also be exported from a Slice. Exporting Fact Data from a Slice will also result in a flat, delimited text file being written to a chosen folder or network location.

Select Slice, Open or proceed with a currently opened Slice.
(The following example is a previously configured Slice,
Yearly UnitCost and Price, from the Products cube).

Select Data, Export, Slice. The Export Slice dialog box is displayed with the name of the Slice displayed:

You can use the default name, Yearly UnitCost and Price.txt,
or type a <<file name>> and use the Browse button to choose the path where you want to save the file.

Note: Attempts to export large data sets through the Export Slice feature may not complete successfully. To export large data sets ranging in size up to a full database, use the Export Fact Data or Export Database feature.

The format of the Slice Fact Data is substantially the same as the Cube Fact Data export script, shown previously.

The previous discussion demonstrated how straightforward and simple the export process is, for both Meta Data and Fact Data (whether an entire Cube or a Slice). The import process is equally straightforward, as you will see, but there are issues you must keep in mind when importing Meta Data and Fact Data. For example, there may be a difference in "dimensionality" between the database into which you will be importing data and the import file. Further, in regard to Fact Data specifically: figures may already exist at the Member intersection points referred to in the Fact Data import script. We will consider these issues in this topic about Importing.

In order to import Meta Data you must have a database open. You may want to import Meta Data into a pre-existing database-for example, to re-create a Cube whose Meta Data you exported from another database; or to add Members to a Dimension in the database. Or, you may create a new database, as in this example-therefore, create a new database, giving it the name, e.g., TestImpMetaData.

From the main menu, select Data, Import, Metadata.
The Import Metadata dialog box is displayed.

       Select <<file name>> to import and press Open.
(For example, pick the AvonMargin.txt, created previously in the
Export Meta Data topic.)
You are returned to the Import Metadata dialog box.

At this point in the import process-whether for Fact Data or Meta Data-PowerOLAP will immediately determine whether the selected file is a valid import script. If the file is invalid, a message box will appear, stating Incorrect Import File Type, and you will be returned to the main application window.

If the file type is correct, PowerOLAP imports the file, creating in the database all Meta Data found in the text file.

As the message box shows, if there are errors during the Meta Data import process, PowerOLAP will indicate the number of errors and ask if you would like to save all correctly imported lines to PowerOLAP. If you select OK, errors will be written to a log file stored in the database directory. The log file takes the default name ImportErr.log, which can be opened in Windows Notepad or Wordpad, possibly for identifying and fixing errors for re-importation into PowerOLAP.

If you are following the example, you can open ImportErr.log and see the following first few lines of the file:

Note that the Member names that are "invalid" all have an apostrophe! When you created a new database for the Meta Data import file, did you check the box, Allow Reserved Characters (circled below)? This error log file was generated as a result of not checking the box. You can now create another new database, and this time, check that box; then import the file-you will see the subsequent message box (no errors) as a result.

Having completed the Meta Data import, you can examine the Cubes/Dimensions/Formulas and confirm that all the Meta Data elements that existed in the text file now exist in the database into which you imported the file (in our example, TestImp.olp).

An important point about Formulas brought in via a Meta Data import: check to determine whether there are any cross-cube Formulas that refer to Cubes that did not exist in the import file. If this is the case, be aware of the implications and determine how you want this (formerly) formula-driven data to report.

Once you are satisfied with the results of the Meta Data import,
Save changes to the database.

Changes made to Meta Data, including those made by a Meta Data import, are not saved until you save the database.

The Fact Data import process is where a potential difference in dimensionality really comes into play. After all, there may be Member intersection points in the import file (e.g., for the Region Canada, Unit Cost, 1998, for Products X, Y and Z)) that do not exist in the database into which you intend to import the Fact Data (for example, if Canada is not a Member of the Region dimension in the database). On the other hand, there may exist a Dimension in the database (for example, Version) that does not exist in the import script-here you will have to decide, what Member of the Version dimension should the data be made to represent when it is imported?

We will consider some of these issues in the following example, which involves importing a previously created Slice Fact Data export file, Yearly UnitCost and Price.txt, into the database created in the previous steps, TestImp.olp.

In order to import Fact Data, you must have a database open, and a Cube must already exist-logically, you will have many of the Meta Data parameters set up beforehand, otherwise it would make no sense to import figures!

From the main menu, select Data, Import, Fact Data.
The Import Data dialog box is displayed:

Select the <<file>> you want to import
(for this example, Yearly UnitCost and Price.txt).

Click OK. (There will be a message if the file is invalid.)
The Import Fact Data dialog box appears:

Located at the top right of the Import Data dialog box is a text box to select the Cube into which to import the data. All Cubes for the open database are listed. (In the example, there is a Margin cube in the database.)

PowerOLAP automatically compares the Dimensions in the import file to those in the chosen Cube and places an icon to the left of the Dimension name as follows:

Often, if you are importing Fact Data, there will be an exact match (the first icon)-because you intend to import Fact Data into a database that is set up to precisely "contain" the data in the import script. But there will be instances when there are no exact matches of Dimensions-the so-called "difference in dimensionality." (Thus, in our example there are Year and Product dimensions in both the import file and the Cube; Version, Month and Margin Account exist only in the Cube; and Product Account exists only in the import file. This is a good example of how dimensionality may differ.) What follows is a consideration of each scenario:

For Dimensions that exist in both the import file and a Cube, you can choose to import data to populate individual, multiple or All Members, which is the default:

In the Dimensions list box select a Dimension that exists in both the Cube and the import file (for example, Years). You are given a warning about the time involved to list all Members; and then the Members list box, on the right, displays Members available in the import file.

Assuming you want only certain Members, check those you want from the import file.
(In the example, you can keep the default, which will import data from 2000 and 2001 from Years and 16 Members listed from Products.)

For Dimension(s) found in the Cube but not the import file, you may need to decide what Member in the Cube should be the receptor of the data-which Member logically best describes the data being imported. The example provides a good test case: for Version-which is in the Cube, but not the import file-which Member (Actual, Budget or Variance) should we use to contain the data being imported? Here we know that the data (Unit Price and Unit Cost data for 2000 and 2001 for a group of products) will be the "Actuals" data, so you can select Actual from the Members list box on the right (as shown in the following image).

In the Dimensions list box select a Dimension that exists only in the Cube; then select a Member to contain the data from the import file. (In the example, choose Actual for the Version dimension; USA for the Region dimension; Unit Price for the Margin Account dimension; and January for the Month dimension. These latter two also make logical sense vis-à-vis this particular import example.)

Finally, from the Dimensions list box, we must consider those that exist in the import file but not the Cube (such as Product Account in the example). Here, too, we apply some logic. Above, we made the decision that for the Margin Account dimension, we would select Unit Price as the Member in the Cube to contain data from the import file. Thus, we want to consider, what data from the Product Account do we want to bring over given the fact that only one Member's data can be brought over? (The fact is, we can only bring over one Member's data, if the Method is selected for Set-more on this momentarily.) We see that in the Product Account dimension, there also exists a Unit Price member-so, logically, we might want to bring that Member's data over.

In the Dimensions list box select a Dimension that exists only in the import file; then select a single Member whose data you want to import into the Cube.

At this point, if you were to proceed with the import (with Set selected as the Import Method), you could do the following:

Click OK in the Import Fact Data dialog box.
The import file would process and you will receive a message indicating how many data points have been updated:

You can check a Slice to confirm that the data appears from the Fact Data import procedure (a Slice configured to show data from the example appears as follows):

The following page discusses the Import Methods appearing in the Import Fact Data dialog box.

In the Import Fact Data dialog box, there are choices for the Import Method, which appear at the bottom of the dialog box:

With Set selected, the data in the import file will be imported into the Cube-the data in the Cube, therefore, will be an exact copy of the data described in the import file.

With Accumulate selected, the data in the import file will be added to the data in the selected Cube. Further, if a Dimension appears in the import file and not the Cube, you may select multiple Members-all their data will then be accumulated at the determined Member intersection points in the Cube (thus, in the example, you could have chosen both Unit Price and Unit Cost from the Product Account dimension in the import file-and the combined data for these two Members would appear in the Cube.)

All the data at the Member intersection points determined in the Import Fact Data dialog box will be cleared and made zero.

After you import (or by any other means, enter) new data into your database, be sure to save the database changes to disk:

IMPORTANT: If you import a text file that includes data for Aggregate Members, you will receive a message indicating that a number of lines COULD NOT be imported-referring to the Aggregate Members in the Cube. You cannot import data into cells with aggregate information. In the created error log, the Aggregate members and their data points will be listed.

Select one of the exported files, and all the related files are imported automatically, for the Meta data and Fact data.

You can import data from .txt files created in Excel. The file must take the same structural form as the Slice into which it will be imported.

Using the import file dialog box, locate the tab delimited text file you want to import.