Forum Stats

  • 3,768,299 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

Process of Creating a Data Mart

1014077
1014077 Member Posts: 50
edited Jul 7, 2013 10:36PM in SQL & PL/SQL

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,

Tagged:

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown

    "

    Comment: The reason for creating and using the triggers is to avoid 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.

    "

    You're never going to achieve gap-less-ness utilizing sequences. Which is totally fine, you need a guaranteed mechanism to generate unique numbers, that's what a sequence is for, nothing else.

    With respect to using triggers, you'll want to avoid that like the plague in a data-mart / warehouse. Unless you're dealing with a very small set up, triggers will greatly inhibit your ability to scale the code. This also goes for foreign key constraints (again, really depends on your set up .. it's possibly you'd be fine with enabled foreign keys, it's possible you would set them to rely novalidate because you need faster loading capabilities). You'd also want to explore things like partition exchange, compression, etc...

    That's about all I really have for you unfortunately. I'm by no means a warehouse guru, but I know enough to let you know that it's highly unlikely you're going to be able to flush out ALL the things you need to know to properly implement this given that you're new to Oracle (and data warehouses) on your own (even via the forum ... it's just too broad a topic to impart all the necessary knowledge).

    You should really be looking for someone in house to help mentor you along with this. The documentation is a great place to start and I would highly encourage you to read as much of that as you can, but having someone in your organization with hands on experience can be an invaluable asset. Short of that you're going to learn on the job, and that's going to be a painful road to traverse.

    Cheers,

  • 1014077
    1014077 Member Posts: 50

    Thank you for your reply, but, unfortunately for me, there isn't anyone at work who knows about or has worked on building a data-mart before, if there was, I wouldn't have gotten this assignment in the very first place . But, on the upside, I will get to learn about it - I guess and yes, it is painful, so trying every possible way I could think of to get any help I can.

This discussion has been closed.