I have the following data in my olap cube

OrderID Hours DateInvoiceSent BillAmt
1 200 1/1/04 $10
1 200 2/1/04 $20
1 200 3/1/04 $30
1 200 4/1/04 $40
2 100 1/1/04 $10
2 100 2/1/04 $20
2 100 3/1/04 $30

OrderID: the ID of the order
Hours: the number of hours it took to process a order
DateInvoiceSent: the date that an invoice was sent to a client. We send
multiple invoices on several different dates for a single order.

The problem is when I use a Excel PivotTable to look at this data, asking
for the orderID and the number of hours spent processing the order. I get
the following
OrderID Hours
1 800 (4*200)
2 300 (3*100)

This is incorrect since there were only 200 hours spent processing order #1.

What I wish I could get is
OrderID Hours
1 200
2 100

Does anyone know of a way to write a calculated measure that would return
the correct value for hours? Perhaps by correcting by the number of bills
sent?

P.S. I think this problem is basically caused by having measures resides
that too different levels of the heirarchy, the orders level and the invoice
level

Well this really all depends on how you want to accomplish the task.
Do you want to do this on the OLAP side or do you want to do this on
the ETL side. Personally, I would do it in the ETL, but since you were
asking from some kind of calc member I will go the OLAP route.

By looking at your sample data set I am assuming that for every
iteration of an OrderID in your fact table the Hours column will be the
same number. If this isn't the case then this approach won't work, but
here you go.

All you should have to do is set the Aggregate Function (found in the
measure properties in the cube editor) for your measure (in this case
Hours) to the MAX function. This will basically look at your query for
hours and based on your criteria will choose the largest number of
hours spent. Since (and again the assumption) all of the numbers for
an OrderID are the same this will pull back the right number.

If my assumption above is incorrect let me know and I can post a
different solution for you.

V

This is a good suggestion (probably better than my initial idea of
adding a count over the orders and dividing the sum by the count). But I
can see one possible issue.

This would work at the leaf level, but if you had any hierarchies over
the top of the orders, you would only get the maximum hours of all the
child members. If you needed to get this solution to work across more
than one level you would need to write a custom rollup formula or use a
calculated measure to add up the descendants at the leaf level.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1137798074.632176.167010 (AT) g14g2000cwa (DOT) googlegroups.com>,
voorshwa (AT) gmail (DOT) com says...

Hi

number of hours it took to process a order is attribute of the Order not of
the Fact, therefor it belongs Order dimension as Attribute.
Than you can build a calculated measure, based on dimesion property

iif (IsLeaf(Order.CurrentMember), Order.Properties("Hours"), null ... or
smth. else)

Vladimir Chtepa

"J" <A@b.c> schrieb im Newsbeitrag
news:u2ydz7gHGHA.2896 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

I agree with Vladimir.

I (belatedly) arrived at the same conclusion today. This is a
granularity issue more than an aggregation issue. Based on the sample
given, hours does not belong in the fact table with the invoice data.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <eZyov$OIGHA.1288 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, vc.nospam@diacom-
systemhaus.nospam.de says...

Looking back on it, I have to agree with both Vladimir and Darren, this
would definitely be better done outside the fact table. And in AS2K,
Vlad's solution will work very well. However, if you plan on going to
AS2K5 anytime soon keep in mind that there is no longer the concept of
member properties. You will have to include hours as an attribute.

However, if you are in AS2K for a while, there is no reason to borrow
trouble.

V

In AS2k5 it would not need to be an attribute, a table can play the part
of both a fact and a dimension. So if the hours were in the orders table
you could have your order dimension and an orders measure group with the
hours measure.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1138211594.201312.145010 (AT) f14g2000cwb (DOT) googlegroups.com>,
voorshwa (AT) gmail (DOT) com says...

Hi

I started trying to use your suggestion and it appears to be working except
for the totals. I understand how to retrieve the member property for a leaf
but I don't understand what to do if it is not a leaf. How to do I add up
all the member properties for the children of a member?

iif(
IsLeaf([Order ID].currentmember),
val([Order ID].Properties("Hours")),
sum(
val([Order ID].children.Properties("Hours"))
)
)

I don't think the last part of my iif function is valid MDX syntax. What
would be the valid equivalent?

"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote

You would need to sum all the leaves below the current member, like the
following.

iif(
IsLeaf([Order ID].currentmember),
val([Order ID].Properties("Hours")),
sum(
DESCENDANTS([Order ID].CurrentMember,LEAVES)
,Measures.<This Measure Name>
)
)


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <O5nqf5oIGHA.2212 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, A@b.c says...