If you’re an existing user or you’ve been keeping up with our previously released Cogent DataHub blogs, you’re already aware that Cogent DataHub is renowned for its extensive connectivity options to industrial data systems, but most importantly its secure, high performance, scalable, firewall, DMZ, & proxy friendly data transfer within premises and to/from the cloud. However, it’s worth emphasizing that DataHub’s connectivity includes MQTT, IoT, OPC DA, OPC UA, OPC UA Alarms & Conditions, OPC Classic Alarms & Events, Modbus, Excel, ODBC & more. These methods of connectivity help users realize that with DataHub, data has the freedom to seamlessly and securely traverse across various platforms. We truly believe that "once the data is in DataHub, it can go anywhere, securely".
In this blog post, I'll provide insight into using DataHub’s Transform property to manipulate data that is being logged to a database table. To follow along with this blog, as a prerequisite, you will need to have an ODBC database configured and have DataHub set up to be writing to that database. For more information on connecting and writing to a database, please read our blog “DataHub – 5 Quick Steps to Writing to a Database Table”. Unlike loggers that only work with OPC data sources, DataHub’s capability to securely tunnel data and aggregate from diverse sources besides OPC empowers many different types of applications.
Throughout this blog we will be using TOP Server’s simulator driver to simulate the data to log to our ODBC database. The driver simulates live data by traversing through all the records in the selected user table at a rate set by the user. For more information on setting this up, you can visit the Advanced Simulator Help File.
What is the Transform(x) Property?
When it comes to database management, the Transform(x) Property emerges as a valuable tool for users seeking to manipulate their data entries according to personal preferences and business demands.
This allows you to modify the entry or to insert a text string. With x being the value of the Item + Property combination.
The Transform property, equipped with the capability to accept scripting commands, serves as a versatile tool for modifying database entries in several ways. As we delve into 3 use cases for the Transform(x) field, it's important to note that the potential applications of this property are not confined to these examples, and the possibilities for customization are virtually limitless. Let's explore the flexible ways in which the Transform property can elevate your database logging experience.
Use Case #1 – Setting a Value to Null
In our experience we have encountered user applications where they wanted to have two databases with the same schema, even if one lacks data for a specific column. Fulfilling this need would involve inserting the term "Null" into the table at the corresponding index.
The below database table is configured to show null in the NullPlace column, with the time showing in the TimeS column. To get “Null” to show, we must set the Transform(x) property to nil (the scripting language’s keyword for null).
In order to simulate the data for this database, as mentioned above, we will be using some tags that are simulated from TOP Server. As you can see above, this use case utilizes the String Tag. This tag is set to “Example”, but using the transform we are going to override this with “NULL” when inserting into our database.
After running a SQL query on our data, we see that we get the desired NULL in our NullPlace column along with the timestamp displayed in TimeS.
Use Case #2 – Custom Functions
Incorporating custom functions written in DataHub's scripting language into your data processing workflow can greatly enhance its efficiency and accuracy. For instance, leveraging the example custom function below named "convert_time()" within the Transform property enables you to format the time according to your preference.
While this use case is relatively simple, the possibilities are endless. For example, consider a scenario where your dataset contains columns for "temperature" and "humidity." To derive insights effectively, you can implement a "calculateMean()" function within the Transform(x) property for both columns. This function, tailored to your specific needs, could be used to ensure precise computation of the mean temperature and humidity values.
Below is the outcome of using our convert_time() function, where TimeSF is the formatted/altered timestamp and TimeS is the timestamp before using the transform property. In this use case, TimeS is shown in Central European Time (UTC +1) and the TimeSF is formatted to show in Eastern Standard Time (UTC -5) through our custom convert_time() function that successfully accounts for the 6 hour time difference. Our convert_time() function does not consider milliseconds.
By integrating custom functions into your data collection process, and performing the transformations as close to the data source as possible, you streamline analysis while maintaining data accuracy. This empowers you to unlock deeper insights and make informed decisions based on reliable information with a fully customized approach.
Use Case #3 – Using Built in Methods
The DataHub scripting language comes with many built in methods. The Gamma Reference section of the help file is where these methods can be found. We will look at different examples to offer some ideas on how these methods can be used.
Convert Values to a Percentage
The format in which data is received and logged can be subject to various interpretations. In this use case, we will be interpreting the data format from a tank monitoring system, let's say the liquid height is conveyed as a decimal ranging from 0 to 1. If we aim to represent this value as a percentage, we can achieve that through the Transform property. We'll employ a mathematical function cast as a string within the Transform property to convert the decimal to a percentage format and round that value to avoid any precision calculations on the original float value:
string((round(x * 100)), "%")
While similar to the custom functions covered in Use Case #2, this is leveraging a single Gamma command within the Transform(x) field instead of a separate script defining the function.
For the data in this example, we will be updating the tags in our TOP Server Simulated device. As you can see above, this use case utilizes the FormatDecimals Tag. This tag is set to “.86” by default and using the transform we are going to log this as “86%”.
The SQL query below demonstrates the transformative capability of the 'transform' property on the data. You can take this use case a step further by also formatting the floating point value, which we will cover later.
Setting Value to a String
In addition to modifying data, another valuable application of the Transform property is logging specific strings as desired. For instance, if we intend to log a predetermined string instead of the default value such as the point name, we can achieve this using Transform(x). For example, setting Transform(x) to "SWTB Office" would log the string SWTB Office instead of the value of a selected DataHub point.
The SQL query below exemplifies the dynamic nature of the 'transform' property, allowing for seamless control to suit any desired value.
Format Floating Point Values
Precision is indeed critical in data monitoring; a decimal point could be the difference in distinguishing between valid measurements and defects. To format a floating point value according to specific requirements, you can utilize the Transform(x) capability to explicitly define the desired format for the DataHub.
Suppose we aim to ensure that floating-point values have an overall width of 4, with 2 digits of precision after the decimal point. This objective can be accomplished by employing the following expression within the transform property: format("%4.2f", x)
In order to simulate the data for this use case, we will be utilizing the FormatDecimals Tag. This tag is set to “12.3457” by default and using the transform we expect this to log as “12.35” by rounding using the format command.
The SQL query below shows the transform property in action by showing both the formatted float as well as the tag’s original precision floating point.
Conclusion
In conclusion, the use cases of the Transform(x) Property are just the beginning of an expansive realm of possibilities with the Cogent DataHub. With the use cases above as well as the scripting capability, the true depth of the transform property’s versatility becomes apparent. From simple data adjustments to complex transformations, the Transform(x) Property opens the door to endless opportunities for innovation and efficiency in data management. When combined with the secure data transfer capabilities, the range of applications for Datahub is extensive.
We hope this blog has provided meaningful insight into the transform property and its use with Database Configuration within Cogent DataHub that makes anything possible.
Ready to try out logging and querying a database for yourself? We believe in having you try our software for yourself, and helping you get started quickly. Check out our video tutorials on writing to a database and reading from a database, and download a free trial to start using DataHub with your own systems.
As always, please contact our support team with any questions, and don’t forget to subscribe to our blog to find out about the latest updates to Cogent DataHub.
