1. Administrator's Guide
  2. Extracting Data to a Database
  3. Star Schemas

Star Schemas

You can create multiple star schemas per application. The table names in each star schema begin with a prefix that you specify. You can select multiple members in all dimensions to create the star schema that best reflects the information to export.

Note:

Cell text and line item detail are not exported to the star schema.

The data combinations in the star schema are created based on the dimension members that you select to export. The more dimension members selected, the more possible data combinations that need to be created in the star schema, and the more time needed to complete the export process. You can calculate the number of data combinations by multiplying the number of members selected for each dimension.

Caution:

Do not select to export all members from every dimension; select segments of data to export. Depending on the application size, the number of data combinations, and the amount of time to complete the export time could be excessive.

For example, you can export this data:

  • Scenario - Actual

  • Year - 2014

  • Period - January

  • View - Periodic

  • Entity - Regional, United States, Florida, Connecticut

  • Value - USD

  • Account - Gross Margin, Sales, Total Costs

  • ICP - [None]

  • C1 - Balls, Tennis Balls, Golf Balls

  • C2 - All Customers, Customer2, Customer3, Customer4, Customer5

  • C3 - [None]

  • C4 - [None]

The star schema that is created can then be used by Oracle Essbase Integration Services to create one or many data cubes to reflect the audience that needs to see and use the information. The star schema contains 180 data combinations for these members (1 * 1 * 1 * 1 * 4 * 1 * 3 * 1 * 3 * 5 * 1 * 1 = 180).

Note:

When you export metadata to Oracle Essbase through Integration Services, the Extract Data to a Database feature uses metadata in a different order than it displays in the Oracle Hyperion Financial Management application.

You can extract only local currency data if you prefer. To extract only local currency data, use the Entity Currency member from the Value dimension in the Point of View. Entity Currency acts as placeholder for the currency and extracts the default currency for each entity that is selected.

If the selected Scenario is YTD, the data extract extracts periodic derived data. For example, suppose the Scenario is ZeroView=YTD. There is a value of 100 in an expense account in the first period. In the second period, no data is reported for that account. The derived periodic value for the second period is –100, forcing the YTD amount to 0. If Extract Data to Database is run for this Point of View on a periodic basis, for the first three periods, the values are 100, –100, and 0, respectively.