Example MDX Calculation for SSAS Cube

Example MDX Calculations

Sales Growth

([Time].CurrentMember, [Measures].[Sales]) - ([Time].CurrentMember.PrevMember, [Measures].[Sales])

PP Growth

([Time].CurrentMember, [Measures].[Sales]) - (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

YTD Sales

sum(ytd([Time].CurrentMember), [Measures].[Sales])

YTD Sales Using PeriodsToDate

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

Product Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

Product Total Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].[All Products], [Measures].[Unit Sales]) * 100

Average Stock - Brute Force

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity]) / Count(Descendants([Time].CurrentMember, [Month]))

Average Stock - Elegant

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Closing Period - Brute Force

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

Closing Period - Elegant

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max for Period

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min for Period

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Moving Average

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Products Down

Count(Filter(Descendants([Product].CurrentMember, [Product Name])             , ([Time].CurrentMember, [Measures].[Unit Sales])               < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales]))) / Count(Descendants([Product].CurrentMember, [Product Name]))

Dynamic Custom Default Member

Tail(Filter([Time].[Month].Members      , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Properties Query

Filter([Store].[Store Name].Members        , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

Products Down Query

select       {{{[Measures].[Products Down]}         * {[Customers].DefaultMember}         * {[Product].DefaultMember}}} on columns      , {[Time].&[1998].&[Q1].&[1]         , [Time].&[1998].&[Q1].&[2]         , [Time].&[1998].&[Q1].&[3]         , [Time].&[1998].&[Q2].&[4]         , [Time].&[1998].&[Q2].&[5]         , [Time].&[1998].&[Q2].&[6]         , [Time].&[1998].&[Q3].&[7]         , [Time].&[1998].&[Q3].&[8]         , [Time].&[1998].&[Q3].&[9]         , [Time].&[1998].&[Q4].&[10]         , [Time].&[1998].&[Q4].&[11]} on rows  from      [Sales_MDX2]

MDX Query

select     {[Measures].[Unit Sales]      , [Measures].[MA]} on columns     , {Descendants([Time].[Year].&[1997], [Month])} on rows from     [Sales_MDX2]

System DSN ODBC Connection Missing While Creating SSIS Connection Manager

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:

1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.

3. Create a new system DSN there.

4. Now try creating Connection Manager in SSIS package