Sigma BI Analyst App: Comprehensive User Guide for Extracting and Analyzing BI Metadata

Introduction

The Sigma BI Analyst App is a Snowflake Native Application that allows you to easily extract and analyze metadata from your BI environments. Currently the app supports Tableau, Power BI, and Looker. The app connects to the BI Tool’s API and extracts metadata storing it in the current Snowflake account. Once the metadata is extracted, you can use the app to trigger the automatic provisioning of Sigma Workbooks. These workbooks will provide a visual analysis of your BI environments.

This guide will walk you through the entire process of using the app. The guide is broken into the following sections:

Installation
- Install Preparation
- Installation
Provisioning API Credentials
Extracting BI Metadata in App
- Step by Step
Configuring Sigma in App
- Step by Step
- Connecting to Snowflake
Viewing the Workbooks
Data Access, Security, and Credit Usage
Support

Installation

Install Preparation

Before the app can be installed the following steps must be executed by the consumer of the app: App consumer is to share Snowflake account name with Sigma Customer Success team in the OrgName.AccountName format. See details here. Provision a Snowflake role to own and install the app. See details below. Role Provisioning Before installing, access to a role with the following privileges will be required:

CREATE DATABASE ON ACCOUNT WITH GRANT OPTION
IMPORT SHARE ON ACCOUNT
EXECUTE TASK ON ACCOUNT WITH GRANT OPTION
CREATE WAREHOUSE ON ACCOUNT WITH GRANT OPTION
CREATE INTEGRATION ON ACCOUNT
CREATE APPLICATION ON ACCOUNT

It is highly recommended that the app consumer set up a dedicated role to install and thus own the app. This app owner role can manage access to the app and the extracted metadata. By default, some privileges required can only be granted by ACCOUNTADMIN. If the user installing the app does not have access to ACCOUNTADMIN or a custom role with required grant privileges, they will need to request an app owner role be provisioned for them. Below is a templated SQL script that can be used for this purpose. The script can be run by ACCOUNTADMIN or a similar role that has privileges required to create a role and grant the required privileges.

USE ROLE ACCOUNTADMIN;
SET role_name = 'SIGMA_BI_ANALYST_APP_ROLE';
SET wh_name = 'SIGMA_BI_ANALYST_APP_WH';

CREATE ROLE identifier($role_name);
CREATE WAREHOUSE identifier($wh_name);
GRANT OPERATE, USAGE ON WAREHOUSE identifier($wh_name) TO ROLE identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE ACCOUNTADMIN;

GRANT CREATE DATABASE ON ACCOUNT TO ROLE identifier($role_name) WITH GRANT OPTION;
GRANT IMPORT SHARE ON ACCOUNT TO ROLE identifier($role_name);
GRANT EXECUTE TASK ON ACCOUNT TO ROLE identifier($role_name) WITH GRANT OPTION;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE identifier($role_name) WITH GRANT OPTION;
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE identifier($role_name);
GRANT CREATE APPLICATION ON ACCOUNT TO ROLE identifier($role_name);

Installation

Once a role has been provisioned to install the app, the user should switch to that role and execute the following:

Select ‘Get’ in the app’s listing in the Snowflake Marketplace.
Select a warehouse to use for installation and an alternate name for the app if desired.
Click ‘Get’ again.
The app will begin its installation and an email will be sent to the user when it is complete.
When done, press the ‘Configure’ button or open the app from the ‘Apps’ screen.
Select a warehouse to run the app with.
Once the app opens, the user will be prompted to grant additional privileges to the app through the UI, execute the grants in the UI.
The app will now briefly finish setting up.

Once done, the app is fully installed and ready for use.

Provisioning API Credentials

The app requires API credentials for each BI tool you want to analyze. The user will need to provision these credentials outside of the app. Each BI Tool uses a different method of authentication. The access control applied to the API credentials will be of importance as well with varying requirements for each tool. Instructions for provisioning API credentials can be found in the following sections:

Tableau

For Tableau, the user will need to generate a personal access token (PAT) and provide it to the app. Extra steps are required if Tableau Server is being used as opposed to Tableau Cloud. For extended details on Tableau PATs, see here.

Generating Credentials

Follow steps below to generate a PAT:

Open Tableau.
Go to My Account Settings.
Under Personal Access Tokens, enter a descriptive name for your token in the Token Name field, and then click Create Token.
In the Personal Access Token dialog box, click the Copy Secret button.
Paste the secret to a file and store the file in a safe location.
When finished, click the ‘Close’ button.

For any detailed questions regarding generating a PAT, see here.

Determining the API Host URL

The Tableau Server URL parameter is required by the app to connect to the Tableau API. If using Tableau Server, the URL is going to be the same as the server URL where Tableau Server is hosted. In this case the URL may look something like ‘hakkoda.tableau.com’. If using Tableau Cloud, the URL will be different. For cloud, sign into Tableau and look at the URL in your browser. The URL should be roughly of the format ’10ay.online.tableau.com’, or something similar. You may include or exclude the https:// prefix.

The user should now have the following credentials:

Tableau URL
Personal Access Token Name
Personal Access Token Secret

Permissions and Other Config

Additional requirements for Tableau include the following:

If using Tableau Server, a server admin must first run the tsm maintenance metadata-services command.
The user, which the PAT belongs to, is RECOMMENDED to have admin privileges, however it is not required. If a PAT with admin privileges cannot be obtained, some analysis will be limited but the app will still function.

Power BI

To provision credentials for Power BI, you need to register an Azure Active Directory (Azure AD) application in Azure. The app uses the credentials for the Microsoft Entra application to authenticate with Power BI. Due to complexity of this process, please use provided links for detailed instructions. A highl level workflow is provided herein.

Generating Credentials

Create a Microoft Entra tenant to use with Power BI. See here.
Register a Microsoft Entra app, see here.
Write down credentials for later use.

The user should have the following credentials:

Entra App ID
Entra Tenant ID
Username
Password

Looker

For Looker, the user will need to generate API credentials for a given user. A user with admin privileges is needed to create the API key.

Generating Credentials

Go to the users page in the admin section of Looker.
Click the Edit Keys button.
Click New API Key.
Write down the key and secret.

Determining the API Host URL

For looker the user will also need to determine the API host URL. The API Host URL is the user-facing domain name (and port optionally) that the app needs to reach the Looker API. For details on determing the URL see here.

The user should have the following credentials:

API Host URL
API Key
API Secret

Permissions and Other Config

The app will connect to the Looker API “as the user” who owns the API key, so access will be limited to that of the user. The user that the key belongs to should have the below permissions in Looker:

manage_project_connections
see_user_dashboards
see_looks
develop
see_user_dashboards
access_data
see_users (optional)
manage_roles (optional)

If the two optional permissions are not granted, some analysis will be limited but the app will still function. If all other required permissions are not granted, the app will fail when extracting metadata. A failure message will be displayed on the home page if a permission is missing.

A lag in the application of the manage_project_connections permission has been observed. If this permission has been recently granted it may take a few minutes for the job script to be able to access the API properly.

Extracting BI Metadata in App

Once credentials have been provisioned for the BI tools the user needs to analyze, the user needs to enter these credentials into the app. The app uses these credentials to create connections to the BI tools and subsequently extract metadata from them.

Step by Step

The following steps show how to input API credentials and start extracting data from BI tools. These steps are agnostic to the BI tool.

Open the Sigma BI Analyst App.
Assure that the ‘Home’ screen is open by clicking on the ‘Home’ button in the left sidebar.
Click ‘Configure’ on one of the three BI tool panes near the center of the page.
Enter API credentials into the appropriately marked fields.
Click ‘Submit Credentials’.
Now, copy the SQL code presented into a separate worksheet within Snowflake. Make sure the code is ran with the role that was used to install the app. This role also needs to have the CREATE INTEGRATION privilege on the account.
Return to the app, and click ‘Execute Job’.
Click ‘Home’ to monitor the status of the data extraction job.
Click the refresh button in the top right corner to refresh the status of the job.
To refresh the data, click the ‘Rerun’ button in the middle pane of the page. This will fully refresh all metadata tables from scratch.
You can use this page to also monitor the freshness of the data by observing the ‘Finished At’ field.
Once done, the user can move on to configuring Sigma in the next section.

NOTE: Tableau’s API is very unstable. If the tableau job has failed for an unknown reason, try running the job again.

Configuring Sigma in App

Once the data has been extracted, the user can use the app to automatically generate Sigma Workbooks to analyze their BI environments visually.

Step by Step

Open the Sigma BI Analyst App.
Assure that the ‘Home’ screen is open by clicking on the ‘Home’ button in the left sidebar.
At the bottom of the page, click ‘Configure’ in the Sigma Status section to open the Sigma config page.
If the user does not have a Sigma account, click the ‘Start Free Trial’ to create one. Follow the instructions in the link.
After an account has been created, enter the Sigma API Client ID and Client Secret as well as the cloud provider. For info on how to create these credentials see here. For full functionality, use API credentials associated with an ADMIN account in the Sigma instance.
Click ‘Submit Credentials’.
Similar to the last step, copy the SQL code presented into a separate worksheet within Snowflake. Make sure the code is ran with the role that was used to install the app. This role also needs to have the CREATE INTEGRATION privilege on the account.
Once code has been run, click ‘Confirm Script Run’.
Now the user must input information required for connecting the Sigma account to Snowflake. The user must either input Snowflake credentials for the current account or select ‘Use Existing Connection’ if there is already a connection to the current snowflake account from the Sigma account being used. See the ‘Connecting to Snowflake’ section below for details on providing these credentials.
Once a configuration has been selected for connecting to Snowflake, click ‘Create Workbooks’. This will automatically generate workbooks for each BI tool that has been configured.

Connecting to Snowflake

To connect the app to the user’s Sigma account, the user can either input their Snowflake credentials for the current account or select ‘Use Existing Connection’. It is highly recommended that the user create a new connection for the native app. The user should only use an existing connection if user/password authentication is not possible due to internal policy. It is important that the role used in either the new or existing connection has access to the BI_ASSESSMENT_DB. If this role does not have access, it will cause problems later on. The only role that has access to the BI_ASSESSMENT_DB by default is the role that installed the app. To grant access to the database to other roles the application role needs to be granted. Grant the application role, SIGMA_BI_ANALYST_APP_ROLE, to other roles using the following SQL:

GRANT APPLICATION ROLE SIGMA_BI_ANALYST_APP_ROLE TO ROLE OTHER_ROLE;

Viewing the Workbooks

After all previous steps have been completed the user can access the workbooks in Sigma by following the steps below:

Click ‘Home’ in the Sigma BI Analyst App.
Click ‘Open Workspace’ or any of the ‘Workbook’ buttons to open the location of the workbooks in Sigma.
Open a specific workbook.
The user will be prompted to update the data sources for the workbook when opened.
Follow the UI prompts to redirect the workbook datasources automatically to the BI_ASSESSMENT_DB database.
Once this step is completed, the visualization will populate.

Data Access, Security, and Credit Usage

Data Access

The app creates a database at installation called BI_ASSESSMENT_DB which is used for storing the metadata. This database contains a schema for each BI tool, where tool specific metadata is stored. The user who installed the app will have access to these tables. Access to these tables can be granted to other users by granting the application role, SIGMA_BI_ANALYST_APP_ROLE, to other roles. This can be done by running the following SQL:

GRANT APPLICATION ROLE SIGMA_BI_ANALYST_APP_ROLE TO ROLE OTHER_ROLE;

Security and Secrets

The app uses the Snowflake secret objects to store credentials for BI tools. This allows for secure storage of credentials which are not accessible by any other user in the account other than ACCOUNTADMIN and the application itself. The secrets are not shared with Sigma or Hakkoda. The secrets are stored in the BI_ASSESSMENT_DB, in the BI specific schemas. No user will have access to these secrets unless the ACCOUNTADMIN transfers ownership of the secrets from the application to a user manually. This is not recommended as it will potentially compromise app functionality.

Warehouse Usage

Metadata extraction jobs triggered by the app run on the BI_ASSESSMENT_WH warehouse. This warehouse is created and owned by the application but it exists in the user’s Snowflake account and thus incurs minor credit usage. It is an X-SMALL warehouse so credit usage will be minimized.

Support

For support issues please reach out to app_support@hakkoda.io.