The Data Warrior

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

Archive for the tag “SQL Developer Data Modeler”

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
26 Jan 2014
1 Comment

Better Data Modeling: The Oracle Data Warrior Speaks!

Looks like I will be doing a bit of speaking this year at a number of  events around the country, and later on, the globe (more on that later).

As usually all my talks will center around using SQL Developer Data Modeler, data modeling standards, Data Vault, agile, or a combination of all of the above.

If you have budget and time, please come out to at least one of these events this year, I would love to meet you in person and talk about the world of Oracle and Data Modeling.

If you aren’t planning to attend one of these – WHY NOT?

These are all great events with tons of learning opportunities. The networking alone is worth the price of admission.

Here is a list of the first three events confirmed on my calendar (and SURPRISE – they are NOT all Oracle related events):

RMOUG Training Days

In less than two weeks: The Rocky Mountain Oracle Users Group Training Days 2014 in Denver, Colorado. This runs from Feb 5-7 , will have at least 1,000 people and you cannot beat the price.I will be presenting Friday at 1:30PM on how I save my clients big $$ by applying repeatable processes and standards to my data models.

Follow it on twitter with #RMTD14.

Data Vault Consortium

Next up March 20 – 22nd , I will be participating in the 1st ever World Wide Data Vault Consortium and User Group meetup in beautiful northern Vermont near the home of my good friend, the inventor of the The Data Vault Model and Methodology, Dan Linstedt. I will be speaking about agile and data warehousing, using SDDM to do Data Vault modeling, and no doubt engaging in some lively debates with Data Vault experts from around the globe. Check out the agenda on the event page for more details on who will be speaking (hint: Bill Inmon, father of  data warehousing is participating!).

Enterprise Data World 2014

The #EDW14 event is really the annual conference put on by DAMA International and the speaker list is a veritable who’s-who of the data architecture and modeling world. This year the event is in Austin, Texas on April 27 – May 1. Since that is quite close to where I live, I figured I would submit an abstract and I was honored to be accepted. I have attended this event only once before when it was in Denver (a long time ago!) and have been a member of DAMA on and off for years, but this is the first time I have been asked to speak. I am looking forward to it for sure (not sure how I will fit my talk into a 45 minute slot!). Sign up for it here.

If you are planning to attend any of these, drop me a line over Twitter or LinkedIn so we can plan to meet up.

Later.

Kent

The Oracle Data Warrior

Posted in Data Modeling, Data Vault, Data Warehouse, Oracle, SQL Developer Data Modeler, User Groups and tagged , , , , , , , , , , , , ,
04 Dec 2013
4 Comments

Better Data Modeling: The best FREE data modeling tool just got better!

Yes, it true Virginia, the is a Santa Claus!

And this year Santa brought you a new, improved version of the best FREE data modeling tool in the known universe: Oracle SQL Developer Data Modeler 4.0.

The team at Oracle went all out this year and produced three (yes three) pre-release versions to make sure all the fixes and new features were rock solid before they called it production.

That is a lot of testing and work.

But worth the effort – they fixed piles of bugs and added dozens of new features.

Oracle product manager Jeff Smith (@thatjeffsmith) has already published several articles highlighting his favorite new features. Check out what he has to say here then go download the new version and give it try.

Let me know what you your favorite new feature is.

Merry Christmas!

The Oracle Christmas Elf

(Kent)

P.S. Without proven methods and standards, even the best tool will not insure you build the best model, so why not increase your chances by giving yourself the gift of knowledge by picking up a copy of my data model checklist book (on sale for a few more hours).

 

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , , ,
03 Nov 2013
3 Comments

Agile Data Warehouse Modeling: How to Build a Virtual Type 2 Slowly Changing Dimension

One of the ongoing complaints about many data warehouse projects is that they take too long to delivery. This is one of the main reasons that many of us have tried to adopt methods and techniques (like SCRUM) from the agile software world to improve our ability to deliver data warehouse components more quickly.

So, what activity takes the bulk of development time in a data warehouse project?

Writing (and testing) the ETL code to move and transform the data can take up to 80% of the project resources and time.

So if we can eliminate, or at least curtail, some of the ETL work, we can deliver useful data to the end user faster.

One way to do that would be to virtualize the data marts.

For several years Dan Linstedt and I have discussed the idea of building virtual data marts on top of a Data Vault modeled EDW.

In the last few years I have floated the idea among the Oracle community. Fellow Oracle ACE Stewart Bryson and I even created a presentation this year (for #RMOUG and #KScope13) on how to do this using the Business Model (meta-layer) in OBIEE (It worked great!).

While doing this with a BI tool is one approach, I like to be able to prototype the solution first using Oracle views (that I build in SQL Developer Data Modeler of course).

The approach to modeling a Type 1 SCD this way is very straight forward.

How to do this easily for a Type 2 SCD has evaded me for years, until now.

Building a Virtual Type 2 SCD (VSCD2)

So how to create a virtual type 2 dimension (that is “Kimball compliant” ) on a Data Vault when you have multiple Satellites on one Hub?

(NOTE: the next part assumes you understand Data Vault Data Modeling. if you don’t, start by reading my free white paper, but better still go buy the Data Vault book on LearnDataVault.com)

Here is how:

Build an insert only PIT (Point-in-Time) table that keeps history. This is sometimes referred to as a historicized PIT tables.  (see the Super Charge book for an explanation of the types of PIT tables)

Add a surrogate Primary Key (PK) to the table. The PK of the PIT table will then serve as the PK for the virtual dimension. This meets the standard for classical star schema design to have a surrogate key on Type 2 SCDs.

To build the VSCD2 you now simply create a view that uses the PIT table to join the Hub and all the Satellites together. Here is an example:

Create view Dim2_Customer (Customer_key, Customer_Number, Customer_Name, Customer_Address, Load_DTS)
as
Select sat_pit.pit_seq, hub.customer_num, sat_1.name, sat_2.address, sat_pit.load_dts
from HUB_CUST hub,        
          SAT_CUST_PIT sat_pit,        
          SAT_CUST_NAME sat_1,        
          SAT_CUST_ADDR sat_2
where  hub.CSID = sat_pit.CSID           
    and hub.CSID = sat_1.CSID           
    and hub.CSID = sat_2.CSID           
    and sat_pit.NAME_LOAD_DTS = sat_1.LOAD_DTS           
    and sat_pit.ADDRESS_LOAD_DTS = sat_2.LOAD_DTS 
 

Benefits of a VSCD2

  1. We can now rapidly demonstrate the contents of a type 2 dim prior to ETL programming
  2. With using PIT tables we don’t need the Load End DTS on the Sats so the Sats become insert only as well (simpler loads, no update pass required)
  3. Another by product is the Sat is now also Hadoop compliant (again insert only)
  4. Since the nullable Load End DTS is not needed, you can now more easily partition the Sat table by Hub Id and Load DTS.

Objections

The main objection to this approach is that the virtual dimension will perform very poorly. While this may be true for very high volumes, or on poorly tuned or resourced databases, I maintain that with today’s evolving hardware appliances  (e.g., Exadata, Exalogic) and the advent of in memory databases, these concerns will soon be a thing of the past.

UPDATE 26-May-2018  – Now 5 years later I have successfully done the above on Oracle. But now we also have Snowflake elastic cloud data warehouse where all the prior constraints are indeed eliminated. With Snowflake you can now easily chose to instantly add compute power if the view is too slow or do the work and processing to materialize the view. (end update)

Worst case, after you have validated the data with your users, you can always turn it into a materialized view or a physical table if you must.

So what do you think? Have you ever tried something like this? Let me know in the comments.

Get virtual, get agile!

Kent

The Data Warrior

P.S. I am giving a talk on Agile Data Warehouse Modeling at the East Coast Oracle Conference this week. If you are there, look me up and we can discuss this post in person!

Posted in Data Modeling, Data Vault, Data Warehouse, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , ,

Post Navigation

← Older Entries
Newer Entries →
%d bloggers like this: