K N Yadav
Thanks for the reply and help.
My data source is Oracle 11g, so since i am a bit newbie am not 100% able to memories the tables and joins between them. i was wondering if there is a tool that will show you the tables, FK,PK and relationship with other tables
Does it show me the join expressions, PK,Fk the default ones ? as in oracle side ? if so can you please help me how to do it step by step ?
Thanks much appreciated
If you have an ETL team working then using ODI you should be able to built proper warehouse tables in the form of facts and dimensions that will include the pk and fk , through which relationship can be maintained between the tables, joins can be given and a star schema can be built in OBIEE RPD.
So ODI you can consider as an option if not Toad.
Referring to the subject line, I would suggest http://www.eveandersson.com/writing/data-model-reverse-engineering, since you mentioned "a way".
If constraints are created then the queries should be useful. If not (unlikely in an OLTP application), searching columns in user_tab_columns should be useful to begin with.
EMPLOYEE_WID is meant as a primary key in dimension table and EMPLOYEE is used in fact table then :
select * from user_tab_columns where column_name like '%EMPLOYEE% should list both the tables. This should be relatively straight forward in a DW application than an OLTP.
Little guesswork is needed but good table and column naming conventions and / or a colleague can help. If you like documenting, store results to an Excel file and query the Excel workbook using Excel's data filters. Using the above query frequently till the time you are happy with your guess-work will help you memorising the relationships as well.
If you are going to create a brand new model, then SQL Developer / ERwin can be useful.