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.

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