Creating and Exporting Data to a Star Schema

You can create a star schema by specifying the prefix that identifies the tables for the schema and the dimension members of the cells to be exported. Before you export the data, make sure that the application data is consolidated.

Caution!

Do not use the Financial Management database as the destination database for the data export.

Note:

For Oracle database users: The extracted text data is stored in NVARCHAR(Unicode) format. Use the Oracle “translate” command in SELECT statements to convert the text from Unicode to ANSI format. For example, SELECT translate(LABEL using CHAR_CS) as LABEL FROM DEMO_ACCOUNT.

You can also extract selected data to a flat file. The data is extracted as a .dat file to the HFM Server Working folder on the application server.

Note:

If you are extracting a large amount of data, make sure that you have adequate disk space for the extract.

Oracle recommends that you add Financial Management to the exceptions for your Web pop-up blocker. When you perform some Financial Management tasks on the Web such as loading data, a status window pops up showing the task status. If you have a pop-up blocker enabled on your computer, the status window is not displayed.

  To create a star schema and export Financial Management data to it:

  1. Open the application.

  2. Select Administration, then Extended Analytics.

  3. Do one of these tasks:

    • From Template, select the saved POV template to use.

    • Select the POV dimension members to export.

  4. From Destination Database (DSN), select the database to which you are exporting the star schema.

    Note:

    Do not use the same Financial Management database that you are exporting data from as the destination database.

  5. For Relational Table Prefix, enter the prefix that will identify the tables for the star schema.

    Note:

    The prefix can contain up to 10 alpha-numeric characters and must start with a letter. It cannot contain an underscore. The default prefix is the application name.

  6. From Extract Format, select the type of extract to perform on the data:

    • Standard

    • Metadata Only - extracts metadata only

    • Selected Metadata Only - extracts only the metadata for the selected dimension members.

    • Essbase

    • Data Warehouse

    • Flat File with Header

    • Flat File No Header

  7. Optional: Uncheck the Exclude Dynamic Accounts option to include dynamic accounts in the star schema.

  8. Select Create Star Schema.