Thursday, March 22, 2012
Distinct count in case of Custom Rollup formula in a dimension very Urgent.........
of custom rollup formula
BrhanCreate a separate cube for the distinct count measure, and remove any custom
rollup formula in cube editor.
Ohjoo Kwon
"Burhan" <burhanuddin.ahmed@.gonetbpo.com> wrote in message
news:%233m4VeSMFHA.436@.TK2MSFTNGP09.phx.gbl...
> how we can calculate the dictinct customer ID in case
> of custom rollup formula
>
> Brhan
>
>
Distinct Count Custom Rollup
I have a vehicleID in my fact table. I want to create a measure called VehicleCount. This would be a distinct count of vehicles per day, but would be a sum of the per day counts for months, years, etc. If I just use distinct count on the VehicleID field will that give me the per day count and how do I make it so that the rollup is a sum instead of doing a distinct count for the higher levels (month, year, etc?)
I'm using SSAS2005.
Thanks for any help.
One approach would be to create a "distinct count" measure like [DistinctVehicles] on the VehicleID field, then sum its daily values in [VehicleCount] measure:
Create [VehicleCount] as a "count" measure on the fact table, and [DistinctVehicles] as a "distinct count" measure on the VehicleID field. Add an assignment to the cube MDX script like: ([Measures].[VehicleCount], [Date].[Date].[Date]) = [Measures].[DistinctVehicles];|||Thanks for the response.
I'm a slightly confused though. You said, "Create [VehicleCount] as a "count" measure on the fact table." Shouldn't this be a "Sum" measure? Otherwise wouldn't you just end up with the count of days?
Thanks for your help.
|||Should work either way (I did test with a "count" measure), because the [Day] level is overwrtten by the "distinct count" assignment; and those daily values should get summed up at higher [Date] levels ...sql