Business Intelligence Interview: Top 30 Questions and Answers
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.
General Business Intelligence Interview Questions
Q1- Brief me on your work experience relevant to Business Intelligence.
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.
Q2- What other Business Intelligence roles have you worked with?
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.
Q3- Do you have experience in acquiring business requirements from users of Business Intelligence applications?
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 🙂
Q4- Have you worked on QA testing of Business Intelligence applications before?
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.
Q5- Tell me about an interesting Business Intelligence project you have worked on.
Showcase Opportunity!
Choose an interesting and comprehensive project.
Explain your approach to the project, the process you followed, the achievements and the end result/impact.
Q6- What skills are you looking forward to add to your Business Intelligence skillset?
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.
Q7- What KPIs are usually used in the [example] industry?
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.
Q8- What is the difference between descriptive and predictive analytics?
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.”
Front-End Business Intelligence Interview Questions
Q9- What data visualization tools do you use? What is your preferred one and why?
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.
Q10- Tell me what you know about Guided Analytics.
Here’s a cheat:
“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.”
Q11- What chart would you use for comparison? What about for distribution?
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.
Q12- How would you visualize data across 3 dimensions?
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.
Here is an example from d3-graph-gallery.com:
Q13- Do you know what a Mekko chart is?
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.
Q14- What is the importance of benchmarking? Give me an example use case.
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.
More on Healthcare Metrics Here
Q15- What are the aggregate functions you know?
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:
- Average
- Sum
- Min
- Max
- Median
- Count
- Standard Deviation
Q16- What is the difference between a KPI and a metric?
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.
Back-End Business Intelligence Interview Questions
Q17- How do you rate your SQL knowledge in: data extraction, query performance tuning, stored procedures?
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.
Q18- What do you know about data warehousing?
You knew this question was coming..
You can find the full answer in our Beginner’s Guide to Data Warehousing.
Q19- What is a data mart?
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.
Q20- Explain to me briefly the architecture of a data warehouse.
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).
Q21- Explain what you know about dimensional modeling. When do we use it?
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.
Q22- What is a surrogate key?
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.
Q23- What is the purpose of a factless 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.
Q24- What is the purpose of normalization? How does it differ from de-normalization?
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.
Q25- What is the difference between full load and incremental load?
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.
Q26- Do you know what a data lake is?
This article from Amazon Web Services explains it well.
Q27- Explain to me what a Slowly Changing Dimension is.
SCD, or Slowly Changing Dimension usually stores static data that takes a long time to change, but does so in an unpredictable manner.
Q28- Explain the concept of granularity.
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.
Q29- How experienced are you with ETL? What are ETL tools that you are familiar with?
Here you would describe any experience you have with designing ETL flows and building ETL jobs.
Some well known ETL tools would be Talend Data Integration, AWS Glue and Informatica PowerCenter.
Q30- What is OLAP server?
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
Originally published at https://house-of-analytics.com on August 19, 2020.
