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® component 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:
- Importing from or exporting to non-ODBC compliant databases or mainframes;
- 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.)
In sum, this section will instruct you in the following procedures:
- Export Meta Data
- Export a Cube or Fact Data
- Export Slice
- Export Security
- Export Database
- Import Meta Data
- Import Fact Data
- Import Security
- Import Database
- Importing From a Tab Delimited Text File in Excel
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.