Friday, April 15, 2011

BI Developer Interview Questions

I want to share with you set of interview
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.

68 comments:

  1. Thank you for sharing "BI Developer Interview Questions".

    Placement Papers

    ReplyDelete
  2. Can you please send me the query for 2nd question of SQL Knowledge?

    dhandmca@gmail.com

    ReplyDelete
  3. Here you go:

    CREATE 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.

    ReplyDelete
  4. Hi

    Tks very much for post:

    I like it and hope that you continue posting.

    Let me show other source that may be good for community.

    Source: Business intelligence developer interview questions

    Best rgs
    David

    ReplyDelete
  5. Hi,

    Very useful indeed! Could you send link / copy of answers to sharmslad@gmail.com

    Thanks


    Dal

    ReplyDelete
  6. can you send me the answers pls? petermat31@gmail.com

    ReplyDelete
  7. it would be much appreciated if you could flip the answers to all the above questions to shama.kutub@gmail.com

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Please could you send me the answers and thanks for publishing to mcdowallronald@yahoo.co.uk

    ReplyDelete
  10. Hi, Thank you so much for the questions, can you please send me the answers at: pragatim19@gmail.com

    Thanks once again :-)

    ReplyDelete
  11. As others have requested could you also send me the answers at:
    heather.tillo@t-mobile.com

    ReplyDelete
  12. Could you also send me the answers: jodiedizhu@gmail.com
    Thank you!

    ReplyDelete
  13. hi, thank you so much, can I please get a copy of the answers sent to Mahesiang@Nedbank.co.za

    ReplyDelete
  14. Hiii Thank you so much for the questions, can you please send me the answers at: amrutashelke19@gmail.com

    ReplyDelete
  15. Hiii Thank you so much for the questions, can you please send me the answers at: setshwaneng@gmail.com

    ReplyDelete
  16. could you please send the answers on Garima.jha1234@gmail.com

    ReplyDelete
  17. Hi,

    Very useful indeed! Could you send link / copy of answers to sindhuguttulasqlbi@gmail.com

    Thanks

    ReplyDelete
  18. Awsome. Can you please me the answers on d.pereanen@gmail.com

    ReplyDelete
  19. Hi. Can you please send me the answers to boyinapalli@hotmail.com
    Thank you..

    ReplyDelete
  20. Can you send me the answers to the questions above? Thank you! sa6621@att.com

    ReplyDelete
  21. Hi. May I please have someone send me answers to the questions above? Thank you!

    Rob
    ne088@hotmail.com

    ReplyDelete
  22. Hi, can you please send me the answers?

    izzyha06@gmail.com

    ReplyDelete
  23. These are awesome questions. Can you please send me the answers?
    gapydwh@yahoo.com

    ReplyDelete
  24. can you send me the answers on gudekarparesh@gmail.com

    ReplyDelete
  25. Please send me the answers to zhiyzhang@yahoo.com. Thanks a lot!

    ReplyDelete
  26. Please send me the answers to zhiyzhang@yahoo.com. Thanks a lot!

    ReplyDelete
  27. Thank you very much and appreciated for posting. Please I would like to have the answers send to: laekhamphi@gmail.com Thanks again!

    ReplyDelete
  28. Thank you very much and appreciated for posting. Please send me the answers to nomaninzi@yahoo.com

    ReplyDelete
  29. Thank you very much for a great post! I would appreciate if you could email answers to olga_godo@hotmail.com. Thank you.

    ReplyDelete
  30. can you please send me the answers to sahasras09@gmail.com..... thank you

    ReplyDelete
  31. Thank you very much for a great post! could email answers to r7135808@hotmail.com. Thank you.

    ReplyDelete
  32. Could you please send me the answers too :) (ma3l87@gmail.com). Thank you!

    ReplyDelete
  33. Those are good questions. May I have the answers? Please Email me. velbony@yahoo.com.tw

    ReplyDelete
  34. Great post and questions. Would you be able to please send me the answers at seems710@yahoo.com

    ReplyDelete
  35. Gud Questions .Can you Plz share the answers at lanka.phani@outlook.com
    Its urgent i have interview tmrw.

    ReplyDelete
  36. Hello, May I please have the answers? bkwon(at)netspace(dot)net(dot)au. Thanks heaps!

    ReplyDelete
  37. Hi
    Thanks fore these questions. Kindly help with the answers to tundeake@gmail.com

    Thanksagain

    ReplyDelete
  38. Hi,

    Could you please send me the answers to chris_chambo@yahoo.co.uk

    Kind regards

    ReplyDelete
  39. Hi May you please send the answers to donaldmatundu@yahoo.com

    Thank You

    ReplyDelete
  40. Hi May you please send the answers to deepnce2006@gmail.com

    ReplyDelete
  41. Hi

    May you please send the answers to dhamijayash@gmail.com

    ReplyDelete
  42. Hi,

    Could you please send me the answers to uday.bea02@gmail.com

    regards

    ReplyDelete
  43. Thank you for sharing the questions. Could you please send me the answers to yizuo.zhao0204@gmail.com. Thank you in advance!

    ReplyDelete
  44. Could you please send the answers on bhavikparikh13@gmail.com

    ReplyDelete
  45. If any of you have the answers, can you send to notutui@yahoo.com?

    ReplyDelete
  46. Hi!
    May I plz have the answers here: vitalka900@gmail.com?

    ReplyDelete
  47. answers plz ivanoel_77@hotmail.com thanks

    ReplyDelete
  48. Great questions! Can I please have answers sent to hamid.kassar@gmail.com
    Thanks!

    ReplyDelete
  49. Hi,
    Can you send the answers to maxwang1480@gmail.com?
    Thank you!

    ReplyDelete
  50. Nice questions. May I get the answers at qpwoei4756@yahoo.com ty

    ReplyDelete
  51. Hi, would it be possible for you to send the answers at ojuchku@gmail.com?
    Thank you.

    ReplyDelete
  52. Hi, would it be possible for you to send the answers at ojuchku@gmail.com?
    Thank you.

    ReplyDelete
  53. Thank you for sharing the questions. Could you please send me the answers to viola_inberg@yahoo.com ? Thank you in advance!

    ReplyDelete
  54. Thanks for sharing. could you please send the answers to jpraneethrao@gmail.com

    ReplyDelete
  55. Thank you for sharing.Could you please kindly send the answers to zxy.cesc@gmail.com

    ReplyDelete
  56. Thank you for sharing.Could you please kindly send the answers to kirthithakur@gmail.com

    ReplyDelete
  57. Thank you for your blog post. Will you please send answers to sqldbaandbideveloper@gmail.com

    ReplyDelete
  58. Very useful post abd questions, thanks a lot! Will appreciate getting the answers to mail annytik@yahoo.com. Thanks again.

    ReplyDelete
  59. Hi can you please send me answer in atif.mohmood@hotmail.com

    ReplyDelete
  60. Hi. Many thanks for the questions. Can you please send answers to arpitha_molugu@yahoo.com

    ReplyDelete
  61. Hi. Many thanks for the questions. Can you please send answers to sinyoro.pams1982@gmail.com

    ReplyDelete
  62. thank you for sharing! Can you please send me answers to the questions at danminhvu56@gmail.com. Thank you!

    ReplyDelete
  63. Hi, can u send answers to usman.tariq@conver2.com

    ReplyDelete
  64. Hi,

    Nice questions, Could you please send the answers to chantivarma999@gmail.com.

    Thanks,
    Chanti

    ReplyDelete
  65. Hello,

    Good questions. Would you please send the answers to marvin.moorman@sgsco.com

    Thanks

    ReplyDelete
  66. Hello,

    Good questions. Would you please send the answers to rajshekar.m.99@gmail.com

    Thanks

    ReplyDelete
  67. Thanks , would you please send answers to tpnzvede@gmail.com

    ReplyDelete
  68. Thanks you so much for your question,
    Can you send answer to hmtuan197@gmail.com

    ReplyDelete