Configuring Tableau to connect to a DataMosaix project

DataMosaix has an egress data interface that makes it easier for select Business Intelligence (BI) tools to build reports and dashboards based on contextualized data.
This egress data interface is exposed as a virtual PostgreSQL server that emulates a real Postgres server, and it currently supports Tableau Desktop and Tableau Server (refer to the following details). This section explains how to connect these tools to a FactoryTalk DataMosaix project. After the configuration is performed and the connection is established, users can run SQL queries directly from supported tools.
NOTE:
For more information on Cognite-related documentation, see Assets tag and Timeseries tag.
Prerequisites
  • Tableau Cloud/Desktop is installed. For more information, refer to Download Tableau.
  • License for Tableau Cloud/Desktop.
  • Postgres JDBC driver is installed, refer to Driver Installation.
    NOTE:
    The driver should be installed on the machine that will run the Tableau Desktop client to connect Tableau with the PostgreSQL server.
  • Access to FactoryTalk DataMosaix.
  • Register the application using the Machine-to-Machine type in FactoryTalk DataMosaix. For information on registering an application in FactoryTalk DataMosaix, refer to Register Application.
Supported Versions
The following Tableau versions are supported by virtual Postgres interface:
  • For Tableau Server, the supported version is 2024.2.2.
  • For Tableau Desktop, the supported version is 2024.2.2.
  • For Tableau Cloud, the supported version is 2024.2.0.
Datasource Connection using Tableau Cloud
Perform the following steps to connect to data source using Tableau Cloud:
  1. In the browser, use your Site URL and press
    Enter
    .
  2. In the
    Tableau
     page, provide the username and password shared by the Tableau Admin and click
    Sign In
    .
    Tableau Sign in Page
  3. To create a new workbook, navigate to
    New
     >
    Workbook
    .
    Create New Workbook
  4. In the
    Connect to Data
     dialog box, click
    Connectors
     >
    PostgreSQL
    .
    Select PostgreSQL
    The PostgreSQL dialog box is displayed.
  5. Provide the following details:
    • Server:
      dm-virtualpostgres.cloud.rockwellautomation.com
       (Production URL).
    • Port: Provide port number as 5432.
    • Database: The name of the project in FactoryTalk DataMosaix.
    • Authentication: By default, Username and Password is selected.
    • Username: Refer to the Client ID generated while registering the application.
    • Password: Refer to the Client Secret generated while registering the application.
      NOTE:
      SSL is always enabled in both checked and unchecked conditions of the
      Require SSL
       checkbox.
  6. Click
    Sign In
    .
    PostgreSQL Dialog
  7. Drag and drop a table to open campus and click
    Update Now
     to get the table data.
    View Table Data
Datasource Connection using Tableau Desktop
Perform the following steps to connect to data source using Tableau Desktop:
  1. Open
    Tableau Desktop
    .
  2. Click
    Activate by signing in to a server
    .
    Activate Tableau
    The
    Tableau Server Sign In
    dialog box is displayed.
  3. Click
    Connect
    .
    Tableau Server Sign In Dialog
  4. Provide the username and password shared by the Tableau Admin and click
    Sign In
    .
    Sign In Page
  5. Navigate to
    Connect
     >
    To a server
     >
    More…
     >
    PostgreSQL
    .
    Select PostgreSQL
    The
    PostgreSQL
     dialog box is displayed.
  6. Provide the following details:
    • Server:
      dm-virtualpostgres.cloud.rockwellautomation.com
       (Production URL).
    • Port: Provide port number as 5432.
    • Database: The name of the project in FactoryTalk DataMosaix.
    • Authentication: By default, Username and Password is selected.
    • Username: Refer to the Client ID generated while registering the application.
    • Password: Refer to the Client Secret generated while registering the application.
      NOTE:
      SSL is always enabled in both checked and unchecked conditions of the
      Require SSL
       checkbox.
  7. Click
    Sign In
    .
    PostgreSQL Dialog
  8. Drag and drop a table to open campus and click
    Update Now
     to get the table data.
    Update Table Data
Supported Tables and Schema
The following tables are supported under the
Public
 schema:
  1. assets
  2. timeseries
  3. timeseries_datapoints
Supported Fields for the Assets Table
The following table provides the supported fields for the Assets table:
Asset Table Supported Fields
Column Name
Column Type
Description
Example
id
bigint
A server-generated ID for the object.
454501654069443
createdTime
timestamp
Timestamp when asset was created.
2019-09-07T15:50+00Z
lastUpdatedTime
timestamp
Timestamp when asset was updated.
2019-09-07T15:50+00Z
rootId
bigint
The ID of the root asset. The root asset is the asset spanning the entire asset hierarchy that this asset belongs to.
3039255880161656
name
varchar
The name of the asset.
function-variable-test
externalId
varchar
The external ID provided by the client. It must be unique for the resource type.
CDF_ExternalID
description
varchar
The description of the asset.
CDF project: testing_purpose
dataSetId
bigint
The id of the dataset this asset belongs to.
7166729101688251
source
varchar
The source of the asset.
test
parentExternalId
varchar
The external ID of the parent. The property will not be permitted if the asset does not have a parent or if the parent does not have externalId.
ftdm-parent-externalId
parentId
bigint
The ID of the parent of this node is null if it is the root node.
3281201682942258
Supported Fields for the Timeseries Table
The following table provides the supported fields for the Timeseries table:
Timeseries Table Supported Fields
Column Name
Column Type
Description
Example
id
bigint
A server-generated ID for the object.
454501654069443
createdTime
timestamp
Timestamp when timeseries data was created.
2019-09-07T15:50+00Z
lastUpdatedTime
timestamp
Timestamp when timeseries data was updated.
2019-09-07T15:50+00Z
isString
bool
True if the time series contains string values; false if it contains numeric values.
true
isStep
bool
It defines whether the time series is a step series or not.
true
externalId
varchar
The externally supplied ID for the time series.
CDF_ExternalID
name
varchar
The display short name of the time series.
function-variable-test
unit
varchar(32)
The physical unit of the time series (free-text field).
Meter/sec
assetId
bigint
The asset ID of equipment linked to this time series.
1027806836454786
description
varchar
Description of the time series.
CDF project: testing_purpose
dataSetId
bigint
The dataSet Id for the item.
7166729101688251
Timeseries Data Points
The DataMosaix timeseries can have either numeric or string data, so there are 2 columns for each data type. The value will be present in any one of them. This table will return 0 rows if queried without a filter on internal or external id. So timeseries_ID or external_ID is mandatory in where clause to fetch the data. The
timeseries_datapoints
 tables include the actual data points for a timeseries. This table has the following columns:
Timeseries Data Table
Column Name
Data Type
Description
timeseries_id
BIGINT
Internal ID of the timeseries.
external_id
VARCHAR
External ID of the timeseries.
timestamp_utc
DATETIME
Recorded timestamp of the data points.
value_numeric
DOUBLE
The numeric value of the data point if is_string is false otherwise null.
value_string
TEXT
The string value of the data point if is_string is true otherwise null
is_step
BOOL
TRUE if data point is a step
is_string
BOOL
TRUE if data point is a string
Sample Reference Tableau Dashboard and Steps to Include All the Supported Tables
Instead of passing hardcoded
timeseries.id
, user can create parametrized query in Tableau by passing
timeseries.id
 column as value option and select any particular
timeseries.id
 from the list to get its associated
timeseries_datapoints
.
User can prepare a dashboard in Tableau without providing hardcoded
timeseriesid
 or
externalid
 in SQL query datasource.
The following steps help to create a parameterized SQL query:
  1. Create relation in asset and timeseries table.
  2. Extend relation by custom parameterized SQL of
    timeseries_datapoints
    .
  3. Prepare Tableau parameterized sheet.
Create Relation in Asset and Timeseries Table
Perform the following steps to create relation in assets and timeseries table:
  1. Drag and drop the
    assets
     table from the left pane to open campus.
    Drag and Drop Assets Table
  2. Drag and drop the
    timeseries
     table from the left pane and place it next to the assets table.
    Drag and Drop Timeseries Table
  3. Select
    Id
     from the
    assets
     drop-down list.
    By default,
    =
     symbol is selected in the
    Operator
     drop-down list.
  4. Select
    Asset Id
     from the
    timeseries
     drop-down list.
    Drop-down Selection
Extend Relation by Custom Parameterized SQL of timeseries_datapoints
Perform the following steps to extend relation by custom parameterized SQL of
timeseries_datapoints
:
  1. Drag and drop the
    New Custom SQL
     from the left pane and place it next to the timeseries table.
    The
    Edit Custom SQL
     dialog box is displayed.
  2. Provide the following custom SQL query. 
    select * from public.timeseries_datapoints where timeseriesid =
    IMPORTANT: It is recommended to always specify the table with the
    public.
     nomenclature when writing custom queries. For example, to query from
    timeseries
     table, use
    select columns from public.timeseries
    .
  3. Select the
    Create a New Parameter
     option from the
    Insert Parameter
     drop-down list.
    Select Create Parameter
    The
    Create Parameter
     window is displayed.
  4. Provide the following details:
    • Name: Provide the name of the parameter.
    • Data type: Select
      Integer
       from the drop-down list.
    • Display format: Keep the default value.
    • Current value: Keep the default value.
    • Value when workbook opens: Keep the default option.
    • Allowable values: Select the List option.
  5. Select the
    When workbook opens
     option.
  6. Select
    Id(Timeseries)
     from the drop-down list.
  7. Click
    OK
    .
    Create Parameter Dialog
  8. In the
    Edit Custom SQL
     dialog box, click
    OK
    .
    Edit Custom SQL Dialog
  9. Select
    Id (Timeseries)
     from the timeseries drop-down list.
    By default,
    =
     symbol is selected in the
    Operator
     drop-down list.
  10. Select
    timeseriesid
     from the
    Custom SQL Query
     drop-down list.
  11. Click
    Update Now
    .
    Drop-down Selection
Prepare Tableau Parameterized Sheet
Perform the following steps to prepare Tableau parameterized sheet:
  1. Click
    Sheet1
     from the bottom-left corner.
  2. Drag and drop the following columns in the
    Rows
     field:
    1. Id
       and
      Name
       columns under
      assets
       table.
    2. Id (Timeseries)
       column under
      timeseries
       table.
    3. timeseriesid
       and
      valuenumeric
       columns under
      Custom SQL Query
       table.
      Drag and Drop Columns
  3. Under
    Parameters
    , select
    Show Parameter
     from the
    setParamTimesId
     drop-down list.
    Click Show Parameter
  4. From the right pane, select any value from the
    setParamTimesId
     drop-down list.
    Select Value
    All associated data will be displayed.
    Associated Data
Supported Queries for the Assets and Timeseries Table
The following list provides the supported queries for the Assets and Timeseries table:
  • The system supports `SELECT` statements on all columns like `Id`, `Name`, and so on.
  • It supports operations such as filtering and joining between tables. This includes the use of `WHERE` clauses with various operators such as `=`, `IN`, `LIKE`,'AND', and 'OR'. The 'LIKE' operator is supported by string and text type columns and not supported by numeric, boolean and other type of columns. For more information, refer to the following table:
    Columns Not Supported by LIKE Operator
    Table Details
    Column Details
    assets
    number ( id, parentid, rootid, datasetid ), date and time (createdTime, lastUpdatedTime).
    timeseries
    number type (id, assetid, datasetid), date and time (createdtime, lastUpdatedTime), boolean ( isStep, isString).
    timeseries_datapoints
    number (timeseriesid, valuenumeric) date and time (timestamputc), boolean (isStep, isString).
  • Join operations between the exposed DataMosaix tables are supported.
  • Inner join statements are supported.
Sample Queries
The following table displays the sample queries for the Assets and Timeseries table:
Sample Queries
S.No
DataMosaix Resource
SQL Query
1
Asset
SELECT * FROM public.assets WHERE name like 'testasset-37%'   OR name like 'testtraining-%'   OR name = 'test-13 on "asset-1"';
2
Asset
SELECT * FROM public.assets WHERE name IN (   'testasset-37 on "test-1"',   'testtraining-01 on "test-1"',   'asset-13 on "test-1"');
3
Timeseries
SSELECT * FROM timeseries  WHERE externalId LIKE 'ext%';
4
Timeseries
select * from timeseries where name = '<timeseriesName>'
5
Timeseries Data Points
SELECT * FROM public.timeseries_datapoints WHERE timeseriesid = <timeseriesid>; SELECT * FROM public.timeseries_datapoints WHERE externalid = '<externalid>';
6
Timeseries Data Points
select * from public.timeseries_datapoints where (externalid= '<externalid>' AND isstring = '<boolean>' ) OR ( timestamputc BETWEEN '2024-08-26 08:30:49.039' and '2024-05-26 08:30:49.039' AND  valuenumeric > 100)
7
Timeseries and Assets Join
select * from public.timeseries join public.assets on public.timeseries.assetId = public.assets.id;
8
Timestamp Functions (NOW() and CURRENT_TIMESTAMP())
SELECT NOW(); SELECT NOW() - INTERVAL 1 hour; SELECT CURRENT_TIMESTAMP(); SELECT CURRENT_TIMESTAMP() - INTERVAL 1 hour;
Provide Feedback
Have questions or feedback about this documentation? Please submit your feedback here.