BI xPress MSDN Universal Contest

I'm excited to announce that BI xPress 3.0 has released with it's new MDX Calculation Builder feature, which creates MDX calculations visually.

We're looking for the most useful MDX calculations to put into the product and want to incent you to come up with them! Please reply to this post with your best MDX statement. If it's really unique and useful (determined by our panelist), we'll put it into BI xPress, giving you a license of the product ($795 value*) and author credit in the product's feature for your script.

If you have the MOST useful one judged by a panel of your peers, you could win an MSDN Universal Subscription (an $11,899 value)!

You have until 2/13 at midnight EST to submit your MDX script right here. In your submission, please also provide a description of the script and it's use and as much documentation as you can provide about how it works. It must NOT be plagurized (we can tell )! We'll select the final winners that week and announce the winner here on 2/18.

*(one license of BI xPress eligible for the contest per contestant).

I'm much more insecure about my MDX than my SQL, but what the heck. Worst case, I learn something!

CREATE MEMBER CURRENTCUBE.[Measures].[Balance Diff %]
 AS CASE WHEN 
        IsEmpty(([Report Date].[Calendar].CurrentMember,[Measures].[Balance]))
    OR 
        IsEmpty(([Report Date].[Calendar].PrevMember,[Measures].[Balance]))
    THEN NULL
    ELSE
        CASE WHEN 
            ([Report Date].[Calendar].CurrentMember,[Measures].[Balance]) -
            ([Report Date].[Calendar].PrevMember,[Measures].[Balance]) = 0 
        THEN NULL
    ELSE
        (([Report Date].[Calendar].CurrentMember,[Measures].[Balance]) -
        ([Report Date].[Calendar].PrevMember,[Measures].[Balance])) / 
        ([Report Date].[Calendar].PrevMember,[Measures].[Balance])
        END
    END, 
FORMAT_STRING = "Percent", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Account Measures'; 

The following calculation provides direct cost per day using a scope statement to do the calculation just for inpatient records:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Total Direct Cost Per Day]
 AS 0, 
FORMAT_STRING = "##,##", 
VISIBLE = 1;    scope([Encounter].[In Out Code].&[I],[Total Direct Cost Per Day]);   
this=[Measures].[Actual Direct Cost]/[Measures].[Length Of Stay];   
format_string(This)="#,#";     

Here are some simple but useful calculations

with

  -- Returns the same period in the previous year.  e.g for June 2011, return June 2010
  member [Measures].[Same Period Previous Year] as
    (PARALLELPERIOD([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])
    ,format_string = 'Currency',associated_measure_group = 'Internet Sales'

  -- Returns the percent change from same period in previous year.
  member [Measures].[Percent Change Year Over Year] as
    iif(
      isempty([Measures].[Internet Sales Amount]) or [Measures].[Internet Sales Amount] = 0,-1,
      ([Measures].[Internet Sales Amount] - [Measures].[Same Period Previous Year]) / [Measures].[Internet Sales Amount]
    )
    ,format_string = 'Percent',associated_measure_group = 'Internet Sales'

  -- Perhaps this could be parameterized to an N period moving average 
  member [Measures].[3 Period Moving Average] as
    avg([Date].[Calendar].CurrentMember.Lag(2):[Date].[Calendar].CurrentMember,[Measures].[Internet Sales Amount])
    ,format_string = 'Currency',associated_measure_group = 'Internet Sales'

  -- Perhaps this could be parameterized to an N period weighted moving average
  member [Measures].[3 Period Weighted Moving Average] as
    (
    ([Date].[Calendar].CurrentMember,[Measures].[Internet Sales Amount]) * 3
    +([Date].[Calendar].CurrentMember.lag(1),[Measures].[Internet Sales Amount]) * 2
    +([Date].[Calendar].CurrentMember.Lag(2),[Measures].[Internet Sales Amount]) * 1
    ) / 6
    ,format_string = 'Currency',associated_measure_group = 'Internet Sales'
   
  --Simple application of the LinRegPoint function
  member [Measures].[LinearTrend] as
    LinRegPoint(
      Rank([Date].[Calendar].CurrentMember,[Date].[Calendar].CurrentMember.Level.Members)
      ,{[Date].[Calendar].CurrentMember.Level.Members}
      ,[Measures].[Internet Sales Amount]
      ,Rank([Date].[Calendar].CurrentMember,[Date].[Calendar].CurrentMember.Level.Members)
    ),format_string = 'Currency',associated_measure_group = 'Internet Sales'
   
select
  {
    [Measures].[Internet Sales Amount]
    ,[Measures].[Same Period Previous Year]
    ,[Measures].[Percent Change Year Over Year]
    ,[Measures].[3 Period Moving Average]
    ,[Measures].[3 Period Weighted Moving Average]
    ,[Measures].[LinearTrend]
  } on 0,
  non empty [Date].[Calendar].[Month] on 1
from
  [Adventure Works]

Hello Brian,

It's glad to know that there is such a creative and useful competition organized by BIDN network and I am happy to be a family of this network.

Well,below is a MDx script in order to find out the latest Current  12 months Internet Sales Amount accourding to Month wise and 

compare with the Previous 12 Month Total Internet sales amount along with finding the average value and percentage changes per previous 12 months 

and also displaying the total Internet sales amount of products in that particular month along with selecting for the particular Sales Territory country wise.

My Mdx script based on AdventureWorks2008 ---

WITH

 SET [Current 12 Months Sales] AS ' tail( { [Date].[Fiscal].[Month].members  },1 ).item(0):  tail( { [Date].[Fiscal].[Month].members  },1 ).item(0).lag(11)' 

 MEMBER [Date].[Fiscal].[Current 12 Months Total] AS aggregate( [Current 12 Months Sales] )  

 MEMBER [Product].[Category].[Total ] AS aggregate( [Product].[Category].[Category])               

 MEMBER [Date].[Fiscal].[Current 12 Months Average] AS aggregate( [Current 12 Months Sales] )/12                                                                                              

 MEMBER [Date].[Fiscal].[Previous 12 Months Total] AS aggregate( tail( { [Date].[Fiscal].[Month].members  },1 ).item(0).lag(12):  tail( { [Date].[Fiscal].[Month].members  },1 ).item(0).lag(23))                                                                                          

 MEMBER [Date].[Fiscal].[Previous 12 Months Average] AS aggregate( tail( { [Date].[Fiscal].[Month].members  },1 ).item(0).lag(12):  tail( { [Date].[Fiscal].[Month].members  },1 ).item(0).lag(23))/12                                                                                                                         

 MEMBER [Date].[Fiscal].[ Percent Change per Previous 12 Months ] AS IIF(  [Date].[Fiscal].[Previous 12 Months Total] =0, null , (( [Date].[Fiscal].[Current 12 Months Total] - [Date].[Fiscal].[Previous 12 Months Total]   )/[Date].[Fiscal].[Previous 12 Months Total])  ) , format_string='#0.00%', SOLVE_ORDER =1                                                                                                                       

SELECT   { Nonempty({ [Product].[Category].children, [Product].[Category].[Total ]} ) }

ON 0 ,

{[Current 12 Months Sales] ,  [Date].[Fiscal].[Current 12 Months Total] ,[Date].[Fiscal].[Current 12 Months Average],

 [Date].[Fiscal].[Previous 12 Months Total] ,  [Date].[Fiscal].[Previous 12 Months Average]  , 

 [Date].[Fiscal].[ Percent Change per Previous 12 Months ]}

ON 1

From [Adventure Works]

where ([Measures].[Internet Sales Amount],[Sales Territory].[Sales Territory Country].&[Australia])

---

Talking about how it works,firstly I have created the SET for finding the latest 12 month by going back /lag 12 month from the current month of fiscal year and then taking aggregate of that SET to find the Total Internet sales amount  and also similarly by lagging to 23 months to calculate the previous 12 month Internet sales amount and other calculations for the particular sales territory country by putting it in slice condition.Also,someone can see how these MDx function works on http://mdxpert.com/

This may be simple for one but hope this MDX script might helps for someone who need such a solution .

Hope you will consider this MDx script. :) 

Regards,

Anil Maharjan

The winner of the MSDN Subscription is dpurdy!  I will be contacting him now to work out getting him his prize.  Second place goes to Anil who has won a one year license to BIxPress

Hello Devin,

I am so glad to know that I won the second position, thanks for the prize hope I will be fully utilizing the BI XPress tool. I am wondering that is there any brief documents on 'How to use BI xPress and its features' I have downloaded some document from pragmatic works too and it's sad to say that I miss the web session on  BI xPress in Pragmatic works, hope to see the recorded session soon.

Lastly, thanks for the license of BI xPress tool.

Regards,

Anil Maharjan

When you download the tool you will find the documentation included for usage.  If you would like a live demo you can email sales@pragmaticworks.com.  Also, I forgot to mention this but both the submissions will be included in the next version of the product with a by line for Anil and Dave.