Integrate Oracle Database with Cascade

If you’re storing your KPI data like revenue metrics, sales numbers, customer experience metrics, employee data, inventory information, and so in a database somewhere, you can now easily connect them to Cascade. Once set up, this is a completely automated workflow to sync your progress in Cascade to the live information in the database.

Using Power Automate, we can easily create an integration to automatically update the current progress of your Cascade Actions or Measures. You can then build reports and dashboards from this live and accurate information to gain powerful insights and make data-driven decisions.

Prerequisites

Before you establish the connection, it is important that these prerequisites are met:

Cascade:

• An Essentials or Enterprise+ tier account to access Power Automate connections.

• A Measure or Action created in Cascade that you want to update with the information from your database.

Power Automate:

• Microsoft 365 or Dynamics 365 license with Power Automate capabilities or a Power Automate license.

Oracle Database:

• An Oracle database with valid authentication credentials.

• Install the on-premises data gateway, and the Oracle Client on the same machine as the gateway. See detailed documentation on Oracle in the Microsoft website.

• A database table or view that has the Measure's or Action’s progress value (that needs to be updated in Cascade)

Integrate Oracle Database with Cascade:

Step 1: Connect Power Automate with Cascade

It is important to establish a connection between Power Automate and Cascade to start integrating the measures or actions. You can then use the same connection to integrate other apps to Cascade via Power Automate.

1. From the action or measure’s sidebar, under the Tracking section, select Microsoft Power Automate.

   1. In case of a measure, enter the initial and target values to track progress. If you wish to customize the tracking option of the measure, choose the relevant "maintain" option from the Target type dropdown, and define target and critical limits.

2. Click Save.

When you select Power Automate from the action or measure's tracking section, a modal pop-up redirects you to Power Automate. You'll now see the Power Automate's Flow interface.

Step 2: Pull information from the Oracle Database

1. Set up the trigger:

   1. Click + Create or My flows from the left navigation pane to either create a flow from scratch or to use a pre-made flow. It’s recommended to use the Scheduled cloud flow option for this integration.

   2. The trigger is going to be the ‘Recurrence’ step within Power Automate. You need to decide the frequency that you would like this integration to run on.

2. Search the Oracle database actions and choose the relevant action that you would like to perform.

3. Enter the relevant authentication details to connect to your Oracle database.

4. Fill out the Oracle database action to run against your database to source the relevant data.

Step 3: Map database values to Cascade

Now, you need to correctly map the data from your database to the relevant measure or action within Cascade. Before learning the steps on mapping, let’s first see how to get a action or measure ID from Cascade. You will need this ID to create a mapping between the database values and Cascade.

Get measure ID from Cascade:

Open the relevant Measure’s sidebar, and from the browser URL, copy the alphanumeric ID.

For example, if this is the URL of a measure's sidebar: https://go.cascade.app/plan/98b3b450-3dfe-4371-abba-7361da19b6dg/planner?sidebar flow=view&sidebar_entity_type=measure&sidebar_measure_id=4c33ae53-9764-4ca2-9534-1a0fdae00ffd, the Measure ID, in this case, is 4c33ae53-9764-4ca2-9534-1a0fdae00ffd.

Let's now see the different ways of mapping the values.

1. Storing Cascade IDs directly in the database:

You can create the mapping between the data and the action or measure that will be updated from within the database itself, so that the Cascade ID gets included in the database query. By doing this, you can dynamically reference the Cascade ID within the Power Automate flow to make this integration scalable and robust.

• You can create a database view that you query within Power Automate, where you would enter the Cascade ID directly in the database column that is associated with a certain row of data.

• You can create a database table to hold the Cascade IDs with a primary key and foreign key relationship to the information that is being queried. By joining these two tables in the SQL query that you’re running, you can pull over the Cascade ID directly into the Power Automate flow to reference it.

2. Secondary mapping using Excel or Google Sheets:

Within a spreadsheet, you can create a table of information where each row has a unique database ID that is associated with the specific row of data. You would input the database ID in one column of the spreadsheet table and the Cascade ID in another column on the same row. Within the Power Automate flow, you would then reference that sheet to match the database ID of the data while returning the Cascade ID.

3. Referencing the Cascade ID directly in the Power Automate flow:

When the Power Automate flow returns the database data, you would either manually input the Cascade ID within the Power Automate flow or manually select the action or measure from the drop-down menu.

You will need to add conditional statements within the Power Automate flow to correctly map the information into Cascade.

Step 4: Update action or measure in Cascade

1. Now, select Cascade Strategy New to update progress of the action or measure in Cascade.

2. Select the Cascade workspace, plan, and action or measure. Depending on the method of mapping the data, you will either:

   1. Select ‘Enter custom value’ where you will reference the Action or Measure ID that is included from your database or secondary mapping table.

   2. Select ‘Enter custom value’ where you will manually enter the ID of the Action or Measure.

   3. Click on the drop-down menu and choose the correct Action or Measure.

3. Click Save.

After successfully querying your database and mapping the database values into Cascade, your Cascade environment will stay in sync with everything that is happening within your database.

FAQs

My Power Automate flow failed. How should I proceed?

Click My Flows and choose the flow that you're working on. Now, click on the run that failed, and from the Error Details displayed on the right sidebar, analyze and troubleshoot it. You can also reach out to the Cascade team for consultation.

There is a connection error with Power Automate

From the left navigation pane, click Data > Connections, and delete the connection that you used. Now, when you start with the flow, it'll establish a new connection. At any point in time, you can click the three dots against the action title and click +Add new connection, and you can select other connections you have already established.

Error communicating with the database

Verify your authentication details and that the firewall or any other network security system isn't blocking the connection between Power Automate and the database. Also, verify that your on-prem data gateway and the Oracle client are operational.

Oracle database connection to Excel

An Oracle database connection can also be established with Microsoft Excel. Then, an integration with Microsoft Excel and Cascade can take place through our native Microsoft Excel integration or through Power Automate/Zapier