SEO

#1 (**permalink**) 04-17-2008, 04:08 PM

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.

#2 (**permalink**) 04-17-2008, 04:08 PM

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.

<sergei.sheinin@ru.nestle.com> wrote in message
news:188680f1-e710-4b23-bf09-bd33eec38ce3@a23g2000hsc.googlegroups.com...
> 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.

#3 (**permalink**) 04-19-2008, 02:49 PM

On Apr 17, 1:37*pm, "Jeje" <willg...@hotmail.com> wrote:
> 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.
>
> <sergei.shei...@ru.nestle.com> wrote in message
>
> news:188680f1-e710-4b23-bf09-bd33eec38ce3@a23g2000hsc.googlegroups.com...
>
>
>
> > 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.- Hide quoted text -
>
> - 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

#4 (**permalink**) 04-24-2008, 06:07 PM

On Apr 19, 11:55*am, Marco Russo <marco.ru...@loader.it> wrote:
> On Apr 17, 1:37*pm, "Jeje" <willg...@hotmail.com> wrote:
>
>
>
>
>
> > 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 fora
> > process.
>
> > <sergei.shei...@ru.nestle.com> wrote in message
>
> >news:188680f1-e710-4b23-bf09-bd33eec38ce3@a23g2000hsc.googlegroups.com...
>
> > > 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.- Hide quoted text -
>
> > - 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 Russohttp://www.sqlbi.euhttp://sqlblog.com/blogs/marco_russo- Hide quoted text -
>
> - 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Incremental cube update with changing data in M2M dimension