How to calculate YTD, QTD, MTD and WTD?

YTD:

YTD function returns a set of sibling members from the Year level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Ytd([Member_Expression ])

Example:

Let's discuss YTD function by using a sample query. If you execute following MDX query,it will return a set of sibling members from Year level starting with the first sibling member i.e. January 2003 and ending with the specified member i.e. September 2003.

WITH

  SET [Required Set of Months] AS

    YTD([Date].[Calendar].[Month].[September 2003])

SELECT

  {[Measures].[Reseller Sales Amount]} ON COLUMNS

 ,[Required Set of Months] ON ROWS

FROM [Adventure Works];

Output:

Let's have a look at another example.If you execute following MDX query, then query will return the cumulative SUM of “[Reseller Sales Amount]” from Jan 03 to Dec 03.

WITH 
  MEMBER [Measures].[Calculate YTD] AS 
    Sum
    (
      YTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Calculate YTD]
  } ON COLUMNS
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month]
   ,SELF
  ) ON ROWS
FROM [Adventure Works];

Output:

If you observe the value of a measure “Calculate YTD” for a month of February 2003 then that is the SUM of “Reseller Sales Amount” of “January 2003 and February 2003”.Similarly the value of measure for a month of “December 2003” is SUM of “Reseller Sales Amount” from “January 2003 to December 2003”.

QTD:

QTD function returns a set of sibling members from the Quarter level as a given member, starting with the first sibling member and ending with the specified member.

Syntax: Qtd([Member_Expression ])

Example:

If you execute following MDX query,it will return a set of sibling members from Quarter level (i.e. from 3^rd Quarter of year 2003 (Q3)) starting with first sibling member i.e. July 2003 and ending with the specified member i.e. September 2003

WITH 
  SET [Required Set of Quarters] AS 
    QTD ([Date].[Calendar].[Month].[September 2003]) 
SELECT 
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
 ,[Required Set of Quarters] ON ROWS
FROM [Adventure Works];

Let's have a look at another example.If you execute the following MDX query,the query will return the SUM of “Reseller Sales Amount”, aggregated over each quarter i.e. if you observe value of a measure “Calculate QTD” for “March 2003”, you will find that it is a sum of “January 2003, February 2003 and March 2003” as these members belongs to first Quarter. Similarly if you observe a value for “June 2003”, you will find that it is a sum of “April 2003, May 2003 and June 2003” as these members belongs to second Quarter.

WITH 
  MEMBER [Measures].[Calculate QTD] AS 
    Sum
    (
      QTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Calculate QTD]
  } ON COLUMNS

,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month]
   ,SELF
  ) ON ROWS
FROM [Adventure Works];

MTD:

MTD function returns a set of sibling members from the Month level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Mtd([Member_Expression ])

Example:

If you execute following MDX query,it will return a set of sibling members from Month level,starting with the first sibling member i.e. “September 1, 2003” and ending with the specified member i.e. “September 8, 2003”.

WITH 
  SET [Set Of Members] AS
    MTD([Date].[Calendar].[Date].[September 8, 2003])
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,[Set Of Members] ON ROWS
FROM [Adventure Works];

Let’s discuss one more example, If you execute following MDX query, you will get a value “$525,802.16” which is the SUM of “Internet Sales Amount” from “1^st July 2003” to “19^th July 2003”.

WITH 
  MEMBER [Measures].[Calculate MTD] AS 
    Sum
    (
      MTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[Calculate MTD]} ON COLUMNS
 ,[Date].[Calendar].[Date].[July 19, 2003] ON ROWS
FROM [Adventure Works];

WTD:

WTD function returns a set of sibling members from the Weeks level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Wtd([Member_Expression ])

Example:

To understand WTD in more proper way, modify “Calendar Weeks” hierarchy. Add “Date” level to the “Calendar Weeks” hierarchy and execute following MDX query.

WITH 
  SET [Output set of members using WTD] AS 
    WTD([Date].[Calendar Weeks].[Date].[September 8, 2003]) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,[Output set of members using WTD] ON ROWS
FROM [Adventure Works];

If you execute above MDX query,it will return a set of sibling members from Week level,starting with the first sibling member i.e. “September 7, 2003” and ending with the specified member i.e. “September 8, 2003”.

Let's have a look at another example;If you execute the following MDX query,it will return  “$114,368.59” which is the SUM of “Internet Sales Amount” from “16^th February 2003 to “21^st February 2003”.

WITH 
  MEMBER [Measures].[Calculate WTD] AS 
    Sum
    (
      WTD
      (
        [Date].[Calendar Weeks].CurrentMember
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[Calculate WTD]} ON COLUMNS
 ,[Date].[Calendar Weeks].[Date].[February 21, 2003] ON ROWS
FROM [Adventure Works];

How designing proper partitions helps in improving MDX query performance?

You can improve and enhance mdx query performance by using proper partition strategies. If your measure group contains a single partition and if a partition is very large then it’s always a good practice to split a single partition into multiple smaller partitions. If your fact table contains huge data for multiple years then you can create multiple partitions as per year. Just for a example consider your fact table contains data for 4 years i.e. from year 2002 to 2006 and you have created 4 partitions i.e. one partition for each year. Let’s say a partition named “Partition_2002” contains data for year 2002 only, a partition named “Partition_2003” contains data for year 2003 only and so on up to year 2006. Now whenever you execute MDX query to fetch data for year 2003, analysis services will scan “Partition_2003” partition only. Let’s discuss this scenario with Adventureworks sample database, adventureworks sample contains “Reseller Sales” measure group which contains 4 partitions i.e. Reseller_Sales_2001, Reseller_Sales_2002, Reseller_Sales_2003 and Reseller_Sales_2004. Reseller_Sales_2001 partition contains data up to year 2001, Reseller_Sales_2002 partition contains data for year 2002, Reseller_Sales_2003 partition contains data for year 2003 and partition Reseller_Sales_2004 contains data for year 2004. Now let’s demonstrate this with some mdx example. Before executing mdx, clear analysis services cache. For 

clearing cache, execute following Clear cache xmla statement.

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

    <DatabaseID>Adventure Works DW 2008</DatabaseID>

  </Object>

</ClearCache>

After clearing cache, Open SQL Profiler and connect to analysis services instance. Execute following MDX query for retrieving data of [Reseller Sales Amount] for year 2003. 

SELECT

{[Measures].[Reseller Sales Amount]} ON COLUMNS

FROM [Adventure Works]

WHERE

([Date].[Calendar].[Calendar Year].&[2003])

 

After execution of mdx query if you see Profiler, you will find following text in the TextData column;

This indicates that analysis services scanned only 2003 partition i.e. Reseller_Sales_2003 partition and ignored unnecessary scan of other partitions (i.e.'Reseller_Sales_2001', 'Reseller_Sales_2002' and 'Reseller_Sales_2004'). So this means, if you split a large partition into smaller multiple partitions then analysis services scans only required partition i.e. Partitions which contain required data in the subcube are queried only, thus avoiding the cost of reading unnecessary data which in turn helps in improving MDX query performance.

Even while doing Partitioning, setting Slice property on partitions is very important. Sometimes you will find that, though you have multiple partitions (let’s say one partition for each year) but when you execute mdx query, profiler show scans of other partitions also and such behavior seems if we have not set the Slice property either. Let’s discuss this with some sample demonstration, Clear the cache and execute following mdx query;

SELECT

{[Measures].[Internet Sales Amount]} ON COLUMNS

FROM [Adventure Works]

WHERE

([Date].[Calendar].[Calendar Year].&[2003])

After execution of mdx query if you see Profiler, you will find following text in the TextData column;

So though you have executed mdx query to fetch data for year 2003 only but analysis services scanned two extra partitions (i.e. partition 'Internet_Sales_2001' and 'Internet_Sales_2002').

Now set Slice property for each partition i.e. set slice property for a partition 'Internet_Sales_2001' as [Date].[Calendar].[Calendar Year].&[2001], set slice property for a partition 'Internet_Sales_2002' as [Date].[Calendar].[Calendar Year].&[2002],

Set slice property for a partition 'Internet_Sales_2003' as [Date].[Calendar].[Calendar Year].&[2003] and set slice property for a partition 'Internet_Sales_2004' as [Date].[Calendar].[Calendar Year].&[2004].

and after that do ProcessFull of “Internet Sales” measure group.

After processing,clear the cache.Open SQL profiler and execute same mdx for retrieving data of [Measures].[Internet Sales Amount] for year 2003,

SELECT

{[Measures].[Internet Sales Amount]} ON COLUMNS

FROM [Adventure Works]

WHERE

([Date].[Calendar].[Calendar Year].&[2003])

Now if you notice a Profiler TextData,you will find that analysis services is scaning only 'Internet_Sales_2003' partition.

So when you split large partition into smaller partitions,always set appropriate Slice property on partitions.

Even Sometimes when you execute mdx query,analysis services may retrieve more data than required to answer future queries more efficiently. this behaviour is called Prefetching and you can turned off this by setting connection string parameter "Disable Prefetch Facts=True; Cache Ratio=1”

How to give administrative rights on analysis services

Several times people ask question “How to give administrative rights on analysis server”? So let’s discuss on this topic. The users belong to server administrators have complete access to the features and functionality of the  SSAS instance like complete access to all SSAS databases on that instance. If you want to assign administrative rights to any user then follow following steps for doing the same.

Step1:

Connect to the analysis services instance by user with administrative rights, right click on instance name and select "Properties" option; it opens “Analysis Services Properties” window.

Step2:

In “Analysis Services Properties” window, click on “Security” tab.

Step3:

Now click on “Add” button, it opens “Select Users or Groups” window.Enter the active directory user to whom you want to give server administrative rights and click OK button.

Step4:

Now Active directory user will appear under the list of “Server Administrators”. Click on OK button.

For BOL article,you can refer;
http://technet.microsoft.com/en-us/library/cc304417.aspx

Retrieving data for current year, current month and current day.

You can retrieve current year, current month and current day by using VBA functions.

MDX for retrieving current Year;

WITH MEMBER [Measures].[Current Year] AS

    VBAMDX.Format(VBAMDX.Now(),"yyyy")

SELECT

  {[Measures].[Current Year]} ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Month;

WITH MEMBER [Measures].[Current Month] AS

    VBAMDX.Format(VBAMDX.Now(),"MM")

SELECT

  {[Measures].[Current Month]} ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Date;

WITH MEMBER [Measures].[Current Date] AS

    VBAMDX.Format(VBAMDX.Now(),"dd")

SELECT

  {[Measures].[Current Date]} ON COLUMNS

FROM [Adventure Works];

Sometimes user wants to retrieve data for current year, current month, and current day. You can retrieve data by using VBA function and STRTOMEMBER function.

Check following MDX samples by using Adventure Works sample.

MDX for retrieving current Year;

WITH

  MEMBER [Measures].[Current Year Value] AS

    (

      StrToMember

      (

            "[Date].[Calendar].[Calendar Year].&["

          + VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]"

      )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Current Year Value] ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Month;

WITH

  MEMBER [Measures].[Current Month Value] AS

    (

      StrToMember

        (

            "[Date].[Calendar].[Month].&[" +

             VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]

            &[" + VBAMDX.Format(VBAMDX.Now(),"MM") + "]"

        )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Current Month Value] ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Day;

WITH

  MEMBER [Measures].[Today's Value] AS

    (

      StrToMember

      (

           "[Date].[Calendar].[Date].&[" +

           VBAMDX.Format(VBAMDX.Now(),"yyyyMMdd")

           + "]"

      )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Today's Value] ON COLUMNS

FROM [Adventure Works];

Note : AdventureWorks sample does not contain data for current year,so if you execute above mentioned MDX,it results in Null values but if you insert data for current year,it will result the required output.