The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “Oracle”

31 Aug 2016
10 Comments

Maintaining disabled FK’s, wisdom or farce?

A while back, I wrote a post about having FKs (foreign keys) in your data warehouse.

Well, a similar question came up recently on an Oracle forum with the above title. It is a fair question and it does surface fairly regularly in a variety of contexts (not just data warehousing).

Of course, as The Data Warrior, I felt is was my duty to respond.

The Question

Is there any reason to maintain a permanently disabled FK in the data model?  I’m not envisioning a reason to do it.  If it is not going to be enabled, then from my perspective, it would not make any sense to have it defined.  If anything, provide the definition of the relationship in the comment of the child column.

My Answer

Yes, by all means keep the FK please!

I see three good reasons for doing so:

  1. It is valuable metadata (& documentation). If somebody reverse engineers the database (say with ERWin or Oracle Data Modeler), the FK shows up in the diagram (way better than having to read a column comment to find out)
    Data Vault 2.0 Example

    A picture is worth a thousand words!

    .

  2. BI Metadata – If you want to use any sort of reporting or BI tool against the database, most tools will import the FK definition with the tables and build the proper join conditions. Way better than having someone guess what the join will be and then manually adding it to the metadata layer in the reporting tool. Examples that can read the Oracle data dictionary include OBIEE, Business Objects, COGNOS, Looker, and many others.(Note here that since the FK is not enforced on the remote databases, you might want to make sure these are treated as outer joins, lest you lose some transaction in the reports).
  3. The Oracle optimizer will use disabled constraints to improve query performance of joins. Again, this is metadata in the data dictionary which the optimizer can read. This is documented in the Oracle Data Warehouse guide and I have validated it on multiple occasions with Oracle product management.

While #3 applies specifically to Oracle, for other databases like MS SQL Server and Snowflake, #1 and #2 still apply.

Even if only one of the above is true for a given database, that, in my opinion, still justifies keeping the disabled constraint around.

Final Answer = Wisdom

What do you think? Feel free to comment below.

And please share on your favorite social media platform!

Model on!

Kent

The Data Warrior

 

Posted in Data Modeling and tagged , , , , , , , , , ,
18 Dec 2015

Data Warrior Agenda for 2016

Hard to believe 2015 is almost over.

It was a very busy year for me:

All of that has entailed a lot of air miles! This year I have visited:

  1. Denver (several times!)
  2. Salida, Colorado
  3. Hollywood, Florida,
  4. Raleigh, NC
  5. Charlotte, NC (thanks to Lynn Winterboer for that one!)
  6. San Francisco
  7. Redwood City, California (Oracle HQ)
  8. Austin (drove this one)
  9. Minneapolis/ St Paul (thanks to Redpill Analytics mostly)
  10. Kansas City, Missouri
  11. Portland, Maine
  12. St Albans, Vermont
  13. Stowe, Vermont
  14. San Mateo, California (HQ for Snowflake Computing)

And that was just work related! Family trips took me to:

  1. Galveston (beach!)
  2. South Padre Island, TX (more beach!)
  3. Road trip to Central NY:
    1. Joplin, Missouri
    2. Hannibal, Missouri (Mark Twain museum)
    3. Chicago (to see robots at the Museum of Science and Industry)
    4. Sandusky, Ohio (just to sleep)
    5. Fulton, NY (to see my dad)
    6. Old Forge, NY (summer vacation in the mountains!)
    7. Huntsville, Alabama (NASA Rocket Center!)
  4. Who knows – the year is not over yet!

Speaking in 2016

2016 will be very busy with the new job for sure. I am already booked for a bunch of events. Here they are so far:

Data Day Texas – January 16 in Austin, TX

TDWI Webinar – Dymstyfying Elastic Data Warehousing (with Philip Russom) – January 26th

BIWA Summit – January 26-28 at Oracle HQ

RMOUG Training Days 2016 – Febuary 9-11 in Denver, CO (I have 2 hour deep dive on Feb 9th). Register early for discounts.

Enterprise Data World – April 17-22 in San Diego. Register early for discounts (by the end of the year for the best rate).

ODTUG KScope16 – June 26-30 in Chicago, IL. Register early and be sure to book the hotel!

Also likely speaking at World Wide Data Vault Consortium (WWDVC) – May 25-28 in Stowe, Vermont (TBD)

And many more to come! (watch my twitter feed for updates)

Hopefully I will see you at one or more of these events!

Wishing a safe a joyous holiday season!

Merry Christmas & Happy New Year!

Kent

The Data Warrior



 



		
	
						

							Posted in Data Modeling, Data Vault, Data Warehouse, ODTUG, Oracle, SnowflakeDB, SQL Developer Data Modeler, Tae Kwon Do and tagged , , , , , , , , , , , , , , ,  | Leave a comment						

					

				

			

		


	
		

			

					

						
							16
							Mar 2015						
					

							

			

				

					

						

							Better Data Modeling: The Book
						

						
Trying to be as productive as possible during my infrequent down time, I just published another Kindle book with some of my best tips for Oracle Data Modeler. it is called Better Data Modeling: Tips for Enhancing Your Use of Oracle SQL Developer Data Modeler.


If you are one of the 3.5 million users (or so) who have downloaded this tool, and you want to know my little secrets for getting the most out of SQL Developer Data Modeler (#SQLDevModeler), this book if for you.


If you were an Oracle Designer user and are looking for a replacement data modeling tool, or you are using one of the other mainstream, expensive modeling tools and want a more cost effective alternative, then you owe it to yourself to look at Oracle SQL Developer Data Modeler (SDDM). Oracle Data Modeler has been around for over five years now and is up to version 4.1. It really is an industrial strength data modeling tool that can be used for any data modeling task you need to tackle.


SQL Developer Data Modeler (SDDM) is a fully functional tool provided for FREE by Oracle. It has many features built in that can be leveraged to capture the design of an existing (probably undocumented) database or you can use it to design a new database, even a data warehouse from scratch. There are a load of great features. This book will show you my favorite features along with detailed step by step instructions (with screen shots) on how to use them.


Tips include:


    

  • How to easily color code your diagrams
    • 

  • How to make hundreds of views really fast
    • 

  • How to find missing foreign keys
    • 

  • How to find missing unique keys
    • 

  • How to connect to a SQL Server database (if you must…)
    • 



As a bonus, there are two appendices with my run down on common data modeling mistakes and my famous rant on why you need foreign keys in your data warehouse.


So if you don’t use Oracle Data Modeler yet, read my book to see why you should.


If you do use it, I hope this little book will make you even more productive than you already are!


Model on!


Kent


The Data Warrior


P.S. After you read the book, please leave a review on Amazon to help other folks decide if the book is for them.

						

							Posted in Data Modeling, SQL Developer Data Modeler and tagged , , , , , , , , , , , , , , , ,  | Leave a comment						

					

				

			

		


	
		

			

					

						
							04
							Mar 2015						
					

							

			

				

					

						

							End Your Data Modeling Pain
						

						
Tired of hand drawn data models, or models drawn in Visio or even PowerPoint?


Still limping along on an old Oracle Designer repository?


Can’t afford the license for your current modeling tool anymore?


Or maybe your shop has no documented data model diagrams at all (horrors!).


Well, if you were an Oracle Designer user and are looking for a replacement data modeling tool, or you are using one of the other mainstream, expensive modeling tools and want a more cost effective alternative, then you owe it to yourself (and your organization) to look at Oracle SQL Developer Data Modeler (SDDM). Data Modeler has been around for over five years now and is up to version 4.1. It really is an industrial strength data modeling tool that can be used for any data modeling task you need to tackle.


As I have mentioned before (many times) in this blog, SQL Developer Data Modeler (SDDM) is a fully functional modeling tool provided for FREE by Oracle. It has many features built in that can be leveraged to capture the design of an existing (probably undocumented) database and re-engineer it or you can use it to design a new database, even a data warehouse from scratch. There are features to apply standards, and then generate DDL to implement your newly standardized design. You can even use one logical model to develop multiple physical models that can be deployed to different RDBMS (like Oracle, SQL Server, and DB2). You even have options on the type of notation used in the diagrams (i.e., Barker, IE, etc.).


Unfortunately there are no comprehensive, end-to-end classes that can teach you how to really get started with SDDM and can point you to some of the awesome features and show you how they work.


Not until NOW!


Announcing my new online class: Introduction to Oracle SQL Developer Data Modeler.


This course will start from a totally blank page and walk you through all the major features and options of the tool to show you how to design build and deploy a database. I provide lots of examples and tips on how to do all this so that you can quickly be productive (while you watch!).


In this class, I will introduce you to the tool and show you how to use some of the cool features I use almost daily. I will provide step-by-step instructions on how to use these features. The class includes both lectures with screen shots and hands on demos of the tool (recorded live by me). Hint: some of my best tips are in the demos.


In this course you will see:


    

  • How to create new objects (entities, attributes, relationships) from a blank page
    • 

  • How to reverse engineer and document existing databases
    • 

  • How to reverse engineer logical models from physical to get a clean ERD
    • 

  • How to reconcile an existing data model to the database or the database to a model
    • 

  • How to generate a script to change an existing database
    • 



Tips, tricks and features I demonstrate include:


    

  • Modifying the delivered reporting templates
    • 

  • How (and when) to use the abbreviations utility
    • 

  • How to use and apply domains
    • 

  • How to create and applying object naming templates
    • 

  • How to add audit columns to every table in a model
    • 

  • How to add custom design rules for model quality checks
    • 

  • How to use the built in quality checks
    • 

  • How to use the newest find and extract feature to enable updating table and column comments by end users
    • 

  • How to generate a simple data dictionary
    • 



As an added bonus, in addition to step by step slides, and the demos, I have included a zip file with example output, templates, and DDL scripts that I created during the demos. You will be able to download these to your machine to try the exercises yourself.


If you have questions along the way, our modern learning management system (LMS) lets you enter your questions in the course comments whenever you want. I will get an immediate email notification so I can promptly answer your questions.


Unlike typical user group presentations, where I can only scratch the surface or show a few tricks, this course contains over four and a half hours of content broken up into bite-sized modules so you can easily digest the detailed information. If it does not make sense, you can simply play the same segment over again and even follow along on using your own install of SDDM.


By the end of the course you will be able to effectively use Oracle SQL Developer Data Modeler for all your data modeling tasks.


So head over to Learn Data Vault and check out the preview video of the class, then sign up and learn. Use my special offer code of GRAZIANO10S to get 10% off.


See you online!


Kent


The Data Warrior


P.S. The current price of the course is the initial sale price. I am not sure when the price will go up but Dan and Sanjay tell me that I set the price too low and they will have to raise it in the near future. So, I would sign up now, before that price goes up.

						

							Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , ,  | Leave a comment						

					

				

			

		


	
		

			

					

						
							25
							Feb 2015						
					

							

			

				

					

						

							Meet me in Austin?
						

						
Late breaking news (aka I forgot to post this earlier)!


I will be speaking at the first meeting of the Austin Oracle Users Group for 2015. It will be this coming Friday February 27, 2015 starting at 11:00 AM. I will give an intro talk on Oracle SQL Developer Data Modeler (the FREE data modeling tool from Oracle).


So if you ever wanted to find out if this is the data modeling tool for you, join me in Austin and find out first hand.


After my talk there will be a free lunch, followed by a talk on “Leave the Crowd: Join the Cloud” by Lakshmi Sampath, ERP Cloud Evangelist and Brent Grech, VP, Fusion Applications, Global Sales Engineering, Oracle.


I plan to stick around for that talk too!


The meeting will be held at the Oracle offices at 9600 North MOPAC, Plaza II, Suite 700. 


So please RSVP and let them know you are coming.


If you want to meet up after the session, leave me a comment below, or drop me a line.


See you soon!


Kent


The Data Warrior


P.S. If you are in the Austin area and have not joined, please join the LinkedIn group for AOUG here: https://www.linkedin.com/groups?home=&gid=4308479

						

							Posted in Data Modeling, Oracle, User Groups and tagged , , , , , , , , ,  | Leave a comment						

					

				

			

		


	
		

			

				Post Navigation			

			

				← Older Entries