Addcalculatedmembers() – MDX function

in Analysis services as we write MDX queries against cube we add calculated members to the dimensions by using CREATE MEMBER or WITHMEMBER

Syntax : Addcalculatedmembers({set})

Addcalculatedmembers() will returns set of calculated members in cube or scope

lets see what MSDN says

: Returns a set generated by adding calculated members to a specified set.

well questions arises when we added a calculated member cant we retrieve it using the metadata functions like .Members, .Children, Descendants()

NO , only Base members are returned

lest’s go in to the details with some examples

OpeningPeriod(), ClosingPeriod() – MDX

Hi,

Today let’s learn two important functions in SSAS

  1. OpeningPeriod()
  2. ClosingPeriod()

In a BI reporting we definitely need to know the opening and closing time dimension at any level of the hierarchy

lets look at the behavior of these two functions and then we will go through some examples

ClosingPeriod()

OpeningPeriod (MDX)

Returns the first sibling among the descendants of a specified level, optionally at a specified member.

OpeningPeriod( [ Level_Expression [ , Member_Expression ] ] )
Level_Expression
A valid Multidimensional Expressions (MDX) expression that returns a level.
Member_Expression
A valid Multidimensional Expressions (MDX) expression that returns a member.

This function is primarily intended to be used the Time dimension, but can be used with any dimension.

  • If a level expression is specified, the OpeningPeriod function uses the hierarchy that contains the specified level and returns the first sibling among the descendants of the default member at the specified level.
  • If both a level expression and a member expression are specified, the OpeningPeriod function returns the first sibling among the descendants of specified member at the specified level within the hierarchy containing the specified level.
  • If neither a level expression nor a member expression are specified, the OpeningPeriod function uses the default level and member of the dimension with a type of Time.

Examples

The following example returns the value for the default measure for the FY2002 member of the Date dimension (which has a type of Time). This member is returned because the Fiscal Year level is the first descendant of the [All] level, the Fiscal hierarchy is the default hierarchy because it is the first user-defined hierarchy in the hierarchy collection, and the FY2002 member is the first sibling in this hierarchy at this level.

SELECT OpeningPeriod() ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for July 1, 2001 member at the Date.Date.Date level for the Date.Date attribute hierarchy. This member is the first sibling of the descendant of [All] level in the Date.Date attribute hierarchy.

SELECT OpeningPeriod([Date].[Date].[Date]) ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for January, 2003 member, which is the first sibling of the descendant of the 2003 member at the year level in the Calendar user-defined hierarchy.

SELECT OpeningPeriod([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Year].&[2003]) ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for July, 2002 member, which is the first sibling of the descendant of the 2003 member at the year level in the Fiscal user-defined hierarchy.

SELECT OpeningPeriod([Date].[Fiscal].[Month],[Date].[Fiscal].[Fiscal Year].&[2003]) ON 0
FROM [Adventure Works]

ClosingPeriod (MDX)


Returns the member that is the last sibling among the descendants of a specified member at a specified level.

ClosingPeriod( [ Level_Expression [ ,Member_Expression ] ] )
Level_Expression
A valid Multidimensional Expressions (MDX) expression that returns a level.
Member_Expression
A valid Multidimensional Expressions (MDX) expression that returns a member.

This function is primarily intended to be used against a dimension with a type of Time, but can be used with any dimension.

  • If a level expression is specified, the ClosingPeriod function uses the dimension that contains the specified level and returns the last sibling among the descendants of the default member at the specified level.
  • If both a level expression and a member expression are specified, the ClosingPeriod function returns the last sibling among the descendants of specified member at the specified level.
  • If neither a level expression nor a member expression is specified, the ClosingPeriod function uses the default level and member of the dimension (if any) in the cube with a type of Time.

The ClosingPeriod function is equivalent to the following MDX statement:

Tail(Descendants(Member_Expression, Level_Expression), 1).


Examples

The following example returns the value for the default measure for FY2007 member of the Date dimension (which has a semantic type of Time). This member is returned because the Fiscal Year level is the first descendant of the [All] level, the Fiscal hierarchy is the default hierarchy because it is the first user-defined hierarchy in the hierarchy collection, and the FY 2007 member is the last sibling in this hierarchy at this level.

SELECT ClosingPeriod() ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for November 30, 2006 member at the Date.Date.Date level for the Date.Date attribute hierarchy. This member is the last sibling of the descendant of [All] level in the Date.Date attribute hierarchy.

SELECT ClosingPeriod ([Date].[Date].[Date]) ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for December, 2003 member, which is the last sibling of the descendant of the 2003 member at the year level in the Calendar user-defined hierarchy.

SELECT ClosingPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Year].&[2003]) ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for June, 2003 member, which is the last sibling of the descendant of the 2003 member at the year level in the Fiscal user-defined hierarchy.

SELECT ClosingPeriod ([Date].[Fiscal].[Month],[Date].[Fiscal].[Fiscal Year].&[2003]) ON 0
FROM [Adventure Works]

Rolling averages or Moving averages in SSAS

Moving averages smooth the price data to form a trend following indicator. They do not predict price direction, but rather define the current direction with a lag. Moving averages lag because they are based on past prices. Despite this lag, moving averages help smooth price action and filter out the noise. They also form the building blocks for many other technical indicators and overlays, such as Bollinger BandsMACD and the McClellan Oscillator. The three most popular types of moving averages are the Simple Moving Average (SMA) and Weighted Moving Average(WMA) the Exponential Moving Average (EMA). These moving averages can be used to identify the direction of the trend or define potential support and resistance levels.

Simple Moving Average(SMA)

A simple moving average is formed by computing the average price of a security over a specific number of periods. Most moving averages are based on closing prices. A 5-day simple moving average is the five day sum of closing prices divided by five. As its name implies, a moving average is an average that moves. Old data is dropped as new data comes available. This causes the average to move along the time scale. Below is an example of a 5-day moving average evolving over three days.

Daily Closing Prices: 11,12,13,14,15,16,17 First day of 5-day SMA: (11 + 12 + 13 + 14 + 15) / 5 = 13 Second day of 5-day SMA: (12 + 13 + 14 + 15 + 16) / 5 = 14 Third day of 5-day SMA: (13 + 14 + 15 + 16 + 17) / 5 = 15

The first day of the moving average simply covers the last five days. The second day of the moving average drops the first data point (11) and adds the new data point (16). The third day of the moving average continues by dropping the first data point (12) and adding the new data point (17). In the example above, prices gradually increase from 11 to 17 over a total of seven days. Notice that the moving average also rises from 13 to 15 over a three day calculation period. Also notice that each moving average value is just below the last price. For example, the moving average for day one equals 13 and the last price is 15. Prices the prior four days were lower and this causes the moving average to lag.


In a Moving aggregation , the important technique is to construct a range with in the level using endpoints that are relative to the current member

we can create this range using many functions in MDX depending upon the range

Examples

Averages for 6 months range

Avg({[date].[month].currentmember.Lag(5): [date].[month]},[Measures].[sales])

Averages for 6 month range

Avg(Lastperiods(6,[time].[month].currentmember),[Measure].[sales])

Average of current period and  prior period

Avg({[date].[month].currentmember.prevmember, [date].[month].currentmember},[Measure].[sale])

Using Parallel periods

Avg({parallelperiods([date].[halfyear],1,[date].[currentmember]):[date].currentmember]},[Measure].[sale])

 

Here is the example on Adventure Works cube

WITH
 MEMBER [Measures].[SMA12] AS Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
 )
 MEMBER [Measures].[SMA6] AS Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(5):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
 )
 MEMBER [Measures].[SMA3] AS Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(2):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
 )
SELECT
 {[Measures].[Internet Sales Amount], [Measures].[SMA12], [Measures].[SMA6], [Measures].[SMA3]} ON 0
 ,[Ship Date].[Calendar].[Month] ON 1
FROM [Adventure Works]

code

with
member
[Measures].[avg12ms] as

avg([Date].[Month of Year].lag(11):[Date].[Month of Year] ,[Measures].[Internet Sales Amount])
member [Measures].[avg6ms] as

avg([Date].[Month of Year].lag(5):[Date].[Month of Year] ,[Measures].[Internet Sales Amount])
member [Measures].[avg3ms] as
avg([Date].[Month of Year].lag(2):[Date].[Month of Year] ,[Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount],[Measures].[avg12ms],[Measures].[avg6ms],[Measures].[avg3ms]} on columns

,{[Date].[Calendar].[Month]} on rows

from [Adventure Works]


					

PeriodsToDate (MDX)

Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.

PeriodsToDate( [ Level_Expression [ ,Member_Expression ] ] )

Level_Expression

A valid Multidimensional Expressions (MDX) expression that returns a level.

Member_Expression

A valid Multidimensional Expressions (MDX) expression that returns a member.

Within the scope of the specified level, the PeriodsToDate function returns the set of periods on the same level as the specified member, starting with the first period and ending with specified member.

  • If a level is specified, the current member of the hierarchy is inferred hierarchy.CurrentMember, where hierarchyis the hierarchy of the specified level.
  • If neither a level nor a member is specified, the level is the parent level of the current member of the first hierarchy on the first dimension of type Time in the measure group.

PeriodsToDate( Level_Expression, Member_Expression ) is functionally equivalent to the following MDX expression:

TopCount(Descendants(Ancestor(Member_Expression, Level_Expression), Member_Expression.Level), 1):Member_Expression

The following example returns the sum of the Measures.[Order Quantity] member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.

WITH MEMBER [Date].[Calendar].[First8Months2003] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year],
            [Date].[Calendar].[Month].[August 2003]
        )
    )
SELECT
    [Date].[Calendar].[First8Months2003] ON COLUMNS,
    [Product].[Category].Children ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

The following example aggregates over the first two months of the second semester of calendar year 2003.

WITH MEMBER [Date].[Calendar].[First2MonthsSecondSemester2003] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Semester],
            [Date].[Calendar].[Month].[August 2003]
        )
    )
SELECT
    [Date].[Calendar].[First2MonthsSecondSemester2003] ON COLUMNS,
    [Product].[Category].Children ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]
please use the below picture to better understand PeriodsToDate()