BI Developer Interview Questions
questions relevant for BI Developer.
What do I mean by saying BI Developer?
A person that understands SQL,
knows how to build efficient ETL process,
knows how to design a Datawarehouse,
knows how to design and build cubes,
understands how BI works, knows to
provide design of ETL/DWH/Cubes from
analyzing the business requirements.
0) Describe the BI system you have in your company, including
ETL process. What were your responsibilities in your last job ?
What are the challenges you faced in your BI system ?
-----------------------------------------------------------------------
-- SQL knowledge ---------------------------------------------------
-----------------------------------------------------------------------
1) What is the difference between a Clustered and NonClustered
index? For what type of queries each index is good?
2) Write at least two different queries(different ways) that return
from the table below, the 2 most latest released books for each Author.
Note: the table above may contain more rows for each author.
------------------------------------------------------------------------
3) Why OLTP database design not generally a good idea for
a Database Warehouse?
4) What type of schemas you know in DataWarehouse DBs?
What are the differences? Is there any performance differences?
5) What are fact and dimension tables?
6) What is Dimensional Hierarchy in OLAP ? Why we need it?
7) If we need to limit the access to a data in a cube,
How do you do it?
8) What are different storage mode options in SSAS?
How do you decide when to use each of them?
What are the differences?
What the Advantages and Disadvantages of each technology?
How they effect on performance?
9) If you need to create your own measure that holds some logic,
How do you do it?
What are Additive, Semi additive and No additive measures?
10) How would you optimize the dimensions?
11) What really ‘Optimize schema’ option is SSAS does?
12) Can you give an example when you need to create
two measure groups?
13) What is the difference between attribute hierarchy and user hierarchy?
-----------------------------------------------------------------------------
-- SSIS related questions ---------------------------------------------------
-----------------------------------------------------------------------------
14) What is Lookup is SSIS?
15) Is it possible to restart failed SSIS package from the point of
failure, instead of rerunning the whole package?
How to implement this?
16) Is it possible to define breakpoints in SSIS package?
If yes, how it helps the developer?
17) Is it possible to define transaction in package/container or
Control Flow task level in a SSIS package? How you do it?
18) What's the difference between Control Flow and Data Flow?
----------------------------------------------------------------------------
-- MDX related questions -------------------------------------------------
----------------------------------------------------------------------------
19) What are the differences between MDX and T-SQL?
20) How can you retrieve Top 10 customers without using
TopCount function?
If you need the answers to any question above,LMK.