Welcome! This guide on Business Intelligence interview questions and answers aims to prepare you for your next BI career endeavor.
It is divided into a General questions section targeting your experience and domain knowledge, a Front-end questions section that tests your data visualization expertise, and a Back-end questions section that focuses on your knowledge in data warehousing and ETL.
The traditional question.. Your first opportunity to impress!
First, start by describing your background and then the most significant BI role. Give that the most focus. Then, brief the interviewer on your other BI roles, if any.
If you have no BI experience, but you do have relevant experience, like data analysis, explain that as it relates to BI.
The purpose of this question is to speculate how well you would fit in and work with other BI team members, whether it is the BI analysts, ETL developers, … or any of the other roles.
Explain how well you would work within a team and try to give examples.
Acquiring business requirements from end users or product owners, and translating them into technical requirements for the team to implement is a valued skill in Business Intelligence.
Illustrate how well you communicate with users and POs, how well you work with team, and how concrete the Business Requirements Documents that you prepare are.. A positive impression is guaranteed 🙂
Your previous experience in QA testing of BI applications demonstrates that you are committed to quality and that you can ensure that the products being rolled out are robust and up to the standards.
Choose an interesting and comprehensive project.
Explain your approach to the project, the process you followed, the achievements and the end result/impact.
The interviewer here is trying to assess your passion and ambition for BI.
Express your career development goals and make the interviewer understand that you are serious about adding skills that will make you a more valuable BI resource.
This one is a domain knowledge question.
If you are an expert in the industry you are seeking a position in, it will make you a very attractive candidate.
Knowing the KPIs used, let’s say in the financial industry shows that you are an analytical domain expert that knows how to extract value out of data, how to measure performance and how to deliver business-specific insights.
Your answer should be something similar to this:
“Descriptive analytics helps us look back through historical data and learn lessons that would drive future decision-making.
On the other hand, predictive analytics attempts to forecast the future by providing estimates of important metrics or alerts about specific events with high probability of occurring.”
Expectedly, you know a few ones. Tableau, Power BI, Qlikview, Microstrategy, Domo, … are some you can name.
As for the preference, provide a valid reason. For instance many BI developers like the slick design of Tableau’s front-end, while other love the associative model of Qlikview, linking the objects through the dimension across the dashboard.
“Guided analytics is about providing ready-made business intelligence applications for the end user, where they are guided by the dashboard’s design to extract insights in a structured and easy manner. Whenever the user clicks or makes selections, the dashboard responds by filtering results accordingly. In guided analytics, the user usually is deprived of the ability to create their own visualizations and calculations.”
For comparison, the most common chart used is the bar chart, as it allows for a simple and elegant way to compare values across many categories.
Moreover, for distribution, a histogram would be the best candidate. It visualizes data into intervals (bins), which allows for the probability distribution to appear, at least approximately.
This one is a bit of a trickster.
Most visualizations act on 2 dimensions or levels. To add a third dimension, you need to find another method to visualize the data.
One example I can give you is the bubble chart, which adds an extra layer to the classic scatter plot, by replacing the points with bubbles and using their size to visualize a third dimension.
In bubble charts, you can even add a fourth dimension by using the color of the bubbles to categorize them.
In BI interviews, sometimes the interviewer will go further to ask about less common charts, to see if you would be familiar with them.
A Mekko Chart is used when you want to visualize distribution across two dimensions. It looks similar to a stacked chart except the the bars have varying dimensions to allow for visualizing one more dimension.
Let us say you were visualizing the distribution of sales across three products, using a stacked bar chart.
Now, you want to visualize the sales distribution not only across the product categories, but also across the product brands. This is where the Mekko chart steps in and allows for using the column width, and not just the column height (like the stacked chart does) to visualize one more dimension.
Here is an example of a Mekko Chart from AnyChart:
It is better to familiarize yourself with all the common and uncommon charts out there, before stepping into your interview.
Benchmarking is about comparing the metrics you measure for a business, against industry values and best-practice values.
It’s importance lies in evaluating how well an organization is doing, relative to itself (a self-benchmark), relative to competition in the market, and relative to the best values in the metrics under measure. It allows the organization to understand where it stands and how long it still has to go.
One example about benchmarking is a project I did for a rural hospital whereby I benchmarked their cost of care and their length of stay against other hospitals having approximately the same bed size, and also located in a rural region.
It turned out that the hospital was doing quite well in terms of controlling their costs, even though their average length of stay for the majority of diagnoses was higher than the overall average across the other hospitals in the study.
From there, medical practices and doctor assessments were revised to make a change in deciding on how many days a patient is supposed to stay in the hospital.
Aggregate functions are commonly found in databases. They are functions that aggregate many rows into a single one, or one value across many rows into a single value.
These would be the following:
The answer put simply is this:
Metrics are simply measures used to track and monitor a process.
KPIs are specific measures that tell whether a company meets its targets and how well it is performing.
For instance, the number of products manufactured in a day is a metric, while % monthly sales is a KPI, telling us whether we exceeded (>100%) or fell short (<100%) on meeting our monthly sales target. It is calculated as 100 * sum(sales)/monthly target.
Wise advice: Be very honest. You will be tested if you take the job, if not in the technical interview before that.
If you are a 2, say it. If you are confident you are a 5, say it.
On the other hand, if you are not very adept in one of the above, express your commitment to strengthen yourself in it.
You can use this Introduction to SQL to learn SQL quickly and easily.
You knew this question was coming..
You can find the full answer in our Beginner’s Guide to Data Warehousing.
A data mart is a fundamental element of the data warehouse, whereby we store data relevant to one department or area.
You can find the full answer in our Beginner’s Guide to Data Warehousing.
A data warehouse’s architecture consists of ETL reading data from source systems into the staging area where this data gets cleaned and prepared. Then, ETL extracts the data from the staging area, applies business rules on it and loads it to the data warehouse. From there, this data populates the data marts, and an OLAP server, if it exists (check Q30).
Dimensional Modeling is about building a data model that allows for fast retrieval of data stored in a data warehouse. For more on dimensional modeling, read the Beginner’s Guide to Data Warehousing.
The surrogate key is created by the BI developer to be used in place of the natural key, for many reasons. One reason is that the natural key in the database might be changed, which causes errors and inconsistencies in the data warehouse.
The surrogate key is what the developer uses to link the dimension table to the fact table.
A factless fact table does not carry any fact, but is there as a link table that stores the keys of dimension tables, in order to define many-to-many relationships between them.
Normalization is a technique applied to data stored in a database, for the purpose of eliminating data redundancy (storing the same data in more than one place / repeatedly).
This leads to storing less data, and having more consistency. This article explains this in detail.
On the contrary, data warehouses utilize denormalization in order to achieve complex querying and fast data retrieval.
A full load loads the whole data in bulk, while an incremental one loads the data that has changed (added, deleted, updated), on a periodic basis.
If your fact table contains sales data that you need to use in a BI application you just developed, you load the whole data the first time (full load) and then you build an incremental load that you run periodically (incremental load), let’s say, everyday at 9:00 pm, and it checks for changed records and loads them only.
This article from Amazon Web Services explains it well.
SCD, or Slowly Changing Dimension usually stores static data that takes a long time to change, but does so in an unpredictable manner.
Granularity refers to the level of detail in a data warehouse.
For instance, if the company sells certain products, and each record is a product sale, granularity is at product level.
Another company sells these products as well, but in bulk, so granularity is at level of product batches.
Here you would describe any experience you have with designing ETL flows and building ETL jobs.
You can read about OLAP servers in this article from Microsoft.
In this article, I have attempted to prepare you for your next Business Intelligence Interview, with Questions and Answers that are very likely to appear. Good Luck!
Let us help you increase your chances of getting hired in Business Intelligence, for $15/hour only! Contact us