MDX
1. Explain the structure of MDX query?
2. 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
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?
2. 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
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?
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.