Join index and views

Database
Fan

Join index and views

Hello,

The architecture I 'm using today is a follow : Teradata DWH --> Views --> Business Object XI

Views are used to denormalize and have more friendly objects in BO. So I want to use Join Index and agregate join index to improve the performance. My problem is that I have a lot of difficults to create indexes that are used by Teradata. It looks like that Teradata Engine solve the views and then use these results to solve the full query. It is not efficient because All the object used as restriction (where clause) are applied after a sum aggregate... so if I need one month of data, Teradata will sum All my history and then filter on the month!
If anyone have any idea to improve that kind of behavior...

Thank you
Paul
1 REPLY
Enthusiast

Re: Join index and views

don't use views simple!!!

try with ETL->DWH->XI

and in the ETL u study hou to implement the normalization. The DWH needs to be engineered and deisgned (with a visula tool) providing the requets of client so u'll build and indexes projected structure and not a "3 rd form, with integrity ref., structure.

for ex:
3 tables in logic view:
Logevents:id_eve(pk),timestamp,id_process(fk),id_eve(fk)
Process:id_process(pk),name_process,description_process
Domevents:id_eve(pk),decription_eve

Maybe by xi u need to show generally the relation between time and processes generally happening, or process generated by and events:
The same table in (my) physical context:
Logveents: (timestamp,id_process)UPI,(id_eve)NUSI
Process: (id_process)UPI,name_process,description_process
Domevents: (id_eve)UPI,decription_eve

Generally i use view if i have imported a table used by some different depts that it's impossible to delete (for BUROCRACY!!!!) and i have to show different context to every depts...but for me it's preferible suggesting instead of 3 views ,3 new tables for every client (if the redudancy of attributes is less than 50% of original table, for every new tables created), with datas that come from the parent table through an etl-procedure (In wich is included the same parent table!).