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.

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 tasks 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 extract Financial Management data:

  1. Select Consolidation, then Extract, and then Data.

  2. Do one of these tasks:

    • If you saved POV dimensions in a template, from Template, select the template.

    • If you are not using a template, select the POV dimension members to export.

  3. From the Extract Destination section, for the Type option, select Database.

    Note:

    This option is only available if you are assigned the Administrator or Extended Analytics security role.

  4. From Extract Format, select an option:

    • Standard

    • Metadata Only - extracts metadata only

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

    • Essbase

    • Data Warehouse

  5. From Options, select one or more options:

    • Extract Dynamic Accounts

    • Calculated Data

    • Derived Data

  6. From Line Item Details, select an option:

    • Total Summary for Cell

    • None

  7. From Schema Actions, select Create Star Schema.

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

    Note:

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

  9. For Relational Table Prefix, enter a prefix to identify the tables for the star schema, or use the default application name prefix.

    Note:

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

  10. Click Extract.