TIBCO Data Virtualization

Background

An enterprise contains data stored in various applications as part of different data storage implementations. A Data Virtualization (DV) technology allows for providing a single view of the data to a customer. It combines, correlates and transforms if needed, so that the customer may have a holistic view of the data, from a higher vantage point. Usually, DV supports multiple data source types, for example, connecting to data storages through JDBC drivers or connecting to file systems with support for various file formats. Some advanced features of DV tools allow for a REST call to retrieve data from the views, based on the parameters.

In the example below, we take you through connecting to a couple of data sources, (1) Database table with Vendor information and (2) An XML file with sales generated through the Vendors. In the resultant view created on TIBCO DV, it can be seen that a consolidated view of the sales is obtained.

Steps

A database contains one or more tables. Each table is identified by a name, contain records (rows) with data in tabular format.

Below is a section from the Vendor table in a SQL Server Data Base. In the following steps we shall see creation of a folder in TIBCO DV and creating appropriate views.

Creating a folder in TIBCO DV

Select New> New Folder in the shared environment or File > Folder
Name the Folder as demo

Configuring SQL Server Connection in TIBCO DV

Make sure that the appropriate driver is installed and configured in the system
Right click on the Demo folder and select New Data Source or Select File >New> Data Source and search for Microsoft SQL Server 2016 and click Next
Name the Data source as local dB and provide the below details to connect to SQL server data base.
a. User Name – sa
b. Password – 12345
c. Host – localhost
d. Port – 1433
e. Database Name – sales
Provide and click ok

Adding a table in TIBCO DV

Right click on the local_db data source and then click on Add /Remove Resources to add the required table into TIBCO DV as shown below
Right click on the Demo folder and select New New View or Select File >New> View
Name the view as Vendor_view
Drag the vendor table onto the vendor view canvas and click execute as shown below

XML Data Source

The below XML file contains data related to Sales. The following fields are available as part of the XML - Sales ID, VendorID, Productid, LastMonthSales. in-browser-as

Importing the XML file in TIBCO DV

Right click on the Demo folder and select New Data Source or Select File >New> Data Source and search for File –XML as shown below
Name the data source as sales_details and provide the appropriate root path of the file where it is located and click next
Since the data stored is in XML format, we need to apply transformations to convert the XML data into tabular format data using transformations

Transforming the XML Data Using Transformations

Right click on the Demo folder and select New Transformation or Select File >New> Transformation and the transformation type as XSLT transformation and click Next
Locate and select an appropriate XML source in the resource tree, we will name the transformation as sales_view and Click Finish
Open the transformation, Right click on the source columns and click the create link and target button as show below
Save the transformation and Click on execute the data transform as shown below, this will result that the XML data is transformed into the tabular format.

Creating a common view

Now we will create a common view based on the sales view and the vendor view (we will join the two views based on the column VendorID)
Right click on the Demo folder and select New View or Select File >New>View
Name the view as combined view
Drag the sales_view and the vendor_view onto the working canvas as shown below.
In the Grid tab section, define the columns which are used for displaying a view
Click on execute the view as shown below.

Aggregating Total Sales based on Vendor ID

An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement.

In this demo we will create a view to calculate the total sales based on Vendor ID

Right click on the Demo folder and select New View or Select File >New>View
Name the view as aggregate_1 view
Drag the combined view onto the working canvas which we have created it earlier.
We will use the Group by option provided in the Grid tab to group the values based on VendorID
Right Click on the column and select the aggregate function sum on the LastMonthSales column as shown below
We will rename the LastMonthSales column as TotalSales using Alias key word and Click on execute the view as shown below, this will result in the total sales provided for a given VendorID.

Aggregating Total Sales based on Product ID

Right click on the Demo folder and select New View or Select File >New>View
Name the view as aggregate_2 view
Drag the combined view onto the working canvas which we have created it earlier.
Group the Values based on ProductID
select the aggregate function sum on the LastMonthSales column as shown below
Rename the LastMonthSales column as TotalSales and click on execute the view as shown below, this will result in the total sales provided for a given ProductID.

Conclusion

As the final consolidated view through TIBCO DV shows, it contains the information from both the data sources that is correlated and ready with holistic view. In the following views, the aggregation may be performed based on a certain field and the resultant values may be added as part of another view.

The way TIBCO DV may be used to correlate and perform aggregations based on data from multiple sources, provides powerful insights for business users. It will also eliminate the need to create another repository to hold data from multiple sources, and only acts as a view that connects the data.

Blog