Importing Metadata and Fact Data
Compared to the export process, the import process is equally straightforward, but there are issues you must keep in mind when importing Metadata and Fact Data. For example, there may be a difference in the “dimensionality” between the database into which you are 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 section about Importing.
The Import Metadata function imports a file you previously exported file. Such an export typically has a subset of model that you want to copy from one database to another. To import an entire metadata structure, go to Importing metadata from XML.
In order to import Metadata, you must have a database open. You may want to import Metadata into a pre-existing database—for example, to re-create a Cube whose Metadata you exported from another database; or to add Members to a Dimension in the database (assuming the new Members for the same Dimension exist in the import file). Or, you may want to create a new database, as in the following example—thereby creating a new database (which we will call TestImpMetadata.)
To import metadata:
From the Data ribbon's Import group, click Metadata to open the Import Metadata dialog.
Click Browse to select AcmeMargin.txt, created previously in Exporting metadata.) You are returned to the Import Metadata dialog.
Select Tab as the file Delimiter. Go to Exporting metadata for information on the other delimiters.
At this point in the import process—whether you are importing Fact Data or Metadata—proCube 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, proCube will import the file, creating in the database all Metadata found in the text file. If you are following the example, you will receive this message:
Figure 1. Saving Imported Lines to proCube Message
As the message shows, if there are errors during the Metadata import process, proCube will indicate the number of errors and ask if you would like to save all correctly imported lines to proCube. 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 and edited in Windows Notepad or Wordpad so that you can identify and fix errors for re-importation into proCube.
Select OK. The database is updated by the Metadata import file.
Again, if you are following the example, you can open ImportErr.log and see the following, first few lines of the file:
Figure 2. ImportErr.log in Notepad
Note that the Member names that are “invalid” all have an apostrophe. When you created a new database for the Metadata import file, did you select Allow Reserved Characters within the New Database Dialog? This error log file was generated as a result of not checking the box. You can now create another new database, check that box, and then import the file.
A message opens indicating that there are no errors. Click OK to dismiss.
Having completed the Metadata import, you can examine the Cubes/Dimensions/Formulas and confirm that all the Metadata elements that existed in the text file (here, generated by an export process from the Acme Trading Company Margin cube) now exist in the database into which you imported the file (in our example, TestImp.olp)
Regarding Formulas brought in via a Metadata import, remember to check to determine if 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 Metadata import, Save changes to the database.
From the Data ribbon's Import group, click Metadata From XML to open the Select dialog.
Enter a file name and path or press the Browse button and select a file.
Click Open to begin the import process. A message appears indicating that the process was successful.
Figure 3. Successful Metadata Import Message
Errors during XML import may require editing the XML file. In such cases, please contact 9Dots Technical Support.
The Fact Data import process is where a potential difference in dimensionality really comes into play; there may be Member intersection points in the import file (e.g., for the region Canada, Unit Cost, 2007, for Products X, Y and Z)) that do not even 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, a Dimension in the database may exist (for example, Version) that does not exist in the import script. You will have to ask yourself, what Member of the Version dimension does the data represent in the import file?
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 Metadata parameters set up beforehand (otherwise it would make no sense to import figures!).
From the Data ribbon's Import group, click Fact Data to open the Import Data dialog.
Figure 4. Import Data Dialog
Click Browse to select the file to import.
Select the <<file>> to import (for this example, Yearly UnitCost and Price.txt).
In the Import Data dialog, select a Delimiter.
Click OK. If the file is invalid, a message appears. Otherwise, the Import Fact Data dialog opens.
Figure 5. Import Fact Data Dialog
We will consider this dialog box in detail:
Located at the top right of the Import Data dialog box is a text box used to select the Cube into which you want to import the data. All cubes for the open database are listed. (In this example, there is only a Margin cube in the database.)
Select a Cube.
Note the Dimensions list box on the left. proCube 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:
The Dimension is present in both the Cube and the import file.
The Dimension is found in the Cube but not the import file.
The Dimension is found in the import file but not the Cube.
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 has been set up to precisely “contain” the data in the import script. But there will be instances when there is not an exact match of Dimensions—resulting in what we call a “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 Accountexists only in the import file. This is a good example of how dimensionality may differ between the two Cubes.) 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 (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 first given a warning about the time involved to list all Members. Then the Members list box (on the right) displays the Members available in the import file.
Assuming you want only certain Members, check those you want from the import file. (In the example, you keep the default, which will import data from 2000 and 2001 from Yearsand 16 specific 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—in other words, which Member 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). The general procedure, therefore, for this situation is as follows:
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.)
Figure 6. Import Fact Data Dialog - Version Dimension
Finally, from the Dimensions list , 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 the question, 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. We will discuss this in more detail momentarily.) We see that in the Product Account dimension, there also exists aUnit Price member—so, logically, we might want to bring that Member’s data over.
The general procedure in this case (when Set is selected as the Method) is as follows:
In the Dimensions list , 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. The import file would process, and you would receive a message indicating how many data points have been updated.
Click OK to return to proCube Management Studio. You can check a slice to confirm that the data appears from the Fact Data import procedure.
The following concerns the final choices in the Import Fact Data dialog, in particular, the Import Method, which is located at the bottom of the Import Fact Data dialog:
Figure 7. Import Fact Data Dialog - Method Options
If you proceeded with the example using the default Set method, you saw the exact data from the import file appear in the Cube—as described below. The Accumulate and Clear methods provide three more choices:
Set — 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.
Accumulate — 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.)
Clear — All the data at the Member intersection points determined in the Import Fact Data dialog will be cleared and made zero.
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.