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

Advertisements

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()

Introduction to Storage Modes in SSAS(MOLAP,ROLAP,HOLAP)

Introduction
There are three standard storage modes (MOLAP, ROLAP and HOLAP) in OLAP applications which affect the performance of OLAP queries and cube processing, storage requirements and also determine storage locations.
SSAS (2005 and 2008) supports not only these three standard storage modes but supports proactive caching, a new feature with SSAS 2005 which enables you to combine the best of both worlds (ROLAP and MOLAP storage) for both frequency of data refresh and OLAP query performance.

Basic storage modes
The cube data can be divided into three different types – meta-data, detail data and aggregate data. No matter what storage is used, the meta-data will always be stored on the  OLAP server but storage of the detail data and aggregate data will depend on the storage mode you specify.
MOLAP (Multidimensional OLAP)
This is the default and most frequently used storage mode. In this mode when you process the cube, the source data is pulled from the relational store, the required aggregation is then performed and finally the data is stored in the Analysis Services server in a compressed and optimized multidimensional format.
After processing, once the data from the underlying relational database is retrieved there exists no connection to the relational data stores. So if there is any subsequent changes in the relational data after processing that will not reflect in the cube unless the cube is reprocessed and hence it is called offline data-set mode.
Since both the detail and aggregate data are stored locally on the OLAP server, the MOLAP storage mode is very efficient and provides the fastest query performance.
·         Pros
o    Stores the detail and aggregate data in the OLAP server in a compressed multidimensional format; as a result the cube browsing is fastest in this mode.
o    Provides maximum query performance, because all the required data (a copy of the detail data and calculated aggregate data) are stored in the OLAP server itself and there is no need to refer to the underlying relational database.
o    All the calculations are pre-generated when the cube is processed and stored locally on the OLAP server hence even the complex calculations, as a part the query result, will be pefromed quickly.
o    MOLAP does not need to have a permanent connection to the underlying relational database (only at the time of processing) as it stores the detail and aggregate data in the OLAP server so the data can be viewed even when there is connection to the relational database.
o    MOLAP uses compression to store the data on the OLAP server and so has less storage requirements than relational databases for same amount of data. (Note however, that beginning with SQL Server 2008 you can use data compression at relational database level as well).
·         Cons
o    With MOLAP  mode, you need frequent processing to pull refreshed data after last processing resulting in drain on system resources.
o    Latency; just after the processing if there is any changes in the relational database it will not be reflected on the OLAP server unless   re-processing is performed.
o    MOLAP stores a copy of the relational data at OLAP server and so requires additional investment for storage.
o    If the data volume is high, the cube processing can take longer, though you can use incremental processing to overcome this.
ROLAP (Relational OLAP)
In comparison with MOLAP, ROLAP does not pull data from the underlying relational database source to the OLAP server but rather both cube detail data and aggregation stay at relational database source. In order to store the calculated aggregation the database server creates additional database objects (indexed views). In other words, the ROLAP mode does not copy the detail data to the OLAP server, and when a  query result cannot be obtained from the query cache the created indexed views are accessed to provide the results.
·         Pros
o    Ability to view the data in near real-time.
o    Since ROLAP does not make another copy of data as in case of MOLAP, it has less storage requirements. This is very advantageous for large datasets which are queried infrequently such as historical data.
o    In ROLAP mode, the detail data is stored on the underlying relational database, so there is no limitation on data size that ROLAP can support or limited by the data size of relational database. In nutshell, it can even handle huge volumes of data.
·         Cons
o    Compared to MOLAP or HOLAP the query response is generally slower because everything is stored on relational database and not locally on the OLAP server.
o    A permanent connection to the underlying database must be maintained to view the cube data.
Note:If you use ROLAP storage mode and your relational database is SQL Server, the Analysis Services server may create indexed views for aggregation. However this requires a few prerequisite to be available – for example, the data source must be a table, not a view. The table name must use two part naming convention or it must be qualified with owner/schema name etc. For a complete list of these prerequisites you can refer to the link provided in reference section.
HOLAP (Hybrid OLAP)
This mode is a hybrid of MOLAP and ROLAP and attempts to provide the greater data capacity of  ROLAP and the fast processing and high query performance of MOLAP.
In HOLAP storage mode, the cube detail data remains in the underlying relational data store and the aggregations are stored on the OLAP server. If you query only summary data in aggregation, the HOLAP storage mode will work similar to MOLAP. For the detail data queries, HOLAP will drill through the detail data in underlying relational data store and hence performance would not be as good as MOLAP. Therefore, your query would be as fast as MOLAP in if your query result can be provided from query cache or aggregation but performance would degrade if it needs the detail data from relational data store.
·         Pros
o    HOLAP balances the disk space requirement, as it only stores the aggregate data on the OLAP server and the detail data remains in the relational database. So no duplicate copy of the detail data is maintained.
o    Since HOLAP does not store detail data on the OLAP server,  the cube and partitions would be smaller in size than MOLAP cubes and partitions.
o    Performance is better than ROLAP as in HOLAP the summary data are stored on the OLAP server and queries can be satisfied from this summary data.
o    HOLAP would be optimal in the scenario where query response is required and   query results are based on aggregations on large volumes of data.
·         Cons
o    Query performance (response time) degrades if it has to drill through the detail data from relational data store, in this case HOLAP performs very much like ROLAP.
Summary and comparison
Basic Storage Mode
Storage Location for Detail Data
Storage Location for Summary/ Aggregations
Storage space requirement
Query Response Time
Processing Time
Latency
MOLAP
Multidimensional Format
Multidimensional Format
MediumBecause detail data is stored in compressed format.
Fast
Fast
High
HOLAP
Relational Database
Multidimensional Format
Small
Medium
Fast
Medium
ROLAP
Relational Database
Relational Database
Large
Slow
Slow
Low
Configuring the storage mode
Setting the storage mode is a relatively straightforward process – select a particular OLAP object in BIDS (Business Intelligence Development Studio), right click on it and then select properties. The property calledStorageMode will allow you to set the storage mode to MOLAP, ROLAP or HOLAP.
References
http://msdn.microsoft.com/en-us/library/ms174915.aspx
Note: Content of the above article had been shared from multiple sources :S.SRIVATHSANI(sqlperformance.com),MSDN Books Online