MDX measure as on date

Question

I need a help in MDX

My product data(ProductId and Launch date) is like this...

P1 4/1/2011

P2 5/5/2011

P3 3/6/2011

My fact(Trans Id, Prod Id, Qty, Sales,Txn date) is like this...

T1 P1 200 2000 2/4/2011

T1 P1 200 2000 7/4/2011

T2 P1 100 2000 10/4/2011

T3 P1 20 2000 15/4/2011

T4 P2 200 2000 6/5/2011

T5 P3 200 2000 6/6/2011

When I put Apr 2011, I need to see only P1's qty (520) since it got launched on April and Count of Qty and Transaction amount for the same month....

Thanks

When I put a date dimension on Axies i need to see, only the product launched in that month along with itsTransaction numbers (sum of sales and Quantity)....

Tullo_x86 Tullo_x86 · Accepted Answer · 2011-06-07T00:15:42

This is going to depend on your cube structure. From how you've described it, the launch date is an attribute of a product (because it maps 1:1 to a product - a product is only going to be launched once, not once per fact) and therefore should be an attribute of your Product dimension. The transaction date is independent of this - it should be in its own dimension.

The query you would then write would look something like this:

SELECT { [Measures].[Quantity] } ON 0,
       { 
         [Product Dimension].[Launch Date Hierarchy].[Month Level]
         *
         [Product Dimension].[Product Name Attribute].[Product Name Level]
       } ON 1
FROM [Cube]

If you want to filter for sales made in a particular month, you might add:

WHERE { [Transaction Date Dimension].[Transaction Date Hierarchy].[Month Level].[2011 January] }

MDX measure as on date

1 Answers