Re: How to change ETL layer dynamically?
From: joel garry <joel-garry_at_home.com>
Date: Wed, 24 Mar 2010 08:58:20 -0700 (PDT)
Message-ID: <ddf64f55-4f23-46cf-9d9a-d336f2f9fb7d_at_s2g2000prd.googlegroups.com>
On Mar 24, 4:48 am, guser78 <qazmlp1..._at_rediffmail.com> wrote:
> On Mar 24, 8:44 am, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> wrote:
>
>
>
> > In article <eed0e313-0f02-4a83-ba87-1623c9045ab3_at_
> > 19g2000yqu.googlegroups.com>, guser78 says...
>
> > > We have to create a Datamart containing the Customer characteristics
> > > like 'Heavy Buyer; Chocolate lover' etc., and this has to be done by
> > > evaluating the relevant attributes in the Data ware house tables.
> > > But, in our case, the definition of Customer characteristics can
> > > change occasionally based on the new market conditions.
>
> > > This means that we should allow the dynamic administration of the
> > > definition of Customer characteristics.
>
> > > Please guide me on we can do this from an Application which uses the
> > > Datamart as the backend. Do we need to write an additional software
> > > module (for e.g. JDBC) to interpret the 'write' requests coming from
> > > the Application, and adapt the ETL layer accordingly?
>
> > Google: "slowly changing dimensions"
>
> If my requirement was not understood, here is the requirement I have:
> All raw tables related to the Customer are located in the
> Datawarehouse, and the specific Customer characteristics for each of
> the Customer should be evaluated regularly at run time, and loaded
> onto the 'Datamart' tables.
>
> My concern in the original post was more on whether I can change the
> 'Transform' portion at run time.
>
> For example, I might have calculated whether a customer is a
> 'Chocolate lover' based on whether he is buying some specific brands.
> At run time, my administrators of my 'Marketing' application should be
> able to change the definition of how the 'Chocolate lover' is
> calculated, maybe by adding/removing the brands, or/and by adding an
> additional criteria of checking the no. of chocolate packs that were
> sold out.
>
> I went through the information on different types of SDCs that are
> possible. In my case, it has to be 'Type 1' (no need to maintain the
> history of changes).
> I understand that this defines how the changes should be stored in the
> 'Datamart' for each of the load done by the ETL layer. But, my concern
> is on how I can change the 'Transform' SQL statements during run time.
Date: Wed, 24 Mar 2010 08:58:20 -0700 (PDT)
Message-ID: <ddf64f55-4f23-46cf-9d9a-d336f2f9fb7d_at_s2g2000prd.googlegroups.com>
On Mar 24, 4:48 am, guser78 <qazmlp1..._at_rediffmail.com> wrote:
> On Mar 24, 8:44 am, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> wrote:
>
>
>
> > In article <eed0e313-0f02-4a83-ba87-1623c9045ab3_at_
> > 19g2000yqu.googlegroups.com>, guser78 says...
>
> > > We have to create a Datamart containing the Customer characteristics
> > > like 'Heavy Buyer; Chocolate lover' etc., and this has to be done by
> > > evaluating the relevant attributes in the Data ware house tables.
> > > But, in our case, the definition of Customer characteristics can
> > > change occasionally based on the new market conditions.
>
> > > This means that we should allow the dynamic administration of the
> > > definition of Customer characteristics.
>
> > > Please guide me on we can do this from an Application which uses the
> > > Datamart as the backend. Do we need to write an additional software
> > > module (for e.g. JDBC) to interpret the 'write' requests coming from
> > > the Application, and adapt the ETL layer accordingly?
>
> > Google: "slowly changing dimensions"
>
> If my requirement was not understood, here is the requirement I have:
> All raw tables related to the Customer are located in the
> Datawarehouse, and the specific Customer characteristics for each of
> the Customer should be evaluated regularly at run time, and loaded
> onto the 'Datamart' tables.
>
> My concern in the original post was more on whether I can change the
> 'Transform' portion at run time.
>
> For example, I might have calculated whether a customer is a
> 'Chocolate lover' based on whether he is buying some specific brands.
> At run time, my administrators of my 'Marketing' application should be
> able to change the definition of how the 'Chocolate lover' is
> calculated, maybe by adding/removing the brands, or/and by adding an
> additional criteria of checking the no. of chocolate packs that were
> sold out.
>
> I went through the information on different types of SDCs that are
> possible. In my case, it has to be 'Type 1' (no need to maintain the
> history of changes).
> I understand that this defines how the changes should be stored in the
> 'Datamart' for each of the load done by the ETL layer. But, my concern
> is on how I can change the 'Transform' SQL statements during run time.
Sounds like it should be a data issue, a join with a table of those attributes like 'Chocolate Lover'. This is when normalization is a good thing.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/mar/21/goddess-of-surfing-makes-a-sacrifice/Received on Wed Mar 24 2010 - 10:58:20 CDT