Common Business Intelligence and Analytics Interview Questions
In this post, We are going to talk about Common Business Intelligence and Analytics Interview Questions. Business intelligence (BI) in general refers to a concept where all the skills, technologies, applications and practices are used to help a business get better insights of their operations and achieve its goals. Business intelligence can also refer to a collection of data or information collected from successful business. In this article, we have a collection of Questions and answers covering the basic concepts of data warehousing in the form of interview questions.
Question: What is data warehouse?
Answer: A Data warehouse is an electronic storage of a large store of data accumulated from a variety of sources within a company. Based on the Organization’s historical data specialists can do Data Analysis, such as Reporting, Analysis and other knowledge discovery activities.
Before doing Data Analytics, a data warehouse can also be used for the purpose of improving Data Quality, Data integration with other systems, master data management (MDM), Budget Estimations, etc.
Question : What is Business Intelligence?
Answer: By definition, Business Intelligence is a set of techniques and tools for the acquisition and transformation of raw data into meaningful and useful information for Data Analytics and Business Insights. When Organizations collect large amounts of Data, Data Analysts can use various BI Tools to create “Data Models” to allow for the easy interpretation of these large volumes of data. By creating “Facts” and “Dimensions“, aggregating data, defining KPI‘s helps to identify new opportunities and implementing an effective strategy based on insights can provide businesses with a competitive market advantage and long-term stability.
By implementing BI tools and technologies, it can provide you historical, current and predictive views of your current business operations.
Business Intelligence comprises of the following components:
- OLAP (Online Analytical Processing)
- Denormalization of the Data, Tagging and Standardization
- Corporate Performance Management (CPM)
- Real-time BI (Streaming Data or IOT Scenarios)
- Data Warehousing (Traditional Historical Data)
- Data Sources (Structured and Un-Structured Data)
In order to achieve all these goals, you use BI Tools and Techniques to Achieve all of the above.
Question: What is data mart?
Answer: Data marts are generally sourced from a single Data source and designed for a single subject area. For example: A LOB (Line of Business) application for POS (Point of Sale) systems containing Sales Data. An organization may have data pertaining to different departments like Finance, IT, HR, Marketing etc. stored in a data warehouse and each department may have separate data marts for each. Combining these various data marts one can built a complete data warehouse. Often BI applications use data gathered from a data warehouse (DW) comprising of various data mart’s.
Questions: What is dimensional modeling?
Answer: A Dimensional Model is a de-normalized database structure (Snowflake or Star) that is optimized for online analytical queries and Data Warehousing tools (Microsoft Excel) . It typically is comprised of “facts” and “dimension” tables. A “Fact” is a numeric value that a business wishes to do aggregations on. For example: mean, average, count or sum. The aggregations are called “Measures“. A “dimension” is essentially what defines the Fact data and acts as an entry point for getting at the facts.
What is a dimension?
Answer: A “dimension” is essentially what defines the Fact data and acts as an entry point for getting at the facts. These Dimensional are normalized or snow flaking which removes redundant attributes, which are known as normal flattened de-normalized dimensions.
What is a Fact?
Answer: A fact is something that is quantifiable (Or measurable). A telephone number cannot be measurable, but sales amount is. Facts are typically (but not always) numerical values that can be aggregated.
What is Star-schema?
Answer: A star schema is the simplest style of data modelling schema and is the most commonly used approach to develop data warehouses and dimensional data marts. It is the simplest form of a dimensional model designed to handle simple queries, in which data is organized into facts and dimensions. A fact is an event that can be quantified (counted or measured), such as a sale or cost. A dimension defines the fact and contains reference data about the fact, such as date, product, location or customer. A star schema is diagrammed (See Above Image) by surrounding each fact with its associated dimensions. The resulting diagram resembles a star.
What is snow-flake schema?
Answer: In a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. These are more complex and are not all dimensions are directly connected to the fact tables. The resultant structure resembles a snowflake with the fact table in the middle, connecting to dimensions and other bridge tables.
- The Microsoft Data Warehouse Toolkit
- The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence Remastered Collection