Hello,
I need some help in building a data-mart, I have the design but I am not sure what steps to follow, to accomplish the same as I am (very) new to Oracle and also to the data warehousing concepts.
I am outlining the steps/process I think I need to follow to create the data-mart and would really appreciate your inputs regarding this process:
Step 0 - Design the fact and dimension tables and make sure all the necessary source tables/mat-views are present before moving forward
Step 1 - Write a script that will create the necessary entities and any related constraints
Comment: Using triggers to increment the primary key might help in avoiding unnecessary increments of the sequences during merge-update operations which
I want to avoid - Not exactly sure if the triggers would help in keeping a gap-less sequence.
Step 2 - Populating the dimension tables - Using merge (update+insert) to populate the dimension tables from the source tables/mat-views
Step 3 - Populating the fact tables - Using merge (update+insert) to populate the fact tables - what is the best way of populating a fact table ?
Step 4 - Once the initial population of the fact and dimension tables are done, we need to create all the indexes that will make querying the data mart faster. Can
someone please shed some light on what fields and what kind of indexes should I be using in a data-mart.
This is all I understand about building this data-mart, please let me know if there is a better way to accomplish all this. Any help would be greatly appreciated.
Thanks,