Join Algorithim - Nested Loop Join

Logical Joins (Cross, Inner & Outer joins) are internally implemented using one of the following physical joins -

1. Nested Loop Join
2. Merge Join
3. Hash Join

Optimizer decides at run time which algorithim to use for the logical joins depending upon the type of join and the nature of the inputs like the number of rows in the input, sorting of records based on the join column etc.

Here I'll discuss Nested Loop Join -

Suppose there are two tables or rather inputs T1 & T2, with size N1 and N2 and N1>N2, which we need to join.

Optimizer will take one record from T1 and will scan all the records from T2 one by one. If a record matches, based on the joining column it will be returned so for every input there would be N2 scans and then total scans would be N1*N2.

As you can see for large values of N1 and N2, the complexity of this algorithim increases drastically (N1*N2), this join is not recommended for large values of N1 and N2 rather used for smaller input size.

Unlike other join algorithims, which requires at least one predicate as equi join, Nested loop join could be used for both kind of predicates - equi or non equi joins. Nested loop join cannot be used with Right outer joins.

You can force optimizer to use any kind of join algorithims, provided logical join supports it. E.g. if you run below query,

SELECT * FROM Sales.SalesOrderDetail AS SOD INNER JOIN
Production.Product AS P ON SOD.ProductID=P.ProductID

Optimizer might use Hash join in the query execution plan, but if you want to inforce Nested Loop Join on the optimizer, you need to use below syntax -

SELECT * FROM Sales.SalesOrderDetail AS SOD INNER Loop JOIN
Production.Product AS P ON SOD.ProductID=P.ProductID

Also, following hint in the query cannot be used because Nested loop join cannot be used with Right Outer Joins -

SELECT * FROM Sales.SalesOrderDetail AS SOD RIGHT OUTER LOOP JOIN
Production.Product AS P ON SOD.ProductID=P.ProductID

and you will get error message as "Msg 8622, Level 16, State 1, Line 1Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

2. Merge Join: Details could be found here.

3. Hash Join: Details could be found here.

Understanding Impersonation in SSAS 2008

It took some time for me to understand impersonation in SSAS 2008, hope following links, would be helpful to understand it in better way:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/80799351-a1d1-4c08-8b31-bb95d1053fd3/#0e002801-236c-481b-8e1a-5d40015e2020

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/62e56047-442e-4d6e-9aa3-a3ae6a52e206

Any further questions would be greatly appreciated.

SSAS Interview Questions.

Following could be some basic SSAS & MDX questions which can be asked in any interview process -

1. What is UDM? Explain.
2. Explain Star & Snow Flake schemas.
3. In which situations we should use SnowFlake schemas?
4. What are pros & cons of star and snowflakes?
5. Explain many to many relationship.
6. Explain factless fact table.
7. Explain Attribute relationship, their type and usage.
8. What is Degenerated dimension?
9. What is Audit dimension?
10. What is Junk dimension?
11. What is SCD dimensions?
12. Explain Dimension Usage tab?
13. Explain Named Sets.
14. Explain Calculated Members.
15. Explain Bit Map indexes.
16. Explain types of processing modes.
17. Explain cube processing task in SSIS.
18. How you will create dynamic partitions?
19. Explain Transalations, KPI.
20. How you will design your aggregations?
21. What is proactive caching?
22. What is usage based optimization?
23. Explain MOLAP, ROLAP and HOLAP. Explain scenarios where each feature is helpful.
24. Explain cold & warm caching.
25. Explain how you will tune a given MDX query.

Microsoft MSBI Inteview Questions!

I was lucky to get call from one of my dream companies, Microsoft. Following are few questions, which were asked in the first round of telephonic interview -

1. What are the default databases created when SQL Server is installed?
2. What are deterministic & non deterministic functions in T-SQL? Give some examples.
3. There is a table with exactly 100 records. How you will categorize them into 4 parts each with 25 records?
4. How many isolation levels are there in SQL Server transactions?
5. What is Fill Factor? Explain.
6. Questions were asked around taking back up and back up related files.
7. What is Scope in MDX? Explain with example. Question was asked based on some scenario.
8. What is a subcube?
9. What are the basic steps for tuning a MDX query?
10. What are the different ways to migrate SSAS 2008 cubes?
11. In which scenario you should use SnowFlake schema?
12. Explain many to many relationship?
13. What is an audit dimension?
14. What is a degenerated dimension?
14. Give some example of fully blocked transormation?
15. What is Checkpoint?
16. You have a package, it loads 3 million rows. You have used checkpoints. Now Package failed after 2.5 million rows are inserted. You fix the problem and restart the package. What will happen?
17. Explain Attribute relationship.
18. What are different types of attribute relationship?
19. Will incremental processing will work with flexible relationship?
20. What are different methods of doing iteration in T-SQL?
21. How to eliminate Cursors in T-SQL? Explain with examples.
22. How will you deploy SSIS package from command line?
23. What is indirect configuration in SSIS package?
24. What are semi additive measures?
25. What is range operator in MDX?
26. How you can see data of current date in cube using MDX?
27. Explain Transalation. How you will configure so that user belonging to particular region can see translated captions?
28. What steps you will follow to migrate script task from 32 bit to 64 bit environment?
29. Explain lock escalation?
30. Explain proactive caching.
31. What are materialized views?

Based on the above list you can see that question which were asked are quiet basic & fundamental. If you get call from them, try to buy out some time and referesh you basic beforehand. Interviewer was cool and was listening me properly, they were just checking your basics.

Good luck and share your experience, if you have any, with Microsoft.