 | |
Aggregate Function in a stock measure
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Aggregate Function in a stock measure -
02-05-2004
, 08:29 AM
#2
| |||
| |||
|
|
-----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#3
| |||
| |||
|
|
You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#4
| |||
| |||
|
|
You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#5
| |||
| |||
|
|
I can not implement it since it tells me that the level MONTH of the DATE dimension is not Unique. I tried setting the property Member Keys Unique to true, but it wont work, since the months are not unique.. One point, the size of the estimated cube exploded up to 600 MB, no matter there's no new data and the estimated size for the regular time dimension was about 30 MB... Let's forget about the size stuff since I checked after processing the cube and the directory in the disk has no more than 5 MB, no matter AS reports 30+ MB  "Sanka" <loonysan (AT) mailcity (DOT) com> escribió en el mensaje news:a86e01c3ebfb$76aa8070$a001280a (AT) phx (DOT) gbl... You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#6
| |||
| |||
|
|
I take it that modifying the dimension is not an option? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:OeShEGA7DHA.2432 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I can not implement it since it tells me that the level MONTH of the DATE dimension is not Unique. I tried setting the property Member Keys Unique to true, but it wont work, since the months are not unique.. One point, the size of the estimated cube exploded up to 600 MB, no matter there's no new data and the estimated size for the regular time dimension was about 30 MB... Let's forget about the size stuff since I checked after processing the cube and the directory in the disk has no more than 5 MB, no matter AS reports 30+ MB "Sanka" <loonysan (AT) mailcity (DOT) com> escribió en el mensaje news:a86e01c3ebfb$76aa8070$a001280a (AT) phx (DOT) gbl... You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#7
| |||
| |||
|
|
I take it that modifying the dimension is not an option? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:OeShEGA7DHA.2432 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I can not implement it since it tells me that the level MONTH of the DATE dimension is not Unique. I tried setting the property Member Keys Unique to true, but it wont work, since the months are not unique.. One point, the size of the estimated cube exploded up to 600 MB, no matter there's no new data and the estimated size for the regular time dimension was about 30 MB... Let's forget about the size stuff since I checked after processing the cube and the directory in the disk has no more than 5 MB, no matter AS reports 30+ MB "Sanka" <loonysan (AT) mailcity (DOT) com> escribió en el mensaje news:a86e01c3ebfb$76aa8070$a001280a (AT) phx (DOT) gbl... You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#8
| |||
| |||
|
|
The problem is : When I check the syntax of the MDX query in the Calculated Member Generator Window in AS, it states that the following statement can not be generated since the Member Month is not Unique... [Measures].[Quantity] / Count(Descendants([Time].CurrentMember, [Months])) Suppose Im working with SPanish AS, and the Time dimension was created as usual.I browsed the dimension in the MDX Sample Application and it shows (In Spanish) three January months since I'm Including three years in the analysis.What's wrong?Thank you,Michael "Tom Chester" <public (AT) NOSPAMtomchester (DOT) net> escribió en el mensaje news:vsVUb.76$an2.53187 (AT) news (DOT) uswest.net... I take it that modifying the dimension is not an option? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:OeShEGA7DHA.2432 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I can not implement it since it tells me that the level MONTH of the DATE dimension is not Unique. I tried setting the property Member Keys Unique to true, but it wont work, since the months are not unique.. One point, the size of the estimated cube exploded up to 600 MB, no matter there's no new data and the estimated size for the regular time dimension was about 30 MB... Let's forget about the size stuff since I checked after processing the cube and the directory in the disk has no more than 5 MB, no matter AS reports 30+ MB "Sanka" <loonysan (AT) mailcity (DOT) com> escribió en el mensaje news:a86e01c3ebfb$76aa8070$a001280a (AT) phx (DOT) gbl... You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#9
| |||
| |||
|
|
If you have 3 years in your cube, what's wrong with there being 3 instances of January? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:O1Wib$x7DHA.2628 (AT) TK2MSFTNGP10 (DOT) phx.gbl... The problem is : When I check the syntax of the MDX query in the Calculated Member Generator Window in AS, it states that the following statement can not be generated since the Member Month is not Unique... [Measures].[Quantity] / Count(Descendants([Time].CurrentMember, [Months])) Suppose Im working with SPanish AS, and the Time dimension was created as usual.I browsed the dimension in the MDX Sample Application and it shows (In Spanish) three January months since I'm Including three years in the analysis.What's wrong?Thank you,Michael "Tom Chester" <public (AT) NOSPAMtomchester (DOT) net> escribió en el mensaje news:vsVUb.76$an2.53187 (AT) news (DOT) uswest.net... I take it that modifying the dimension is not an option? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:OeShEGA7DHA.2432 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I can not implement it since it tells me that the level MONTH of the DATE dimension is not Unique. I tried setting the property Member Keys Unique to true, but it wont work, since the months are not unique.. One point, the size of the estimated cube exploded up to 600 MB, no matter there's no new data and the estimated size for the regular time dimension was about 30 MB... Let's forget about the size stuff since I checked after processing the cube and the directory in the disk has no more than 5 MB, no matter AS reports 30+ MB "Sanka" <loonysan (AT) mailcity (DOT) com> escribió en el mensaje news:a86e01c3ebfb$76aa8070$a001280a (AT) phx (DOT) gbl... You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
#10
| |||
| |||
|
|
If you have 3 years in your cube, what's wrong with there being 3 instances of January? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:O1Wib$x7DHA.2628 (AT) TK2MSFTNGP10 (DOT) phx.gbl... The problem is : When I check the syntax of the MDX query in the Calculated Member Generator Window in AS, it states that the following statement can not be generated since the Member Month is not Unique... [Measures].[Quantity] / Count(Descendants([Time].CurrentMember, [Months])) Suppose Im working with SPanish AS, and the Time dimension was created as usual.I browsed the dimension in the MDX Sample Application and it shows (In Spanish) three January months since I'm Including three years in the analysis.What's wrong?Thank you,Michael "Tom Chester" <public (AT) NOSPAMtomchester (DOT) net> escribió en el mensaje news:vsVUb.76$an2.53187 (AT) news (DOT) uswest.net... I take it that modifying the dimension is not an option? public @ the domain below www.tomchester.net "Michael Prendergast" <mikepre (AT) hotmail (DOT) com> wrote in message news:OeShEGA7DHA.2432 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I can not implement it since it tells me that the level MONTH of the DATE dimension is not Unique. I tried setting the property Member Keys Unique to true, but it wont work, since the months are not unique.. One point, the size of the estimated cube exploded up to 600 MB, no matter there's no new data and the estimated size for the regular time dimension was about 30 MB... Let's forget about the size stuff since I checked after processing the cube and the directory in the disk has no more than 5 MB, no matter AS reports 30+ MB "Sanka" <loonysan (AT) mailcity (DOT) com> escribió en el mensaje news:a86e01c3ebfb$76aa8070$a001280a (AT) phx (DOT) gbl... You can solve this problem using Calculated Members. Check out this URL. http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnolap/html/semiadd2.asp?frame=true Cheers, Sanka -----Original Message----- Suppose I need to show daily stock level in a warehouse. My problem is that there's no AVERAGE Aggregate Function, instead, I can only select SUM, MAX, MIN, COUNT and Distinct count. Knowing that a stock measure is Non Additive, how do you prevent AS to aggregate them fro different dates? Thank you, Michael . |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.