Hi

I'm working in my first OLAP-AS based project. I already have the
de-normalized database, analysis services database and cubes working (using
BIP as client). The data we are analysing is sales from a fast food chain. I
created two cubes: one has sales data down to the transaction count detail,
the other has data down to units per product sold. As each transaction can
have more than one product, I found this approach easier to understand. We
have many predefined BIP "views", some based on the transaction count cube
and others based on the product sales cube.

Now, I need to add two new features to de database:

1. I need to calculate "daily units sold average" in the product sales cube.
That is, if viewing data for a year, calculate: sum of units sold in the
year / 365. If viewing data for a week, sum of units sold in the week / 7. I
guess this is a simple MDX formula, but I'm totally lost on where to start.

2. I need to create a new virtual cube based in the product sales cube, but
with all the measures scaled down to what sales would have been if the
store/city/district only had had 100 transactions. For example, if a store
has 800 transaction in one day, and sold 300 big sandwich and 250 beverages,
the scaled down data should show 37.5 big sandwich (300*100/800) and 31.25
beverages (250*100/800). This scaling down should work the same while
viewing a week/month/year, etc. sales. The transaction count measure is in
the
transaction count cube, which shares many of the dimensions with the product
sales cube.

I'll appreciate any comments. Thanks in advance.

--
Carlos Gutiérrez
carlosg (AT) NOSPMsca (DOT) com.mx

Assuming that you combine the [Product Sales] and [Transaction Count]
cubes in a single virtual cube, sharing common dimension, then here are
some ideas:

- Assuming that there is a [Time] dimension that goes down to the [Day]
level, define a calculated measure like [DayCount] as
'Descendants([Time].CurrentMember, [Day]).Count'

- Then [AvgDailyUnitsSold] is '[UnitsSold]/[DayCount]'

- [TrasactionCount] from the 2nd cube may have to wrapped in
ValidMeasure() to return a valid value, in case some analysis dimensions
like [Product] aren't present in that cube.

- An example of scaling down 1st cube measures using calculated cells
(assuming you have AS2K Enterprise): calculation subcube is
{[UnitsSold]}, calculation formula is
'(CalculationPassValue([UnitsSold], -1,
RELATIVE)*100)/[TransactionCount]'. This could be generalized.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!