11 Essential Business Intelligence Interview Questions *

Toptal sourced essential questions that the best Business Intelligence developers and engineers can answer. Driven from our community, we encourage experts to submit questions and offer feedback.

Hire a Top Business Intelligence Developer Now
Toptal logois an exclusive network of the top freelance software developers, designers, finance experts, product managers, and project managers in the world. Top companies hire Toptal freelancers for their most important projects.

Interview Questions

1.

What is a data cube (or “OLAP cube”)?

View answer

A data cube describes the BI data structure in memory before it is shipped to a BI UI tool to be displayed to the user. It is a multi-dimensional data representation made for better visualization, data slicing, and drill-down techniques. The UI usually does not display a literal cube, but generally 2D slices of it for better human readability:

A typical dashboard backed by a BI database designed around the data cube concept. It visualizes data using graphs, pie charts, and color-coded maps.

A data cube is usually based on a single denormalized fact table and some number of dimension tables representing data cube dimensions. The star and snowflake schemas were specifically designed to aid in building data cube structures in memory.

An example schema might consist of:

  • Time buckets—time dimension table
  • Customers—customer dimension table
  • Products—product dimension table
  • Sales amount (units sold)—fact table

The data cube structure for this schema can be thought of like this:

A cube made of smaller cubes. Its axes are Customers, Products, and Time, and the smaller cubes each represent Measures, which in this example consist of the sales amount.

2.

Describe fact and dimension tables.

View answer

A fact table contains dimension keys and numerical values for some measures. Each dimension key represents a dimension that measures are for. Measures can be aggregated across dimensions to build a drillable data cube.

Dimension tables are dictionary tables used to display dimension labels and information on BI visual interfaces.

3.

What are the steps to implement company BI analytics from the ground up?

View answer
  1. Build company analytical data storage (data warehouses, data marts).
  2. Devise an analytical data storage schema based on both actual company data and BI demands.
  3. Initially, populate analytical data storage with existing company data, and then update it regularly.
  4. Set up BI tools on top of analytical data storage.
  5. Develop BI reports.
  6. Maintain and modify BI reports according to changing needs.

Apply to Join Toptal's Development Network

and enjoy reliable, steady, remote Freelance Business Intelligence Developer Jobs

Apply as a Freelancer
4.

Name some benefits of data normalization.

View answer

The candidate should name at least two benefits from those listed below. It can be in their own words, as long as it’s close in meaning. The more benefits they can name, the better.

Data normalization:

  1. Removes data duplication.
  2. Allows finer transaction granularity. Each referenced table data could be changed independently in its own transaction without affecting its foreign key relationships.
  3. Enables clearer referential integrity. The smaller entities produced by normalization allow modeling business objects and their relations as close to the real world as is possible.
  4. Allows incremental schema changes. Adding or deleting columns in one table does not affect the structure of referenced tables.
5.

What is a data mart? When is it appropriate to use data marts instead of a single data warehouse?

View answer

A data mart stores a subset of company data that focuses on a specific department, activity type, or set of subproblems.

Separating data into data marts allows for better performance and separation of tasks for BI analysts and business users.

This strategy is a matter of design and operational convenience. While there is no definitive answer on when to use it or not, it’s usually considered appropriate to build a data mart when a company runs different lines of businesses that are very much independent in terms of their underlying data and reporting needs.

For example, if the same company is building trucks and running an online game application, it likely makes sense to handle these sub-concerns in separate data marts.

6.

What are the star and snowflake schemas?

View answer

The star schema consists of dimension and fact tables. Each dimension table represents a “metric” that can be used in BI reporting. A fact table references dimension tables for each corresponding metric the fact table covers.

An example star schema. A central Sales Fact table has a sales ID and sales units, and four IDs referring to dimensional tables: Customer, Product, Date, and Store.

The snowflake schema is an extension of the star schema in such a way that dimension tables could be further normalized and split into main and secondary dictionary tables.

The previous star schema extended to become a snowflake schema. The original five tables are still present, but each dimension table now links to further sub-dimension tables. For example, the Customer Dimension table consisted of a customer ID, name, address, and city; here the city is replaced with a city ID, linking it to a City Dimension table that stores a city name, state name, and zip/postal code for each city id.

7.

Define OLTP and OLAP. What is the difference? What are their purposes?

View answer

OLTP stands for “online transactional processing.” It is used for company business applications. They are most often customer- (i.e., people- or business-) facing.

OLAP stands for “online analytical processing.” It is used for a company’s internal analysis by department leads and company top management to steer the company.

8.

Which BI tools have you used, and what are their good and bad sides?

View answer

There are numerous BI tools on the market, but among the best-known are:

  1. Oracle Business Intelligence Enterprise Edition (OBIEE)
  2. IBM Cognos Analytics
  3. MicroStrategy
  4. The SAS product line
  5. SAP BusinessObjects
  6. Tableau
  7. Microsoft Power BI
  8. Oracle Hyperion
  9. QlikView

This type of free-form question isn’t about the candidate providing a correct answer, per se. It’s more about sparking a discussion so interviewers can get a sense of the depth of the candidate’s expertise, and where that overlaps with the company’s current needs.

9.

What is the purpose of BI?

View answer

BI provides quick and simple methods to visualize company metrics, generate reports, and analyze data.

These methods, in turn, help top management to:

  1. Analyze existing trends.
  2. Lay out company development plans.
  3. Ensure such plans are executed as scheduled.
  4. Detect anomalies and problems.
  5. Apply corrective actions.
10.

Name some benefits of data denormalization.

View answer

The candidate should name at least two benefits from those listed below. It can be in their own words, as long as it’s close in meaning. The more benefits they can name, the better.

Data denormalization provides:

  1. Simpler initial data schema design.
  2. Better data write/read performance.
  3. Direct applicability in data warehouses. Fact and dimension tables in data warehouses are usually designed without regard to data normalization to ensure fast and straightforward data retrieval.
  4. Precomputation and query performance improvements for data cube BI slice-and-dice and drill-down analysis.
11.

What are the primary responsibilities of a BI developer?

View answer

BI developers are generally expected to:

  1. Analyze company business processes and data.
  2. Standardize company data terminology.
  3. Gather reporting requirements.
  4. Match the above requirements against existing data.
  5. Build BI reports.
  6. Analyze the fleet of existing reports for further standardization purposes.

This question can be useful as an opening one—not just to help filter undesirable candidates and put more qualified candidates at ease but also to provide an opportunity to discuss any nonstandard responsibilities that may be involved in the particular job at hand.

There is more to interviewing than tricky technical questions, so these are intended merely as a guide. Not every “A” candidate worth hiring will be able to answer them all, nor does answering them all guarantee an “A” candidate. At the end of the day, hiring remains an art, a science — and a lot of work.

Why Toptal

Tired of interviewing candidates? Not sure what to ask to get you a top hire?

Let Toptal find the best people for you.

Hire a Top Business Intelligence Developer Now

Our Exclusive Network of Business Intelligence Developers

Looking to land a job as a Business Intelligence Developer?

Let Toptal find the right job for you.

Apply as a Business Intelligence Developer

Job Opportunities From Our Network

Submit an interview question

Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.

* All fields are required

Looking for Business Intelligence Developers?

Looking for Business Intelligence Developers? Check out Toptal’s Business Intelligence developers.

Laura Michela

Freelance Business Intelligence Developer
United States
Toptal Member Since September 7, 2019

Laura is a data-driven strategist with expertise in turning data into actionable insights. Laura fell in love with Tableau 7.0 in her MBA business analytics program and never looked back. Now, she is a Tableau certified professional and spends her days telling stories with data to influence business decisions.

Show More

Khalil Kafrouni

Freelance Business Intelligence Developer
Cyprus
Toptal Member Since September 16, 2020

Khalil is a data scientist and analyst with five years of experience and a keen eye for finding hidden patterns in data. Specializing in mathematical modeling and business intelligence, he is highly skilled in statistical analysis, predictive analytics, Python, SQL, and visualization tools. Khalil has helped companies reduce marketing costs, increase ROIs, predict customer lifetime values, increase conversions and retention, and reduce fraud.

Show More

Victor Lupaescu

Freelance Business Intelligence Developer
Romania
Toptal Member Since November 2, 2022

Victor is a versatile big data and analytics consultant with 10+ years of experience helping companies shift from gut-based to data-driven decisions. He's a thought leader and data visualization best practices evangelist. He has spoken at various local and global events, showcasing his passion for data visualization. Victor has also managed big data and DevOps practices with 150+ engineers in a large digital transformation company.

Show More

Toptal Connects the Top 3% of Freelance Talent All Over The World.

Join the Toptal community.

Learn more