In the previous blogs, I shared how to extract data from API and parse JSON directly in Tableau Desktop. In those blogs, I talked about what is API, and JSON structure, and how to parse data from simple and complex JSON structures in Tableau Desktop. You can read it here (part 1, part 2). The purpose of those blogs is to show the method when you send the request, receive the response, and parse JSON.
How about for a real project where the data sources are published in a server with credentials information? Are there any differences between extracting data from different APIs?
Tableau already developed some APIs for users to use and each API serves different purposes that users want to get from the response. Some Tableau APIs are Embedding API, Rest API, Dashboard Extensions API, and Metadata API, ... You can check and read more information here.
In this blog, I will share a new API from Tableau which was first introduced at the Tableau Conference 2023. That API is VizQL Data Service (VDS). Although the current version is a developer preview release, developers can explore some features from VDS now. I will go through:
1/ What is VizQL Data Service and its benefits?
2/ Send a request and get a response from VDS API with Postman
3/ Compare calculations in Tableau Desktop and VDS API
Are you ready to explore the VizQL Data Service? Let's get started!
1/ What is VizQL Data Service and its benefits?
According to a blog from Tableau - VizQL Data Service: Extend Your Data Beyond Visualizations, VDS is an innovative API that you can access the published data source through programmatic queries rather than visualizations. If you are a Tableau user, you already know that we drag and drop the dimensions and measure fields into Rows or Columns to explore the dataset or build visualizations in Tableau Desktop. In VDS, you can send a request by writing a query and get the response with the result in JSON or other formats without creating any visualizations.
The client will send the request by writing a query. The query will include the connection data (Tableau Server name, site ID, the published data source name) and the main query including the field name, calculations, and filters. Then, the server will receive the request from the client and will check the authentication, and validate data. After that, it will send back the response with a status code. The client can retrieve the data from the response.
Fig. 2 is an example of sending the request to the VDS API (on the left) and the result data from the response (on the right). Then, you can save the response as JSON is an ideal format for transferring data between a server and a web app.
What are the benefits of using VDS? Why to use it?
Three main points are mentioned in the Tableau's blog. They are:
- Enable swift decision-making with automated data retrieval and analysis
- Empower efficiency for developers and end users
- Break free from visualizations
Automating data processes is ideal for modern businesses now. Imagine a scenario case like you have to import the data source and drag some tools to transform the data. It could lead to a long workflow if you meet a complex problem. Instead of building a long and complex workflow, you can use VDS to transform data by writing the query to retrieve data and apply the response in many flatforms.
Developers can use other tools from Tableau to extract data and publish the data on the Cloud. Then, apply the query to retrieve data from published data sources in VDS. It creates an automated process from extracting data, loading data and transforming data before using that data for creating visualizations or building a Machine Learning model.
Developers don't need to use visualization to analyze data by drag and drop fields, but can get the result from the calculations. It would save and reduce the time for querying the calculation in Tableau Desktop.
2/ Send a request and get a response from VDS API with Postman
In this section, I published the Superstore data source from Tableau on Cloud. That Superstore data source includes 3 tables (Orders, People, and Returns). I followed the instructions from the VizQL Data Service API Reference page to set up the environment in Postman. You can check it here.
In Postman, you can send the POST method to request data from VDS API. Furthermore, on the right window, you can also choose the language you are working on. It will convert the query into that language. For example, I work on Python language. I chose Python - Requests (using the requests package). It will show the code snippet in Python and I can apply it to my Python script to return the response data (Fig. 4). Then, you can flatten the JSON objects, and arrays as I shared in the previous blogs and save them in a data frame or you can pass that JSON data into your web app to show data.
From the query, I can get the result by only using the aggregate function similar to Tableau Desktop without using any Python functions. That's cool!!!
3/ Compare calculations in Tableau Desktop and VDS API
I used the joined dataset which contains Orders, People, and Returns tables, and published it to Tableau Cloud.
Simple query with aggregate function
For example: Find the total sum of sales of each category which has returned in each region.
In Tableau Desktop, I will drag region, category, returned, and sum of sales into Rows (right side of Fig. 5).
In Postman, I only need to write a query to list all dimensions I want to get in the result such as Region, Category, Returned, and Sum of Sales. Because Sales is a measured field, I used the function key with the value "SUM". In the filters key, I set the filter for the column "Returned". The filter type is "SET". There are 4 different filter types, I will talk more in another blog. The value for this filter only includes the "Yes" value, so I set false for the exclude key.
After sending that request query, you can see the Response result (in the middle of Fig. 5) and compare it with the result in Tableau Desktop. They are all similar.
Custom calculation with Top N
I want to find the top 10 States/Provinces that have the highest number on Profit Ratio. In Tableau Desktop, I have to drag State, Profit, Sales, and Profit Ratio into Rows. Then, drag the State/ Province field into Filters to set the Top 10 by the Profit Ratio field and sort the State by Descending with the measure field is Profit Ratio.
In VDS, I set the column that I want to show in the result.
- If the column is a measure field, then I will set it with an aggregate function.
- A new column called Profit Ratio with a custom calculation. Note that, for the aggregate function, I used the key "function" while the custom calculation used the key "calculation". In a column, cannot exist both function and function.
- In the filters, the filter type would be "TOP" for the column State/ Province.
- You will set the number N filter with the key "howMany", in this case, the value is 10.
- Set the measure to filter by using the "fieldToMeasure" key with the value is the calculation that we applied in Profit Ratio column.
- To sort the data table by using the key "direction" with the TOP value for Descending. BOTTOM for Ascending.
In the result, you can see the result in VDS is the same as the result in Tableau Desktop.
Level of Details (LOD) calculation
One issue that comes from the LOD is when adding the filter, the value doesn't change. For example: I want to calculate the sum of sales of East and West regions only. However, in my view, I also have many dimension levels (Region, Category). So, I used FIXED LOD for the Region field to calculate the total sum of sales.
If I drag the Region field into Filters and keep East and West only, the FIXED LOD of the SUM of Sales does not change. The reason is the Dimension Filters order after the Fixed LOD. So, to apply the Dimension Filters for the view, we need a higher priority order for the dimension filters by changing to the context filter.
In VDS, I created a new column called Total Sales in East and West with the calculation is a Fixed LOD for the sum of sales. In the filters, I set the filter on the region with the values East and West. Then, add a new key called context and set it to True.
You will see the result similar to the result in Tableau Desktop (Fig. 9).
I hope this blog provides you with an overview of the VizQL Data Service (VDS) and its benefits. I’ve also explained how VDS works, from sending requests to receiving responses. VDS is a game-changer, enabling users to analyze data with calculations by simply writing queries and pulling data from the VDS API—without the need to create visualizations. While there are some limitations in this developer preview release, I believe VDS will continue to be developed with even more exciting features in the future.
See you again in the new blog!