Connect Google BigQuery with Cascade

Integrate Google BigQuery 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.

Every time the value changes in the database tables, Cascade automatically updates those for you. This integration with Cascade is enabled through Power Automate. You can then build reports and dashboards from this up-to-date and accurate data to gain powerful insights and take guided decisions.

Remember that the steps in this article provide a general guideline, and the specific steps and details may vary based on the features and capabilities provided by Google BigQuery and Cascade at the time of your integration. Please reach out to us for assistance if you are looking to connect Google BigQuery with Cascade and create an automated workflow.

If you're looking for more possibilities from an integration with Google BigQuery, our "Enterprise+" subscription now includes custom and advanced integration options. This allows for a tailored approach to meet specific needs, enhancing the functionality and efficiency of your strategic planning within Cascade. If you are interested in a more bespoke integration with Google BigQuery, please get in touch and we can explore your options further.

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' that is associated with your 'Objective' in the plan that you created in Cascade and want to link to your metric in database table.
• The tracking type of the measure is set as Power Automate, with the initial and target values defined.

Power Automate:

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

Google BigQuery Database:

• A Google BigQuery database with authentication type, and database credentials. For an on-premises database, identify the data gateway that was shared or create a new one. See detailed information on Google BigQuery in Power Automate's help center.
• A database table that has the measure's or action's progress value (that needs to be updated in Cascade).

Integrate Google BigQuery 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.
   • 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 Google BigQuery Database

1. Set up the trigger:
   • 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.
   • 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 Google BigQuery actions and choose the relevant action that you would like to perform.
3. Enter the relevant authentication details to connect to your Google BigQuery database.
4. Fill out the Google BigQuery 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:

This is the simplest and fastest way of integrating, where you can create a direct mapping of the Cascade ID within Power Automate itself.

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.

Remember that these steps provide a general guideline, and the specific steps and details may vary based on the features and capabilities provided by Google BigQuery and Cascade at the time of your integration. Please reach out to us for assistance if you are looking to connect Google BigQuery with Cascade and create an automated workflow.

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.

Google BigQuery connection to Excel

A Google BigQuery 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.

Note:

• You will need the appropriate driver installed (Microsoft.ACE.OLEDB).
• The database name is considered the name of the Excel worksheet followed by a dollar sign ($), for example, [Sheet1$].
• The database columns are the headers of the Excel data table.