nerdexam
Microsoft

70-466 · Question #52

You are developing a SQL Server Analysis Services (SSAS) cube. You are writing the following Multidimensional Expressions (MDX) statement for use by a calculated measure. The measure computes the sale

The correct answer is E. PARALLELPERIOD. PARALLELPERIOD navigates a time hierarchy to return a member from a prior period at the same relative position, making it the correct choice for same-period-previous-year calculations in MDX.

Build an analysis services database

Question

You are developing a SQL Server Analysis Services (SSAS) cube. You are writing the following Multidimensional Expressions (MDX) statement for use by a calculated measure. The measure computes the sales amount for the same time period of the previous year. (Line numbers are included for reference only.) 01 CREATE MEMBER CURRENT CUBE.Measures.SamePeriodPreviousYearSales AS 02 (Measures.[Sales Amount], 03 04 [Date Order].[Calendar].[Calendar Year], 05 1, 06 [Date Order].[Calendar].CurrentMember), 07 FORMAT_STRING = "$#.##", You need to complete the MDX statement. Which MDX function should you use in line 03? To answer, select the appropriate MDX function in the functions list.

Exhibit

70-466 question #52 exhibit

Options

  • ANONEMPTYCROSSJOIN
  • BOPENINGPERIOD
  • CORDER
  • DORDINAL
  • EPARALLELPERIOD
  • FPARENT
  • GPERIODSTODATE
  • HPREDICT
  • IPREVMEMBER
  • JPROPERTIES

How the community answered

(43 responses)
  • A
    5% (2)
  • D
    2% (1)
  • E
    81% (35)
  • G
    9% (4)
  • J
    2% (1)

Why each option

PARALLELPERIOD navigates a time hierarchy to return a member from a prior period at the same relative position, making it the correct choice for same-period-previous-year calculations in MDX.

ANONEMPTYCROSSJOIN

NONEMPTYCROSSJOIN returns a non-empty cross-join of multiple sets and is not a time-navigation function.

BOPENINGPERIOD

OPENINGPERIOD returns the first descendant of a specified level within a period, not a member from a parallel prior period.

CORDER

ORDER sorts a set of members based on an expression and has no time-navigation capability.

DORDINAL

ORDINAL returns the zero-based ordinal position of a level in a hierarchy and is unrelated to time period navigation.

EPARALLELPERIODCorrect

PARALLELPERIOD([Date Order].[Calendar].[Calendar Year], 1, [Date Order].[Calendar].CurrentMember) returns the member that is one calendar year prior at the same hierarchical level and relative position as the current member. This is precisely the time-intelligence behavior required to compute sales for the same period in the previous year, using the level, lag count, and current member as its three arguments.

FPARENT

PARENT returns the immediate parent of the current member, which moves up the hierarchy rather than to a parallel period in a prior parent.

GPERIODSTODATE

PERIODSTODATE returns a set of periods from the start of a containing period up to the current member, not a single parallel period from a prior year.

HPREDICT

PREDICT is used with data mining model predictions and has no role in time-period navigation.

IPREVMEMBER

PREVMEMBER returns the sequentially previous member at the same level, not the member from one full year prior at the same relative position.

JPROPERTIES

PROPERTIES returns a member or cell property value and has no time-navigation function.

Concept tested: MDX PARALLELPERIOD function for year-over-year time intelligence

Source: https://learn.microsoft.com/en-us/sql/mdx/parallelperiod-mdx

Topics

#MDX#PARALLELPERIOD#time intelligence#calculated measures

Community Discussion

No community discussion yet for this question.

Full 70-466 Practice