Extract data from Oracle Hyperion Financial Management to Workiva as a file

With this chain, you can extract data from Oracle® Hyperion® Financial Management (HFM) as a file for use in the Workiva platform. When you run this chain, it:

• Automatically extracts data from Oracle HFM as a flat file
• Prepares the flat file for the Workiva platform
• Uploads the file into Wdata as a table dataset

Ingredients

To create this chain, you'll need connectors for:

• Workiva
• File Utils
• Tabular Transformation
• Oracle HFM
  • For Oracle HFM version 9.3.1 to 11.1.2.3, an Oracle HFM Legacy connector
  • For Oracle HFM version 11.1.2.4.x or 11.2.x, an Oracle HFM connector

You'll also need the ID of the table set up in Wdata to upload the Oracle HFM data to as a dataset. In the table's URL, the ID is the alphanumeric string after tables/.

Configuration file

For Oracle HFM 11.1.2.4.x or 11.2.x, the Extract Data to File command requires a configuration to set the Point of View (POV) dimensions. Each line of the configuration must be in this format: [DATA_SUBSET],<DIMENSION>,<ELEMENT>,<EXPANSION FUNCTION>. The command requires at least one reference to these dimensions:

• SCENARIO
• YEAR
• PERIOD
• ACCOUNT
• ENTITY
• VALUE
• VIEW
• ICP
• Any custom dimensions

For example:

[DATA_SUBSET],SCENARIO,Actual,[MEMBER]
[DATA_SUBSET],YEAR,2020,[MEMBER]
[DATA_SUBSET],PERIOD,Jan,[Member]
[DATA_SUBSET],VIEW,Periodic,[MEMBER]
[DATA_SUBSET],Entity,WorkivaConsolidated,[MEMBER]
[DATA_SUBSET],VALUE,USD,[MEMBER]
[DATA_SUBSET],ACCOUNT,BalanceSheet,[IDESCENDANTS]
[DATA_SUBSET],ICP,[ICP Top],[MEMBER]
[DATA_SUBSET],COSTCENTER,TotalCostCenter,[Member]
[DATA_SUBSET],Product,TotalProduct,[Member]
[DATA_SUBSET],DIVISION,TotalDivision,[Member]
[DATA_SUBSET],GEOGRAPHY,UnitedStates,[Member]
[DATA_SUBSET],DATAFLOWS,GeneralLedger,[Member]

Note: To automatically update the YEAR or PERIOD dimension with the current year or month, replace its value with the System Date and Time variable, and then apply the Parse Date/Time transformation to extract just the year or month.

You can reference multiple elements per line and use multiple lines per dimension. Any overlapping spaces are automatically combined and included in the focus area. You can also use these expansion functions, where I includes the referenced member:

You can provide this configuration as a file or as text. If a file, add it as a resource on the connector:

1. From Chains , click Connections sync_alt, select the Oracle HFM connector, and click Edit.
2. Under Resources, upload the configuration file.
3. Note the configuration's filename, and click Save. 

Step 1. Create the chain

1. From Chains , click Build  device_hub, Chains, and Create Chain.
2. Under Setup, enter a unique name and description to help identify the chain and its intent.
3. Under Variables, add a variable for the table ID with a placeholder value.
4. Click Save.

Step 2. Add an Extract Data to File command

To extract data from Oracle HFM 9.3.1 to 11.1.2.3:

1. Under Available BizApps, click Oracle HFM Legacy, move Extract Data to File to Start, and click Edit  create.
2. Under Basic Info, enter a unique name and description to help identify the command.
3. Under Command Properties, enter the name of the Oracle HFM application to extract from.
4. Enter the values for the dimensions members and any custom dimensions.
5. In Delimiter, select Comma (,).
6. In Line Item Detail, select Exclude.
7. Select Include calculated data, Include derived data, and Include dynamic accounts.
8. Click Save.

To extract data from Oracle HFM 11.1.2.4.x or 11.2.x:

1. Under Available BizApps, click Oracle HFM, move Extract Data to File to Start, and click Edit  create.
2. Under Basic Info, enter a unique name and description to help identify the command.
3. Under Command Properties, enter the name of the Oracle HFM application to extract from.
4. Enter the name of its uploaded configuration file, or enter the configuration as text.
5. In Extract format, select Flat file.
6. In Delimiter, select a comma (,).
7. In Line item option, select None.
8. Select Display members, Extract calculated data, Extract derived data, and Extract dynamic data.
9. Click Save.

Step 3. Add File Utils commands to prepare the flat file for Workiva

To use the flat file extracted from Oracle HFM in Workiva, use the File Utils connector to convert the unicode standard and remove unnecessary lines:

1. Under Available BizApps, click File Utils, and move UTF-8 Encode to the canvas.
2. Drag a link from Start to UTF-8 Encode, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. In Source File, select the Extract data file output from the Extract Data to File command.
5. In Source Encoding, enter ucs-2.
6. Click Save.
7. Under Available BizApps, click File Utils, and move Strip Lines to the canvas.
8. Drag a link from UTF-8 Encode to Strip Lines, and click Edit  create.
   
9. Under Basic Info, enter a unique name and description to help identify the command.
10. In Source File, select the UTF-8 File output from the UTF-8 Encode command.
11. In Lines to Strip, enter 1:8.
12. Click Save. 

Step 4. Add Tabular Transformation commands to add and map headers

Use the Tabular Transformation connector to add headers and map them to the POV dimensions:

1. Under Available BizApps, click Tabular Transformation, and move Add Header to the canvas.
2. Drag a link from Strip Files to Add Header, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. In Input file, select the Strip Lines Output File output from the Strip Lines command.
5. Select Preview Results.
6. In Header Row, enter the headers for each column, separated by a column. For example, account,icp,costcenter,product,division,geography,dataflows,amount.
7. In Delimiter, select Comma.
8. In Header Delimiter, enter a comma ,.
9. Click Save.
10. Under Available BizApps, click Tabular Transformation, and move Select POV (Advanced Query) to the canvas.
11. Drag a link from Add Header to Select POV (Advanced Query), and click Edit  create.
    
12. Under Basic Info, enter a unique name and description to help identify the command.
13. Under Tables, click Add, and add the headers as a table for use in the query:
    • In File, select the CSV with Headers output from the Add Header command.
    • In Table Name, enter a name to identify the table, such as headers.
14. In Query, enter the SQL of the SELECT query to map the headers to the POV dimensions. For example:

    SELECT 'Actual' as scenario, '2020' as year, 'Jan' as period, 'Periodic' as view, 'UnitedStates' as entity,account,icp,costcenter,product,division,geography,dataflows,amount from header ORDER by account

15. In Input Delimiter and Output Delimiter, select Comma.
16. Select Preview Results, and click Save.

Step 5. Add a Preview File Contents command

To enable a preview of the prepared data file, such as for troubleshooting:

1. Under Available BizApps, click File Utils, and move Preview File Contents to the canvas.
2. Drag a link from Select POV (Advanced Query) to Preview File Contents, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. In File name, select the Result output of the Select POV (Advanced Query) command.
5. In Preview lines, enter the number of lines to include in the preview, such as 10.
6. In Offset, enter 0.
7. Click Save.

Step 6. Add commands to add the file as a table dataset

To upload the file to Wdata as a table dataset:

1. Under Available BizApps, click Workiva, and move Upload File (Create File) to the canvas.
2. Drag a link from Preview File Contents to Upload File (Create File), and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. In Table ID, select the chain variable for the table ID.
5. In File, select the Result output of the Select POV (Advanced Query) command.
6. In Name, enter the file name to use with the uploaded table dataset, with a file extension of .csv.
7. Click Save.
8. Under Available BizApps, click Workiva, and move Import File into Table to the canvas.
9. Drag a link from Upload File (Create File) to Import File into Table, and click Edit  create.
   
10. Under Basic Info, enter a unique name and description to help identify the command.
11. In Table ID, select the chain variable for the table ID.
12. In File ID, select ID under the Result output of the Upload File (Create File) command.
13. Click Save.

Step 7. Finalize the chain and table

1. In the chain, click Publish  publish and Execute  flight_takeoff.
2. To test the chain, refresh the table, and verify the data set appears.
3. Select whether to run the chain on-demand or automatically as scheduled.
4. To best fit your organization's needs, customize the table as necessary. For example:
   • For clarity and to avoid name collisions, add a timestamp to the table's filename.
   • Remove the previous run's dataset from the table.
   • Build a query based on the table's columns. You can then connect the query to a spreadsheet, or include VLOOKUP or SUMIF calculated columns to ease connected data.
   • To ensure data accuracy, set permissions for collaborators.
   • To keep the table current with the latest data, create a chain to automatically update its datasets.

   Note: For personalized assistance specific to your organization, contact Workiva Support.