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.
Thank you for sharing "BI Developer Interview Questions".
ReplyDeletePlacement Papers
Here you go:
ReplyDeleteCREATE TABLE dbo.SomeTable
( AuthorName varchar(50),
BookName varchar(50),
ReleaseDate datetime
)
INSERT INTO dbo.SomeTable
SELECT 'George','BookA',GETDATE()-1 UNION ALL
SELECT 'George','BookB',GETDATE()-2 UNION ALL
SELECT 'George','BookC',GETDATE()-3 UNION ALL
SELECT 'Tom','BookA',GETDATE()-4 UNION ALL
SELECT 'Tom','BookB',GETDATE()-5 UNION ALL
SELECT 'Tom','BookC',GETDATE()-6 UNION ALL
SELECT 'Tom','BookD',GETDATE()-7 UNION ALL
SELECT 'George','BookD',GETDATE()-8
GO
---------------------------------------------
One way is to use ROW_NUMBER():
SELECT *
FROM ( SELECT ROW_NUMBER() OVER( PARTITION BY AuthorName ORDER BY ReleaseDate DESC) AS RowID,*
FROM dbo.SomeTable
) as Results
WHERE RowID<=2
Second way is to use CROSS APPLY
SELECT DISTINCT a.*
FROM dbo.SomeTable as t1
CROSS APPLY
(SELECT TOP 2 *
FROM dbo.SomeTable as t2
WHERE t2.AuthorName=t1.AuthorName
ORDER BY t2.ReleaseDate DESC
) as a
The options above are good for SQL 2005 and above.For SQL 2000 you will need to use correlated subqueries.
This comment has been removed by the author.
ReplyDelete