Business Views for 3rd Party Visualization Tools

Tristan · ‎02-28-2022

Introduction

As a Universal Data Analytics Platform (UDAP), Incorta provides a fully featured and rich experience from data acquisition, to data enrichment all the way through to data visualization - all with massive data sets and at incredible speeds. As such it is a great choice for your analytics data hub as it is also capable of serving up data through not only its own visualizations but also through many other common visualization tools like Tableau and Power BI. This article discusses the best practices for setting up Incorta business views to support integration with third party visualization tools.

What you should know before reading this article

We recommend that you be familiar with these Incorta concepts before exploring this topic further.

Applies to

These concepts apply to versions 4.0+ of Incorta. Work with your administrator to enable the appropriate CMC settings if needed.

Let's Go

As with most of what you do with Incorta, it is important to get the data model right before you try to access data from Incorta. While it is technically possible to access Incorta physical schema objects from your third party visualization tool, you will want to define runtime business views in Incorta that are self-contained for each of the reports that you build.

How to access Incorta Objects

The analytics service exposes all tenants in an Incorta cluster as separate databases available via the SQL Interface (SQLi) using the PostgreSQL protocol. Via SQLi, as long as your tool can connect to PostgreSQL, you can set up a JDBC connection to the tenant database you wish to query and run SQL statements to retrieve data. Note that depending on the shape of the SQL you are executing, Incorta will execute the statement directly with the engine or will fall back to Spark to handle the query. As a result, you will need to make sure that Spark is enabled. See Docs for more information on how to set this up.

Why Define Business Views?

The big advantage of accessing data from Incorta is that you can access more data more quickly than from your source systems directly and with no runtime impact to them. This is only true if you do not need to create additional calculations or joins within the visualization tool though as these operations will not use Incorta’s engine. To avoid this pitfall, prepare your data in full in Incorta before accessing it.

Runtime business views, specifically business schema views, are the cleanest way to prepare your Incorta data for use in reports. They allow you to bring columns from multiple data sources into a single object, eliminating the need for joins in your tool, and they allow you to control the naming of your data elements.

Designing your business views

Here are the best practices for designing the business views that you will use in Power BI, Tableau or your third party visualization tool of choice:

Create new business views

Define new runtime business views for the reports that you build in your third party visualization tool. Do not use the business views when building visualizations in Incorta and do not use existing business views that are already being leveraged by Incorta visualizations when writing reports with your tool. What works well in Incorta may not work well or efficiently in the third party tool.

The business views that you define in Incorta should be sufficient to meet the full needs of the reports that you build without having to create joins in the third party tool. All the needed columns should be included. The business views can be defined broadly enough such that they cover the needs for many of the reports you will build in your tool. It is typical to build as few as one business view in Incorta for an entire business area (e.g. AR or Sales) that you will build dashboards for in your tool. Note that you may need to flatten your data within Incorta to achieve this.

Specifically for Tableau, if you plan to replace a data source for an existing workbook with Incorta as the data source, please match column names in the Incorta business view with the column names in the existing workbook. This will simplify things for you significantly when making the change and will reduce the amount of rework required in Tableau.

Define a base table

By defining a base table, Incorta enforces a well-understood "record grain" such that it cannot change as different fields within the business view are used in the third party dashboard. You can define your base table for your business view in the Business Schema Designer.

Do not include aggregate calculations

Your business view should not include any aggregated formula calculations, like sum, max or average. This is a standard practice in Incorta but typically third party visualization tools expect fact data only and will do the aggregations on their own.

Avoid Incorta views

There are two types of runtime business views: business schema views and Incorta views. Business schema views are defined within the Business Schema Designer. Incorta views are defined using the Analyzer UI. Incorta views are useful for aggregated queries but they are held in memory only. This means that if a query falls back to Spark, it will fail as there is no parquet for Spark to read. As such, it is usually best to avoid Incorta views in favor of business schema views which can be built on top of materialized views (MVs) if needed. MVs are flexible enough to handle aggregated queries as well as a wide variety of other logic and their results are materialized to parquet.

Validate Business Views in SQL

If, via the Analyzer, you are able to see that the business view generates valid SQL then you can guarantee that the business view will work in your third party tool. To do this, open up Analyzer (you could do this via the Explore Data link when viewing the business schema that contains your business view), drag a column from your business view into the measure tray and then click on the SQL icon in the upper right corner of the screen. If it opens up and displays a valid SQL statement you are good to go.

Complex runtime security filters for implementing row level security are a typical cause for the SQL not to generate as expected. You may need to review the physical schema tables underlying your business view to find the issue. Note that the SQL you see in the Analyzer will not include security filter conditions but that these conditions will be picked up by SQLi and applied to the results that pull through to your third party tool.

Organizing your business views

Runtime business views are defined within business schemas and can be organized by folders. We have already established that each report you write in your third party tool should be dependent on one and only one Incorta business view for performance considerations. Within Incorta, we recommend that you define at least one business schema for each third party visualization tool you intend to use Incorta as a source for. The number will depend on how you want to organize your accessible Incorta business views for your report developers and possibly on how you will apply security.

The simplest setup is to define a business schema for the third party visualization tool and then define a folder for each business area that you will build visualizations for in the tool. The business views built for each business area would then go into the corresponding folder.

Example:

Setting permissions

Incorta assigns access to objects by sharing them with Incorta users. This means that for third party tools to see Incorta business views, the tool needs to have an Incorta user with permission to them. With this in mind, you will need to set up a service user account for each of your third party tool users in both the tool and in Incorta.

Furthermore, in order to enable record level security through your third party tool, you must first define row level security in Incorta. You will connect to Incorta using the service user account, but will also send the matching third party tool user information to Incorta so that Incorta can apply row level security before sending the response back.

Recommended steps for setting permissions

Set up your Incorta business views/folders/schemas and define your row level security as appropriate.
Enable SQL App in CMC
Set up your service account user in both Incorta and the third party visualization tool.
- Set up service account user in Incorta
- Set up service account user in third party tool
Set up connection in the third party tool.
- Set up connection in Power BI
- Set up connection in Tableau
- Set up connection for PostgresSQL compliant visualization tool
Share only the business schema(s) that the tool should have access to with the service account user.
Validate that Incorta data is available and successfully pulls into the tool

Column names or column labels?

It is possible to configure Incorta to display column labels in your third party tool instead of column names. This can be quite advantageous when the original source table names and column names are cryptic as they are with some applications (e.g. SAP, Netsuite).

Work with your Incorta Administrator to enable the Use column labels instead of column names configuration property in the SQL Interface section of the Server Configurations tab under Cluster Configurations in the CMC if you want to take advantage of this feature.

Supported third party visualization tools

Incorta supports integration with many third party visualization tools. Below are tips for how to work with them.

Tableau

Incorta has a partnership with Tableau and as such has a custom connector specifically for Tableau. Work with your Incorta Administrator to enable the connector in the CMC. Follow the instructions here for how to set up the Tableau integration within Incorta. Note that within Tableau, the Incorta connector is now available starting with Tableau v2020.3. Also see this Community post for best practices for building Tableau dashboards against Incorta.

Power BI

Microsoft is also an Incorta partner and not surprisingly Power BI is a popular visualization tool to integrate with Incorta. You must work with your Incorta Administrator to enable the PostgresSQL connector in the CMC in order to get the Power BI integration working. See the instructions here. Also see this Community post for best practices for building Power BI dashboards against Incorta.

The normal way to bring Incorta data into Power BI is via a direct connection that queries Incorta at runtime. It is also possible to import data from Incorta into Power BI and to have the dashboards run on the imported data. This works well for small data sets because, as there is no query to perform, it is very fast. The same business view design considerations come into play for either type of data load. There are, however, a number of disadvantages to importing Incorta data into PowerBI.

The data is static until the next import
The data takes up storage on your Power BI server
The process to import the data is heavy on resources - certainly compared to direct queries of Incorta
Again, you can not important the massive amounts of data that Incorta makes available via direct query

Excel Add-in

Excel is maybe not the first visualization tool you think of when you think about building dashboards but it is probably the most popular BI tool in the world. It certainly has its limitations (e.g amount of data it can handle) but it is a very important tool nonetheless. Incorta has built an Excel Add-in that lets you take advantage of the power of Excel with Incorta data. The same recommendations apply for designing your business views but there are a couple of additional recommendations that we can make specific to the Add-in:

Design your business views such that it is easier for users to keep the number of rows under Excel’s limit. This may require some pre-aggregation.
Keep your data on separate worksheets from your charts/dashboards. This will allow you to have cleaner dashboards that will automatically update whenever you refresh your Incorta queries.
- For Incorta Excel Add-in releases prior to 16.6, the data worksheet/tab is refreshed completely when you re-query Incorta. This means that you should put formula columns on a separate worksheet/tab because if you add them to the Incorta data tab, they will be lost the next time that you refresh your data.
- For Incorta Excel Add-in release 16.6 and above, it is possible to add formula columns to the Incorta data worksheet itself as refreshing your Incorta data will only overwrite the columns that contain Incorta data and not the entire worksheet/tab.

Other third party visualization tools

It is possible to configure other visualization tools to use Incorta as a source. As noted previously, the tool need only support PostgresSQL as a data source. Follow the instructions here to configure your tool. If you have any questions about the integration, check or post to the community or work with Support or Customer Success who will be happy to help.