What SQL Cannot Do

I recently asked my Twitter followers if they had good examples of queries, business questions that SQL can't do. It turns out a better question is "what SQL can't do easily", so I thought I'd share with everyone what I heard and found. Seth Grimes was the first one to provide an excellent answer with some informative examples - thank you, Seth! I also found very useful articles on typical SQL challenges such as avoiding multiple duplicate sets in your SQL results, and why NULLs create tons of headaches for SQL coders.

There's also a typical SQL challenge with ragged, sparse, unbalanced hierarchies and dimensions. For example, a retail store, a wholesaler or a distributor with thousands of products, and a manufacturer with thousands of parts often struggle with dissimilar data. A pencil in an office supply store does not have the same descriptive attributes (lead type, for example) as a calculator (scientific, financial, etc.) or an office chair (number of wheels, etc.). Or a tire in a car manufacturing supply chain does not have any common descriptive elements (rubber grade, width-to-height ratio) with gear boxes (automatic vs. manual, 4 or 5 speed, gear-to-gear ratios, etc). When looking for correlation between two entities (for example, what is a potential product quality issue that is making my sales go down?) in cases with disparate, dissimilar products (as in retail products or manufacturing parts), the same SQL query cannot work for all products or parts. One would be forced to write multiple SQL queries for each product or part type to find such a sales/quality relationship.

BI Megavendors Continue To Round Out Their BI Portfolios - IBM Acquires Coremetrics

IBM announced its intentions to acquire Coremetrics, a leading Web analytics vendor, as BI megavendors continue to round out their BI portfolios (the other leading vendor in the space, Omniture, was recently picked up by Adobe). Good move, IBM. Web analytics can't really continue to exist in a silo. In order to get truly complete 360-degree view of customers, prospects and products, one needs to combine Web analytics data with ERP, CRM, HR, Financials and other transactional and analytical data sets. Currently, there are no off-the-shelf solutions that do that - it's pretty much the realm of customized offerings and systems integration. If IBM can indeed plug Web analytics into its data integration, data warehouse and BI products and solutions, it'd be quite a differentiated offering. Other large BI vendors, like Microsoft, Oracle and SAP will probably pick up one of the remaining Web analytics vendors Nedstat, Unica and Webtrends sometime soon.

Autonomy Purchase Of CA Technologies’ Information Governance Business – Will Customers Benefit?

Autonomy announced today that it plans to acquire CA Technologies’ Information Governance business. The deal, expected to close in Q3, focuses on archiving, records management, and eDiscovery. Given overlaps in the vendors' offerings, I see this as a somewhat surprising move and suggest that customers keep a close watch on how this plays out. 

With this purchase, the major offerings that Autonomy picks up are CA Records Manager (which stems from CA’s 2006 acquisition of MDY Group International) and CA Message Manager (which comes from CA’s 2005 acquistion of iLumin). In 2009, I evaluated records management offerings and rated CA Records Manager as a leader in this category. Forrester clients can access the June 23, 2009, “The Forrester Wave™: Records Management, Q2 2009” report for further details.

From its prior acquisitions of Interwoven in 2009 and Meridio in 2007, Autonomy has two existing records management applications. Largely leveraging its 2007 purchase of ZANTAZ, Autonomy also currently markets several message archiving solutions including Digital Safe (cloud-based archiving solution), Enterprise Archive Solution (on-premise archiving software), Arcpliance (on-premise archiving appliance), and more. After it completes the acquisition of CA Technologies’ Information Governance business, Autonomy will have three distinct offerings for records management and over four for message archiving.

Read more

BI vs. Analytics

I know many of you already know my position on this, but I thought I'd get it out in the open and challenge all of you with a controversial discussion. In my definition – and believe it, I am fighting and defending it every day – analytics has always been, and will always be, part of BI. What many of the vendors and analysts describe as "the new age of analytics" I built at Citibank in the early '80s and then built in about 50+ enterprises in the '90s at PwC. I think the effort of trying to differentiate analytics from BI is a vendor-invented hype, since many BI vendors are running out of ways to differentiate themselves (and incorrectly so: see the next paragraph, and many other next-gen BI trends). I also disagree with the “old BI = bad”, “new analytics = good” premise that I see in many analysts' papers. You and I know that you can’t build analytics (OLAP, advanced analytics, etc.) without basic ETL, DW, MDM, etc. So nothing’s really changed as far as I am concerned: we are still fighting the same battles – silos, data quality, etc.

Read more

10 Components Of A Successful BI Strategy Plan

Defining a successful BI strategy is a lot more than gathering requirements and selecting a vendor. While it’s been a subject of many books, I know few of you have time to read them, so here’s a short version.

  1. First defining what BI is and what it is not. Is it just reporting, analytics and dashboards? Or does it involve ETL, DW, portal, MDM, etc., as well?
  2. If the former, you then need to define linkages, dependencies, overlaps and integration with all of the latter (including - very importantly - integration and coordination with the higher level enterprise architecture efforts). If latter, it’s a whole different subject. You then really do need to read a few thick books.
  3. Ensure senior business executive commitment and top down mandate. If you cannot get that, do not proceed until you do. Two ways to “sell BI” to them (even though that’s not a good position to be in):
Read more