QuickBase documentation simplified with Power BI
Power BI & QuickBase – Part 1
Documentation is a nightmare, a necessity, but a total nightmare. Over the next several blog posts we will show how to easily create documentation for any relational database or data model that allows you to query its schema. For this post, we are using QuickBase and Power BI Desktop.
First, a quick primer on QuickBase. QuickBase is a cloud-based, low-code app, similar to AirTable or Zoho Creator. A recent Datalore project required us to diagnose an existing QuickBase app and rebuild it using traditional relational database techniques, with the goal of establishing a predictive costing model. Luckily for us, QuickBase provides excellent API access and documentation, which we leveraged to create a Power BI report analyzing the existing app and documenting our new application.
Again – we can use this method for any database that allows you to query its schemas, like SQL Server, MySQL, or SAP HANA.
Through the course of this series, we will use three concepts
A database schema is your blueprint. It shows you how tables are configured and how they relate to each other. Like a blueprint, you can use the schema to understand the architecture of a database and rebuild if necessary.
Metadata is data about data. There are three types – descriptive, structural, and administrative. We will use all three in our documentation.
The best way to maintain documentation is to keep as much information in the source as possible. Our reporting is not storing the data in a separate warehouse. Instead, we will read from the database itself and present in a user-friendly report.
So often we see projects with disconnected documentation. How many times have you asked for documentation only to be sent, if you are lucky, a static file that is incomplete and out of date? These files are cumbersome to maintain and stale as soon as they are published.
I love self-documenting because the information is stored at the source, inside your data model or database, making it easier to maintain and very, very hard to lose.
Self-documentation in a gif:
Connecting to QuickBase
Now let’s connect to our QuickBase app! We are going to use the following API call:
This call will be made inside the Power BI Desktop application.
Since we want to keep things easy and make this report easy to use for new apps, these will be parameters in our Power BI report. In the query editor, select ‘Manage Parameters’ to open the parameter management window.
There are four values common to both API calls, domain, database ID, app token, and authentication ticket. Each of these values will have their parameter. These parameters are the only part of our report that is unique to the QuickBase app! So when you want to change to a new app, change the parameter values as needed.
Make sure to enter your app’s data in the Current Value section of each parameter. If you need more information on Power BI parameters, Microsoft’s PBI team has a great blog post here.
Now we know our API call from the documentation, and we have the parameters configured, we can get the schema data from QuickBase. Again, in the Power BI Desktop query editor, from the New Source menu select the ‘Web’ option.
In the popup menu, select the ‘Advanced’ tab.
Power BI makes building the URL call easy. Add the URL parts, alternating between text and parameters, until the URL preview looks like this:
After selecting OK, the parameters will be replaced with the current values, and your query will return in the Power BI window.
From here, you can navigate through the XML structure by expanding the table and manipulating the data. Data manipulation in Power BI is a much longer topic, and for our purposes, we will skip straight to the result – sort of like a cooking show where a prebaked cake comes right out of the oven!
We are going to create three queries.
It is important to keep the TableID as we will use the ID as our key to relate each query. Note that there is a column with the table description. This is maintained directly in the QuickBase app and is an excellent example of self-documentation. As new tables are created or their purpose changes, the only requirement to keep our report fresh is that the user or developer update this description. This happens natively in the database application and the closer to the source the greater chance of success in keeping the documentation up to date.
A list of all columns or fields in each table. Here we are showing only a fraction of all attributes for each column. Feel free to bring in others, such as default value and aggregation behavior, as needed.
A list of reports associated with each table. Note that, as in the field list, we are keeping the TableID in this query.
There you go! We have used the QuickBase API calls to bring schema and metadata information into Power BI! In the next post, I will show you how to take this information and create a simple dashboard which we will embed directly into the QuickBase app.
Are you ready to unlock the potential of your data?
We are business minded individuals who love data. Contact us today to find out how Datalore can transform your data into actionable insights.