Data Doghouse - performance management, business intelligence, and data warehousing

Web Seminar - The Key to Operational BI: Keep It Simple

Rick Sherman — Tue, 10 Nov 2009 18:29:59 -0500

Registration is now open for my upcoming free webinar: The Key to Operational BI: Keep It Simple.

Date: November 17, 2009
Time: 12PM ET/ 9AM PT

REGISTER TODAY

Delivering insights to front-line workers can significantly extend the value of your information management investments. What's more, operational BI can offer more immediate, tangible return than traditional, analyst-focused BI. The key is to keep it simple: operational workers often don't have much time for training or learning complex analysis tools. Instead, give them simple applications that deliver targeted advice.

Register for this Espresso Shot Web Seminar to hear Jake Freivald and me discuss the virtues of Operational BI, and outline the practical steps necessary to get there quickly.

Attendees will learn:

How to combine traditional and operational BI to align strategy with operations
Which delivery mechanisms work best for operational BI
How the information infrastructure should be handled
How Operational BI can improve enterprise efficiency

Speakers:

Rick Sherman, Athena IT Solutions
Jake Freivald, Information Builders

Data Integration: Hand-coding Using ETL Tools Part 2

Rick Sherman — Fri, 06 Nov 2009 07:23:21 -0500

This is a continuation of an earlier post that discussed the problems of hand-coding using ETL tools.

What Went Wrong?

There are two aspects of effectively leveraging an ETL tool. First is learning the tool’s mechanics. e.g. taking the tool vendors’ training either in a class or through their on-line tutorials. Most IT people have no problem learning a tool’s syntax. Since they most likely already know SQL, they learn the tool very quickly.

But the second aspect actually involves understanding ETL processes. This includes knowing the data-integration processes needed to gather, conform, cleanse and transform; understanding not only what is dimensional modeling but why and how do you deploy it; being able to implement slowly changing dimensions (SCD) and change data capture (CDC); understanding the data demands of business intelligence; and being able to implement error handling and conditional processing.

Without understanding the why of ETL processing, IT developers either quickly become disillusioned with ETL tools or simply under utilize them. Typically these ETL implementations merely result in the ETL tools executing SQL scripts or stored procedures, e.g. hand-coding.

These hand-coded processes within ETL tools are big trouble-makers. First, the tools have built-in transforms such as SCD and CDC which, if you don't use, make you re-invent the wheel (code something you already bought). In doing so, you're likely doing something inefficient at best and outright wrong at worst.

Second, ETL tools are built to be more efficient at extracting, transforming and loading data than SQL coders.

Third, the IT staff is not likely to code extensive error handling or audit routines that are pre-built in the ETL tools. This lessens productivity and responsiveness to issues in data quality.

Fourth, hand-coded processes are often not documented or, if they are initially, they're not likely to be maintained.

Finally, each hand-coded operation is a custom job that each new developer has to learn, versus being able to bring in a developer who knows an ETL tool.

How to Avoid Repeating History’s Mistakes

You don’t know what you don’t know. It’s not that the IT staff wants to use these ETL tools either incorrectly or inappropriately, but they don’t know any better.

I’ll keep preaching that data-integration processes should be developed using ETL tools rather than hand coding. But what I have learned along the way is I also need to advocate that anyone using these tools learn not just about the tool but more importantly about ETL processing.

Fyi: a good starting place is my articles on ETL. Check out my corporate library pointing to my articles, posts, webinars, podcasts and white papers.

Data Integration: Hand-coding Using ETL Tools

Rick Sherman — Wed, 04 Nov 2009 13:44:18 -0500

We are creatures of habit. It’s not easy to stop doing something the way we’ve always done it. Especially when we think we are right (but actually we’re not). Let’s explain.

I have discussed (some might say preached) in many posts, articles, webinars, podcasts, classes and client discussions that for any recurring data integration tasks IT should use an Extract, Transform and Load (ETL) tool.

This certainly has been the best practice for enterprise data warehousing projects in the Fortune 1000. This is where I got my early experience in data integration and got to use the ETL tools that annually rank in Gartner’s Upper Magic Quadrant and Forrester’s Top Wave. These ETL tools enabled IT groups and SI (system integrator) project teams to tackle data integration challenges too complex and extensive for hand-coding.

However, while the enterprise data warehousing projects were being developed with enterprise class ETL tools, most Fortune 1000 departmental projects and small to medium business (SMB) companies were hand-coding their data-integration processes.

IT groups choose to hand-code because for quite a while the enterprise class ETL tools they heard of were too expensive for their budgets. In addition, these tools required dedicated, trained developers. Life in a small IT group means doing multiple tasks and never really having time to get training. The result is millions of lines of hand-coded ETL in enterprises today -- most of which is not documented and took much longer to develop than it would have with an ETL tool.

Times are Changing

But times have changed. There are now very robust, affordable ETL tools that can easily handle any departmental or SMB data integration projects (and might even be able to handle some enterprise data warehouses too).

In fact, two classes of ETL tools are free (or almost so). First, database vendors have “bundled” ETL tools with their databases. Although initially these tools were very simplistic they have expanded over the years have quite robust functionality. Second, open source software (OSS) ETL tools have also emerged and are capable of handling many departmental and SMB needs.

The emergence of these tools has broken through the pricing barrier with ETL tools that are not only quite capable, but not difficult for a small IT staff to learn. I have been advocating these tools in the departmental and SMB data integration market for years. The good news is these tools are picking up converts but the bad news is IT may not be using these tools as well as they might.

Stay tuned for my next post, where I continue this discussion with Hand-Coding: What Went Wrong and How to Avoid Repeating History’s Mistakes

Discount on my Online DW and BI Data Modeling Course

Rick Sherman — Mon, 02 Nov 2009 13:40:46 -0500

My new DW and BI Data Modeling course is now available on ELearningCurve. This is a great bargain compared to traveling to a conference. From now until November 6 you can get a 20% discount by using the coupon code DM0420.

A well designed data model is the cornerstone to building business intelligence and data warehouse applications that provide significant business value. My DW and BI Data Modeling course will help you:

understand what Logical Data Modeling is, its goals, and its components
know the basics of Entity-Relationship (ER) and dimensional modeling
understand how to benefits of and applicability of Dimensional Data Modeling
be able to create a Dimensional Data Models for BI & DW applications
be positioned to learn more about Data Modeling

Click for a preview of the introduction and to learn more.

Leveraging Enterprise Data Virtual Seminar

Rick Sherman — Thu, 22 Oct 2009 10:03:16 -0400

I'll be speaking at TechTarget's Leveraging Enterprise Data Virtual Seminar today at 11:15 and 3:15.

Here's their description:

Attend this seminar and learn new ideas and actionable tactics for cost-effectively optimizing or expanding existing BI-PM implementations. Get essential tips to better leverage data from ERP, CRM or other business applications such as SAP, Oracle or other enterprise vendors. Key presenters Mark Smith, CEO & EVP Research at Ventana Research and Rick Sherman, Founder, Athena IT Solutions teach new approaches to overcoming the integration and architecture challenges that can plague BI and data management projects with tactical, technical advice.

And all of this valuable information is delivered in an easy to navigate interface that provides a unique virtual seminar experience - right from your desktop!

Today's Virtual Seminar Agenda (All times are Eastern Standard) 9:00 AM......... Doors Open

9:00 - 9:30 AM ....... Visit our Sponsor Booths

9:30 - 10:15 AM....... Expert Session - Innovative Technologies for Leveraging Enterprise Data

10:15 - 10:30 AM......... Exhibit Hall Open, Live Chats

10:30 - 11:00 AM....... The Case for Investing in Business Analytics Technology - Presented by SAS

11:00 - 11:15 AM........ Exhibit Hall Open, Live Chats

11:15 - 12:00 PM......... Expert Session - Enterprise Data Integration Strategies for BI Systems

12:00 - 12:45 PM............ Join a Live Customer Chat in the Networking Lounge

12:45 - 1:15 PM............ What Every IT Manager Should Know About Users' Real Need for BI - Presented byTableau Software

1:15 - 1:30 PM............Exhibit Hall Open, Live Chats

1:30 - 2:15 PM.........Expert Session - Inexpensive but Capable BI and PM Technology

2:15 - 2:30 PM.........Exhibit Hall Open, Live Chats

2:30 - 3:00 PM.........Sponsor Webcast - Presented by Sun Microsystems

3:00 - 3:15 PM.......Exhibit Hall Open, Live Chats

3:15 - 4:00 PM.......Expert Panel Discussion - The Reality of BI Vendor Offerings

4:00 - 4:15 PM.........Exhibit Hall Open, Prize Giveaway

4:15 PM........... Doors Close