Exploring the Technical Side of Wdata

tl;dr

Wdata is similar to a traditional relational database (RDBMS) like Postgres, SQL Server, or Oracle Database but with some noted differences.  

‍

Wdata has three primary components:

1. Tables: Store data
2. Queries: Ask questions of the data stored in Wdata and make it available to the reporting solutions of the Workiva platform
3. Views: Different that traditional RDBMS views; limited utility with Workiva reporting solutions; primarily an ad hoc analysis tool

‍

Learn more about the benefits of Wdata when used in concert with Workiva reporting in our Workiva’s Data Management Suite article.  

‍

Tables

Not unlike a traditional RDBMS, tables are considered the data storage mechanism of Wdata; however, data is not actually stored in tables.  Data is stored in text files and when the data needs to be used (see queries) the file is read into memory creating a “virtual” table populated with the data stored in the text file.   

‍

The key takeaway for tables is that they make data available in a structured (rows and columns) format.  The rows are the actual data and the columns provide information about the data.  For example, consider a shopper that goes to Best Buy and purchases a new television before the Super Bowl.  The data is the transaction (purchase) and the attributes of the data are the purchase date, price, TV brand, TV model number, TV size, Best Buy store number, and so on.  

Data that is structured in this manner is well suited for Wdata tables.  In this post, we’ll refer to this table as Transactions.

‍

Queries

Queries are the primary mechanism to utilize the data stored in Tables.  Of the three Wdata components, Queries are the most similar to a traditional RDBMS.  Wdata queries use Presto Structured Query Language (SQL) and can perform many of the same core operations that a query run against a traditional RDBMS does.  

‍

Another way to think about a query is that they ask questions of the data.  For example, how many LG TVs were sold by store number 536?

‍

Queries have two required elements:

‍

• The information (columns) that is needed about the data
• The table from which data (rows) should be retrieved

‍

A query in its most basic form:

‍

Select Store,Brand from Transactions

‍

The columns are Store, and Brand and the data will come from the Transactions table.  As currently written, this query returns the following data:

‍

‍

This data may not be terribly useful.  Queries generally leverage additional functionality to refine the data including filtering, sorting, or applying logic including math operations such as aggregating.  Let’s return to the question - how many LG TVs were sold by store number 536?  In this case we need to refine the data:

‍

• Filter by store number and brand 
• Add up each of the data rows to get the total number

‍

Where clauses enable data to be filtered:

‍

Select Store, Brand from Transactions
Where Store = '000563' 
And Brand = 'LG'

‍

This query returns the following data:  

‍

‍

Finally, to provide a response to the question asked, math needs to be applied to summarize the results.  This is accomplished by combining a function (COUNT) and the GROUP BY operator.  

‍

Select Store,Brand,Count(Brand) as Quantity_Sold from Transactions
Where Store = '000563' 
and Brand = 'LG'
Group By Store,Brand

‍

In this final state, this query returns data that meaningfully answers the question, how many LG TVs were sold by store number 536?

‍

Queries are the components that actually make data stored in Wdata tables available to the reporting solutions of the Workiva platform.  

‍

Views

Of the three primary Wdata components, Views are the most dissimilar from the RDBMS object that share the same name.  In a traditional RDBMS, views are powerful mechanisms that enable data to be retrieved from the database and are essentially another query mechanism in overly simplified terms.  RDBMS views can retrieve data from tables, queries or even other views.  

‍

Wdata views differ in that they can only retrieve data from a Wdata query.  Moreover, Wdata views do not have the ability to combine results from multiple objects in the way a traditional RDBMS view is able.  Wdata views are more of a presentation and analysis mechanism than a query mechanism.  They allow the results of a Wdata query to be displayed as a chart or grouped similar to how a pivot table organizes data.

‍

Unfortunately, the results of a Wdata view are not able to be directly connected to reporting solutions in the Workiva platform so they have limited utility.  There have been discussions within the Workiva R&D team about enabling Views to be consumed directly within a Workiva platform solution but as of this article’s publication that has not happened.  

‍

Summary

In this article we reviewed the three primary technical components of Wdata - Tables, Queries, and Views.  We discussed the differences between each of these components and their traditional database counterpart.  We also explored how queries can provide meaningful data to be used by Workiva reporting solutions.  

To gain a deeper understanding of the functional value of Wdata, please be sure to review our Workiva’s Data Management Suite article.