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.

So, you call yourself a DBA ..., Let`s see

I got an opportunity to do job interviews when
we were looking for development DBA.

Honestly, I was in shock from some candidates.
We got many CVs where people wrote that
they have 5-6 and more years of 'hands on' experience.

Nice, I said,
there will be no
problem to find
a good DBA.
BUT,
I was wrong !!!!!!!!




People calling themselves a DBA with many years
of experience did not answer very primitive
questions related to the SQL Server.

Here some examples:

Question:
What is the difference between @@identity and scope_identity()?

Answers we got:

- @@identity returns the process id of your connection.
- @@identity is a constant and you can use it later in the code.
- @@identity is a global variable and scope_identity() is a func.
- "I don`t know, but I can search for it in the Google" (cool answer)

Question:What is the difference between varchar and nvarchar?

Answers we got:

- varchar can hold only letters and nvarchar can hold letters+numbers
- varchar is a fixed set of characters per column and nvarchar is
   'flexible' to use, as much characters as needed per row.

Question:
How SQL Server stores its indexes?
What B stands for in the 'B-Tree'?

Answers:

- B is for 'Group by' clause and A is for Child (WTF !!??)
- Binary (90 % of candidates answered Binary)
Question:

How to fix index fragmentation?

Answer:

You update the statistics.

-----------------------------------------------------------

I interviewed a DBA with 10 years of expirence and he:

Did not know what the difference between Identiy and ScopeIdentiy is.
Did not know that varchar(max) hold data in row until 8k.
Did not know what the OUTPUT clause in sql 2005 is.
------------------------------------------------------------

Come on , people !
How dare you lie in your CV and come to job interview when
you know you will be tested.
I really do not get it.

I want to emphasize, these are not the only questions
that were asked. This post is not about which questions
to ask or how to do the interview.
It is just an example of answers.

Applicative / Development DBA Interview Questions - Part 1

If you got the chance to interview a person for applicative
(meaning development)  DBA position, you will find the
following questions helpful:

1. What is an execution plan and what for it needed?

A query execution plan is
an ordered set of steps used
to access or modify
information in a database.
An SQL statement tells
what you want and the
execution plan tells you how
the SQL Server going to
do it, meaning what
operations,operators it
will use (Table/Index
scan,Index Seek, Nested loop and etc)

SQL Server builds an execution plan in order to save
all steps it does upon submitting a query for execution
(like parsing, optimizing) and of course for reuse purpose.

2. When the execution plan created?

It created on the first execution of a SP.

3. Is an execution plan takes into an account values of SP`s input parameters?

Yes, the execution plan is built considering the value of SP`s
input parameters.(Prameters Sniffing)

4. Is an execution plan is permanent? If not, when the SQL Server changes it?

It is not permanent, the SQL Server can decide to recompile
a SP and different execution plan can be created. There are
many reasons for SP recompilation, like Schema/Statistics
changes, Temp table(s) usage and etc.

5. When you rebuild a Clustered Index are non clustered indexes rebuilt too?

Starting from SQL Server 2005, the answer is NO!
SQL Server 2005 will re-use the old uniquifier values so the
cluster keys don't change. This means that non-clustered indexes
are NOT rebuilt.

See Paul Randal Article.

6. Where does a Non Clustered index points to?

In case you have table without a Cluster index, it ponits to
data pages, once clustered index is created, non clustered
indexes will be reorganized and point to clustered index.

To be continued....