QlikTech’s QlikView reporting and analysis tool is among a new class of Business Intelligence (BI) software tools. As Ben Harden reported in a recent blog post, BI vendors like SAP, Microsoft, and IBM have traditionally sold “to the IT enterprise, but companies like QlikTech and Tableau are targeting the business and bypassing IT. Their tools are quicker to stand up, more intuitive and don’t need the configuration, support, and hardware that the bigger players require.”

A Quick Overview

At first look QlikView is fairly accessible to those experienced with BI tools. A “.qvw” QlikView file contains three classes of user-facing components: a script-based data integration language that runs when the user requests a “reload”, a data modeling component that looks deceptively like a relational data modeling tool, and a familiar array of data visualizations: graphics, charts, lists, etc.

In the past I've never understood what people really mean they say "think outside the box" but Jim Harris, in a recent OCDQ blog post, helped me figure it out.

Mr. Harris ends with this provocative line: "the bottom line is Google and Facebook have socialized data in order to capitalize data as a true corporate asset."  The post starts with a cold war analogy and proceeds to describe how Facebook and Google have made big money as "internet advertising agencies:" offering free services with which users (like us) serve up personal data in return for use of the service, then selling advertising space based on our data (hopefully anonymized).

I’m a data modeler, so I enjoyed Jonathon Geiger’s recent article entitled “Why Does Data Modeling Take So Long”.  But why does he say it like it’s a bad thing?

Mr. Geiger’s bottom line is exactly right: “Most of the time spent developing data models is consumed developing or clarifying the requirements and business rules and ensuring that the data structure can be populated by the existing data sources." On the projects he describes, no one took time before modeling to determine available data sources and identify business entities of interest, relationships among them, and attributes that describe them before database design started, so the data modeler had to do it.

I recently completed ScrumMaster training ably presented by Lyssa Adkins. Throughout the two-day class we appreciated Lyssa’s Zen-like, enabling, style. If her name is familiar, it’s because Ms. Adkins is the author of the book Coaching Agile Teams, one of the leading texts on the subject.

I’ve participated on agile projects, but so far only in a piggish/chickenish role, once in a three-week stint as a consulting architect and twice as the project manager serving as interface to the non-agile organization. To me Ms. Adkins rocks at making students very introspective and critical of their past project experiences.  These lessons stand out:

Margaret, who was an average sales person, moved from Washington, DC to Richmond, VA, whose market is one fifth the size, during the month of June.  When the annual evaluations of sales performance were done in the month of December, she was listed as the top performer in the Richmond market resulting in the company promoting her to Sales Director.  The next two highest ranked Richmond salespeople had been the consistent leaders for the last several years and outperformed Margaret since she arrived in Richmond.  Her very high sales numbers during the first six months of the year skewed her average, placing her above the rest of the Richmond area.  In this example, if the decision makers had correct information handy, and used it appropriately, would they have promoted Margaret over her new Richmond peers?

Here is another example.

A colleague introduced the term “convenience view” to me and that term resonated with me ever since.  A convenience view is one of those database objects intended to make life easier for people to access data without actually understanding the nuances and relationships of those data.  Convenience views frequently join multiple tables together so data users will not need to code or optimize those joins.  Convenience views may also include business logic which transforms data so end users will not need to code or argue those transformations.  The concept seems noble enough.  Who opposes simple data access, optimized joins and centralized business rules?  Just like that store that sells everything right off the interstate, convenience comes at a cost.

One obvious cost is all of those optimized joins.  Sure each individual join may be optimized, but the database engine has to collectively consider all available join options before an execution

I've worked with health care data for the past few years, and in a recent conversation I realized it might be valuable to detail some of the complexities of health care data for those who might enter this growing field.  Of course these considerations aren't unique to health care, but they are typical of the challenges that the new health care application developer or analyst might face.

When I design a database, and local standards permit, I include both a surrogate primary key and audit columns on every table. 

A surrogate primary key is a system-generated integer that increments by one with each new row inserted.  Most DBMSs make it easy to add surrogate keys.  Oracle uses a construct called a Sequence, SQL Server calls its variant an Identity. 

The audit columns I like to include are these, shown with their SQL Server datatypes:  CreationDatetime (datetime), UpdateDatetime (datetime), CreationUserId (varchar (30)), and UpdateUserid (varchar (30)).  Most DBMS’s offer the ability to update columns like these with either default values or triggers.

Recently there has been a long, and very interesting, discussion of do-it-yourself versus third-party metadata tools on LinkedIn's TDWI BI and DW discussion forum (membership required to follow the link). I have followed but haven't commented, but I suppose I contributed in a way when Information Management kindly published my article on DIY metadata.

I’ve often thought that conceptual data modeling was an underused tool in the arsenal available to requirements analysts, and in a recent conversation I found that many were surprised that it would be used in the requirements phase at all.  Checking the Business Analysis Body of Knowledge (BABOK) I found data modeling listed among the tools available to requirements analysts to “to describe the concepts relevant to a domain, the relationships between those concepts, and information associated with them.”  There’s also Steve Hoberman’s excellent book on the topic, Data Modeling for the Business, an introduction to data modeling aimed at a business audience