Connect FireForm and Denodo

• Overview
• Set up the data source
• Set up the base view
• Set up the derived view
• Set up a refresh scheduler for base views

---

Overview

By connecting Denodo to FireForm, you can present form entry data in applications outside of FireForm. For more about Denodo, see Best practices for using Denodo at IU.

Make a FireForm API user request

To request a FireForm API user status, you will need to submit a FireForm API Access Request. Indicate which API you are requesting access to, state that you need configuration assistance, and provide the Denodo IP addresses for DEV, STG, and PRD.

You will be provided with a Client Identifier and Client Secret code to be used in the steps below.

Set up the data source

1. Navigate to File > New > Data Source > JSON. Complete the following fields:
   • Name: Enter a descriptive name of your choosing.
   • Data route: Set to HTTP Client.
2. Select Configure.
3. In the Configuration tab, enter the following:
   • HTTP Method: GET
   • URL: https://[tenant]-fireform.eas.iu.edu/api/v1/forms/entries/[formcode]
   • Check certificates: Yes
   • autodetect encoding: Yes
4. In the Authentication tab, enter the following:
   • Authentication: OAuth 2.0
   • Authentication grant: Client credentials grant
   • Client Identifier: The Client Identifier code provided by the FireForm team for the API user
   • Client Secret: The Client Secret code provided by the FireForm team for the API user
   • Authentication Method: Send client credentials using the HTTP Basic Authentication scheme
   • Request signing method: "Authorization" request header
   • Token endpoint URL: https://[tenant]-fireform.eas.iu.edu/oauth/token
   • Access token expires in: 3600 seconds
5. Select Launch the OAuth 2.0 credentials wizard.
   1. Ensure the token endpoint URL is https://[tenant]-fireform.eas.iu.edu/oauth/token.
   2. Select Obtain the OAuth 2.0 credentials.
   3. Select OK.
6. Test the connection.
7. Select OK, and then select Save.

Once you have set up the first data source, you can reuse it by copying the data source and changing the form code.

Set up the base view

Set up the base view. Note that if there are no existing form entries, you will receive an error.

1. Open the data source the base view will use.
2. Select Create Base View, and then select OK.
3. Set the view name to [descriptive name of your choosing] bv.
4. Save the new base view.
5. Switch to Options, and then choose Full Cache mode.
6. Set "Time to Live" as Never Expire.
7. Save the base view.

Set up the derived view

Create two derived views. You can use them to access all data for each form entry after joining them in the "shortidnumber" field.

Form main derived view

1. Right-click a base view, and then select New > Flatten.
2. Right-click jsonarray, and then select Flatten array 'jsonarray'.
3. Switch to Output tab.
4. Set the view name to [descriptive name of your choosing] dv.
5. Save the new derived view.

Form sections derived view

1. Right-click a base view, and then select New > Flatten.
2. Right-click elements (you may have to expand sections to find it), and then select Flatten array 'elements'.
3. Switch to the Group By tab.
4. Check Use group by.
5. Select shortidnumber and select Add>>.
6. Switch to the Output tab.
7. Pivot form responses:
   1. Select New field.
   2. In "Field Name", enter the name of a form element.
   3. Enter the following (replace [ELEMENTID] with the number ID for the desired element on the form):

      max(case WHEN(v.id=[ELEMENTID])THEN v.response ELSE NULL END)

      This places the associated response per "element_id" into a new column.

   4. Repeat the steps as needed for each form element.
8. Set the view name to [descriptive name of your choosing] dv.
9. Save the new derived view.

Note that all or a select few form elements can be pivoted. Multiple section derived views can be created.

Set up a refresh scheduler for base views

Each base view will need a schedule job to keep the data refreshed. For instructions, see Add a VDB Cache Refresh job to the Denodo 8 Scheduler.

You can use Tableau to display the FireForm data that is now connected to Denodo; see Denodo/Tableau ODBC Setup.