Welcome in Dinesh Patel Blog

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]

Micorosft SQL Server: http://www.microsoft.com/events/podcasts/default.aspx?podcast=podcast&pageId=x40&topic=Topic-202f8d31-bbff-4d7e-a3f5-c4f19f935a09 The Microsoft IT Manager: http://feeds.feedburner.com/itmanager The Workshop Leaders Resource: http://feeds.feedburner.com/TheWorkshopLeadersResource

ESL Podcast - Previous Episodes: http://www.eslpod.com/past.xml English as a Second Language: http://feeds.feedburner.com/englishasasecondlanguagepodcast English Teacher John Show: http://feeds.feedburner.com/englishteacherjohn The Word Nerds: http://thewordnerds.org/?feed=podcast An Idiom a Day with Dave Jackson: http://www.languagelearningcenter.net/feed/

Hindi Audio: http://jewels.brahmakumaris.org/alfresco/wcs/classes/podcast?guest=true&siteName=Media4Seva&language=h&type=audio Hindi Video: http://jewels.brahmakumaris.org/alfresco/wcs/classes/podcast?guest=true&siteName=Media4Seva&language=h&type=video English Audio: http://jewels.brahmakumaris.org/alfresco/wcs/classes/podcast?guest=true&siteName=Media4Seva&language=e&type=audio English Video: http://jewels.brahmakumaris.org/alfresco/wcs/classes/podcast?guest=true&siteName=Media4Seva&language=e&type=video More on: http://jewels.brahmakumaris.org/