Using Birst Data in Tableau

Setting Up Birst with Tableau Desktop

The examples in this topic use the Retail Analytics sample data, which is similar to the Northwind sample data.

These instructions were created using Tableau 9.0.

Best Practices

Consider the following best practices when using Birst with Tableau:

  • Use Birst access control and security features to provide data governance.
  • You can use multiple data sources for the same space. Each data source could have different user credentials; the users could see different views of the same data.
  • You can use one Birst space per Tableau workbook, because a workbook uses one ODBC data source, which is one space. If you need to include data from multiple spaces in a single workbook, create a new space that contains all the data you need and configure a new data source for it.
  • Custom subject areas are a Best Practice for organizing data for use by ODBC clients such as Tableau. Custom subject areas let you limit the attributes and measures to only what is needed. See Creating a Custom Subject Area for OCI.
  • As a general guideline, use one Birst fact (measure) per worksheet. You can have multiple worksheets per workbook.
  • Always use a Tableau Live Connection, not an Extract connection. A Live Connection ensures the most current data and access controls from Birst.
  • Use the Tableau Data - Refresh menu to update the metadata if the data model changes in Birst.

Prior to configuring Tableau Desktop

  1. Prior to configuring Tableau Desktop to use data from Birst, install the Birst ODBC Driver and configure the data sources. See Using the ODBC Driver.
  2. If you are using Tableau 9.0: Locate the connection-configs.xml file in the Birst ODBC Driver installation directory, which is normally C:\Program Files\Birst ODBC Driver. Copy it to the Tableau Desktop folder, by default at: C:\Program Files\Tableau\Tableau 9.0. This file keeps Tableau Desktop 9.0 from creating multiple parallel login sessions to a single Birst data source, which is currently not supported by Birst. You must have Windows administrator permissions to add this file to the Tableau 9.0 folder.
  3. If you are using Tableau Server: Ensure that the driver is installed on the server and a server DSN (not user DSN) created with all the required credentials. The server DSN should have the same name as the Desktop DSN from where workbooks are being published.

To connect Tableau Desktop to a Birst data source

  1. Open Tableau Desktop.
  2. In the Connect column click Other Databases (ODBC).
  3. In the Server Connection Other Databases (ODBC) window, click Connect Using DSN and pick the data source you configured from the list. If you prefer, you can connect using the Driver configuration.

    Tip: If you do not see the data source you want, check the data source configuration. See Using the ODBC Driver.
  1. Click Connect. The Connect to Birst window opens.
  2. If you did not save the password when configuring the data source, enter it here.
  3. If you previously checked Use Subject Areas, it remains checked.
  4. If the Space ID is not the one you want, click List Spaces and choose another one from the list.
  5. Click OK. The Server Connection Other Databases (ODBC) window displays the connection information. The String shows the space ID and Birst URL.

    Tip: If you checked Use Subject Areas, the String Extras will show USESUBJECTAREAS=true.
  6. Click OK. Tableau opens a new workbook that is connected to the Birst data source using the ODBC connection.

Tips:

  • This is the Tableau Data Source tab. Tabs are at the bottom of the Tableau workbook.
  • To change or add new data sources in a Tableau workbook, click the down triangle menu.
          

Next, create a new workbook in Tableau that uses the Birst data.

To show Birst subject areas in a Tableau workbook

  1. Create a new workbook in Tableau.
  2. In the Tableau workbook click Select Database and pick your Birst database.

    Tableau shows the Table section.
  3. In the Table name field, click the Search icon.

    Tableau lists all of the subject areas from the space in Birst.

Tips:

  • Hover over a subject area to see its full name.
  • Click the View Data icon to see the rows from the subject area.


Next, use the Birst measures and dimensions to create a Tableau visual model.

To refine the fields in Tableau

In the Tableau Data Source tab you review and refine the Birst attributes and measures imported to Tableau from the ODBC Driver.

  1. Double-click, or drag the subject area to the model area. Tableau lists the fields, the attributes and measures, that are from the Birst subject area.

Tips:

  • The Field Name is from the label used in the Birst subject area. In the Field Name list you can edit the display field title to change how it appears in Tableau.
  • For Field Names, the far left column shows the data type, and if relevant, whether Tableau interprets the data as having a Geographic role. You can change these designations.

  • If some items are within folders in the Birst subject area, Tableau shows the path.
  • To avoid clutter and confusion, use the Tableau Hide option to hide all fields that will not be useful in your Tableau reports. Click on the right of the column name from the menu click Hide.
  • The Remote Field Name is the original name in Birst.

Next, create a Tableau worksheet using the new Infor fields.

To create a worksheet in Tableau

Tip: Tableau automatically categorize fields as measures (pre-pended with "Facts_") or dimensions (what Visualizer calls attributes).

  1. When you have finished defining the fields in Tableau, you are ready to create a Tableau worksheet. Click the Sheet at the bottom.
  2. Tableau may provide an informational message. Check Do not show again and click OK.

    The Worksheet opens showing the Data tab with Dimensions and Measures on the left, and the visual display area on the right.

    Important: Tableau categorized the data into dimensions and measures using the data type. In some cases this will not be the same categorization as in Birst. You may need to adjust the Tableau categorizations in the next step.
  3. Shift-select and drag any mis-categorized fields to the correct Dimensions or Measures area.
    Important: If you leave attributes in the Measure area and measures in the Dimensions area, there will be errors when you try to create queries with them, because they won't make sense in the Birst data model.
  4. Save the workbook.
  5. Drag and drop a measure onto the Rows display area. For example, the Units In Stock measure.

  6. Drag and drop  a dimension onto the display area. For example, Product Name.

  7. Use the Tableau visualization tools to add tooltips or create new visualizations. For example, add Supplier and Supplier Phone to the tooltips.

  8. Save the workbook.

Known Issues, Limitations, and Workarounds

  • Connections of type Extract are not supported. The Connection should be set to Live.
  • Tableau Quick Filters on continuous attributes do not work. Filtering works, but not when used as a quick filter.
  • Tableau sets and histograms are not supported.
  • Creating TDE files in Tableau is not supported. Contact your Birst representative for a standalone tool that can take a BQL query and generate a TDE file.
  • Custom SQL for a Tableau data sources is not supported.
  • Top N on a field (as opposed to Top N on a query) is not supported.
  • Unaggregated queries are not supported.
  • Tableau 9.0 multiple connections is not supported. Add the connections-configs.xml file as previously discussed.
  • Only Birst aggregation types (COUNT, COUNT DISTINCT, SUM, and AVERAGE) are supported.
  • There may be cases where you want to treat a measure as an attribute, such as slicing on revenue or price. It’s unlikely you want to slice directly on the value, but instead want to slice on ranges of those values, such as breaking price down to less than 100, between 100 and 1000, and greater than 1000. In Birst you would create a Bucketed Measure, for example:
  • You cannot have “, {, or } in your password when using Tableau. If your password contains any of those characters you will see the following error message:
    [Birst][ODBC] (10380) Unable to establish a connection with data source. Missing settings: {[SPACE]}
    Unable to connect to the server "Birst ODBC Driver". Check that the server is running and that you have access privileges to the requested database.
    Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.