Import Fact Data
We will consider some of these issues in the following example, which involves importing a previously created Cube Fact Data export file, ‘Margin-Actual Unit Cost 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!
1. From the PowerOLAP ribbon, go to Data tab, then in the Import control group select Factdata icon or Import Data command.
The Import Data dialog box is displayed:
2. Click Browse to select the file to import.
3. Select the <file> you want to import
(for this example, Margin-Actual Unit Cost and Price.txt).
4. Click Open.
5. In the Import Data dialog box, select a Delimiter (e.g., Tab).
6. Click OK. (There will be a message if the file is invalid.
The Import Fact Data dialog box appears:
7. Select a Cube (E.g., Margin).
a. For Dimension(s) that exist both in the cube and the import file
– You can choose to import data to populate individual, multiple or All Members, which is the default
b. 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.
c. For Dimension(s) that exist in the import file but not in the cube
– You may want to consider what Member should the data that will be brought over into the cube should come from. This is because we can only bring over one Member’s data, if the method selected is ‘Set’.
8. In the Method section of the Import Fact Data dialog, select from the options: Set, Accumulate and Clear (For this example, select Set).
9. Click OK.
10. Click OK to return to the main application window.
11. Select File button, Save Database.
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.
In another example, let us consider dealing with a cube and an import file that have different dimensionality. For this demonstration, we are going to create a new cube called Margin Test which we will pattern out of the Margin cube but make a few edits on it’s dimension components so that it will have a different dimensionality with the original cube.
For this example, begin by creating the new cube and then import the fact data:
1. From the PowerOLAP ribbon, go to Model tab then in the Model control group select Dimension command.
2. Add a new dimension called Currency with members: USD and Yen.
3. Create a new cube and name it Margin Test which should consist of the following dimensions: Year, Region, Product, Currency, Month and Margin Account.
4. From the PowerOLAP ribbon, go to Data tab, then in the Import control group select Factdata icon or Import Data command.
The Import Data dialog box appears.
5. Browse for the fact data import file Margin-Actual Unit Cost and Price.txt, select Tab as delimeter type and click OK.
6. In the Import Fact Data dialog, select a cube (I.e., Margin Test).
7. In the Dimensions list box select a Dimension that exists in both the Cube and the import file (for example, Year and Product). You are given a warning about the time involved to list all Members.
8. Click Yes to proceed.
9. Assuming you want only certain Members, check those you want from the import file. For example, you can select to import data only for years 1998 and 1999 and select only to import data for the first 16 Members listed from Products.
10. For Dimension(s) found in the Cube but not the import file, which in this case is Currency, select a member which we will use to contain the data being imported. For this example, select USD.
11. For Dimension(s) found in the import file but not in the cube, which in this case is Version, select a member whose data you want to import into the cube. For this example, since the Version dimension only has one member, simply select Actual.
12. 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.
13. Click OK to return to the main application window.
The following topic will discuss the Import Methods appearing in the Import Fact Data dialog box.
Fact Data Import Methods
In the Import Fact Data dialog box, there are choices for the Import Method, which appear at the bottom of the dialog box:
- SET
- ACCUMULATE
- CLEAR
Tip
You can use both impor files generated via an Export Cube Task and Export Slice task when importing fact data into a cube.