One of the prime uses cases for deploying JDBC as a method of delivering data from STEP to a database is to populate dashboards for data analytics tools like Tableau and Qlik. To aid proper setup of a STEP integration with data analytics that makes use of the JDBC method, a use case specific to data analytics integrations is described below. For more information on setting up a Tableau or Qlik analytics integration in the Web UI, refer to the Visual Integration with Tableau or Qlik topic.
One common approach to configuring data analytics dashboards is to enable display of historical data. As an example, a user has configured a Tableau dashboard to display regional sales data for a specific product. For this user, displaying sales figures for that same product a year ago, or two years ago, would greatly enhance the utility of the dashboard. The way to enable display of historical data using the JDBC method of delivering data is done in the mapping step of the Export Manager and outbound integration endpoint (OIEP).
To implement this users will need to:
- Install required drivers for JDBC
- Configure properties in the sharedconfig.properties file
- Select format of CSV, using the required settings
These steps are described in detail in the Exporting Data via JDBC with CSV Format topic in the Data Exchange documentation.
The following steps, which are described in detail in the text below, are specific to this use case:
- Map Data to include the required action field and calculated attribute for date / time
- Select Delivery Method of JDBC, using the necessary settings
Map Data
With this example, data will be published to a table ('stepdata') that has the following layout:
The table has the potential to contain multiple rows representing the same object, and the 'datetime' column will hold information about the latest STEP revision date. This can be achieved with a calculated attribute, mapped in the export configuration as described below. For more information on calculated attributes, refer to the Calculated Attributes topic in the System Setup documentation.
Below is an example of an 'upsert' action in the Map Data step for the external database table shown above. This setup will direct the process to look for four configured attributes (ID, DATETIME, EAN, and Consumer Short Description) in the destination database table, and either insert the object (if not found), or update the object (if found):
- The column headers in the exported CSV file must match the table column headers in the destination database. Map a constant value on the Header row for each mapped object to supply the external database table column header. For example, notice that the external table has a row named 'description' but is mapped to the STEP attribute 'Consumer Short Description.' The header parameter is used to make the exported data match the external table. For details about using the constant value data source, refer to the Constant Value - Data Source Outbound topic in the Data Exchange documentation.
- Create an additional mapped column with the header of 'action' and the appropriate value of either delete or upsert. Use the transformation button to change the text displayed for both the Header and the Value.
- Create a calculated attribute using the function 'revisioneditdate()' and make it valid for your exported products. Then map the calculated attribute for export and update the header to match the column in the external table. Details on the 'revisioneditdate()' function are included in the Other Functions topic in the Resource Materials online help documentation.
The example above uses the 'datetime' header, which is mapped to the 'Last Edited' calculated attribute. In addition to the other steps required to enact the JDBC method, the 'Include Calculated Attribute Values' parameter on the 'Advanced' step (shown below) must be checked.
If using an OIEP, one output template would be created to handle Create and Modify events, and a second output template would handle Delete events. In this way, separate mapping is available for each, allowing for one to include the upsert action and the other to include the delete action.
Select Delivery Method
The appropriate delivery configuration for this scenario can be found below. Notice that both the 'id' and the 'datetime' column values are used as keys for the upsert action, meaning both values must match to determine whether to insert or update the object. When an exact match for both keys is found, the object is updated; when no match is found, the object is inserted.
The value of this configuration is that if multiple instances of an object with the same ID but different datetime values (representing, for instance, different revisions of the object) are exported, the upsert action will insert into the table all instances of the single object. The effect of this setup is that various revisions of the same STEP data will be published to the table, giving users the ability to view historical data for objects in a data analytics dashboard. Further, if the Web UI has been configured to display data analytics dashboards, then Web UI users can view historical data on STEP objects.