Sql Server SSAS MDX Query

Question

I have a table, simplified as follows - FactSales:

Date      Reference      Amount     TypeId     Seller
1/10/2013 A1             100        1          100
3/10/2013 A2             200        1          200
5/10/2013 A1             -100       2          100

And foreign key tables for DimType - 1 as sale, 2 as refund, and DimSellers. Also have a DimDate table etc.

Essentially, this is all processed into a SSAS cube, and am now attempting to create MDX queries to count the total the number of sales, not the sum of the sales amounts. Colums should represent Seller and rows the months for the previous 12 months to current. I am successfully able to perform a count with the seller in the columns and months as rows, but only filtering on the type 1 for sales.

The part i am a little caught on is that a sale should only count as a sale in the month, if the sale was not refunded (type 2) in the same month. If it was refunded in a future month after the type 1 sale, it should be counted. If refunded in the same month as sold, it should not count as a sale.

Expected results should be from my example. As A1 was refunded in the same month as it was sold it's not counted as a sale in October.

         Seller 100.     Seller 200.   ...
Oct 13.    0.                1
Sept 13 ...
Aug 13 ...
...
Nov 12 ...

Any help is appreciated.

Answer 1

Assuming the objects in your cube are named as follows:

• Seller attribute: [DimSeller].[Seller],
• date hierarchy: [DimTime].[Date] with level Month containing the months,
• type attribute hierarchy: [DimType].[Type] with keys 1 and 2,
• the measure based on the Amount column has the name Amount,
• there is an attribute hierarchy [Sale].[SaleId] identifying different sales events (needed as I understand you want to count sales events, not sellers),
• and your cube is named CubeName.

Then the following MDX should deliver what you want:

WITH Member [Measures].[SellerCount] AS
     Filter( [Sale].[SaleId].[SaleId].Members * { [DimType].[Type].&[1] }
             (
              [DimSeller].[Seller].CurrentMember,
              [DimTime].[Date].CurrentMember,
              [DimType].[Type].&[2], Measures.Amount
             ) = 0
           ).Count
SELECT [DimSeller].[Seller].[Seller].Members
       ON COLUMNS,
       [DimTime].[Date].[Month].Members
       ON ROWS
  FROM [CubeName]
 WHERE [Measures].[SellerCount]

This assumes that there are no positive refunds, i. e. refunds with 0 amount do not count, and it cannot happen that several (possibly negative and positive) refunds in a month add up to 0.

However, you could also implement this via an attribute e. g. "Has Refund In Same Month" and assuming a measure "Sales Event Count" just as count on the fact table, then users could make use of this in more or less every client tool that can access the cube, as there would not be any WITH clause needed: The MDX would simply be:

SELECT [DimSeller].[Seller].[Seller].Members
       ON COLUMNS,
       [DimTime].[Date].[Month].Members
       ON ROWS
  FROM [CubeName]
 WHERE (
        [Measures].[Sales Event Count],
        [Sales].[Has Refund In Same Month].[Yes]
       )

To implement this, you would add a column either as named column in the DSV, or maybe in a view on your table with the following SQL code:

CASE WHEN EXISTS ( SELECT 1
                     FROM table e
                    WHERE DatePart(Month, e.Date) = DatePart(Month, mainTable.Date)
                      AND DatePart(Year, e.Date) = DatePart(Year, mainTable.Date)
                      AND e.Seller = mainTable.Seller
                 )
          THEN 'Yes'
          ELSE 'No'
END