| Denny Lee 2005-11-28, 8:25 pm |
| A potentially easier approach to solve this problem is to look at your
queries as different levels of granularities. That is, you have a daily
cube and then a monthly cube. With the monthly cube, you will make the
"decision" and apply the business logic at the SQL (or detail data source)
level. And then when you want to look at the data at a monthly granuality
vs. daily granularity, you switch to the appropriate cube.
--
HTH!
Denny Lee
< dennyglee_at_hotmail
_dot_com>
Blog at:: http://spaces.msn.com/members/denster/
"Jeff" <j.heu@gmx.at> wrote in message
news:1133177642.153039.5320@z14g2000cwz.googlegroups.com...
> Hi!
>
> There's a Customer Table which has an attribute "Type" that can change
> over time and some facts related to the customer.
>
> Time Cust.no Type Revenue
> 2005/01 100 A 1001
> 2005/02 100 A 1002
> 2005/03 100 A 1003
> 2005/04 100 A 1004
> 2005/05 100 A 1005
> 2005/06 100 A 1006
> 2005/07 100 A 1007
> 2005/08 100 B 1008
> 2005/09 100 B 1009
> 2005/10 100 B 1010
> 2005/11 100 B 1011
>
> To keep this example simple let's say the cube has only 2 dimensions
> Time and Customer-Type
> and 2 measures "Customer Count" (distinct count) and "revenue".
> and the 1 customer 100.
> (in real life there are of course additional Dimensions and measures in
> the cube)
>
> The request is to build an AS-Cube (2k) where you distinct count the
> customers and sum the revenue in the following manner:
>
> 100 belongs to Type B.
>
> On querying the month level you have 1 customer for type A in each
> month from 2005/01-07 and one customer for type B from 2005/08-11.
>
> If you look at year level 2005 (aggregation of month) in this example
> the only one customer in the cube must be counted for type B and the
> entire revenue also belongs to B.
>
> Shortly: You allways have to use the last state of "Type" anywhere in
> the Time dimension. I know the approaches for "inventory problem" and
> "distinct count" issue. But this is a little bit more i think.
>
> What is the right approach for modelling this requirements in the cube?
>
>
> Your help would be very much appreciated!
> Thanks in advance!
> Jeff
>
|