Extract data from Oracle Hyperion Financial Management to Workiva via a database

With this chain, you can extract data from Oracle® Hyperion® Financial Management (HFM®) to Workiva through Microsoft® SQL Server®. When you run this chain, it:

• Automatically extracts data from Oracle HFM through Extended Analytics®.
• Loads the data into a set of tables with a star schema in SQL Server.
• Exports the data from SQL Server to a Wdata table, which you can then use with a query to create the desired extracts.

Note: To extract data from Oracle HFM without the need of a relational database, build a chain with the Oracle HFM Extract Data to File command instead.

To best meet your needs, you can choose the star schema format to extract from Oracle HFM:

• Standard, for data in a standard Essbase® format
• Metadata only, for only metadata in a standard Essbase format
• Selected metadata only, for only metadata with the selected dimension members in a standard Essbase format
• SQL Aggregation, for aggregate data as a SQL and Essbase schema
• Data warehouse, for data as a warehouse-normalized hierarchy schema
• Essbase, for data in a SQL and Essbase schema

Ingredients

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

• Workiva
• Microsoft SQL Server 
• Oracle HFM:

To enable the extraction, in Oracle HFM, configure the data source name (DSN) to provide the connection between the database server and application server.

Note: The DSN provides information about the database server to connect to, including its name and the credentials of a user with full rights access.

You can specify the configuration of the Point-of-View (POV) members as a file or as text. To use a file:

1. In Chain Builder , from Connections sync_alt, select the Oracle HFM connector, and click Edit.
2. Under Resources, upload the configuration file.
3. Click Save. 

Step 1. Create the chain

1. From the Chains  device_hub tab, 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 Database command

1. Under Available BizApps, click Oracle HFM, move Extract Data to Database 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. To provide the HFM specification for the extract, enter the name of its uploaded configuration file, or enter the configuration as text.
5. In Extract Type, select the star schema format to extract.
6. In Line item option, select the level of detail to extract for each item—None, Total summary for cell, or Full line item.
7. To include the members of each dimension in the output, select Display members.

   Note: If a large number of members, we recommend you clear Display members.

8. Enter the table prefix of the data to extract.
9. In DSN, enter the data source name configured in Oracle HFM to connect to the database server.
10. In Database type, select SQL Server.
11. Enter the name, host, and port of the database to connect to, and its integration user's authentication credentials.
12. Select whether to create or update the star schema with the extraction, and the type of data to include.
13. Click Save, Publish  publish, and Execute  flight_takeoff .

 Step 3. Configure the view in Microsoft SQL Server and create its table

1. In Microsoft SQL Server Management Studio, use the new set of tables created by the chain to create a view that joins the data for reporting. For example:

   SELECT S.Label AS Scenario, Y.Label AS Year, P.Label AS Period, 
         V.Label AS [View],E.Label AS Entity, PR.Label AS Parent, 
         V.Label AS Value, A.Label AS Account, I.Label AS ICP, 
         C1.Label AS CUSTOM1, C2.Label AS CUSTOM2, 
         C3.Label AS CUSTOM3, C4.Label AS CUSTOM4, F.dData AS DATA
   FROM dbo.EXPORT_FACT AS F INNER JOIN
   dbo.EXPORT_SCENARIO AS S ON F.ScenarioID = S.ID INNER JOIN
   dbo.EXPORT_YEAR AS Y ON F.YearID = Y.ID INNER JOIN
   dbo.EXPORT_PERIOD AS P ON F.PeriodID = P.ID INNER JOIN
   dbo.EXPORT_VIEW AS V ON F.ViewID = V.ID INNER JOIN
   dbo.EXPORT_ENTITY AS E ON F.EntityID = E.ID INNER JOIN
   dbo.EXPORT_PARENT AS PR ON F.ParentID = PR.ID INNER JOIN
   dbo.EXPORT_VALUE AS VL ON F.ValueID = VL.ID INNER JOIN
   dbo.EXPORT_ACCOUNT AS A ON F.AccountID = A.ID INNER JOIN
   dbo.EXPORT_ICP AS I ON F.ICPID = I.ID INNER JOIN
   dbo.EXPORT_CUSTOM1 AS C1 ON F.Custom1ID = C1.ID INNER JOIN
   dbo.EXPORT_CUSTOM2 AS C2 ON F.Custom2ID = C2.ID INNER JOIN
   dbo.EXPORT_CUSTOM3 AS C3 ON F.Custom3ID = C3.ID INNER JOIN
   dbo.EXPORT_CUSTOM4 AS C4 ON F.Custom4ID = C4.ID
   

2. Export the view's output as a comma-separated values (CSV) file.
3. Create and save a Wdata table based on an upload of the CSV output from SQL Server Management Studio.
4. From the table's URL, copy or record its ID—the last string of text following tables/.
5. In the chain, click Chain Settings, paste or enter the ID as the value for the table ID variable, and click Save.

Step 4. Add an Execute SQL command

To run the SQL Server view on the data, add an Execute SQL command to the chain:

1. Under Available BizApps, click Microsoft SQL Server, and move Execute SQL to the canvas.
2. Drag a link from Start to Execute SQL, and click Edit  create.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. Under SQL Text/File, enter a SELECT * on the view created in SQL Server Management Studio.
5. Select Preview Results, and click Save.

Step 5. Add commands to create and import a file into a table

To pull the data into Workiva, add Create File and Import File Into Table commands to the chain:

1. Under Available BizApps, click Workiva, and move Create File to the canvas.

   Note: Verify the connection uses the same GroundRunner as the Oracle HFM and SQL Server connections.

2. Drag a link from Execute SQL 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 Table ID chain variable.
5. In File, select the Result Set (CSV) variable from the Execute SQL command, and click Save.
6. Under Available BizApps, click Workiva, and move Import File Into Table to the canvas.
7. Drag a link from Create File to Import File Into Table, and click Edit create.
   
8. Under Basic Info, enter a unique name and description to help identify the command.
9. Under Command Properties, select the same Workiva connector as the Create File command.
10. Under Table ID, select TableID from the Result output of the Create File command.
11. Under File ID, select Id from the Result output of the Create File command.
12. Ensure the columns between the table and file map correctly, and click Save.

Step 6. 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 permissons for collaborators.