Bring your research and development (R&D) metrics closer to your strategy to help you drive better results with the R&D projects, and achieve new dimensions of your products and services with innovations.
Integrate SQL server database with Cascade to bring visibility on your key metrics like project progress, on-time completion rate, R&D costs, R&D budget, time to market, and so on. With this integration, you can automatically update your measures in Cascade, and report accurate progress on your strategic objectives.
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.
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' that is associated with your 'Objective' in the plan that you created in Cascade and want to link to your metric in SQL 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.
SQL Server Database:
- A SQL server database with authentication type, and database credentials. For an on-premises database, identify the data gateway that was shared or create a new one. Refer SQL server in Power Automate's help center.
- A database table that has the measure's progress value (that needs to be updated in Cascade).
Integrate SQL Server 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. You can then use the same connection to integrate other apps to Cascade via Power Automate.
- From the measure’s sidebar, under the Tracking section, select Microsoft Power Automate.
- Enter the initial and target values for the measure 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.
- Click Save.
When you select Power Automate from the 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 SQL Server Database
- 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.
- Search the SQL Server actions and choose the relevant action that you would like to perform.
- Enter the relevant authentication details to connect to your SQL Server database.
- Fill out the SQL query 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 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.
Storing Cascade IDs directly in the database:
You can create the mapping between the data and the 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.
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.
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 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 measure in Cascade
- Now, select Cascade Strategy New to update progress of the measure in Cascade.
- Select the Cascade workspace, plan, and measure. Depending on the method of mapping the data, you will either:
- Select ‘Enter custom value’ where you will reference the Measure ID that is included from your database or secondary mapping table.
- Select ‘Enter custom value’ where you will manually enter the ID of the Measure.
- Click on the drop-down menu and choose the correct Measure.
- 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.
Benefits
This integration provides you with all the elements for an effective “data storytelling”.
- Bring precise and up-to-date “data”: Track the progress precisely and on time with automatic sync between the values in the database tables and the measures in Cascade.
- Build engaging “visuals”: Visualize the progress with the various measure widgets and custom charts in the dashboards.
- Create compelling “narratives”: Build reports to make guided decisions based on the progress made in these measures and thereby the objectives in the plans.
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.
SQL Server connection to Excel
A SQL server 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.