Search results matching tags 'Tools', 'Administration', and 'PASS'

Is There Such a Thing as Easy ETL?

KKline — Thu, 30 Jun 2011 04:57:00 GMT

E.T.L. That's Extract - Transform - Load. That doesn't sound like a lot of work when all you need to get loaded is a simple Access database or an Excel spreadsheet. In a situation like that, the process is so simple, all you really need to focus on is the L in ETL. There's not a whole lot of E.T. to process, despite how wonderful that movie is. [pun intended] But as soon as your data loading process involves some difficult or sophisticated cleansing or transformations, it gets really, really hard. The other cross-thread that had really caught my interest lately is the USA federal governments Open Data Initiative. I think it's remarkable that President Obama is the first president to appoint a federal CIO. (Shouldn't that have happened in the past?) In addition, President Obama instructed the entire executive branch to open up their data (where security isn't at risk) and make it readily available to the public. And the US government collects mountains of interesting and valuable data for its own uses, but figuring out how or who to share it with was always an afterthought. While I was a contractor for NASA, for example, I worked on some incredibly interesting projects which yielded amazing and commercially valuable information. It was all public domain. But unless you knew it was there, you couldn't get to it. Making use of all of that data always intrigued me. Now, with ODI, it's all being put on the internet at an ever-increasing rate at Data.gov. However, all of this data, while open and available, is not standardized. Some data sets might be a CSV file, while others might be something like a spreadsheet. That means you'll need to extract, transform, and load that data if you want to synthesize more valuable data sets. For those reasons, I've been researching tools to help make this process easier. (I also wanted to research SSIS and ETL tools for my Tool Time column in SQL Server Magazine.) Now, I've been following expressor software for quite some time and really like their unique approach. (I actually ran into the expressor software team at a PASS Summit one or two years ago and asked for a demo of their software. And I really liked what I saw.) Rather than the workflow approach used by SSIS, expressor software uses a data mapping approach combined with reusable business rules. Their mapping approach is fundamentally different from the traditional point-to-point, source-to-target mappings paradigm. Basically, you can define a semantic type representative of your business data, create a business rule(s) to apply to the data, and then implement a "canonical" mapping which connects data sources and targets to that same semantic type. And it's free!

Abstraction is Awesome

What's cool about that? Don't forget that "semantic" means "meaning". So a semantic type is an abstraction of the meaning of the data. The net result is that expressor shields your data integration application, with its associated business and transformation rules, from changes that might occur to underlying target or source files with different field names and data type representations have to be processed. For example, let’s assume that you need to process invoices from different vendors in slightly different formats. If you use a traditional ETL tool like SSIS, any changes in the source and/or target formats will require you to modify your data mappings and transformation rules, because the mappings are tied directly to the metadata structure of the invoice file format(s). expressor, on the other hand, lets you define a common “invoice” semantic type, build all your downstream data processing off that type and map one or multiple invoice file schemas to the type. This approach greatly simplifies the mapping process and provides for more flexible data integration applications that can be more easily adapted to changes in the source and target data sources.

Benefits Abound

Since the semantic types in expressor are captured as reusable artifacts, you can also reuse them again in new data flows within your project(s). You can even share them across your entire organization. As I tinkered with the expressor Studio tool, I hit on a few other benefits with this approach:

Handles data type conversions automatically without having to write data transformation rules for these conversions
Builds new semantic types from existing types and reuses types in existing and new applications
Creates multiple, reusable business rules against a single type and applies them repeatedly as needed
Easily implements data quality rules and constraints

In an Ideal World...

In an ideal world, I'd figure out some brilliant way to make money from bringing together all kinds of that government data that I used to work with. Other folks are doing it at the Windows Azure Data Market. But in the meanwhile, I'm also looking forward to tinkering with this data to build better demos. Along the way, I'm going to use the expressor Studio desktop ETL tool (Did I mention that it's free!) as well as tell you about my experiences as I try to build out some Data.gov data sets. Those of you who know me, know that I look a good discussion and cooperative, constructive team work. So I encourage your feedback and suggestions, as I work through these data integration challenges and share my experiences. I'm looking forward to sharing with you my insights on what the expressor data integration software can do with this challenge and what some of its features and capabilities are. In upcoming releases, I'll let you know what I find intriguing and worth mentioning. Check out their website, www.expressor-software.com, to learn more about their company and products. Enjoy, -Kev Follow me on Twitter More content on KevinEKline.com

Free Training for You, Punishing Workload for Me. Thank You for Enjoying My Misery.

KKline — Tue, 09 Feb 2010 19:12:00 GMT

Everybody enjoys a little free training now and then. I've got mountains of free training in the works. Some of it is in-person and local while some are in webcast format. Please join me as you're able!

Tomorrow - Wednesday!

Wed, Feb 10, 2010 12 pm (noon) MST - PASS DBA Virtual Chapter; Top 10 Administration Mistakes on SQL Server

There are a short list of mistakes that, if you know of them in advance and prepare for them, will make your life much easier. This presentation shows you these mistakes, the "low hanging fruit" of database administration. Once you apply the lessons learned from this session, you’ll find yourself performing at a higher level of efficiency and effectiveness than before. Quest is sponsoring this and raffling off a copy of my book, SQL in a Nutshell, and O-M-G a KINDLE!!! (Free, but registration required.) And if you haven't already done it, join PASS!

Day After Tomorrow - Thursday!

Thu, Feb 11, 2010 at 11 am EST - Quest Software Pain of the Week; Understanding & Preventing SQL Injection Attacks - with Kevin Kline and K. Brian Kelley

SQL Injection attacks is the most common hacker trick used on the Web and yet, one of the easiest to prevent. Learn what they are and why you need to be concerned about them. In this webcast, I and SQL Server MVP K. Brian Kelley (blog | twitter) will demonstrate different types of SQL injection attacks. You’ll learn how to find and block them to keep your databases safe, preventing disruptions to your organization. Quest is sponsoring this as well and raffling off more goodies. (Free, but registration required.)

Thu, Feb 11, 2010 at 6 pm CST - Nashville .NET User Group; Ten Things DBAs Want .NET Developers to Know

Stereotypes abound for different types of people in the IT world. Developers think DBAs are control freaks. DBAs think developers are unruly cowboys. How do we overcome these differences? If you’re a developer, your best strategy is to manage your DBAs’ anxieties and demonstrate your competence and credibility. Attend this session to learn about 10 techniques that developers can apply to their code which will calm your DBAs’ fears and earn their admiration. Techniques include how to analyze a query plan and how to make sure your query is taking advantage of the best available indexes. Attendance is free although registration is encouraged so we can get a good count for the food, plus there's free pizza and a raffle at the end. Drinks afterwards at Chilis.

Coming Soon

Mar 03, 2010 >>>FULL DAY OF FREE TRAINING!<<< - Quest Software SQL Server vConference; Troubleshooting and Performance Tuning with Kevin Kline, Brent Ozar, and Ariel Weil

Join SQL Server MVPs, Kevin Kline and Brent Ozar (blog | twitter), along with Quest Product Manager Ari Weil —all from Quest Software—as they interactively present tips and tricks to help you monitor your SQL Server environment with ease. You’ll learn how to use Dynamic Management Views (DMVs) to simplify troubleshooting and significantly enhance SQL Server performance. The day consists of eight sessions with live Q&A - a great way to get answers to your questions straight from the source. Chat live with Quest experts. Plus, you can visit the Quest booth to download helpful resources like white papers, watch demos and more. This virtual training day is free and you can attend the sessions that fit your needs - perfect for tight budgets and busy schedules.

Mar 06, 2010 - PASS SQL Saturday, Charlotte, SC

The SQL Server Community will experience an event like no other in the Southeast region. Renowned speakers from around the world will gather in Charlotte, NC to share their knowledge on Microsoft SQL Server. This is your opportunity to meet, network with and learn from the SQL Community leading experts such as Andrew Kelly, Rick Heiges, John Welch, Brian Knight, Geoff Hiten, Andy Warren, Jessica M. Moss, Rafael Salas, Sergey Pustovit and Tim Ford. Also scheduled to present are current SQL PASS President Rushabh Mehta and former SQL PASS Presidents Wayne Snyder and Kevin Kline.

Mar 24, 2010 - PASS DBA Virtual Chapter; SQL Internals & Architecture Apr 08, 2010 - Quest Software Pain of the Week; The Admin's Guide to SQL Server w/ Buck Woody Apr 12, 2010 - PASS Chapter, Houston; topic TBD Apr 17, 2010 - PASS SQL Saturday, Chicago; Keynoting w/ Brent Ozar May 13, 2010 - Quest Software Pain of the Week; SQL Server for the Oracle DBA w/ Buck Woody May 13, 2010 - PASS Chapter, Louisville; topic TBD May 15, 2010 - PASS SQL Saturday, Louisville; topic TBD May 22, 2010 - Indy Tech Fest; SQL Internals & Architecture