Send data to BI Connect¶
Business Intelligence Connect is an Amperity-managed cloud data warehouse that provides an easy-to-access location from which you can use any BI tool to access all of your Amperity data.
Important
Business Intelligence Connect is licensed for presentation of Amperity-sourced data within BI tools and contexts. It is not meant to be a general data warehouse solution.
Loading data into the Business Intelligence Connect data warehouse from sources other than Amperity is considered out-of-license. Customers that wish to load data to the Business Intelligence Connect data warehouse from other data sources should contact their Amperity account team to discuss options.
Request to enable warehouse¶
Business Intelligence Connect is available upon request. After the data warehouse for Business Intelligence Connect is configured by Amperity for your tenant, you can send data from Amperity to the data warehouse, and then connect any of your BI tools to that data.
Open a support ticket to make the request to enable Business Intelligence Connect. In the ticket list the specific user accounts in your tenant that should be granted read-only access to the Business Intelligence Connect data warehouse.
Provision the warehouse¶
Your Amperity support team will provision the following:
A dedicated data warehouse in Snowflake that is configured to run in the same cloud platform as your tenant.
Cloud platform
Regions
Amazon AWS
us-west-2
us-east-1
ca-central-1
Microsoft Azure
east-us-2.azure
west-us-2.azure
north-europe.azure
west-europe.azure
A default database and schema within the data warehouse. These are empty by default.
A data staging environment within the data warehouse location.
A role with read-only access to the schema.
Individual user accounts in your tenant are granted read-only access to the data warehouse using the read-only role.
A role with read-write access to the database and schema.
A service account that is granted access to the database and schema using the read-write role.
Connection details¶
Allow the Amperity support team a few days to complete the provisioning process. When finished, the support team will share the following connection details with you via SnapPass:
The URL for the data warehouse.
The Account Name, Region ID, Database name, Schema name, and Staging path.
A JDBC connection string.
An ODBC connection string.
A service account username and password, which has been granted read-only access to the data warehouse. (A dedicated read-write account is used to exchange data between Amperity and the data warehouse.)
Use the service account to connect BI tools, such as Tableau, to the data warehouse.
Important
Store the service account username and password in a safe place.
Service hours¶
Business Intelligence Connect is available to licensed Amperity tenants. This license comes with 300 free annual hours of compute service time, enough to support light-to-moderate use of BI tools.
Customers can track their utilization against their free tier hours by requesting their usage hours from their Amperity account team.
Note
If additional compute service time hours are needed, customers can pre-purchase additional capacity or pay on demand when usage goes outside of free tier limits.
Discuss plans for heavy use of BI tools with your Amperity account team.
Compute service time is calculated in minute increments and is based on the running time of the customer’s data warehouse. The warehouse is started when a query is first run. As queries are submitted, the data warehouse continues to run. Five minutes after all queries have completed, the data warehouse will stop running and service hours will stop accumulating.
Snowflake secure data sharing¶
Snowflake secure data sharing is not enabled by default. If your organization wants to use secure data sharing, your instance of Snowflake must be in the same region as the Amperity instance.
Amperity supports the following regions for Amazon AWS:
US West |
us-west-2 |
US East |
us-east-1 |
Canada |
ca-central-1 |
Amperity supports the following regions for Microsoft Azure:
US West |
west-us-2.azure |
US East |
east-us-2.azure |
North Europe |
north-europe.azure |
Note
If your region is not listed you may make a request through your Amperity representative for Amperity engineering to add support for that region.
User access to warehouse¶
An Amperity user account is required to access the Business Intelligence Connect data warehouse. Two categories of user accounts are supported:
Single sign-on (SSO) for individual user accounts
Single sign-on (SSO)¶
Business Intelligence Connect supports using SSO to provide access to individual user accounts. Use the same credentials to access the data warehouse as those used to access Amperity.
Note
The Business Intelligence Connect data warehouse can only be accessed by users with Amperity credentials.
Important
It is recommended to use SSO to access the data warehouse. SSO accounts are automatically disabled when a user loses access to Amperity.
Service accounts¶
A service account is a static username and password that may be provisioned for use with Business Intelligence Connect. A service account enables non-SSO workflows during the login process, should they be necessary.
Warning
A service account requires explicit rotation and must be revoked during lifecycle events.
A set of service accounts are defined for any tenant that chooses to enable Business Intelligence Connect. Your Amperity representative will send to you your service account username and password via SnapPass, along with the URL for your instance of Business Intelligence Connect. Credentials for a read-only service account and a read-write service account may be provided. You may use these service accounts to log into Business Intelligence Connect.
To log into BI Connect using your service account
Enter the URL for your instance of Business Intelligence Connect into your web browser.
On the login screen, enter the username and password for your service account. For example:
Click Sign in.
Send data to warehouse¶
The Business Intelligence Connect data warehouse is empty by default. You must configure Amperity to send data to the data warehouse by using one of the following workflows:
Export a database or table directly to the Business Intelligence Connect data warehouse.
Send the results of a query to the Business Intelligence Connect data warehouse.
The steps are slightly different, depending on if your tenant runs on Amazon AWS or Microsoft Azure. Both approaches use a data template and orchestration.
Export databases and tables¶
Database tables (or even entire databases) can be sent from Amperity to Snowflake as a database export. A database export is configured from the Customer 360 page, and then added to an orchestration from the Destinations page. A database export can be an ad hoc process or be scheduled as a regular drop to an external filedrop location or data warehouse.
To add a database export for Snowflake
From the Customer 360 page click Configure Exports. This opens the Database Exports page.
Click Create New Export. This opens the Add Export dialog box.
Add the name of the data export, and then click Add. This will add a draft data export to the Database Exports page.
Open the menu for the draft data export, and then select Edit.
From the Database drop-down, select a database.
From the Entities list, select one (or more) database tables to add to the data export.
Click Activate.
Send query results¶
You can send the results of any query from the Queries page to the Business Intelligence Connect data warehouse.
Send to Amazon AWS¶
The following steps describe how to configure a destination for Business Intelligence Connect when it is running in Amazon AWS.
To add a destination for BI Connect
From the Destinations page, click Add Destination. This opens the Add Destination dialog box.
Enter the name of the destination and a description. For example: “BI Connect” and “This sends data to the BI Connect data warehouse”.
From the Plugin drop-down, select Snowflake.
From the Credential Type drop-down, select a credential type: “aws-snowflake”.
From the Credential drop-down, select Create a new credential. This opens the Create New Credential dialog box.
Enter the username and password. This is the service account username and password.
Add the IAM Access Key and IAM Secret Key for the IAM role with permission to access the Amazon S3 bucket.
Under Snowflake Settings, enter the Snowflake account name, Amazon AWS region ID, Snowflake stage name, and Snowflake data warehouse name.
The Account Name is contained within the URL for the Snowflake instance and is a character code located before
snowflakecomputing.com. For example: “ab12345”.The Region ID is the region ID in Amazon AWS. For example: “us-west-2”.
The Stage is the Snowflake stage name.
The Warehouse is the name of the Snowflake data warehouse.
Select Allow customers to use this data template.
Select Allow orchestrations from users with limited PII access.
Click Save.
Send to Microsoft Azure¶
The following steps describe how to configure a destination for Business Intelligence Connect when it is running in Microsoft Azure.
To add a destination for BI Connect
From the Destinations page, click Add Destination. This opens the Add Destination dialog box.
Enter the name of the destination and a description. For example: “Snowflake” and “This sends data to Snowflake”.
From the Plugin drop-down, select Snowflake.
From the Credential Type drop-down, select a credential type: “azure-snowflake”.
From the Credential drop-down, select Create a new credential. This opens the Create New Credential dialog box.
Enter the username and password. This is the service account username and password.
Add the Azure Blob Storage account name and shared access signature for the AZURE_ROLE with permission to access Azure Bob Storage.
Under Snowflake Settings, enter the Snowflake account name, the region identifier, Snowflake stage name, and Snowflake data warehouse name.
The Account Name is contained within the URL for the Snowflake instance and is a character code located before
snowflakecomputing.com. For example: “ab12345”.The Region ID is the region ID in Azure. For example: “west-us-2”.
The Stage is the Snowflake stage name.
The Warehouse is the name of the Snowflake data warehouse.
Select Allow customers to use this data template.
Select Allow orchestrations from users with limited PII access.
Click Save.
Add data template¶
A data template defines how columns in Amperity data structures are sent to downstream workflows. A data template is part of the configuration for sending query and segment results from Amperity to an external location.
To add a data template
|
From the Destinations tab, open the menu for a destination that is configured for BI Connect, and then select Add data template. This opens the Add Data Template dialog box. 
Enter the name of the data template and a description. For example: “BI Connect” and “Send databases, tables, and query results to BI Connect.”. |
|
Verify business user access to queries and orchestrations and access to segments and campaigns. A business user may also have restricted access to PII, which prevents them from viewing and sending customer profile data. 
If business user access was not configured as part of the destination, you may configure access from the data template. Important To allow business users to use this destination with campaigns, you must enable the Available to campaigns option. This allows users to send campaign results from Amperity to BI Connect. If you enable this option, the data extension settings require using campaign name and group name template variables to associate the name of the data extension to your campaign. |
|
Verify all configuration settings. |
|
Under Template Settings, specify the Snowflake Location. This is a period-delimited list of the Snowflake database name, the Snowflake schema name, and the Snowflake table name: SNOWFLAKE_DATABASE_NAME.SNOWFLAKE_SCHEMA.TABLE_NAME
Note Be sure that the table name is appended. For example: AMPERITY.CUSTOMER_360.TABLE_NAME
Select Drop table to overwrite the table and apply an updated schema when the upstream segment or table changes.. |
|
Review all settings, and then click Save. 
After you have saved the data template, and depending on how you configured it, business users can send query results to BI Connect and/or send campaign results to BI Connect. |
Add orchestration¶
An orchestration defines the relationship between query results and a destination, including the location to which those query results will be sent and the frequency at which the orchestration will be run.
To add an orchestration
From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.
From the Object Type drop-down, select Database Export or Query.
From the Object drop-down, select the object to be exported to Snowflake.
Note
This name will also be the name of the orchestration.
From the Destination drop-down, select the name of the destination that is configured to send data to Snowflake.
From the Data Template drop-down, select the data template that maps a database export or query to a table in Snowflake.
Verify the Snowflake Location has the correct namespace, including the appended Amperity table name. This should be a period-delimited list of the Snowflake database name, the Snowflake schema name, and the Snowflake table name:
SNOWFLAKE_DATABASE_NAME.SNOWFLAKE_SCHEMA.TABLE_NAME
Note
Be sure that the table name is appended.
For example:
AMPERITY.CUSTOMER_360.TABLE_NAME
Verify the option to use for managing the table in Snowflake.
Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)
Click Save.
Run orchestration¶
Run the orchestration manually to validate that it works.
To run the orchestration
From the Destinations tab, under Orchestrations, open the menu for the BI Connect orchestration, and then select Run.
The Status column for the orchestration will update to say “Waiting to start…”, after which the notifications pane will update to include a notification that shows the current status.
When the orchestration has run successfully, the status is updated to “Completed”.
Connect BI tools¶
Business Intelligence Connect supports all of the leading BI tools, including Tableau, Looker, Domo, Qlik, Microsoft PowerBI, Amazon QuickSight, Oracle Business Analytics, and SAP Business Objects, along with any BI tool that can connect to a data warehouse using the Open Database Connectivity (ODBC) or Java Database Connectivity (JDBC) standards.
Important
Please refer to the documentation for individual BI tools for troubleshooting connectivity issues with Business Intelligence Connect and to Snowflake documentation for troubleshooting connectivity issues related to the Snowflake ODBC or JDBC drivers.
Amazon QuickSight¶
Amazon QuickSight is a cloud-based, self-service BI tool for creating and publishing interactive dashboards for retail, ecommerce, manufacturing, and more.
Configure Amazon QuickSight to connect to the Business Intelligence Connect data warehouse as a VPC data source.
Domo¶
Domo is a cloud-based, self-service BI tool that helps you visualize data from a single dashboard.
Configure Domo to connect to the Business Intelligence Connect data warehouse using the Snowflake connector or the Snowflake Oauth connector.
JDBC driver¶
Java Database Connectivity (JDBC) uses the Java programming language to define how a client may access a database.
Download the Snowflake JDBC driver to connect BI tools such as Databricks, SAP Business Objects, SQL Workbench , or Sisense to the Business Intelligence Connect data warehouse.
Important
BI tools that use the JDBC driver must set the Authenticator setting to externalbrowser as a requirement for browser-based SSO . The location in which this setting is configured varies, depending on the BI tool. For example, SQL Workbench appends this setting to the URL for the Business Intelligence Connect data warehouse:
URL/?authenticator=externalbrowser
Databricks¶
Databricks provides a unified platform for data and AI that supports large-scale processing for batch and streaming workloads, standardized machine learning lifecycles, and accelerated data science workflows for large datasets.
Important
Use your SSO credentials to connect Databricks to Business Intelligence Connect.
To connect Databricks to BI Connect
Download and install the Snowflake JDBC driver.
Configure Databricks to use the JDBC driver to connect to Business Intelligence Connect.
Enter the following information:
Setting
Description
Driver
The Snowflake JDBC driver.
URL
The URL for the Business Intelligence Connect data warehouse.
This must start with
jdbc:snowflake://, be followed by the URL for the data warehouse, and then appended with?authenticator=externalbrowser.For example:
jdbc:snowflake://ab12345.snowflakecomputing.com/?authenticator=externalbrowser
Username
The string token for Business Intelligence Connect.
Password
The personal access token for Business Intelligence Connect.
After the JDBC driver is configured to use SSO you may begin authoring and running queries from Databricks against data in the Business Intelligence Connect data warehouse.
SQL Workbench¶
SQL Workbench is a cross-platform SQL query tool that runs on any operating system.
To connect SQL Workbench to BI Connect
Download and install the Snowflake JDBC driver.
In SQL Workbench, choose File, Connect Window, and then Create a New Connection Profile. This opens the Connection Profile pane.
Enter the following information:
Setting
Description
Driver
The Snowflake JDBC driver.
URL
The URL for the Business Intelligence Connect data warehouse.
This must start with
jdbc:snowflake://, be followed by the URL for the data warehouse, and then appended with?authenticator=externalbrowser.For example:
jdbc:snowflake://ab12345.snowflakecomputing.com/?authenticator=externalbrowser
Username
Your Amperity username. For example:
justin.currie@acme.com.Password
The password for your Amperity username. Leave this blank.
Click OK. Your browser will open.
If you are already logged into Amperity, you will see a message in your browser that confirms you are authenticated to the Business Intelligence Connect data warehouse. Navigate back to SQL Workbench.
If you are not logged into Amperity, you will be prompted for your username and password. Complete the login process, and then nagivate back to SQL Workbench.
You may begin authoring and running queries from SQL Workbench against data in the Business Intelligence Connect data warehouse.
Looker¶
Looker is an enterprise platform for business intelligence, data applications, and embedded analytics.
Configure Looker to connect directly to the Business Intelligence Connect data warehouse.
Microsoft PowerBI¶
Microsoft Power BI is a collection of software services, applications, and connectors that work together to turn unrelated sources of data into coherent, visually immersive, and interactive insights.
Download the Snowflake ODBC driver, and then connect Microsoft Power BI Desktop to the Business Intelligence Connect data warehouse.
ODBC driver¶
Open Database Connectivity (ODBC) uses a driver-based system to define how any client may access any database.
Download the Snowflake ODBC driver to connect BI tools such as Adobe Campaign , Microsoft PowerBI, SAP Business Objects, Tableau, or TIBCO Spotfire to the Business Intelligence Connect data warehouse.
Important
BI tools that use the ODBC driver must set the Authenticator setting to externalbrowser as a requirement for browser-based SSO . The location in which this setting is configured varies, depending on the BI tool. For example, Tableau configures this setting from the Advanced tab as part of the workflow within Tableau that configures the connection to the Business Intelligence Connect data warehouse.
Oracle Business Analytics¶
Oracle Business Analytics is a desktop BI tool for data exploration and visualization.
Use Oracle Analytics Desktop to connect directly to the Business Intelligence Connect data warehouse.
Qlik Sense¶
Qlik Sense is a cloud-based, self-service BI tool that allows you to explore data in any direction, extract data by making selections, and immediately viewing filtered data and associated items.
Configure Qlik to connect directly to the Business Intelligence Connect data warehouse.
SAP Business Objects¶
SAP Business Objects is a centralized BI tool for data reporting, visualization, and sharing and is the on-premise BI layer for SAP’s Business Technology Platform.
Download the Snowflake JDBC driver, and then configure SAP Business Objects to connect to the Business Intelligence Connect data warehouse.
SAS Cloud Analytic Services¶
SAS Cloud Analytic Services is a cloud-based run-time environment for data management and analytics with SAS.
Download the Snowflake JDBC driver, and then configure SAS Cloud Analytics Services to connect to the Business Intelligence Connect data warehouse.
Tableau Desktop¶
Tableau is a visual analytics platform that enables people and organizations to make the most of their data. Tableau connects to a data source, and then queries that data directly.
Important
You may connect Tableau Bridge or Tableau Server to Business Intelligence Connect using your Amperity service account.
To connect Tableau Desktop to BI Connect
Download and install the Snowflake ODBC driver, and then connect Tableau to the Business Intelligence Connect data warehouse.
Log in to Tableau. Select the Connect menu option, then To A Server, then More.., and then Snowflake. This opens the Snowflake configuration page.
Select the General tab, and then enter the following information:
Setting
Description
Server
The URL for the Business Intelligence Connect data warehouse.
Role
The role that is assigned to this user. Leave this blank.
Authentication
Set this to “Username and Password”.
Username
Your Amperity username. For example:
justin.currie@acme.com.Password
The password for your Amperity username. Leave this blank.
Select the Advanced tab, and then set Custom Driver Parameters to “authenticator=externalbrowser”.
Click Sign In. Your browser will open.
If you are already logged into Amperity, you will see a message in your browser that confirms you are authenticated to the Business Intelligence Connect data warehouse. Navigate back to Tableau.
If you are not logged into Amperity, you will be prompted for your username and password. Complete the login process, and then nagivate back to Tableau.
You may begin authoring and running queries from Tableau against data in the Business Intelligence Connect data warehouse.