BI Interview Question :

The term Business Intelligence(BI) provides the user with the data and tool to answer the questions which are important to run the business or part of business.In short,the business intelligence is used for reporting the specified data of any business which is very important and using which  the higher management of the business will take the decisions for growth of business.Now a days Business Intelligence is demanding technology to analyse the business and its history.In this article i will try to explain 20 Most important BI Interview Question.These questions are helpful to BI developers to crack the BI interviews:

1.What is Business Intelligence?(100 % Asked BI Interview Question)

Answer:

The term Business Intelligence (BI) provides the user with the data and tool to answer the questions which are important to run the business or part of business.In short,the business intelligence is used for reporting the specified data of any business which is very important and using which  the higher management of the business will take the decisions for growth of business.Business Intelligence (BI) is used to take following organization level decisions:

1.BI is used to determine whether the business is running as per plan

2.BI is used to identify which things are going wrong

3.BI is used to take and monitor corrective actions

4.BI is used to identify the trends.

CLICK HERE TO GET DELOITTE INTERVIEW QUESTIONS FOR SQL

2.What are different stages of Business Intelligence?

Answer:

There are following five stages of Business Intelligence:

1.Data Source

2.Data Analysis

3.Decision making support

4.Situation Awareness

5.Risk Management

3.What are different benefits of Business Intelligence?

Answer:

Following are different benefits of Business Intelligence:

1.Improving Decision Making

2.Accelerate Decision Making

3.Optimizing internal business process

4.Increase Operational Efficiency

5.Driving new revenues

6.Gaining competitive advantages over competitors

4.What are different Business Intelligence tools available in market?

Answer:

There are following Business Intelligence Tools Available in Market:

1.OBIEE

2.Cognos

3.Microstratagy

4.SAS

5.Business Objects

6.Tableau

7.Microsoft BI

8.Hyperion

CLICK HERE TO GET INFORMATION ABOUT NEW DATABASE TECHNOLOGY ORACLE 18 C

5.What is universe in Business Analytics?

Answer:

Universe is a Business Object Terminology.Universe is interfacing layer between client and data warehouse.Universe defines the relationship between various tables in datawarehouse.It has different names in different tools.It is called as repository oor RPD in OBIEE.

Universe is a semantic layer between database and user interface.

6.What is OLAP(Online Analytical Processing)?(100 % asked BI Interview Question)

Answer :

OLAP is technology used in many Business Intelligence applications which includes complex analytical calculations.OLAP is used for complex calculations,Trends Analysis,sophisticated data modeling.OLAP database is stored in multidimensional database model.OLAP system contains less number of transactions but complex calculations like aggregation- Sum,count,average,min,max e.t.c.

The Aggregated data in OLAP system must be in months,quarters,years,weeks e.t.c. The key purpose to use OLAP system is to reduce the query response time and increase the effectiveness of reporting.If these aggregated calculations are already stored in repository and if user wants fast access of data then user can use OLAP system.OLAP database stores aggregated historical data in multidimensional schema.

Real Example :

If Company head wants information of Resources salary in year 2000.

In spite of using the transactional system we will use OLAP system here where aggregated data of year 2000 for Resources is already present.

7. What is OLTP(Online Transaction Processing)?

Answer :

 OLTP system is known as large number of small daily transactions like insert,update and delete.Operational database is known as OLTP system.OLTP system provides fast query processing as well as it is also responsible to provide data integrity and data consistency.The actual effectiveness of OLTP is measured in number of Transactions per second.OLTP normally contains current data and data normalization is used properly in OLTP system.

Real Example :

If Company head wants transactional report of all Employees In – Out time.

As Company head wants daily report of in-out time we need to provide it using OLTP system.We need to schedule report on daily basis using OLTP system.

8.What is Data Warehouse?(100 % asked BI Interview Question)

Answer :

Data warehousing is a system which is used for reporting purpose as well as data analysis purpose where data is coming from multiple heterogeneous sources whether it is  oracle, sql server, postgres,simple excel sheet.Data warehousing is specially used for reporting historical data.Data warehousing is core component of Business Intelligence.In Data warehouse there is one central mechanism called as repository using which one can fetch the historical reports.In Data warehouse user can store current data as well as historical data on single place called as repository.Data warehouse provides generalized data and consolidated data in multidimensional view.Data warehouse provides Online Analytical tools to analyse the data and reporting purpose.

Data warehousing is subject oriented,time variant,non-volatile collection of data which is used for creation of transactional reports as well as historical reports.

9.What are dashboards?

Answer :

Dashboards are nothing but the arrangement of all the reports and charts graphs on one page.It is nothing but the collection of reports of different formats which has same functionality displayed on the same page.

10.What are different characteristics of Data Warehouse?

Answer :

1. Data warehouse is a database which is separate from operational database which stores historical information also.
2. Data warehouse database contains transactional as well as analytical data.
3. Data warehouse helps higher management  to take stratagic as well as tactical decisions using historical or current data.
4. Data warehouse helps consolidated historical data analysis.
5. Data warehouse helps business user to see the current trends to run the business.
6. Data warehouse is used for reporting and data analysis purpose.

11.What are different types of Data warehouse Systems?

Answer :

1. Data Mart
2. Online Analytical Processing (OLAP)
3. Online Transactional Processing
4. Predictive Analysis

12.What is difference between Data warehouse and Transactional System?(100 % asked BI Interview Questions)

Answer :

Data warehouse	Transactional System
Data warehouse stores complex and general form of the data.	Transactional system stores the updated daily transactions,workloads etc.
Data warehouse stores historical information	Transactional System Contains current data of organization
Data warehouse database generally have only read only access means user can only select the data	Operational or Transactional database have insert,update privileges as the data processing and updation needed.
Data warehousing requires data cleaning,data validation and data consolidation	Transactional system requires parallel processing of the data,concurrency control.Data consolidation is less required in transactional system as compare to OLAP database.

13.What is Data Mart?

Answer :

Data Mart is a simplest set of Data warehouse which is used to focus on single functional area of the business.We can say Data Mart is a subset of Data warehouse which is oriented to specific line of business or specific functional area of business such as marketing,finance,sales e.t.c. The data come in to Data Mart by different transactional systems,other data warehouse or external sources.

Data Mart is simply a subset of Organization’s Data warehouse.

 14.What is database schema?What are its types?(100 % asked BI Interview Questions)

Answer :

Schema is logical description of whole database.Database schema is a skeleton or structure of the database which represents database logically.Same like a database Data warehouse also requires to maintain database schema.Data warehouse schema includes name of database objects with its relationship maintained in diagrammatic format.Database uses relational model where data warehouse uses following types of Database Schema:

1.Star Schema

2.Snowflake Schema

3.Fact Constellation schema which is also called as Galaxy Schema.

15.What is mean by Fact Tables?Explain with example.(100 % asked BI Interview Question)

Answer :

Fact table is central table found in star schema or snowflakes schema which is surrounded by dimension tables.Fact table contains numeric values that are known as measurements.Fact table has two types of columns:

1.Facts

2.Foreign key of dimension tables.

The measures in a fact table are of three types :

1.Additive :

Measures that can be added across any dimension

2.Non-additive:

Measures that can not be added across any dimension

3.Semi-additive:

Measures that can be added across some dimensions.

Real Example :

Following is a fact table which contains all the primary keys of dimensions table and added measures for ITEM,i.e.Product sold.

ITEM KEY	Time key	Product key	Date key	Product Sold
00001	T001	P001	D001	100
00002	T002	P002	D002	30
00003	T003	P003	D003	15

The fact table contains the foreign keys,time dimensions,product dimension,customer dimension,measurement values.Following are some examples of common facts :

No of unit sold,Margin,Sales revenue and the dimension tables are customer,time and product e.t.c. which is used to analyse data.

16.What is Dimensions in BI?(100 % asked BI Interview Question)

Answer :

Dimension table is table which describes the business entities of an enterprise which describes the objects in a fact table.Dimension table has primary key which uniquely identifies each dimension row.Dimension table is sometimes called as lookup or reference table.The primary key of dimension table is used to associate relationship between fact table which contains foreign key.Dimension tables are normally in de-normalized form because these tables are only used to analyse the data and not used to execute transactions.

The fields in a dimension table is used to complete following 3 important requirement :

1. Query Constrainting
2. Grouping /Filtering
3. Report labeling

Real Life Example :

Consider following table which contains item information.In the following table ITEM KEY is primary key which uniquelly identifies the rows in the dimension table. ITEM KEY will be present in Fact table.

ITEM KEY	ITEM NAME	BRAND	SOLD BY	Category
00001	Yellow shirt	Yazaki	Amit	Shirts
00002	Football	Start sports	Rahul	Sports
00003	Blue Shorts	Puma	Amit	Shorts

In the image i have explained which are fact and which are dimension tables. You will able to see there are four dimesions :

1.Time

2.Location

3.Item

4.Branch

17.What is mean by aggregates?

Answer :

Aggregate table contains aggregated data which can be calculated by using different aggregated functions like count,avg,min,max.e.t.c.Aggregated tables are most widely used tables in OLAP database.Aggregates are functions where the values of table or column are grouped together and form a single value. Following are some aggregate functions:

1.Average

2.Count

3.MAX

4.MIN

5.Median

6.SUM

Using the above aggregate functions the data will be inserted in aggregate table.The aggregate tables are used for performance optimization and data is coming fast using aggregate table.Aggregations applied on database level improves the performance of the query as the query not hits directly on table it will hit on aggregate table and fetches data.

Real Example:

If table contains the data of year 2016 and 2017 ,User wants a actual count of records in the table monthly,Quarterly and yearly.Then We need to make aggregate table which contains count of records  monthly,For Quarterly we need to create other table and push the quarterly count in that table.And we need to use that tables in the reports so that report performance will improve drastically.

18.What is Star Schema?(100 % Asked BI Interview Question)

Answer :

In Star schema there is Fact table as a center and all dimension tables surrounded with that  fact table.It is called as Star schema because diagram resembles a star with points radiating from center.Star schema is used in simple data mart or data warehouse.Star schema is designed in such way that it will optimize the querying on large data sets.In Star schema multiple dimension tables joined with only one fact table in denormalized form.

OBIEE BMM(Business model) Layer always follows star schema.

19.What is snowflakes schema?(100 % Asked BI Interview Question)

Answer :

Snowflake schema is a form of dimensional modeling where dimensions are stored with multiple dimension tables.Snowflake schema is variation over star schema.The schema is diagramed as each fact is surrounded with dimensions;and some dimensions are further related to other dimensions which are branched in snowflake pattern.In snowflake schema multiple dimension tables are organized and joined with fact table.Only difference between star and snowflake schema is dimensions are normalized in snowflake schema.Normalization splits up data in to additional tables.

Real life Example :

In Diagram i shown the snowflake schema where sales table is a fact table and all are dimensions.Store table is further normalized in to different tables name city,state and region.

20.What is mean by Granularity?(80% Asked BI Interview Question)

Answer :

Granularity in table represents the level of information stored in the table.In BI granularity is very important concept to check the table data.The granularity is high and low .High granularity data contains the data with high information or you can say it as transaction level data is high granularity data.Low granularity means data has low level information only.Fact table always have low granularity mean we need very low level data in fact table.

Following 2 points are important in defining granularity :

1.Determining the dimensions that are to be included

2.Determining location to place hierarchy of each dimension of information.

Real life Example :

Date Dimension Granularity level :

Year,month,quarter,period,week,day

Hope you like this article on Business Intelligence Interview questions.This article will definitely helpful for data warehouse developers.These are above some important BI interview questions, kindly comment in to comment section if you like this article or if you have any suggestions or concerns.