The Data Warrior

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

Archive for the category “Data Warehouse”

20 Nov 2015
19 Comments

Tech Tip: Connect to Snowflake db using #SQLDevModeler

So, some of you may have noticed that I took “real” job this week. I am now the Senior Technical Evangelist for a cool startup company called Snowflake Computing.

Basically we provide a data warehouse database as a service in the cloud.

Pretty cool stuff. (If you want to know more, check out our site at snowflake.net)

I will talk more about the coolness of Snowflake (pun intended) in the future, but for now I just want to show you how easy it is to connect to.

Of course the first thing I want to do when I meet a new database is see if I can connect my most favorite data modeling tool, Oracle SQL Developer Data Modeler (SDDM),  to it and reverse engineer some tables.

The folks here told me that tools like Informatica, MicroStrategy, and Tableau connect just fine using either JDBC or ODBC, and that since we are ANSI SQL compliant, there should be no problem.

And they were right. It was almost as easy as connecting to Oracle but it was WAY easier than connecting to SQL Server.

First you need a login to a Snowflake database. No problem here. Since I am an employee, I do get a login. Check.

We have both a web-UI and a desktop command line tool. Turned out I needed the command line tool which incidentally needed our Snowflake JDBC connector to work. Followed the Snowflake documentation, downloaded the JDBC drive (to my new Mac!). Piece of cake.

So connecting from SDDM is really easy. First add the 3rd party JDBC driver in preferences. Preferences ->Data Modeler -> Third Party JDBC Driver (press the green + sign, then browse to the driver).

Add JDBC Driver

As you can see our JDBC driver is conveniently named snowflake_jdbc.jar.

Next step is to configure the database connection. To do this you go to File -> Import -> Data Dictionary, then add a new connection in the wizard.

Configure Connection

Give at a name and login information, then go to the JDBC tab.

So getting the URL was the trick (for me anyway). Luckily the command line tool displayed the URL when I launched it in a terminal window, so I just copied it from there (totally wild guess on my part).

So the URL (for future reference) is:

jdbc:snowflake://sfcsandbox.snowflakecomputing.com:443/?account=<service name>&user=<account>&ssl=on

Where account is whatever you named your account in Snowflake (once you have one of your very own that is).

The driver class was a little trickier – I had to read our documentation! Thankfully it is very good and has an entire section on how to connect using JDBC. In there I found the drive class name:

com.snowflake.client.jdbc.SnowflakeDriver

That was it.

I pushed the Test button and success!

Now to really test it, I did the typical reverse engineer and was able to see the demo schema and tables and brought them all in.

Snowflake Schema

Demo schema in Snowflake (no, not a snowflake schema!)

So I call that a win.

Not a bad weeks work really:

  1. New job orientation
  2. Start learning a new tech and the “cloud”
  3. Got logged in
  4. Installed SDDM on a Mac for the 1st time ever!
  5. Configured to speak to an “alien” database
  6. Successfully reverse engineer a schema
  7. Blog about it.

So that was my 1st week a a Senior Technical Evangelist.

TGIF!

Kent

still, The Data Warrior

P.S. If you want to see more about my week, just check my twitter stream and start following @SnowflakeDB too.

 

 

Posted in Data Warehouse, Oracle, SnowflakeDB, SQL Developer Data Modeler and tagged , , , , , ,
11 Nov 2015
4 Comments

Better Data Modeling: What is #DataVault 2.0 and Why do I care?

Have you heard?

Dan Linstedt has just had his new book published on Data Vault 2.0. It is called Building a Scalable Data Warehouse with Data Vault 2.0. If you are at all into data warehousing and agile design, you need to get this book now. So click here and be done.

For those of you not sure what this DV 2.0 stuff is all about and why you might want to learn about it, I recently did a series of guest posts for Vertabelo to introduce folks to the concepts. In the series I walk you through some of the history of Data Vault and why we need a new way to do data warehousing. Then I get into the basics of modeling the Data Vault, the Business Vault, and finally building Information Marts from a Data Vault.

So you can find the posts here:

– Data Vault 2.0 Modeling Basics

– The Business Data Vault

– Building an Information Mart with Your Data Vault

Once you have read these, I am sure you will want to go buy the new Data Vault 2.0 book and maybe sign up for some online training on DataVaultAlliance.com.

Model on!

Kent

The Data Warrior

P.S. If you want to catch up, you can still purchase the original Data Vault (1.0) modeling book Super Charge Your Data Warehouse. It is a great reference book to have on hand (you can get it on Kindle too). Might as well have the whole set.

P.P.S. I turned this series into a Kindle ebook for easier reference, you can find it on my Author Profile or just click on the book cover in the right side bar above.

Posted in Data Modeling, Data Vault, Data Warehouse and tagged , , , , , , , ,
01 Oct 2015
2 Comments

The Data Warrior Goes to Oracle Open World 2015 (#OOW15)

Yes boy and girls, it is that time of year again. Time for the BIG show in San Francisco – OOW15

I will as usual, attend, but only for a few days this year (been away from home too much).

My Talk

I will be presenting my session Worst Practices in Data Warehouse Design (UGF9985) on Sunday at 10:00 AM in Moscone South, room 274 (courtesy of ODTUG!).  This will be a quick one as I only have 45 minutes to do a one hour talk. Yikes!

If you will be in SF by Sunday, be sure to come by and say “hi.”

Morning Chi Gung?

As usual, I will try to gather some folks near either Union Square or maybe by Yerba Buena Gardens for a little morning relaxation with some Chi Gung (Qigong) . Most likely around 7 AM for 30 minutes. Probably Saturday, Sunday, and Tuesday, Follow me on twitter @kentgraziano and look for #MorningChiGung #OOW15 for final time and location.

Monday for sure though, I will be doing a shortened session on the beach at the 4th Annual OOW SF Bay Swim around 9:15 AM or so. Nothing like a little chi movement to warm you up before and after jumping in the bay.

The Rest of My Schedule

Much shorter than usual but here is what I plan to see so far:

Session Title Session Code Date Start Time End Time Room
Oracle Database 12c-Data Redaction UGF1513 10/25/2015 8:00 8:45 Moscone West-3011
Oracle Business Intelligence Best Practices: the Bad, the Worse, and the Really Ugly UGF7409 10/25/2015 9:00 9:45 Moscone South-301
Worst Practices in Data Warehouse Design (ME) UGF9985 10/25/2015 10:00 10:45 Moscone South-274
Oracle Database Security: Frustrating the Patient Intruder UGF10016 10/25/2015 11:00 11:45 Moscone South-274
Improving Analytics: Experiences Testing Oracle Database In-Memory 12c Feature UGF9936 10/25/2015 13:30 14:15 Moscone South-303
Fennia Two Years in Production with Oracle Exadata UGF5105 10/25/2015 14:30 15:15 Moscone South-305
Managing the Changes in Database Structures (Heli) UGF6317 10/25/2015 15:30 16:15 Moscone South-306
Oracle OpenWorld Welcome Keynote KEY10818 10/25/2015 17:00 19:00 Moscone North-Hall D
Oracle OpenWorld Monday Keynote Swim The Bay!
 KEY10819 10/26/2015 9:00 10:00 Moscone North-Hall D The Dolphin Club
Oracle SQL Developer Tips and Tricks (Jeff Smith) CON8721 10/26/2015 16:00 16:45 Moscone South-304
Database Development for the Cloud and On Premises (Kris Rice) CON8779 10/26/2015 17:15 18:00 Moscone South-304

Other Fun Stuff

Along with the educational sessions is of course the opportunity to network with folks. I expect I will be doing that as much as possible. There is the annual Oracle ACE dinner, and who know what else. I am sure I will find plenty to keep me busy.

Since I arrive early for the Oracle ACE Directors annual briefing at HQ, I know I will be downtown Friday evening and all day Saturday. Drop me a note if you will be there too and want to hang out and see some of the sites and eat some top notch grub.

Cheers.

Kent

The Data Warrior

Posted in Chi Gung, Data Warehouse, ODTUG, Oracle and tagged , , , , , , ,
19 Jun 2015
3 Comments

Do you want a Data Vault 2.0 Bootcamp in The Woodlands, Texas?

Survey time peeps!

Simple Yes/No question:

Would you like me to hold a DV 2.0 Bootcamp (and private certification) west of the Mississippi?

If I set one up a in “America’s Hometown” (really it is even trademarked), The Woodlands, Texas (20 minutes north of Houston Bush Intercontinental Airport), would you come?

The Woodlands Waterway and our concert venue The Pavillion

The beautiful Woodlands Waterway and our concert venue, The Pavilion. You can take a water taxi to a concert!

Since I am an authorized DV 2.0 Trainer, I figure it is time I actually teach a class. And why not in my hometown in Texas?

So what is in a DV 2.0 Bootcamp class?

Three days of intense training on all things DV (followed by a chance to become a Certified DV 2.0 Practitioner).  NOTE: If you are new to Data Vault, you must read the Super Charge book before attending the class.

This class covers what you need to know as a practitioner in the world of Data Warehousing and Business Intelligence.  This is our foundational course.  This is a 3 day (in person) course that covers end-to-end best practices.  Major topics for this class are:

  • Architecture – Including NoSQL, Big Data, Hybrid Systems and Relational stores
  • Methodology – Including CMMI, Six Sigma, Optimization, Automation, and Generation
  • Implementation – Including Performance and Tuning, Set Logic, ELT vs ETL, Parallelism
  • Modeling – Including replacing of surrogates with Hash Keys, data layout, data co-location

This class takes you through the why/what/how of Data Vault 2.0.  It includes the coverage of the business justifications, then follows with the technical descriptions of the architecture, implementation, methodology, and modeling.  Included in the topics are reaching agility, practicing Six Sigma, measuring and optimizing at CMMI level 5, the KPA’s and KPI’s of Data Warehousing, and more.

We also discuss the use of Hadoop, and NoSQL platforms along side the relational world.  The objective is to enrich your understanding of how and when to apply Big Data Solutions.  The course finishes with descriptions on ETL and ELT design time paradigms, including templates, best practices and working SQL.  This class is a prerequisite for anyone wishing to achieve DV2.0 Certified Practitioner status.

When:

Late summer – early fall (depending on interest) of 2015

Cost:

TBD based on how much the space costs me here!

But will definitely be less than $3,000.

Where:

Someplace nice and central in The Woodlands within walking distance to great food and drink.

Apartments over looking the waterway that flows to Lake Woodlands. A great natural setting. Not the usual suburban wasteland.

Apartments over looking the waterway that flows to Lake Woodlands. A great natural setting. Not the usual suburban wasteland.

So who is in?

Please respond in the comments so I can tell if I should start setting something up.

Thanks.

Kent

The Data Warrior

P.S. Since you know I am into fitness, we have great options here to exercise here too. MIles of running trails and even kayaking on the waterway.

The Riva Boathouse were you can rent single and double kayaks by the hour (look close and you find a picture of my son and & on the sign from when they first opened).

The Riva Boathouse were you can rent single and double kayaks by the hour (look close, when you are here, and you find a picture of my son and & I on the sign from when they first opened).

Posted in Data Modeling, Data Vault, Data Warehouse and tagged , , , , , , ,
15 Jun 2015

Surfs Up! #KScope15 Beach Club: Opening Soon!

I know this is last minute but wanted to remind everyone that the Annual ODTUG KScope event starts in less that a week!

As always, I will be there with my ACE Director hat on (literally). I will be giving My Talks at KScope during the week. One on SQL Developer Data Modeler (SDDM), and my fun talk about the Worst Practices in DW Design I have seen. And there will be the Lunch with the ACEs among many other events.

I will also be running Morning Chi Gung on the beach each day to help people stay in shape and stress free.

Just so you know the score, I have captured for you a list of great stuff happening at KScope from the recent attendee email I got. Here it is. Enjoy

Believe it or not, we are only six days away from the opening of the Kscope Beach Club in Hollywood, Florida. This exclusive club is only open to Kscope15 attendees, so consider yourself one of the elite.

This is the third newsletter with content updates, event happenings, and everything you need to know to plan for Kscope15. It’s a long email full of important information to make your week a success, so please read it all.

See you soon!

– Natalie Delemar, Kscope15 Conference Chair

Important Updates

Community Service Day – If you have already signed up to attend Saturday’s Community Service Day, please meet at the Diplomat Conference Center Porte Cochere at 7:00 AM to have breakfast and pick up T-shirts. The activity is within walking distance (.5 mile); however, transportation will be provided to those who need it. Participants will depart the hotel at 8:00 AM. Wear comfortable clothing for both the walk and the activities. If you did not sign up to participate in Community Service Day but you would like to help, show up at the Conference Center Porte Cochere on Saturday morning. Chances are you will be able to join the group on a first-come, first-served basis.

Registration – Kscope15 registration is open on Saturday from 5:00-7:00 PM on the third floor of the Diplomat Conference Center. After that, registration begins at 7:00 AM on Monday and Tuesday; 7:30 AM on Wednesday; and 8:00 AM on Thursday. Check the agenda for daily schedules.

Mobile App – Build your schedule and have all of your devices sync to it with the Kscope15 mobile app. The first time you login you will need to click “create a password” and enter the email address you used for your Kscope15 registration. A password will be emailed to you. Only registered attendees (who have paid) can access the app. Download the app today and get busy building your schedule!

Schedule – Want to see the schedule in a grid format before you receive your printed Schedule at a Glance onsite? Check this out (you will have to log in to http://www.odtug.com to view) – but keep in mind the most up-to-date schedule will be available on the mobile app and the schedule signs outside of the Registration Desk.

Confirm Your Stay – Be sure to double-check your hotel reservations and confirm your arrival and departure dates are correct. Some properties may require a 72-hour cancellation notice.

Say Cheese – Need a new headshot or family pic? We’ve got you covered. Accomplished Kscope photographer Warren Capps is offering free sessions to a select number of Kscope attendees. Find out more here. Space is very limited, so sign up today!

Scavenger Hunt – Are you competitive, driven, and like to be a winner? Then participate in the Kscope15 Scavenger Hunt. Sign up in advance, and stop by the Registration Desk to receive your NFC sticker to fully participate!

Social Media Lounge – Give your brain a break and head over to ODTUG’s Social Media Lounge in the Exhibit Hall. We’ll have giveaways, picture-taking opportunities, and an area where you can sit back and relax. While you’re there, tell us what you think about Kscope in an interview, or watch one while it happens!

Women in Technology Roundtable – Participate in Wednesday’s interactive discussion about issues facing Women in Technology. Men are welcome, too! Ernst & Young is sponsoring the event. To participate in the luncheon, please register here and share your feedback on important issues.

Hands-On Training – If you are planning to participate in any Hands-On Training sessions, please view the mobile app and designated webpage for important prerequisite instructions. Details may be added within the next several days, so check back closer to the classes for updates.

Special Event – The White Party at Nikki Beach comes with a dress code: Plan your all-white outfit to live it up South Beach-style! It’s not required, but we would love as much participation as possible.

Fun for the Little Ones – Sign up any children in your group for Kids Fun Night Out on Wednesday while you are celebrating in South Beach. The cost is $70 per child, which will be charged to your room. Reservations are required; email Natalie.Dotson@diplomatresort.com.

Conference Dress – Casual. We want you to be comfortable! The days and nights will be hot in South Florida, but the conference rooms will be cool, so layers are recommended. Pack your swimsuit and hit the beach or pools in your off-time!

via Kscope Beach Club: Opening Soon!.

Hope to see you next week in Florida.

Kent

The Data Warrior

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

Post Navigation

← Older Entries
Newer Entries →