Connect your Metrics from Snowflake 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 Snowflake views, Cascade automatically updates those for you.

Let's see how the import works before going into the details:

1. Connect your Snowflake with Cascade:

   1. Establish the connection between the Snowflake and Cascade through OAuth.

2. Import your metrics in Snowflake into Cascade:

   1. Choose the Database, Schema and View where your metrics are stored.

   2. Choose only those columns in the Snowflake View which has the metrics information. Do not select the column that has the dates stored.

   3. Select the column that has the dates. When creating the view, please make sure that the date format is mm/dd/yyyy.

   4. Select how you need the imported metrics to display as - cumulative or non cumulative and summed up values/average of values/last updated value, timeframe (weekly, monthly, quarterly, etc.,), and the trend (higher is better or lower is better). In case of selecting sum or average of values, you can also select how the data can be aggregated - All times to include all the data in the sheet or YTD(year to date) to select the start month of your fiscal year. With YTD, the aggregation will reset to the value of the actual data on the selected month and start summing or averaging as of that date up to the next 12 months.

1. Connect your imported metrics to measures in Cascade:

   1. Once imported into Cascade, you can build a "Performance of Single Metric" widget to look at how an imported metric has progressed over time.

   2. You can then connect them to a new measure or an already existing measure to track them with targets and due dates. Note that you can connect the same metrics to any number of measures to track for your specific requirements. This step is done within Cascade since the measure does not live in your sheets. See Connect your Imported Metrics to Measures for details.

Prerequisites:

Before you start with the import, it is important that these prerequisites are met:

Cascade:

• A valid Essentials or Enterprise+ account and workspace in Cascade.

Snowflake:

• A valid Snowflake account.

• A View with date and metric data.

• A security integration with OAUTH_REDIRECT_URI set to https://go.cascade.app/oauth

• User with a role that is not blocked on that security integration.

• User should have access to the Database, Schema and View.

• User’s role has usage in the Snowflake’s warehouse.

Step 1: Set up the Snowflake View with Security Integration and User Access

For this import, it is necessary that the prerequisites in Snowflake are met so that the proper user access and security integration are set up for the View that has the metrics data.

1. Login to Snowflake as an Admin.

2. Create a SQL worksheet and execute the following command. This is to redirect the OAUTH to the Cascade site.

CREATE SECURITY INTEGRATION Name_of_Integration TYPE = OAUTH ENABLED = TRUE OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'PUBLIC' OAUTH_REDIRECT_URI = 'https://go.cascade.app/oauth'

1. Execute the following command in Snowflake to view the details of the security integration:

   1. DESC SECURITY INTEGRATION Name_of_Integration;

   2. Make note of the OAUTH_AUTHORIZATION_ENDPOINT URL.

   3. Additionally, ensure that the BLOCKED_ROLES_LIST does not include the user who will log in to Snowflake to create the metric.

1. Execute the following command in Snowflake to retrieve the client ID and one of the client secrets:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('Name_of_Integration');

1. 1. Make note of the client ID. You’ll get two client secrets, note down either one.

2. If you already have a user that is suitable for the integration with proper role and access, then skip the following steps and go straight to Connect your Snowflake with Cascade step.

   Use the following command to create a user in Snowflake. Replace the placeholders with the appropriate values:

1. 1. Replace “PUBLIC” with a relevant role that is not on the blocked list.

2. Grant permissions to the above created role so it can have access to the Views used for metrics:

   1. Grant usage on the warehouse to the created role. Replace "COMPUTE_WH" with the relevant warehouse or keep it as-is.

1. 1. Grant usage on the database to the created role. Replace "TEST" with the relevant database name. 

1. 1. Grant usage on the schema to the role. Replace "PUBLIC" with the relevant schema name.

1. 1. Grant SELECT permissions on all views within the schema to the role. Replace "TEST.PUBLIC" with the relevant schema name.

Now that you’ve created a user with a proper role and granted required permissions to the Views that has the metrics data, you can now import the metrics from Cascade.

Step 2: Connect your Snowflake Views with Cascade

It is important to establish a connection between Snowflake and Cascade to start importing your metrics. This connection can then be used by all the other metrics in that workspace which have their progress in Snowflake.

You can establish the connection in either of these ways. Click Connect in Snowflake

• either from the Integrations > Metrics & measures tab,

• or, from the Metrics >  Library > + Create new metric > Connect to a source option,

• or, from the Metrics > Connect metrics tab.

1. Connection name: Give the connection a name.

2. Account URL: Get the URL for the snowflake account. It’ll look something similar to: https://abcd1234.ap-southeast-2.snowflakecomputing.com. Just abcd1234.ap-southeast-2 part of the URL is required to be entered (This is obtained from OAUTH_AUTHORIZATION_ENDPOINT URL in the step 3 of Step 1: Set up the Snowflake View with Security Integration and User Access.

3. Client ID and Client Secret: Enter these details that you retrieved from the security integration. (See step 4 in Step 1: Set up the Snowflake View with Security Integration and User Access)

4. Warehouse Name: Enter the Warehouse name that the created user role has usage of.

5. Select whether the connection should be public or private, and click Connect.

A pop-up window appears with the Snowflake login screen. Enter the credentials and click Allow in the next step to establish the connection.

What is a private and shared connection?

When a connection is set to "public", everyone in the workspace can use the same connection while connecting with the same app, i.e., while setting up tracking, public connections will be listed in the dropdown. For example, if a connection to Snowflake is set as public by the owner,

• everyone in that workspace can use the same connection whenever they're trying to connect with Snowflake to import metrics.

• if the user role has access to the Views, then anyone using this connection can access that view to update progress.

• anyone can manage these connections, i.e., edit its name, or remove the connection or delete them, or change it to private.

When a connection is set to "private", none of the above points is valid. Only the owner can use that connection and manage it. Others will not see this connection listed in the dropdown while setting up the tracking.

We recommend setting up connection as "private", unless you use views where everyone has access to those and updates progress in which case you can set up the connection as "shared".

Step 3: Connect your metrics from Snowflake to Cascade

Now that you've successfully established the connection between Snowflake and Cascade, you can start importing the metrics by providing the Snowflake details.

1. Choose the view from the Snowflake:

1. Select the Snowflake connection from the list or create a new one.

2. Choose the Database in which the view is located.

3. Choose the Schema in which the view is located.

4. Select the View with the metrics.

5. Click Continue.

2. Choose the metrics to import:

Choose the column in which your metrics are available, and select the metrics that you wish to import.

Click Next to continue.

3. Select the date column:

This is another important step where you need to select the date format specified in your spreadsheet. Please note that the date column in the View must be mm/dd/yyyy format, make sure you're entering the dates in the same format too.

Without dates, we'll not be able to read or import metrics. Only based on the dates, the values are imported and displayed. This will also enable you to understand the progress made over time.

Select the date column from the dropdown, and click Next.

4. Choose the format of data set-up and timeframe:

Select the way the data is set-up in your view.

• Metrics - If you wish to change the name of the metrics, click into the Metrics name text box, and enter the metrics name.

• Unit - Specify the unit for each metric. If all the metrics that you wish to import have the same unit, then you can apply them universally from the Select unit dropdown at the top-right. In case you do not find the unit you're looking for, you can input your own custom unit - just type it, click + Add "unit" as a new option, and it'll get added. For example, Machines, Sessions, Tickets, Responses, Numbers, and so on. This custom unit will be stored and available throughout this import flow, and if you wish to have the same unit for some other metric, you can add this from the list. 

  However, they won’t be available in the dropdown for other metrics (a new import workflow), and you'll need to manually “create” them again.

• My data is - Choose whether your data is set up cumulative or non cumulative in the View. If you select Cumulative, then the Show total as will automatically default to Last value, and you'll not be able to edit it.

• Show total as - the option that you choose here will decide the way the value is displayed, i.e., if you choose

  • Sum of values, then we'll add the values in that metrics column, and display the summed up value for the time frame that you choose.

    • Avg (Average) of values, then we'll display the average of values in that metrics column for the time frame that you choose. i.e., if you choose the timeframe as "Quarterly", then the average of the values in those months of that quarter will be displayed.

 

• • Last value, then we'll just display the latest value available in the View for that metric. Remember that, any value entered against a future date will not be considered, and only that value which is available latest (as on the current date or before the current date) is displayed.

• Time frame - depending on the frequency or time frame that is set-up for the metric in the View, the data will be displayed. Note that, selecting a time frame that is smaller than what is in the View will show the same value over and over (i.e., selecting "daily" when the data is set up "monthly", will display the same value every day until the month changes, and so on).

• Trend - depending on the trend that you choose, the color will change, i.e., if you choose "Higher is better" or "Lower is better", then when the metric value matches this criteria, the color will be green. If the metric value is opposite to the trend that's set, then the color will be red.

Select the check-boxes against the metrics, if you wish to assign the Display with or Time frame or Trend globally from the top-right. Click Clear All or the trash icon to undo the selections.

Click Connect metrics to finish the import. On successful import, you'll see a message prompt on that. 

The successfully imported metrics will now be displayed in the Metrics > Library tab. Every time, the value in the Snowflake View changes, the relevant metrics will automatically be updated here in Cascade. You can also click by hovering the mouse over the metric tile, or click Sync now in the Metric sidebar to sync the values or updates.

You can then analyze the performance of an imported metric from building a "Performance of Single Metric" widget or you can now connect these metrics to measures, and report accurate progress on your strategic objectives associated with these measures. See Connect your Imported Metrics to Measures for details.

You can search for your metrics or choose options from the dropdown to display in alphabetical order, or make the recent ones to appear first, and so on.

How to edit or delete a metric?

All the imported metrics will be available in the Metrics > Library tab. Click the metric tile to view its information from the Sidebar:

• Details - Displays a line graph of the metrics data against time. If YTD is selected then the aggregation and display will reset to the value of the raw data on the selected month and start summing or averaging as of that date up to the next 12 months.

• Measures - Here, you can either create a new measure from this metric, or you can view the measures that're connected with this metrics.

• Table view - Displays the exact values of the metrics over time, as available in your spreadsheets.

Click the three dots in the metric tile that you wish to edit or delete, and choose the relevant option.

• Click Edit, if you wish to change the name of the metric, unit, display as or time frame, and click Save.

  

• Click Delete - this'll permanently delete the metric from the library. If you wish to have it later, then you'll need to re-import it.

FAQs

Will this import be affected if we have enabled IP restrictions with our Snowflake account?

Yes, if your Snowflake has IP access restriction enabled, then you’ll have issues while connecting to Cascade. To overcome this, please whitelist the cascade IP addresses depending on where your Cascade workspace is located.

Here’s the list of workspace locations and their IP’s to whitelist:

• Sydney:

  • 13.54.171.151

  • 52.64.211.78

  • 3.105.21.179

• US - North Virginia:

  • 34.200.58.81

  • 50.19.40.190

  • 34.194.12.14

• EU - London:

  • 18.168.215.15

  • 13.42.155.249

  • 18.132.150.155

• Canada - Central:

  • 15.156.185.232

  • 3.96.75.136

  • 3.97.180.45

• EU - Frankfurt:

  • 52.57.37.35

  • 54.93.195.63

  • 18.156.206.157

I have metrics in different views, will that mean I need to do separate import every time for all these views?

Yes, you need to create a new metric for a separate view. If you wish to import numerous metrics, then create a new view that brings in all the columns that you require.

What happens when my Snowflake view is set up "monthly", but I chose "quarterly" timeframe during import?

If the View is set up "monthly", and if the Time frame you choose while importing is "quarterly", then the values are grouped and the data points are shown quarter-wise. For example, you've values from January to July in your View, the metric tile will display

• Summed up values from January till July, if "sum of values" is chosen. The Table view tab in the imported metrics' sidebar will show grouped values - Q1: Sum of Jan to March; Q2: Sum of Jan to June; Q3: Sum of Jan to July.

• Average of values from January till July, if "average of values" is chosen. The Table view tab in the imported metrics' sidebar will show grouped values - Q1: Average of Jan to March; Q2: Average of Jan to June; Q3: Average of Jan to July. 

• Value in July month if "Last value" is chosen. The Table view tab in the imported metrics' sidebar will show grouped values - Q1: Value of March; Q2: Value of June; Q3: Value of July. 

I do not find my unit in the drop-down during import, what needs to be done?

You can add your own custom units. Just type the text, click + Add "custom unit" as a new option, and it'll get added to the list for this metric flow.

What is the data polling frequency?

The data polling frequency in case of Snowflake is 1 hour, i.e., whenever there is an update made in your Snowflake view, the data is pulled into Cascade in an hour. However, you can click Sync now if you wish to automatically pull in the data.

What happens if I make a change in the value for a date in the past, i.e., historical data point?

Click Sync now on the metric tile and it’ll automatically pick up the change. The old value will be overwritten by the new one, and you’ll not be required to re import the metric.

Will I see expected progress graph and numbers if I connect to metrics?

Yes, when metrics are hooked up to measures, they'll provide the historical and current values to track the measures. The initial and target values that you set while connecting a metric to a measure will define its expected progress.

Who can do this import workflow?

Anyone with valid user credentials in Cascade and Snowflake. Also, the user must have the relevant privileges on the Snowflake view.

My data is not syncing.

This might be because of an expired token. Once a connection is established between Snowflake and Cascade, it'll be valid only for 90 days. Post which, if you do not refresh the connection, the data will not be pulled into Cascade. The person who created the connection must refresh it to re-establish the connection between Snowflake and Cascade to start syncing the data again.

1. Go to Integrations > Metrics & measures.

2. Click Manage in the Snowflake tile.

3. Click the three dots against the connection name and click Refresh connection.

Once the connection is re-established, the data will automatically be pulled in an hour. If you wish to do a force sync, click on the metric tile.

Why can’t I update the values of a metric from Cascade?

We consider the data coming from Snowflake as the source of truth to avoid having misaligned information in the metric. This is a one-way connection: from Snowflake to Cascade. Nothing you do in Cascade will change the values in Snowflake Views.

Why aren’t my metrics being updated automatically?

Metrics bring in whatever is there in the Views. If you do not update or add anything there, then no new values will be added in the metric.

What happens if the data is set up "weekly", but I choose "monthly" in timeframe on import?

This'll not display any error, however, it'll show the same value over and over every week until the month changes.

I chose the wrong unit for my metric while importing.

This'll just show the incorrect unit, but it won’t affect the actual number.

My data is not showing up.

Double check the date format is mm-dd-yyyy for your snowflake view. If problems persist please contact cascade support.