QuickBase documentation Simplified with Power BI, Part 2
Where did we leave off?
A this point in the tutorial, you should have a PBI model with three queries – one each for your QuickBase schema’s tables, fields, and reports. If you need a refresher or have not read part 1 of this series, you can catch up HERE
Now let’s look at the data structure so far. You should have three tables – fields, reports, and tables. PowerBI detects foreign keys and in this case should have already established the connection among the tables based on TableID.
Open the Relationships view on your model and it should appear like below:
If your tables are not connected use the Tables query as our main record (like a fact table for those familiar with data warehouses) and link it to the Fields and Reports queries (to extend the data warehouse analogy these would be the dimensions). The easiest way is to use the interface, simply drag the TableID from Tables → Reports and then again from Tables → Fields.
Once the tables are connected the most important step is to hide the key fields.
Why? Let’s look at how the dataset would appear to the user now. Below I searched for TableID – you can see that it appears three times for the user. Even though using any of the values would return the same results, this is confusing for the user.
In rare instances you might expose one of the key fields for the user. For our example, TableID is not useful so we will hide them all.
Again in the Relationships View, right-click on each TableID and select ‘Hide in Report View.’
Ready for the fun part?
Data connection? READY!
Data model? READY!
Time to visualize? READY!
Data visualization is the best part of any dashboard project but it typically represents the lowest portion of time spent. Our data model is very simple, but most take many hours, weeks, or even months to establish properly.
Before starting any dashboard ask questions. What information does the user need to know? What are the top 3 things my boss always asks me? What issues get our department into trouble?
Here are the question we are asking with this report:
Which tables house the most data?
How many reports does my app have?
How are my tables connected?
To answer these questions, sketch out a plan for your dashboard, making sure each visual addresses a question to be answered:
Below is the report once all the visuals have been configured according to the template. All visuals are standard to PowerBI except the force-directed graph, which we are using to visualize the connections between tables.