A step-by-step guide to BI reporting
Posted by: Matt David
Reporting with business intelligence (BI) used to require extensive data modeling and deep SQL knowledge in order to find insights. Modern BI reporting is thankfully much simpler.
Business intelligence reporting is the act of visualizing your data in the form of reports and dashboards to make better decisions. Ten years ago, going through this process was a daunting prospect. But with today’s technology, you can break down the process of BI reporting into simple component steps.
If you’re starting from scratch, there are eight steps to modern BI reporting you need to go through. If you already have a BI platform, you can skip on down to step #/4. Everyone else: get your pens and notebooks ready.
1. Assess your data maturity
As your business grows and/or shifts to the cloud, there are four stages of maturity your data passes through. Understanding which stage you’re at will help you understand how to address your specific needs when it comes to BI reporting.
We’ve written an entire book about this maturation process, which we wrote be comprehensive yet accessible. We’ll just do a quick summary here to get you up to speed.
Stage 1: Source data (scattered)
The source data stage is when your data is scattered among a couple of individual sources, like Google Analytics, Salesforce, and an individual database or two. At this stage, when it comes to BI reporting, the bulk of the work has to do with wrangling your disparate data sources. You’re forced to conduct analysis in silos (e.g. within Salesforce alone) without an easy way to combine that data with other source data besides dumping it all into Google Sheets, which can turn into a silo of its own.
Stage 2: Lake (centralized and unstructured)
The data lake stage is when you first start combining data across sources (i.e., blending data) into a central spot where it all exists together, usually in an unstructured way. To do BI reporting at the lake stage, you’ll need to do some preliminary transformations, like column selection or hashing out personally identifiable information, to make sure all your data can work together.
Stage 3: Warehouse (centralized and structured)
The data warehouse stage is when you structure your centralized data in a way that makes it easier to use across your organization. BI reporting at the warehouse stage is a breeze (with the right BI platform, which we’ll get into later), and most of your work will be querying and visualizing data.
Stage 4: Mart (centralized, structured, and specialized)
The data mart stage is when teams across your organization need specialized access to only certain sections of your structured data. BI reporting at the data mart stage is also fairly simple but should be focused on empowering non-technical users to create reports or dashboards on their own.
There’s a lot of nuance we’re glossing over here. The important thing is to recognize which stage your business currently fits within and use that knowledge to help you select the right BI reporting platform.
2. Choose your BI platform
To do BI reporting, you need a (you guessed it) BI platform. A good BI platform fulfills three roles, each of which will help you through the four stages of cloud data maturity. Choose your BI platform based on how well it:
Gathers data
Whether it’s a set of sources, a lake, or a warehouse, your BI platform should be able to ingest all the data you need. The ability to gather all your data is particularly useful for businesses at stage 1, where your data is scattered among a couple of disparate tools. Make sure your BI platform will allow you to blend your data together (i.e., combine it all) into a more cohesive dataset.
Helps you understand data
Your BI platform should provide the ability to query your data using SQL. The easier the BI platform makes it to work with SQL, the better. Easy-to-use query builders, like visual query builders, are particularly useful for businesses in stages 2–4. Businesses at theses stages have a more sophisticated data infrastructure and want more of their employees to leverage it. Yes, we’re biased, but a good example of a BI platform making it easy to understand your data is Visual SQL, which we’ll dive into a little later.
Visualizes data
Finally, your BI platform should have an easy way to turn your queried data into reports and dashboards that you can then share and use for decision-making. The ability to visualize data in order to help you make better decisions is a core function for every BI platform and helps businesses of all cloud data maturity stages.
Again, we’re just breezing through the core concepts. We’ve written extensively on how to choose a BI platform; what’s to take away from here are the core functions of BI platforms that impact your BI reporting process.
3. Understand the difference between reports and dashboards
BI reporting encompasses two different types of visualizations: reports and dashboards. There’s a small but important difference between the two, and you need to understand this difference to do the right type of reporting.
Reports are static and use historical data to predict the future. The purpose of a report is to provide an in-depth analysis of events that have passed in order to inform decision-making and project trends. Because of this more comprehensive nature, reports tend to be more work and are created at regular intervals, like weekly, monthly, quarterly, etc.
Dashboards are real-time and often interactive, focusing on in-the-moment decision-making. The purpose of a dashboard is to provide a pulse check on how certain metrics are performing. Dashboards usually have some up-front setup time involved but take care of themselves from there on out, with the occasional tweak here and there.
For the purposes of this article, the act of “business intelligence reporting” will describe the process of creating both reports and dashboards because they both visualize data to inform decision-making. As we’ll get into with step #5, knowing this difference helps you hone in on the purpose of your BI reporting.
4. Connect your sources
Once you’ve chosen and set up your BI platform, it’s time to hook up your data. There are two overall ways to connect your data to your BI platform:
Direct connection
BI platforms have proprietary connections that do most of the work for you. All you have to do is provide login details and possibly a special key for each data source. A good initial marker for the quality of a BI platform is its library of connections: the more, the better.
SSH tunnel
For everything else, you can have your developers set up an SSH tunnel to securely connect your data source/lake/warehouse to your BI platform. This more technical option is best for businesses that have their databases on private connections or have firewall considerations to take into account.
5. Set Your Goal and Audience
Once you have your data connected to your BI platform, you need to clearly define the purpose of your BI reporting. BI visualizations need to support decision-making. Set your audience according to your answers to these questions:
What do you want to accomplish?
You may just want to slap together a quick ad hoc visualization to illustrate a concept. Or maybe you need to put some “oomph” behind a comprehensive report you’ve been planning for weeks. Either way, a clearly defined goal behind your visualization will keep you on track and keep your work from ballooning on you.
Who is the audience?
Busy business leaders have a lot on their plate and may just need an executive summary. Individual contributors, on the other hand, may want to scrutinize and dive deep. Or vice versa. It all depends on who your audience is, so make sure you’re tailoring every aspect of your BI reporting to their needs.
Why do they need data?
In other words, what decision is your audience making that needs more supporting data? Think through how they’re going to use this data and when they’ll use it. Maybe they need a static, one-time report. Or they might need a real-time dashboard they can check throughout the day. Keep this purpose front and center in your mind as you create your visualizations.
6. Query your data
Next, it’s time to pull in the data you need using queries. The data you use should be determined by your goal and audience.
You write queries with SQL, a coding language designed to interface with databases and data warehouses. Queries select the ranges of data you’d like to compare and allow you to join them for easier comparison. For instance, you can pull marketing qualified leads from one source, like HubSpot, and compare them to advertising spend from Facebook.
SQL is the only way to query data, so it pays to get familiar with it. That said, many BI platforms have proprietary ways of working with SQL to make it simpler for non-technical folks to use. For instance, we created Visual SQL, which allows anyone to build queries with no SQL knowledge required.
7. Visualize your data
Now comes the fun part: creating the charts. In Chartio, once you’ve queried your data (in what we call the “Pipeline”), your results will appear in a table below (which we call the “Result Table”). On the top right is the “Chart Preview,” where you can choose your visualization.
.png?cdnVersion=1578)
Tweaking your visualizations is as simple as working with the Result Table in the same way you would a spreadsheet, then manipulating the automatically generated charts in the Chart Preview. Once you’re done creating your chart, all that’s left is to click the big blue “Save to Dashboard” button.
This process is unique to each BI platform, so we can’t cover everything here.Just make sure you compare thoroughly before selecting one over the other.
8. Share your report/dashboard
The context in which you share your BI reporting is just as important as the report or dashboard itself. In short, you need to share your visualizations in a context in which they can be acted upon. A few options to consider include:
- Keeping the report/dashboard in the BI platform itself, granting access or providing links to others who have logins.
- Sharing the report/dashboard on a TV or monitor.
- Creating an automated email report and Slack alerts tied to certain dashboard conditions.
Embedding the dashboard online, in your own product, or within other tools.