Hi all
I've got a real brain twister for you.
I'm building an OLAP cube (first timer) and it uses two measures; for example items and sales.
Each of the item/sales figures are added at the first of each month, so,
the smallest time dimension is: the 'First of the month',
drill up its parent dimension: 'Quarter',
drill up again to the: 'Year' dimension.
There are other dimensions: 'Customer', 'ProductType' and ItemType and they also drill down to more specific information but I don't think the problem is to do with them.
I need to be able to display the average items and sales for the previous three time measures for example: at month level: say for April; display information for Jan, Feb and Mar, at year level display average for the previous three years.
I came up with a SQL query to calculate the average for the previous three months and I was going to use this as another measure but then I was hit by the fact that the cube will be display the measures at a customer level and also an itemId level and by any other dimension and their drill downs.
This would mean the average for the previous three months for a particular customer or a certain ItemId etc.
Can anyone point me in the right direction I'm stuck between trying to solve the problem at the cube level (involves learning MDX) or finding a SQL query for a measure that satisfies the the cube/ three month average problem..
Any help would earn my eternal gratitude and respect.
Start Free Trial
