Connect Snowflake with Cascade

Connect revenue metrics from Snowflake to a strategy in Cascade

If you wish to connect your metrics to Snowflake, please reach out to us and we can make this available in your workspace. Also, this connection is available only for Essentials and Enterprise+ tiers.

Whether you're a strategic leader responsible for your organization's growth, or an operational leader driving the metrics, bringing your business metrics closer to your strategy will help you drive faster results and improve the efficacy. Connect Snowflake with Cascade to bring visibility on your key metrics like unit sales, revenue per region, revenue per product line, expense ratio, number of customers, and so on. With this connection, you can automatically update your measures from these metrics in Cascade, and report accurate progress on your strategic objectives.

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.

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
• 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.
   1. CREATE SECURITY INTEGRATION Name_of_Integration TYPE = OAUTH ENABLED = TRUE OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'PUBLIC' OAUTH_REDIRECT_URI = 'go.cascade.app/oauth'
   2. Replace "Name_of_Integration" with your desired integration name. Note that the integration name is case-sensitive and must be uppercase.
3. 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.
   4. Note that the ACCOUNTADMIN, ORGADMIN, and SECURITYADMIN roles are included in this blocked list by default; however, if these roles should be removed for your account, ask your account administrator to send a request to Snowflake Support.
4. Execute the following command in Snowflake to retrieve the client ID and one of the client secrets:
   1. SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('Name_of_Integration');
   2. Note that the integration name is case-sensitive and must be in uppercase within single quotes.
   3. Make note of the client ID. You’ll get two client secrets, note down either one.
5. 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. CREATE USER "uniqueid" PASSWORD = "password" LOGIN_NAME = "username" DEFAULT_ROLE = "PUBLIC"
   2. Replace “PUBLIC” with a relevant role that is not on the blocked list.
6. 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. GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE my_role;
   2. Grant usage on the database to the created role. Replace "TEST" with the relevant database name.
      1. GRANT USAGE ON DATABASE TEST TO ROLE my_role;
   3. Grant usage on the schema to the role. Replace "PUBLIC" with the relevant schema name.
      1. GRANT USAGE ON SCHEMA PUBLIC TO ROLE my_role;
   4. Grant SELECT permissions on all views within the schema to the role. Replace "TEST.PUBLIC" with the relevant schema name.
      1. GRANT SELECT ON ALL VIEWS IN SCHEMA TEST.PUBLIC TO ROLE my_role;

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.

Enter the following details:

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.

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. Select the Snowflake connection from the list or create a new one, and choose the Database, Schema, and the View in which the metrics are stored.
2. Choose the column in which your metrics are available, and select the metrics that you wish to import.
3. Do not select the column in which your date resides, i.e., do not select the check-box against the date values while mapping metrics. Select only those boxes against metrics that you wish to import.
4. Select the date format. Without dates, we'll not be able to read or import metrics. Only based on the dates, the values are imported and displayed. 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.
5. Choose the format of data set-up and timeframe:
   1. Specify the unit for each metric. If you do not find the unit that you want from the list, then 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.
   2. Choose whether your data is set up cumulative or non cumulative in the View.
   3. Choose Sum of values if you wish to display the summed up value of the metrics, Avg of values if you wish to display the average of values of the metrics in the chosen time frame, or choose Last value if you wish to display just the latest value available for the metric.
   4. If "sum of values" or "average of values" is chosen, then 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.
   5. Select the time frame or frequency by which you want to display the metrics information. Note that, selecting a time frame that is smaller than what is in the Snowflake 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).
   6. 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.
6. Click Connect metrics to finish the import.

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.

See Connect your Metrics from Snowflake to Cascade for detailed information on this import function.

You can now connect these metrics to measures, and report accurate progress on your strategic objectives associated with these measures. You can either create a new measure to connect to the imported metrics or add the metrics to an existing measure in your workspace. See Connect your Metrics to Measures for detailed steps.

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

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.

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.