Send 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 for Amperity tenants who have licensed Amp360. 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:

  1. A dedicated data warehouse in Snowflake that is configured to run in the same cloud platform as your tenant.

    Cloud platform

    Regions

    Amazon AWS

    aws-us-west-2

    Microsoft Azure

    az-us-east-2

  2. A default database and schema within the data warehouse. These are empty by default.

  3. A data staging environment within the data warehouse location.

  4. 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.

  5. A role with read-write access to the database and schema.

  6. 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:

  1. The URL for the data warehouse.

  2. The Account Name, Region ID, Database name, Schema name, and Staging path.

  3. A JDBC connection string.

  4. An ODBC connection string.

  5. A service account username and password, which has been granted read-write access to the data warehouse.

    Use the service account to configure the destinations in Amperity that will send data to the Business Intelligence Connect data warehouse.

    Important

    Store the service account username and password in a safe place. Service account credentials cannot be used by individuals to access data in the Business Intelligence Connect data warehouse.

Service hours

Business Intelligence Connect is available to Amperity tenants licensed for Amp360. 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 data sharing

Snowflake data sharing is not enabled by default for the Business Intelligence Connect data warehouse. If your organization wants to use Snowflake data sharing with the Business Intelligence Connect data warehouse, they both must be in the same region.

The Business Intelligence Connect data warehouse supports the following regions:

Cloud platform

Regions

Amazon AWS

aws-us-west-2

aws-us-east-1

Microsoft Azure

az-us-east-2

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:

  1. Single sign-on (SSO) for individual user accounts

  2. Service 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

  1. Enter the URL for your instance of Business Intelligence Connect into your web browser.

  2. On the login screen, enter the username and password for your service account. For example:

    The login screen for BI Connect.
  3. 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:

  1. Export a database or table directly to the Business Intelligence Connect data warehouse.

  2. 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 tab, and then added to an orchestration from the Destinations tab. 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

  1. From the Customer 360 tab click Configure Exports. This opens the Database Exports page.

  2. Click Create New Export. This opens the Add Export dialog box.

  3. Add the name of the data export, and then click Add. This will add a draft data export to the Database Exports page.

  4. Open the menu for the draft data export, and then select Edit.

  5. From the Database drop-down, select a database.

  6. From the Entities list, select one (or more) database tables to add to the data export.

    For a single table, click the table. For multiple tables, use click + command for each table to be selected. For all tables click the first table, hold shift, and then click the last table.

  7. Click Activate.

Send query results

You can send the results of any query from the Queries tab 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

  1. From the Destinations tab, click Add Destination. This opens the Add Destination dialog box.

  2. Enter the name of the destination and a description. For example: “BI Connect” and “This sends data to the BI Connect data warehouse”.

  3. From the Plugin drop-down, select Snowflake.

  4. From the Credential Type drop-down, select a credential type: “aws-snowflake”.

  5. From the Credential drop-down, select Create a new credential. This opens the Create New Credential dialog box.

  6. Enter the username and password. This is the service account username and password.

  7. Add the IAM Access Key and IAM Secret Key for the IAM role with permission to access the Amazon S3 bucket.

  8. 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.

  9. Select Allow customers to use this data template.

  10. Select Allow orchestrations from users with limited PII access.

  11. 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

  1. From the Destinations tab, click Add Destination. This opens the Add Destination dialog box.

  2. Enter the name of the destination and a description. For example: “Snowflake” and “This sends data to Snowflake”.

  3. From the Plugin drop-down, select Snowflake.

  4. From the Credential Type drop-down, select a credential type: “azure-snowflake”.

  5. From the Credential drop-down, select Create a new credential. This opens the Create New Credential dialog box.

  6. Enter the username and password. This is the service account username and password.

  7. Add the Azure Blob Storage account name and shared access signature for the AZURE_ROLE with permission to access Azure Bob Storage.

  8. 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.

  9. Select Allow customers to use this data template.

  10. Select Allow orchestrations from users with limited PII access.

  11. 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 for BI Connect

  1. From the Destinations tab, open the menu for a Snowflake destination, and then select Add data template. This opens the Add Data Template dialog box.

  2. Enter the name of the data template and a description. For example: “Snowflake” and “This template maps to Snowflake.”

  3. 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
    
  4. Select Drop table to overwrite the table and apply an updated schema when the upstream segment or table changes.

  5. Click Save.

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

  1. From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.

  2. From the Object Type drop-down, select Database Export or Query.

  3. From the Object drop-down, select the object to be exported to Snowflake.

    Note

    This name will also be the name of the orchestration.

  4. From the Destination drop-down, select the name of the destination that is configured to send data to Snowflake.

  5. From the Data Template drop-down, select the data template that maps a database export or query to a table in Snowflake.

  6. 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
    
  7. Verify the option to use for managing the table in Snowflake.

  8. Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)

  9. Click Save.

Run orchestration

Run the orchestration manually to validate that it works.

To run the orchestration

  1. From the Destinations tab, under Orchestrations, open the    menu for the BI Connect orchestration, and then select Run.

  2. 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.

  3. 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.

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

  1. Download and install the Snowflake JDBC driver .

  2. Configure Databricks to use the JDBC driver to connect to Business Intelligence Connect.

  3. 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.

  4. 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

  1. Download and install the Snowflake JDBC driver .

  2. In SQL Workbench, choose File, Connect Window, and then Create a New Connection Profile. This opens the Connection Profile pane.

  3. 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.

  4. 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.

  5. 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 Power BI

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 empowers 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

  1. Download and install the Snowflake ODBC driver , and then connect Tableau to the Business Intelligence Connect data warehouse.

  2. Log in to Tableau. Select the Connect menu option, then To A Server, then More.., and then Snowflake. This opens the Snowflake configuration page.

  3. 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.

  4. Select the Advanced tab, and then set Custom Driver Parameters to “authenticator=externalbrowser”.

  5. 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.

  6. You may begin authoring and running queries from Tableau against data in the Business Intelligence Connect data warehouse.