How to change ETL layer dynamically?
in [Oracle Help]
|
Prev: Datawarehouse->(ETL)->Datamart; Where should ETL be physically located?
Next: ORA-19804: cannot reclaim nnnnnnn bytes disk space from nnnnnnnnnn limit
From: guser78 on 23 Mar 2010 07:03 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?
From: Mark D Powell on 23 Mar 2010 09:22 On Mar 23, 7:03 am, guser78 <qazmlp1...(a)rediffmail.com> wrote: > 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? Data warehouse architecture and design is a complex topic and you would probably be better off reading a few books on the subject because getting a truely meaningful response to such a broad question as yours without giving any background information on the over-all warehouse design and architecture is unlikely. Oracle has a manual devoted to the subject of data warehousing and there are numerous design options covered in it. Microsoft has articles on the topic available on its web site and even though the subject is placing a warehouse on SQL Server there are still generic topics discussed. HTH -- Mark D Powell --
From: Geoff Muldoon on 23 Mar 2010 23:44 In article <eed0e313-0f02-4a83-ba87-1623c9045ab3@ 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" GM
From: guser78 on 24 Mar 2010 07:48 On Mar 24, 8:44 am, Geoff Muldoon <geoff.muld...(a)trap.gmail.com> wrote: > In article <eed0e313-0f02-4a83-ba87-1623c9045ab3@ > 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.
From: joel garry on 24 Mar 2010 11:58
On Mar 24, 4:48 am, guser78 <qazmlp1...(a)rediffmail.com> wrote: > On Mar 24, 8:44 am, Geoff Muldoon <geoff.muld...(a)trap.gmail.com> > wrote: > > > > > In article <eed0e313-0f02-4a83-ba87-1623c9045ab3@ > > 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 -- @home.com is bogus. http://www.signonsandiego.com/news/2010/mar/21/goddess-of-surfing-makes-a-sacrifice/ |