Welcome in Dinesh Patel Blog

SUM(     LastPeriods(         3,         PARALLELPERIOD(             [Date].[CalenderHierarchy].[Month],             1,    Ancestor(                 [Date].[CalenderHierarchy].CURRENTMEMBER                ,[Date].[CalenderHierarchy].[Month]             )         )     )     ,[Measures].[Count] )

SUM(     PARALLELPERIOD(         [Date].[CalenderHierarchy].[Month],         1, Ancestor(             [Date].[CalenderHierarchy].CURRENTMEMBER            ,[Date].[CalenderHierarchy].[Month]         )     ),     [Measures].[Count] )

http://www.mssqltips.com/sqlservertip/2565/ssas--best-practices-and-performance-optimization--part-1-of-4/ 

WITH MEMBER  [Measures].[ParameterCaption]  AS  [ReInsurer].[Re  Insurer Name ]. CURRENTMEMBER . MEMBER_CAPTION MEMBER  [Measures].[ParameterValue]  AS  [ReInsurer].[Re Insurer Name]. CURRENTMEMBER . UNIQUENAME MEMBER  [Measures].[ParameterLevel]  AS  [ReInsurer].[Re Insurer Name]. CURRENTMEMBER . LEVEL . ORDINAL SELECT  {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]}  ON   COLUMNS ,  ORDER ([ReInsurer].[Re Insurer Name]. CHILDREN ,[Measures].[ParameterCaption], BASC )  ON ROWS FROM   [CBProcedeReporting]

WITH MEMBER [Direct] AS (([dimExpenseDimension].[Direct Indirect].&[1]&[1], [Measures].[YTD Expense Sum]) / [Measures].[YTD Gross Earned Premium]) MEMBER [Regional Office Recharges] AS (([dimExpenseDimension].[Major Expenses].&[1]&[2]&[6], [Measures].[YTD Expense Sum]) / [Measures].[YTD Gross Earned Premium]) MEMBER [Platform Recharge] AS (([dimExpenseDimension].[Major Expenses].&[1]&[2]&[5], [Measures].[YTD Expense Sum]) / [Measures].[YTD Gross Earned Premium]) select {[Direct],[Regional Office Recharges],[Platform Recharge]} on columns , [dimLocationDimension].[Country]. CHILDREN on rows from [cubeExpenseFact] where ([dimTimeDimension].[Month Wise].[Month].&[2009]&[12])

·          Null values in measures —For each fact table measure that is loaded into the partition, you can decide how Analysis Services interprets null values. Consider the following example. Your sales fact table contains a record that has a sales amount of 1000 and a discount amount of null . When discount is loaded into the cube, by default it is interpreted as a zero , which means that it is not considered empty . How Analysis Services interprets null values is controlled by a property called NullProcessing . The NullProcessing property is set on a measure-by-measure basis. By default, it is set to Automatic which means that Analysis Services converts the null values to zero. If you want to preserve the null value from the source system, such as in the example of the discount measure, configure the NullProcessingProperty of that measure to Preserve instead of Automatic . ·          Null values in calc...

WITH SET [TopCountCustomers] AS { TopCount ( NONEMPTY ( [dimCustomerDimension].[Full Name]. CHILDREN *    [dimPolicydimension].[Policy Number]. CHILDREN *          [dimLocationDimension].[Branch]. CHILDREN * [dimChannelDimension].[Intermediary]. CHILDREN * [dimClaimsTransactionFact].[Claim Serial Number]. CHILDREN * [dimPolicydimension].[Reinsurer Code]. CHILDREN * [dimPolicydimension].[RI TYPE]. CHILDREN * [dimClaimsTransactionFact].[Occurrence Date]. CHILDREN * [dimClaimsTransactionFact].[Claim Transaction Date]. CHILDREN ),20, [Measures].[YTD Gross Claims Incurred WO IBNR])} SELECT { [Measures].[YTD Gross Claims Incurred WO IBNR],[Measures].[YTD RI Share of Claim], [Measures].[YTD AXA Share] } on columns ,{ [TopCountCustomers]  } on rows from ( SELECT TopCount ( NONEMPTY ([dimPolicydimension].[Policy Number]. CHILDREN ),20, [Measures].[YTD Gross Claims Incurred WO IBNR] ) on columns from [cubeClaimsT...

Old Query: SELECT {[Measures].[YTD Gross Claims Incurred WO IBNR]} on columns , { TopCount ( NONEMPTY ([dimCustomerDimension].[Full Name]. CHILDREN ), 40 ,[Measures].[YTD Gross Claims Incurred WO IBNR]) }  on rows from [cubeClaimsTransFact] WHERE ([dimTransactionTimeDimension].[Month Wise].[Month].&[2010]&[2]) New Query: select   { [Measures].[YTD Gross Claims Incurred WO IBNR]} ON COLUMNS , TopCount ( Filter ([dimCustomerDimension].[Full Name]. CHILDREN , ( NOT IsEmpty ([Measures].[YTD Gross Claims Incurred WO IBNR]))) ,40 ,[Measures].[YTD Gross Claims Incurred WO IBNR]) ON ROWS from [cubeClaimsTransFact] WHERE ([dimTransactionTimeDimension].[Month Wise].[Month].&[2010]&[2])

WITH SET [TopCountCustomers] AS        { NONEMPTY ( [dimCustomerDimension].[Full Name]. CHILDREN *    [dimPolicydimension].[Policy Number]. CHILDREN *          [dimLocationDimension].[Branch]. CHILDREN * [dimChannelDimension].[Intermediary]. CHILDREN * [dimClaimsTransactionFact].[Claim Serial Number]. CHILDREN * [dimPolicydimension].[Reinsurer Code]. CHILDREN * [dimPolicydimension].[RI TYPE]. CHILDREN * [dimClaimsTransactionFact].[Occurrence Date]. CHILDREN * [dimClaimsTransactionFact].[Claim Transaction Date]. CHILDREN )} SELECT { [Measures].[Claims Paid], [Measures].[YTD Gross Claims Incurred WO IBNR], [Measures].[YTD RI Share of Claim], [Measures].[YTD AXA Share] } on columns ,{ [TopCountCustomers]  } on rows from ( select TopCount (    NONEMPTY (       [dimPolicydimension].[Policy Number]. CHILDREN        ...

To improve MDX Query performance use NonEmpty Function and use filter instead of IIF( IS NULL) Function and  in Calculated measure WITH MEMBER [TEST1] AS SUM(LastPeriods(12,[dimTimeDimension].[Month].&[2009]&[12]), [Measures].[Gross Written Premium]) MEMBER [TEST2] AS SUM(LastPeriods(12,[dimTimeDimension].[Month].&[2008]&[12]), [Measures].[Gross Written Premium]) MEMBER [TEST3] AS SUM(LastPeriods(12,[dimTimeDimension].[Month].&[2007]&[12]), [Measures].[Gross Written Premium]) MEMBER [TEST4] AS SUM(LastPeriods(12,[dimTimeDimension].[Month].&[2006]&[12]), [Measures].[Gross Written Premium]) MEMBER [TEST5] AS SUM(LastPeriods(12,[dimTimeDimension].[Month].&[2005]&[12]), [Measures].[Gross Written Premium]) MEMBER [R12 LR] AS ([Measures].[R12 Claims Incurred WO IBNR] / [Measures].[R12 Gross Earned Premium ]) select { [Measures].[R12 LR] } ON COLUMNS , TOPCOUNT(filter(NONEMPTY( [dimCustomerDimension].[Full Name].children * [dimChannelD...

Below Query Navigate one level up into Calender Year Hierarchy ( from Quarter to Year) SELECT Ancestor ([dimTimeDimension].[Calendar Year].[Quarter].&[1986]&[1],1) ON 0 FROM [cubePolicyFact]

How to Check Opening and Closing Amount using MDX. Here is MDX Query:   SELECT [Measures].[Actual GWP Sum] ON 0, OpeningPeriod ([dimTimeDimension].[Calendar Year].[Date], [dimTimeDimension].[Calendar Year].[Year].&[2009]) ON 1 FROM [cubePolicyFact] SELECT [Measures].[Actual GWP Sum] ON 0, ClosingPeriod ([dimTimeDimension].[Calendar Year].[Date], [dimTimeDimension].[Calendar Year].[Year].&[2009]) ON 1 FROM [cubePolicyFact]