The Data Warrior

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

Archive for the tag “free tip”

30 Sep 2016
1 Comment

Top 3 Tips for Staying Current in the Evolving World of Data Warehousing

The world of data warehousing and analytics has changed! With the advent of Big Data, Streaming Data, IoT, and The Cloud, what is a modern data warehousing professional to do? It may seem to be a very different world with different concepts, terms, and techniques. Or is it?

This is a question I ask myself all the time. So how do you keep up?

Here is what I do:

1 – Follow the Leaders

Yes, social media! Mostly, I use Twitter. I follow the industry thought leaders and analysts like Claudia Imhoff, Tamara Dull, Howard Dresner, Philip Russom, Cindi Howson, and many others. Not only do I see what they are thinking (and speaking) about, but I get to see what they are reading.

2 – Meet the Leaders

While reading books and online articles is great, there is nothing that replaces face to face communication. And the best way to do that is attend educational events where they are speaking. These days that could mean everything from local meet-ups, to regional conferences (like RMOUG), vendor roadshows, and larger annual events (like the recent Oracle OpenWorld).

For meet-ups, simply go to https://www.meetup.com/ and sign up (for free). You can search for meet-ups in your local area by topic. You may be surprised how many there are nearby and how often they have event. This is a great way to network with other professional in your local community.

To learn from the industry leaders, look to larger national and international events. In the data warehousing and analytics world that means groups like The Data Warehouse Institute (TDWI). They have local chapters and run larger national events on a regular basis (the next one is in October in San Diego). Another group I am associated with is DAMA International which also sponsors local chapters, national and international events.

And of course your vendors and solution providers may run their own events, like the Snowflake Cloud Analytics city tour.

3- Be a Leader

Volunteer! Yes by getting involved with these meet-ups, associations, and user groups, whether locally or nationally, you not only get to give back to the community, but you will often benefit by getting to know and speak with leaders one on one in a less formal environment.

Start off small by helping organize a meeting, or getting the refreshments. Help with the web site or the mail list. If the group you choose runs a conference, help with the paper selection process (you will learn a ton reading the abstracts). And then, when you are ready, become a speaker yourself. There is no better way to learn than to try to teach what you know to someone else.

I have been helping with user group conferences and events for nearly 30 years now and have never regretted a minute of the time spent.

 

So those are my top 3 tips for how you can stay fresh and informed and ahead of the game in this crazy world of data warehousing, big data, and the cloud.

Seems to be working for me.

Keep Learning!

Kent

The Data Warrior

P.S. One of our Snowflake customers, IAC Publishing Labs (owners of Ask.com), won the TDWI Best Practice award for the Emerging Technologies and Methods category and Keith Lavery will be speaking about the project at the TDWI Executive Summit in San Diego on Monday, October 3rd.

P.P.S.  And don’t forget to follow some of the leaders at Snowflake like @bob_muglia and @jonb_snowflake.

 

Posted in Big Data, Data Warehouse, User Groups and tagged , , , , , , , , , , , , , , , , , ,
28 Mar 2014
15 Comments

Better Data Modeling: My Top 3 Reasons why you should put Foreign Keys in your Data Warehouse

This question came up at the recent World Wide Data Vault Consortium. Seems there are still many folks who build a data warehouse (or data mart) that do not include FKs in the database.

The usual reason is that it “slows down” load performance.

No surprise there. Been hearing that for years.

And I say one of two things:

1. So what! I need my data to be correct and to come out fast too!

or

2. Show me! How slow is it really?

Keep in mind that while getting the data in quickly is important, so is getting the data out.

Who would you rather have complain – the ETL programmer or the business user trying to run a report?

Yes, it has to be a balance, but you should not immediately dismiss including FKs in your warehouse without considering the options and benefits of those options.

So here are my three main reasons why you should include FK constraints in your Oracle data warehouse database:

  1. The Oracle optimizer uses the constraints to make better decisions on join paths.
  2. Your Data Modeling and BI tools can read the FKs from the data dictionary to create correct joins in the meta data of the tool (SDDM, Erwin, OBIEE, Cognos, Bus Objects can all do this).
  3. It is a good QA check on your ETL. (Yeah, I know… the ETL code is perfect and checks all that stuff, bla, bla, bla)

Now of course there are compromise options. The three main ones are I know:

  1. Drop the constraints at the start of the load then add them back in after the load completes. If any fail to build, that tells you immediately where you may have some data quality problems or your model is wrong (or something else changed).
  2. Build all the constraints as DISABLE NOVALIDATE. This puts them in the database for the BI tools and data modeling tools to see and capture but, since they are not enforced, they put minimal overhead on the load process. And, so I am told by those that know, even a disabled constraint helps the optimizer make a smarter choice on the join path.
  3. (really 2a) Best of both – disable the constraints, load your data, then re-enable the constraints. You get optimization and quality checks.

So NOW what is your reason for not using FKs in your data warehouse?

Happy Modeling!

Kent

Posted in Data Modeling, Data Warehouse, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , , , , , , , , , ,
06 Oct 2013
2 Comments

Better Data Modeling: Finding Missing Unique Keys in Oracle #SQLDevModeler

One of the best practices I recommend is to always define unique business keys for every entity (or table) in a model.

It is the only way to really understand what the data in that object represents.

So what do you do when you inherit someone else’s model with hundreds of tables and few (if any) unique keys to be found?

After you reverse engineer it into SDDM (SQL Developer Data Modeler), you could go through the model table by table and look at the properties.

Or, you could look at all the diagrams to look for the the little U’s indicating a column is part of a unique key constraint (assuming there are any diagrams to look at).

Or you could create a Custom Design Rule that checks for you.

So how do you write a design rule that will list all tables with no UKs on them?

Open your design, the go to Tools -> Design Rules -> Custom Rules.

  1. Hit the green Plus sign to add a new rule.
  2. Give it a name (like Missing UKs),
  3. Select Table for the object type,
  4. Mozilla Rhino for the Engine,
  5. Warning for the type, and
  6. Select table as the variable
  7. Past in this code: 
function checkUKs(table){
ruleMessage=””;
if(table.getUKeys().size() == 0){
  ruleMessage=”no UKs”;
  errType=”Problem:”;
  return false;
} else {
  return true;
}
}
checkUKs(table);

Hit Save, then Apply.

The result will be a list of all the tables in your design that do not have any Unique Key Constraints defined.

Now the real work begins – fixing those tables! As you work your way through the model adding the new business keys, you can keep using this report to see which ones you have left, and make sure you don’t miss any.

Get to it my friends!

Kent

The Oracle Data Warrior

P.S. Special thanks to DimitarSlavov  of Oracle for posting the code to answer my question. If you want to see the whole thread go here.

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , ,
31 Jul 2013

Another Quick Tip for SQL Developer Data Modeler

Not sure how I missed this little utility, but I did.

Ever need to quickly build a set of views on a set of tables to create a read-only data layer you could expose to some users or processes?

Well as I am developing a new data warehouse for my current client, we decided to control access by creating a read-only user that would hold views that pointed back to our main data warehouse schema. The BI tool points to the read only schema (for now anyway).

Anyway, under the Tools menu I found a Table to View generator.

Under the Tools menu look for the Wizard to create a view definition based on a table

Under the Tools menu look for the Wizard to create a view definition based on a table

Once you select it from the menu you get a dialog box with all the tables in your model selected. So with one push of a button you have views on all the tables.

Get a list of table to convert and Select or Deselect all

Get a list of table to convert and Select or Deselect all

Then you can edit the views, if needed, using the view query builder.

Or you can select (or de-select) specific tables to build views on.

Even better – the tool applies a naming standard on the output view names (v_<table name>).

On top of this, if you happen to have some views (maybe for testing?) that you want to turn into tables and then populate with an ETL process, right below the first wizard is an option to create a table definition from the view definition.

Now granted most of you can easily do either of these tasks using plain old SQL, but imagine you need to do it for several hundred tables.

This little wizard would save you a ton of time (and testing).

And you will have a documented data model when you are done.

So go give it a try!

Later.

Kent

NB: I am taking a little downtime in early August so don’t look for any new posts until near the end of the month.

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

Post Navigation