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:
Expansion Function | Element |
---|---|
[DESCENDANTS] or [IDESCENDANTS] | Member |
[CHILDREN] or [ICHILDREN] | Member |
[ANCESTORS] or [IANCESTORS] | Member |
[PARENTS] or [IPARENTS] | Member |
[MEMBER] | HFM member |
[BASE] | Not used |
[MEMBERLIST] | HFM member list |
[ALLMEMBERS] | Not used |
You can provide this configuration as a file or as text. If a file, add it as a resource on the connector:
- From Chains , click Connections , select the Oracle HFM connector, and click Edit.
- Under Resources, upload the configuration file.
- Note the configuration's filename, and click Save.
Step 1. Create the chain
- From Chains , click Build , Chains, and Create Chain.
- Under Setup, enter a unique name and description to help identify the chain and its intent.
- Under Variables, add a variable for the table ID with a placeholder value.
- 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:
- Under Available BizApps, click Oracle HFM Legacy, move Extract Data to File to Start, and click Edit .
- Under Basic Info, enter a unique name and description to help identify the command.
- Under Command Properties, enter the name of the Oracle HFM application to extract from.
- Enter the values for the dimensions members and any custom dimensions.
- In Delimiter, select Comma (,).
- In Line Item Detail, select Exclude.
- Select Include calculated data, Include derived data, and Include dynamic accounts.
- Click Save.
To extract data from Oracle HFM 11.1.2.4.x or 11.2.x:
- Under Available BizApps, click Oracle HFM, move Extract Data to File to Start, and click Edit .
- Under Basic Info, enter a unique name and description to help identify the command.
- Under Command Properties, enter the name of the Oracle HFM application to extract from.
- Enter the name of its uploaded configuration file, or enter the configuration as text.
- In Extract format, select Flat file.
- In Delimiter, select a comma (,).
- In Line item option, select None.
- Select Display members, Extract calculated data, Extract derived data, and Extract dynamic data.
- 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:
- Under Available BizApps, click File Utils, and move UTF-8 Encode to the canvas.
- Drag a link from Start to UTF-8 Encode, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In Source File, select the Extract data file output from the Extract Data to File command.
- In Source Encoding, enter
ucs-2
. - Click Save.
- Under Available BizApps, click File Utils, and move Strip Lines to the canvas.
- Drag a link from UTF-8 Encode to Strip Lines, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In Source File, select the UTF-8 File output from the UTF-8 Encode command.
- In Lines to Strip, enter
1:8
. - 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:
- Under Available BizApps, click Tabular Transformation, and move Add Header to the canvas.
- Drag a link from Strip Files to Add Header, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In Input file, select the Strip Lines Output File output from the Strip Lines command.
- Select Preview Results.
- In Header Row, enter the headers for each column, separated by a column. For example,
account,icp,costcenter,product,division,geography,dataflows,amount
. - In Delimiter, select Comma.
- In Header Delimiter, enter a comma
,
. - Click Save.
- Under Available BizApps, click Tabular Transformation, and move Select POV (Advanced Query) to the canvas.
- Drag a link from Add Header to Select POV (Advanced Query), and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- 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
.
- 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
- In Input Delimiter and Output Delimiter, select Comma.
- 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:
- Under Available BizApps, click File Utils, and move Preview File Contents to the canvas.
- Drag a link from Select POV (Advanced Query) to Preview File Contents, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In File name, select the Result output of the Select POV (Advanced Query) command.
- In Preview lines, enter the number of lines to include in the preview, such as 10.
- In Offset, enter 0.
- Click Save.
Step 6. Add commands to add the file as a table dataset
To upload the file to Wdata as a table dataset:
- Under Available BizApps, click Workiva, and move Upload File (Create File) to the canvas.
- Drag a link from Preview File Contents to Upload File (Create File), and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In Table ID, select the chain variable for the table ID.
- In File, select the Result output of the Select POV (Advanced Query) command.
- In Name, enter the file name to use with the uploaded table dataset, with a file extension of
.csv
. - Click Save.
- Under Available BizApps, click Workiva, and move Import File into Table to the canvas.
- Drag a link from Upload File (Create File) to Import File into Table, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In Table ID, select the chain variable for the table ID.
- In File ID, select ID under the Result output of the Upload File (Create File) command.
- Click Save.
Step 7. Finalize the chain and table
- In the chain, click Publish and Execute .
- To test the chain, refresh the table, and verify the data set appears.
- Select whether to run the chain on-demand or automatically as scheduled.
- 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
orSUMIF
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.