Incremental cube update with changing data in M2M dimension

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sergei.sheinin@ru.nestle.com - 17 Apr 2008 09:29 GMT

Hello,

I have a question about use of many-to-many dimensions. There is a
huge fact table joined to a dimension, and that dimension has a
factless many-to-many table with attributes attached. Question: when
attribues of a dimension record which has been used in the fact table
in the past change, do you have to re-compile the whole cube?

As an example, let's say you have a sales fact table and customers
dimension. There is a factless many-to-many table called
customer_parameters. At one point, one parameter changes for many
customers who've had sales in the past (for example, they change from
single to married). Do you have to re-compille the whole cube every
time such updates take place in a dimension? Is there a way to avoid
and/or delay that?

Thanks!
S.

Reply to this Message

Jeje - 17 Apr 2008 12:37 GMT

you talk about a change in your factless table, this is NOT a change in the
dimension.
in this case you have to reprocess the factless table to make sure that the
customers parameters are correctly associated to the customers.

because you don't change the dimension himself, there is no need for a full
process of the sales.

Now... if your parameters dimension is directly attached to your sales fact
table (or referenced to the fact table), in this scenario you have to
reprocess your sales history. but with a many many, there is no need for a
process.

> Hello,
>
[quoted text clipped - 14 lines]
> Thanks!
> S.

Reply to this Message

Marco Russo - 19 Apr 2008 08:55 GMT

> you talk about a change in your factless table, this is NOT a change in the
> dimension.
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Jeje is right.
I've a concern about your model - when you say that that a parameter
changes for customers who've had sales in the past, are you sure you
want to "lose" the parameters for the past sales? You can also have
both, but depending on your requirement you could choose different
models.
I suggest you to take a look at my paper here: http://www.sqlbi.eu/manytomany.aspx
Look at the models "Transition Matrix" and "Cross-Time", you might
find some ideas for your models.

Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo

Reply to this Message

sergei.sheinin@ru.nestle.com - 21 Apr 2008 09:10 GMT

> > you talk about a change in your factless table, this is NOT a change in the
> > dimension.
[quoted text clipped - 47 lines]
>
> - Show quoted text -

Marco,

I've already downloaded you paper last week. It's great! At the mo,
our requirement is to use only the "current state", but once all that
is done will look into adding the "cross-time" scenario as well.

Most important was for me to make sure that re-compiling the M2M
dimension would not force us to recompile the main cube, as it is very
large.

Thanks!
Sergei.

Reply to this Message