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

Prerequisites

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

• Workiva
• File Utilities
• Tabular Transformation
• Oracle HFM

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

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

Note: In the configuration, [DATA_SUBSET] is case-sensitive and must be in ALL CAPS. Wrap any member names in quotes, such as ["MEMBER"].

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. In Chain Builder, from 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. In Chain Builder, from Build  device_hub, Chains, click 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

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 (no header).
   
6. In Delimiter, enter 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 Utilities commands to prepare the flat file for Workiva

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

1. Under Available BizApps, click File Utilities, 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 Extract Data to File.
5. In Source Encoding, enter ucs-2.
6. Click Save.
7. Under Available BizApps, click File Utilities, 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 UTF-8 Encode.
11. In Lines to Strip, enter 1.
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 Strip Lines.
5. In Header Row, enter the headers for each column, separated by a column. For example, account,icp,costcenter,product,division,geography,dataflows,amount.
6. Select Preview Results.
7. In Header Delimiter, enter a comma ,.
8. In Delimiter, select Comma.
9. Click Save.
10. Under Available BizApps, click Tabular Transformation, and move Advanced Query to the canvas.
11. Drag a link from Add Header to 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 Add Header.
    • 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 List File Contents command

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

1. Under Available BizApps, click File Utilities, and move List File Contents to the canvas.
2. Drag a link from Advanced Query to List 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 from Advanced Query.
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 Create File to the canvas.
2. Drag a link from List File Contents to 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 from Advanced Query.
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 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 from Create File.
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.