DWH: Distinct aggregate

[Zeus80]

hey guys,

i have the following problem and i can't solve it and can't find it in the literature either, because i don't know how to name it.

.) imagine a website with different Objects(ObjectID, value) online.
I designed Objects as Dimensiontable and my Facttable is just: (ObjectID, DateID)

.) So let's say on day 1 there are 100 Objects(ProductID 1-100) online - i can calculate the average value easily.

.) on day 2 ObjectID, 101, 102,... are online, and ObjectID, 55,66,... offline and so on.

Now i want to calculate the average value of all objects within one month. But what i need are distinct objects, so i assume i can't use aggregation, because the value of an object, that was online for 10 days would have more weight in the average value than an object that was online for just 1 day...


i was thinking about an approach, where i have FactTables like FactTableDay (ObjectID, DayID), FactTableMonth(ObjectID, MonthID), FactTableYear(ObjectID, YearID), where i store all distinct objects, that where online for at least 1 day within one month, year...)

Another idea was to use the keyword DISTINCT in every measure, but wouldn't it be very slow?

In fact my project is much more complex of course and there are about 50000 objects online/day and let's say 100 new objects/day and 100 objects go offline/day, and i need a lot of measures like median, max, min, variance,... for week, month, year,...

any hints? thanks in advance

 

[MDXer]

sounds like you want to look at weighted averages you can google it and get lots of information.

 

[Zeus80]

Hi,

No i am not looking for weighted averages.

I need the average value (and other measures) of all distinct objects in a certain time period (e.g. 1 month, 1 year,...), unaffected by the number of days an object was online in the time period.