Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Process of Creating a Data Mart

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,
Answers
-
"
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,
-
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.