SqlServer/MSBI Interview Questions asked by Companies (Part7)

31. What MDX functions do you most commonly use? 
This is a great question because you only know this answer by experience.  If you ask me this question, the answer practically rushes out of me.  “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate.  My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.”  Indeed, CrossJoin has easily been my bread and butter.

32. How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?
Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

33. How in MDX query can I get top 3 sales years based on order quantity?
By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];

Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];

34. How do you extract first tuple from the set?
Use could usefunctionSet.Item(0)
Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]

35. How can I setup default dimension member in Calculation script?
You can use ALTER CUBE statement. Syntax:
ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION <dimension name>, DEFAULT_MEMBER='<default member>';
More Questions & Answers :-
Part1  Part2  Part3  Part4  Part5  Part6  Part7  Part8