|
Home > Archive > MS SQL Server OLAP > January 2006 > Problem with measures
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Problem with measures
|
|
|
| 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
| |
| Voorshwa 2006-01-20, 8:24 pm |
| 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@g14g2000cwa.googlegroups.com>,
voorshwa@gmail.com says...
> 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
>
>
| |
| Vladimir Chtepa 2006-01-24, 9:24 am |
|
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@TK2MSFTNGP09.phx.gbl...
>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
>
| |
|
| 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@TK2MSFTNGP09.phx.gbl>, vc.nospam@diacom-
systemhaus.nospam.de says...[color=darkred]
>
> 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@TK2MSFTNGP09.phx.gbl...
| |
| Voorshwa 2006-01-26, 4:58 pm |
| 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@f14g2000cwb.googlegroups.com>,
voorshwa@gmail.com 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
>
>
| |
|
| 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@diacom-systemhaus.nospam.de> wrote in message
news:eZyov$OIGHA.1288@TK2MSFTNGP09.phx.gbl...
>
> 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@TK2MSFTNGP09.phx.gbl...
>
>
| |
|
| 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@TK2MSFTNGP15.phx.gbl>, A@b.c says...[color=darkred]
> 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@diacom-systemhaus.nospam.de> wrote in message
> news:eZyov$OIGHA.1288@TK2MSFTNGP09.phx.gbl...
|
|
|
|
|