Explain the structure of MDX query?

 MDX functions?

MDX KPI Functions:

KPICurrentTimeMember, KPIGoal, KPIStatus, KPITrend

KPIValue, KPIWeight  

MDX Metadata Functions:

Axis, Count (Dimension), Count (Hierarchy Levels), Count (Tuple)

Hierarchy, Level, Levels, Name,Ordinal, UniqueName  

MDX Navigation Functions:

Ancestor, Ancestors, Ascendants, Children

Cousin, Current, CurrentMember, CurrentOrdinal

DataMember, DefaultMember, FirstChild, FirstSibling

IsAncestor, IsGeneration, IsLeaf, IsSibling

Lag, LastChild, LastSibling, Lead

LinkMember, LookupCube, NextMember, Parent

PrevMember, Properties, Siblings, UnknownMember

To learn MSBI go through ITGuru's MSBI Online Training

MDX Other Functions:

CalculationCurrentPass, CalculationPassValue, CustomData, Dimension

Dimensions, Error, Item (Member), Item (Set)

Members (String), Predict, SetToArray

MDX Set Functions:

AddCalculatedMembers, AllMembers, BottomCount, BottomPercent

BottomSum, Crossjoin, Descendants, Distinct

Except, Exists, Extract, Filter

Generate, Head, Hierarchize, Intersect

MeasureGroupMeasures, Members (Set), NonEmpty, NonEmptyCrossjoin

Order, StripCalculatedMembers, Subset, Tail

TopCount, TopPercent, TopSum, Union

Unorder   

MDX Statistical Functions:

Aggregate, Avg, CoalesceEmpty, Correlation

Count (Set), Covariance, CovarianceN, DistinctCount

LinRegIntercept, LinRegPoint, LinRegR2, LinRegSlope

LinRegVariance, Max, Median, Min

Rank, RollupChildren, Stdev, StdevP

Sum, Var, VarP, VisualTotals

MDX String Functions:

MemberToStr, NameToSet, Root, SetToStr

StrToMember, StrToSet, StrToTuple, StrToValue,TupleToStr, UserName  

MDX SubCube Functions:

Leaves, This 

 

MDX Time Functions:

ClosingPeriod, LastPeriods, Mtd, OpeningPeriod

ParallelPeriod, PeriodsToDate, Qtd, Wtd,Ytd   

MDX UI Functions:

DrilldownLevel, DrilldownLevelBottom, DrilldownLevelTop, DrilldownMember

DrilldownMemberBottom, DrilldownMemberTop, DrillupLevel, DrillupMember

ToggleDrillState

  

MDX Value Functions:

IIf, IsEmpty, MemberValue, ValidMeasure, Value

3. What is the difference between set and tuple?

Tuple: It is a collection of members from different Dimension

Set: collection of one or More tuples from same dimension

4. What do you understand by Named set? 

Named sets are simply MDX expression that return a set of members.

To define a named set:

CREATE SET MySet AS SomeSetExpression

or you can use

WITH SET MySet AS SomeSetExpression

The different between the two is the scope. Using WITH specifies the scope of the named set as the query, so as soon as the query finishes executing, that named set is gone. Using CREATE, the scope of the query is limited to the MDX session as long as you don't drop the set.

When defining your named set, you also have the option to specify when the named set is evaluated using DYNAMIC or STATIC, as seen here:

CREATE DYNAMIC SET MySet AS SomeSetExpression

or

CREATE STATIC SET MySet AS SomeSetExpression

A Dynamic Named Set respects the context of a query's subcube and the query's WHERE clause and is evaluated at the time the query is executed.

A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in WHERE clause.

Example 1:

CREATE SET DateRange AS

[Date].[Calendar Year].&[2001] : [Date].[Calendar Year].&[2004]

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,

DateRange ON ROWS

FROM [Adventure Works]

Example 2:

WITH SET SouthEastUS AS

{[Geography].[State-Province].&[AL]&[US],

[Geography].[State-Province].&[FL]&[US],

[Geography].[State-Province].&[GA]&[US],

[Geography].[State-Province].&[SC]&[US]}

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,

SouthEastUS ON ROWS

FROM [Adventure Works]

5. How will you differentiate among level, member, attribute, hierarchy?

6. What are the differences among exists, existing and scope?

7. What will happen if we remove CALCULATE keyword in the script?

8. How will you pass parameter in MDX?

9. What is the difference between .MEMBERS and .CHILDREN?

10.What is the difference between NON EMPTY keyword and NONEMPTY() function?

NON EMPTY:

Non Empty is prefixed before the sets defining the axes and is used for removing NULLs.

 In short, only the rows having NULL for all the members of the set defined in the column axis is filtered out. This is because the Non Empty operator works on the top level of the query. Internally, the sets defined for the axes are generated first and then the tuples having NULL values are removed.

SELECT

 NON EMPTY

  {

   [Measures].[Hits]

   ,[Measures].[Subscribers]

   ,[Measures].[Spam]

  } ON COLUMNS

,{

  [Geography].[Country].Children

 } ON ROWS

FROM [Blog Statistics];

NONEMPTY():

The NonEmpty() returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. Suppose we want to see all the measures related to countries which have a non-null value for Subscribers

SELECT

 {

  [Measures].[Hits]

  ,[Measures].[Subscribers]

  ,[Measures].[Spam]

 } ON COLUMNS

,{

  NonEmpty

  (

   [Geography].[Country].Children

   ,[Measures].[Subscribers]

  )

 } ON ROWS

FROM [Blog Statistics];

11. Functions used commonly in MDX like Filter, Descendants, BAsc and others

12. Difference between NON EMPTY keyword and function, NON_EMPTY_BEHAVIOR, ParallelPeriod, AUTOEXISTS

13. Difference between static and dynamic set

CREATE DYNAMIC SET MySet AS SomeSetExpression

or

CREATE STATIC SET MySet AS SomeSetExpression

A respects the context of a query's subcube and the query's WHERE clause and is evaluated at the time the query is executed.

A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in WHERE clause.

14. Difference between natural and unnatural hierarchy, attribute relationships

15. Difference between rigid and flexible relationships

16. Write MDX for retrieving top 3 customers based on internet sales amount? 

17. Write MDX to find current month's start and end date?

18. Write MDX to compare current month's revenue with last year same month revenue? 

19. Write MDX to find MTD(month to date), QTD(quarter to date) and YTD(year to date) internet sales amount for top 5 products?

20. Write MDX to find count of regions for each country?

21. Write MDX to rank all the product category based on calendar year 2005 internet sales amount?

22. Write MDX to extract nth position tuple from specific set?

syntax:

Index syntax:

Set_Expression.Item(Index)

String expression syntax:

Set_Expression.Item(String_Expression1 [ ,String_Expression2,...n])

The following example returns ([1996],Sales):

{([1996],Sales), ([1997],Sales), ([1998],Sales)}.Item(0)

23. Write MDX to set default member for particular dimension?

24. What are the performance consideration for improving MDX queries?

25. Is Rank MDX function performance intensive? 

26. Which one is better from performance point of view...NON Empty keyword or NONEMPTY function?

27. How will you find performance bottleneck in any given MDX?

28. What do you understand by storage engine and formula engine?

More info go through our ITGuru's MSBI Online Course Blog