METADATA EXERCISE #3: Data Calculations

You saw the title but then you got nervous... Don't go anywhere we're doing this together!

Metadata and GraphiQL Recap

If you read, Tableau Online - Metadata Exercise #1 or #2 then the next few paragraphs are all a recap. You can either review or skip down to the query and run Metadata Exercise #3. If you are just getting started in metadata, the next few paragraphs will guide you through.

If you are thinking, what is Metadata and where do I begin? I have just the video for you to watch. Back in 2021, Anya Prosvetova introduced us during HER DATA LEARNS: METADATA API. This not only inspired me but helped me solve a practical issue for my organization at the time. It has taken me some time but I finally have a Sandbox Tableau Online account, Query, and Dashboard Template to share as we walk through this first query together.

Before I jump in let's talk a bit about the Sandbox Tableau Online account I am using. This was all possible to create thanks to the Tableau Developer Program. I was able to create a free Tableau Online account for the purpose of learning. Do you want to know more? Check out the link to join the program for free and get started.

Now, let's talk about GraphiQL. What is GraphiQL? "A query language for your API

GraphQL is a query language for APIs and a runtime for fulfilling those queries with your existing data. GraphQL provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more, makes it easier to evolve APIs over time, and enables powerful developer tools."

To jump to GraphiQL and query against your Tableau Online account you would replace everything after the # in your Tableau Online URL with metadata/graphiql/(see the example below).

Example Tableau Online: https://10ax.online.tableau.com/#/site/herdatadev628592

Example GraphiQL Link: https://10ax.online.tableau.com/metadata/graphiql/

You should see the window below if you used the correct URL.

If you made it this far, WOW! You already have accessed the GraphiQL and you are one step closer to querying against Tableau Online.

METADATA EXERCISE #3: Data Calculations

Have you ever wished there was a quick and simple way to pull all of the fields and data calculations from Tableau Server or Tableau Online? Well, I sure have. This might just be the most useful Metadata query I have used thus far. My interest was to create a data dictionary and repository of calculations to not only have a quick resource for assembling future dashboards but to also create an audit trail to make sure when we sale sum of sales that we are all looking at the same fields and calculations. This serves developers on the backend but also serves end-users on the front end.

The Query:

Copy and paste this script into GraphiQL and hit the play button.

query Calc {

 workbooks   {

  name

projectName

    embeddedDatasources {

      name

    id

      fields {

        name

        ... on CalculatedField {

          formula

        }

      }

    }

  }

}

If it ran correctly you should see the window below appear. If so, well done. You just ran a Metadata API query against Tableau Online. Now, we can jump to the exciting and sweet stuff.

Converting to a JSON:

These last few steps are simple. We right-click, select all and paste it into Notepad or Notepad++. Ultimately we want to save this file as a JSON but can't automatically from GraphiQL. I prefer the Notepad++ option myself. Once you save the file as a JSON you should notice the text colors change. Saving as a JSON supports the node structure which will be important when we pull this into Tableau to create the dashboard.

Selecting Schema Levels in Tableau:

Here is why it is important. When you pull the JSON file into Tableau, the first window you will see is 'Select Schema Levels'. I always check the top box to include all Schema Levels.

Once you select ok, you are connected to the JSON file and just successfully pulled Metadata and connected it to Tableau Desktop. From there run wild with creating a dashboard and insights you might need.

Tableau Workbook Template:

To save you some of the work I created a Tableau template for you to use, Data Calculations available on Tableau Public for download. Just download the workbook, replace my data source with your own JSON file, and you are ready to go.

That's a wrap:

Let's talk about the practical uses of this dashboard. This dashboard is a comprehensive formula repository for data governance, providing easy access to categorized and documented formulas.

Intrigued? Keep digging into metadata to see all what's available.