Category Archives: ETL

Micro ETL in the PowerPivot age

Posted on August 20, 2010 by gobansaor| Leave a comment

Although PowerPivot has many of the characteristics of an ETL tool, i.e. the ability to connect to disparate datasources, to filter that data and to transform it, it will still hit a brick wall when confronted by the typical data spewed out by operational systems. I’m sure this is by design as a sophisticated ETL tool is both complex to design and, probably even more relevant, is difficult to use.

Mind you, a few years back we IT pros would have said the same about front-end BI cube configuration, and behold today we have tools such as PowerPivot that prove that this doesn’t always hold true. Perhaps subsequent versions of PowerPivot will do the same for ETL as it has for BI cubes. In the mean time much of the necessary ETL will have to take place prior to loading into PowerPivot.  But where?

First off, what’s ETL?

The term ETL applies to one of the trinity of activities that have, over the last two decades or so, been at the heart of reporting/decision support systems. The other two terms: DW (data warehousing)  & BI (business intelligence), are sometimes used to refer to the whole process but can also be used to refer to two distinct sub-processes. Confused? Well, so you should be; these terms have been abused and redefined by scores of vendors over the years but for our purpose here we’ll stick to their roles as acronyms for the two of the  processes involved in the preparation & presentation of reporting data.

BI is the term now most commonly associated by non-IT folks with decision support systems, as it’s role is the most obvious i.e. front-end presentation and manipulation of data; the dashboards, pivots, charts, summary lists etc…

DW, data warehousing, is the term that most IT people who’ve been in the business for a while would use to describe the techniques, best practice etc. associated with this area. The heart of traditional DW was the data warehouse itself, a mighty repository of historical data optimized for reporting purposes. When DW as a concept started it was very rare indeed for operational (OLTP) systems to hold transactional data for more that a few weeks, usually just long enough to get through month-end.  Such specially built datastores’ days may be numbered as the data capacity of operational systems grow and the data munching ability of new ETL techniques (MapReduce for example) to transform vast amounts of data continues to increase.

ETL stands for Extract,Transform and Load; sometimes also styled, ELT, extract load & transform (PowerPivot would fall into this catergory). This is the process which traditionally swallowed most of the development budget in DW/BI projects (and kept me gainfully employed for years). It was the area where the dark arts of datasmiths collided with the often frightening reality of raw untamed data, with the added venom of corporate-politics-driven “data ownership” battles.  A messy business, and continues to be, even in these days of open data and open APIs.

ETL tools vary from text-editor written SQL to hugely expensive point’n'click ETL packages. Packaged ETL vendors promised (and continue to do so) that their tools would vanquish the dark arts of datasmiths with products that were so easy to use that the CEO would chip in with a few scripts to get the project finished. The reality was that IT types  found they had to learn yet another sub-optimal “language” and more often than not had to drop-down to “proper” languages to actually drive the thing to completion. ETL was (and still largely is) the preserve of IT.

ETL is easier than herding cats but just about ...

The tools have improved a lot since those early days and open source has at least removed for some the 6 figure licensing costs from the equation. ETL, like programming in general, is hard, so get over it. Tools, basic knowledge of SQL and data modelling skills can help to make ETL approachable to non-IT types, but it still has the potential to make your head hurt.

So what’s a PowerPivot’r to do?

If your organisation already has a data warehouse in place you’re in luck as it’s quite likely a lot of the data you require will exist in the optimal PowerPivot import format, i.e. a star schema. You might be out of luck though, a significant percentage of DWs will not have used dimensional modeling and you could find yourself looking a complex OLTP like data model. In that case, and in the case of pulling the data directly from an operational system, you’re in the micro ETL business. Even if your IT infrastructure provides you with cleansed and understandable data, you’ll be faced with integrating external or shadow-IT data (probably one of the main reasons why PowerPivot appeals); again you’ll either need IT support or else you must learn how to do it yourself.

Long before the likes of PowerPivot appeared I regularly found myself  in need of a micro ETL toobox i.e. a set of tools that would enable me to quickly and cost-effectively prepare data for loading into some system or other. Nine out of ten times that system was an Excel PivotTable (the rest of the time it was usually a master data take-on task or some variation of systems’ commissioning). Although the consumers of my datasmithing services would most likely assume that I used Excel alone to perform these works of wonder, I usually had an Oracle database (along with its data loaders and superb PL/SQL language) as my secret ingredient. This combination of Excel and Oracle served me (and my clients) well, but it wasn’t the Oracle bit that gave me the edge; I could, and did, substitute SQL Server and MS Access for the SQL layer. The real trick was the combination of Excel’s flexibility/presentational strengths with SQL’s list handling power.

The problem with this approach was that the interface between the SQL engines & the spreadsheets often involved quite a number of manual steps, and the presence of database software (even MS Access) could not always be depended on. It took my discovery of SQLite to enabled me to finally combine the two worlds; xLite was born!

This combination of Excel, and an in-process SQL engine (provided by SQLite) with the added optional ability to call either VBA, JavaScript or Python scripts, has provided me with a hugely flexible and powerful micro ETL tool. Now, with the arrival of PowerPivot, I have both the micro ETL and micro BI tools to build cost-effective Excel-based decision support systems.

Being Excel based, means that the end result is delivered in a format that many business people are comfortable with; PowerPivot is designed very much with “civilian” datasmiths in mind. Likewise, xLite’s ability to use VBA, simple SQL and Excel formula to perform data transformations makes a large part (if not all) of the ETL process “civilian friendly”.

I’m not saying that everything I can do with xLite will be as easy for an non-IT datasmith; many datasources are either too difficult and/or extremely time consuming for end-users to navigate; but much of the business logic can be expressed in Excel terms with the highly technical or time-consuming tasks  handled by SQL or VBA/Python/JavaScript. xLite is not only for once-off transformations but can also be used to automate ETL, report generation and refresh tasks (including refreshing PowerPivot itself).

So, if you’re thinking about utilising PowerPivot, but need help in preparing your data and automating the tasks involved, perhaps we should talk.

→ Leave a comment

Posted in BI, ETL, PowerPivot, SQLite, VBA, data, excel, xLite

Tagged , ,

Power (Pivot) to the People

Posted on August 11, 2010 by gobansaor| Leave a comment

From a lot of the coverage of PowerPivot you could easily be under the impression that the product is aimed only at those with a reasonable understanding of traditional BI/Data Warehousing/RDBMS techniques. A lot of the people writing about it are from “Big BI”/”Big IT” backgrounds and tend to view the world from that perspective. I too could be accused of this bias with my previous post detailing the benefits of first creating a star schema before loading data into PowerPivot. For those of you from a business or pure spreadsheet background don’t be put off by this coverage; PowerPivot is, like its PivotTable ancestor, a tool accessible to all.

Although a star schema design is necessary to get the most from PowerPivot, it can also add value to large datasets comprised of a single flat table or a table with one or two “vlookup” tables. In fact, the PowerPivot equivalent of ‘vlookup’ is much easier and far more powerful than the Excel original. Such lookups not only replace vlookup but also SQL joins. Unlike SQL joins, Powerpivot joins are case-insensitive (so “PowerPivot” = “Powerpivot”, for example) and trailing spaces are also ignored, so much more forgiving of “bad data”.  The ever-tricky case of SQL outer-joins (allowing for blank lookup codes or the code not existing on the lookup table) is also taken care of via the “Unknown Member”.

The new DAX formula language might be off-putting to many (in particular DAX used in the creation of measures, its most powerful feature). But again, a huge amount can be achieved using the automatically generated measures (near replicas of those available in a normal pivot).

So if you have a data source that is too large to load into a spreadsheet, or perhaps up until now your data has had to be summarised or SQL joined by IT prior to loading, then PowerPivot is well worth investigating for its heavy-lifting capabilities alone.

Even without very large datasets to play with, PowerPivot can be useful as a micro-ETL (Extract, Transform & Load) tool. It’s very easy to load data from multiple databases and other sources and by learning a few simply DAX formulas it’s possible to transform the data to whatever formats required. The resulting table can then either be cut and pasted into an Excel sheet for pivoting/filtering or pivoted using PowerPivot. The main advantage of pivoting such data using a PivotTable rather than PowerPivot is the ability to show-detail (a big missing in the case of PowerPivot).

To further explore the power of this revolutionary tool, I’d recommend Bill Jelen’s (MrExcel) PowerPivot For The Data Analyst book. Aimed squarely at Excel power-users (don’t think it mentioned a star-schema once, and only dealt with SharePoint deployment in the last chapter) this is a much better choice that the IT focused (and MS Marketing driven) Professional Microsoft PowerPivot for Excel and SharePoint.

Another plus of Bill’s book, besides learning PowerPivot, is that you might learn something new about normal PTs, for example look out for the  ”un-crosstab a crosstab” trick using a consolidated range pivot. The  Professional book also offers a behind the scenes look at the development of PowerPivot, including the gem that initially is was envisaged as an MS Access tool!

PowerPivot to the people, viva la Revolución!

→ Leave a comment

Posted in ETL, PowerPivot, excel

Star Schemas – to boldly go where no Excel spreadsheet has gone before

Posted on July 9, 2010 by gobansaor| 7 Comments

One of the many things that delights me about PowerPivot is the central role played by the Star Schema. Those of you reading with a data-warehousing background would shrug your shoulders and say: “So what, what else would you expect to find at the core of a BI tool?”.

Those from an Excel PivotTable background would ask: “What’s a Star Schema, why do we need one,what’s wrong with a the good old-fashioned single flattened table?”.

Those from a classic MOLAP background (Essbase, TM1, Palo) might also ask: “Why do we need this extra layer? Load the cube directly from the operational data model and get on with it!”.

A quick Q&A is perhaps the best way for me to explain why star schema design is a powerful skill in a datasmith’s toolset.

First off, what’s a Star Schema?

A Star Schema (also know as the dimensional model) is a denormalised (flattened) data model used to simplify an operational (OLTP) data model to better accommodate reporting and what-if analysis.

At its simplest, it consists of a central fact table with links back to a “surrounding” set of dimensional tables, hence the star name. A variation is the snow-flake schema, where the dimensional tables are not fully denormalised (e.g. Product Category->Product->Fact instead of  Product->Fact).

The role of the fact table (besides being the table that hosts most of the measure fields) is to create linkages between dimensions (such as Customer, Product, Date) usually based on an actual transactional event (e.g. Invoice) or a proposed event (such as a Budget or Forecasted Sale). In effect, simplifying  the often complex work-flow-driving connections of a typical operational system by using a single many-to-many relationship (modern ERP/CRM systems’ data models consist of scores of configurable many-to-many relationships).

Many wrongly believe the star-schema was adopted for performance reasons and now that in-memory OLAP is becoming the norm it’s no longer necessary to use dimensional modelling techniques. In fact, in the early days of data-warehousing, RDBMs had great difficulty efficiently handling star-queries (and some such as MySQL and SQLite, still do).

The original primary purpose of the star schema was to simplify the SQL required to access reporting data; to make the model more approachable to non-technical users. Of course, even simple SQL was beyond the knowledge or interest of most end-users but a sizeable proportion were happy to do so (often helped by SQL “generators” such as MicroStratery or Business Objects). But even in situations where SQL-wielding civilians were not to be found, the simplicity of the dimensional models  proved to be a valuable aid when establishing and developing the warehouse data requirements. PowerPivot requires no SQL knowledge to manipulate the dimensional model which brings the original concept full-circle but this time opening its possibilities to a much wider audience.

But surely, concentrating on the actual reports would be a more valuable requirements gathering exercise?

A so called “bottom-up approach” is often the best way to approach a reporting request particularly if the reports are simple one-off “traditional” reports. But for self-service BI, this needs to be combined with a top-down dimensional design. The idea is not to build out each and every report or indeed cube but to build a structure that’ll support likely queries. The process of building a star schema provides both a logical model and a physical implementation of that model against which potential queries can be tested. I’ve worked on several POCs destined for implementation in Essbase where the star-schema was built and potential cubes mocked up using Excel PivotTables that subsequently never went any further (except for the star-schema ETL process). The end-users derived sufficient value from the denormalised star-schema pivoted and reported in Excel.

In traditional ROLAP data-warehouses where the cubes were built directly against star-schemas, the pure logical approach to the data model often had to take a back-seat to the necessity of fine-tuning it to make response times (be that ETL or user-pivoting) acceptable. This is why I much preferred situations where the star acted as a logical model from which MOLAP cubes were built.

With PowerPivot, ROLAP has a new champion. The column-oriented high-compression in-memory architecture means that the compromises of the past are no longer necessary. The fact table reverts back to it primary role as a many-to-many connector. In a pure hypercube, measures are just another dimension (the approach that Palo takes), this is also now true of PowerPivot models; measures can be sourced from dimension tables and dimensions from fact tables as it logically should be, but without the performance hit of old.

But what’s the advantage of a star schema over a flattened table when using PowerPivot?

It is true that the same flattened table model as used to backend a PivotTable can be used within PowerPivot. But doing so would limit the potential of the DAX language to construct measures such as average sales spread over potential customers (rather than actual customers that would typically be represented on a flattened table). Also, by creating “conformed dimensions” (single cross-business views of Customer, Product etc.) and using such tables as dimensional sources for multiple fact tables, “virtual cubes” that combine values from multiple fact tables can be built.

If you’re new to dimensional modelling I’d recommend the books & articles of Ralph Kimbal as good starting point. You do have to be aware that some of the advice regarding efficiency trade-offs, surrogate keys etc. do not  apply in a PowerPivot scenario (even though other performance issues still apply) but the logical design tips still apply.

Star Schemas: to explore strange new conformed dimensions, to seek out new measures, to boldly go where no Excel spreadsheet has gone before.

→ 7 Comments

Posted in ETL, Palo, PowerPivot, excel, olap

Tagged

SQLite XML Streaming Virtual Table via Expat

Posted on June 16, 2010 by gobansaor| Leave a comment

A few weeks ago I wrote a post on handling large XML datasets using MS’s SAX2 parser from within Excel. Although fast, the SAX2 parser is not as fast as the original of the species, the Expat Streaming XML parser; being written in C and with a minimalist approach Expat’s speed is to be expected and this speed can be a real time saver.

Being at heart a SQL Jockey, I also like to transfer XML data structures to relational ones at the earliest opportunity, especially when I’m in discovery mode. Small XML pretty-indented documents can be analysed by eye (wasn’t this the great selling-point, XML’s user-friendliness ;-) ), but when the dataset is large,ugly and deeply nested I prefer to  load the data into a classic parent-child table such as this…

static const char* DDL = "create table FOO("
"fileName HIDDEN text,"
"xmlString HIDDEN text,"
"filter HIDDEN text,"
"deParent text,"
"deChild text,"
"deLevel int,"
"isAttribute text,"
"ChildType text,"
"LineNo HIDDEN int,"
"Offset HIDDEN int,"
"ItemNo HIDDEN int,"
"parentRowid int"
")";

As I found myself doing this repeatability I decided to code a SQLite Virtual Table which by wrapping the Expat parser would stream out the parsed data  to populate such tables. The streaming data could either be consumed as it arrived or saved to a real (i.e. non-virtual) table for further analysis. In effect, creating a ‘relational DOM’, but unlike a DOM which is memory bound, it could exist as an in-memory table or as a disk-based one, making the handling and analysis of large datasets easier to achieve. The above snippet of code is the definition of the my VT_xmlPC virtual table. HIDDEN columns are a means of adding utility columns that don’t necessarily belong to the logical dataset, in this case fileName and xmlString are used to identify the XML to load.

If you’re interested in writing your own SQLite virtual tables this Dr. Dobb’s article Query Anything with SQLite offers a good introduction to the subject. Wrapping Expat adds some extra complexity as you have to deal with two sets of callbacks, SQLite’s row request events and Expat’s events, and also with nested Expat parsers; so if you’re new to virtual tables start with the Dr Dobb’s example before diving into the xmlPC.c code.

The resulting virtual table is capable of handling most XML ‘data documents’ as seen in the wild; as coded, it regards attributes as the equivalent of nested elements (with isAttribute = ‘Y’), and it doesn’t handle CDATA (but could easily be modified to so do).

To use it:

(a) load the xmlPC.dll extension

(b) create virtual table myTableName using VT_xmlPC

(c) select * from myTableName where fileName matches “C:\some xml file” or select * from myTableName where xmlString matches ” …”

The “matches” predicates filter prior to row creation, “=”s etc. filter after row is constructed. The “fileName”, “xmlString” and “filter” columns are “matches” only columns as they initiate the creation of the table (using “matches” rather than “=”s for such utility columns is my own convention not a requirement of SQlite virtual tables). The “filter” column will restrict loading to elements of that name and the children nodes of that element.

Find here:

Have fun.

Update:

The source code link on the Dr. Dobbs article appears to be broken, the code can be found here (Note: this is an FTP link) in the 0711.zip file.

→ Leave a comment

Posted in ETL, SQLite

Tagged , , ,

JavaScript as an Excel scripting language via JSDB

Posted on June 4, 2010 by gobansaor| 5 Comments

A few years back I posted about JavaScript as an Excel scripting language via ExcelDNA. That involved using JavaScript (in the guise of JScript.NET) as an ExcelDNA scripting language. It was purely an academic exercise to prove it could be done, I continued to use C# (or increasingly VB.NET) to build .NET user defined functions. This time however, I’ve managed to embed JavaScript (in the guise of Mozilla Foundation’s SpiderMonkey) directly via a native C interface not to prove I could do it (even though there’s a definite satisfaction in simply doing it) but to use it.

Why add another scripting language to xLite, hasn’t it already got Python?

True, Python is and remains a very powerful add-on to xLite. It’s a mature and long-established language, popular amongst IT professionals and “citizen programmers” alike. But, it’s a bit of a monster and can be awkward to package, particularly on Windows. By using Py2Exe and after a lot of digging on the issue of manifest files I have managed to package and isolate xLite’s Pythonic bits so that it can be used on a PC without first installing the required Python version (I’ve only tested against V2.6, Python’s lack of a side-by-side Windows installation capability is a major pain-in-the-butt   bad news: tested against Python 2.5 & it doesn’t work; good news: side-by-side is possible; simply change the system path to reflect which-ever version you wish to run at the command line; xLite will (must) continue to use V2.6). This “version-hell” mitigates against using Python as a core-element of xLite, fine for those of us who are comfortable with and require the full power of Python, but not as the tool’s primary scripting environment.

No, what I need is:

  • a light (single EXE or DLL preferably) and an approachable popular language,
  • with native SQLite support,
  • runable as standalone executable (on both Windows & Linux),
  • embeddable (is that a word?) in Excel via VBA-friendly DLL.

Add to that essential list some nice-to-haves such as:

  • native COM-interface support (for the likes of ADO etc.),
  • native networking support, for HTTP, raw TCP sockets etc.,
  • native (and easy to use) XML and JSON parsers and emitters,
  • ability to spawn detached/attached command line processes and the ability to stream data to and from such processes; allowing me to easily orchestrate & provide a “grid” of processes (scripted, command line executables, Excel instances) both local and remote (with remote being either traditional servers, http servers or Hadoop Streaming grids).

While looking at the various implementations of Javascipt as a server/shell language, I came across JSDB – JavaScript for Databases. It’s a C++ wrapper around Mozilla’s SpiderMonkey, with lots of useful data related utility classes added. To make JSDB a perfect fit it simply required:

  • a few minor changes to the SQlite class (allow the loading of Virtual Table extensions, add the ability to pass in the address of already open SQLite memory structures);
  • a linker change to use the DLL version of SQLite;
  • plus a VBA-friendly DLL wrapper ( & VBA declares, to call the DLL) to replace the JSDB shell when embedding in Excel.

From my SQLite as the MP3 of Data post:  ”Just as “fractional horsepower” electrical motors revolutionised manufacturing and eventually all our lives (car starter-motors, fridge motors, washing machines etc.), “fractional horsepower” databases can do the same for data. Distributing data to where it is needed.” I can now add a distributed “fractional horsepower” processing engine for that distributed data. This transforms xLite from a micro-ETL platform into one capable of handling (or at least orchestrating) practically any ETL (Extract, Transform & Load),DI (data integration) or “Time Asset” (see this post) process.

→ 5 Comments

Posted in BI, ETL, JavaScript, Python, SQLite, VBA, data, excel, xLite

SAX and Bugs and XBRuLe

Posted on May 6, 2010 by gobansaor| 1 Comment

Okay, the XBRuLe is a bit laboured, should be SAX & bugs & XBRL, but any excuse to play some Ian Dury :-)

Bugs (the programming type, not the creepy-crawlies), Simple API for XML and Extended Business Reporting Language;  these represented the trinity of my concerns for the last three weeks  or so.

First, the bugs:

Several weeks back, I decided that the C portion of xLite needed an overhaul. The codebase contained a lot of stuff that I no longer used and also contained code that I’d written when I first re-started using C (after a lapse of 20 years or so, the phrase “I’d forgotten more than I’d ever known” sums up the experience best); some of this code was memory-leaking like a sieve.

Also, the original Pivotal Solutions code was not UTF-8 enabled, instead it used the host pc’s default character set codepage, this needed to change (if you don’t know what I’m talking about see Joel Spolsky’s lecture to the developers of the world – well actually, primarily to those of us in the Anglo-Saxon “ascii-will-do-fine” world).

The bugs I introduced as a result of this upgrade were not of the logical kind but of a much nastier type, peculiar to the low-level world of C, “bad free()” bugs!

Excel was no longer leaking memory (well no more than it normally does) but it was crashing randomly (usually in a DDL called VB7, 1st upgrade to classic VB in over a decade!), a sure sign I was freeing memory that was not mine to free. Two days later, I’d tracked the bugs down, but only by a painful line by line code walk-through. If you’ve no idea what I’m talking about here, count your blessings and move on.

The other major change I added to xLite is the ability to code SQLite Virtual Tables in VBA. The Python side of xLite has always had that facility, but I look on Python as a nice-to-have add-on, not as a core component. The growing need for “core” virtual tables meant either coding them in C or in VBA, see previous paragraphs for why VBA won the day.

The immediate driver for adding both UTF-8 support and quick-to-build virtual tables was the need to better handle XML data within xLite.

… then the SAX:

For small XML/HTML datasets, I, like the rest of the world, use DOM manipulation; but for larger sets I’ve tended to go down the brute force and ignorance approach of hand coded File I/O combined with regular expressions to efficiently parse out the data required.

Last week, an email from a datasmith name Cathy prompted me to look into using Sax for loading XML. Cathy, like most datasmiths, is not a professional programmer, she has a “real job”; part of that job is analysing large datasets, she’s learned enough programming (mainly Access & Excel) to do that job more efficiently. The data she needed to parse this time was encoded in XML, but being very large and built on a schema that constantly changed, the default DOM approach overpowered both Access and Excel.

Cathy had originally contacted me looking for information on using Talend to read the data and it looked like she was about to start a new side-career as a Java programmer. I figured there must be a way for her to leverage her existing skill-set (VBA) & this led me to MSXML’s implementation of SAX2. She was delighted; although many of the concepts would have been new to her, at least they were bounded within a world she was already comfortable with (the basis of how we all manage to incrementally expand our knowledge).

The only problem was, the example code no longer existed (it would have been in VB6, but who uses that these days, other than a few million VBA para-programmers, let them eat the .NET cake). So I coded up the first example in Excel/VBA and here it is if you need a quick-start to the joys of SAX2.

Which leads me on to XBRuLe:

“One XML to rule them all, One XML to find them, One XML to bring them all and in the darkness bind them…”

A former colleague of mine when explaining his computer science studies to Meath farmers whom he regularly met while hitch-hiking home from college (mid-1970s) was usually met with the response: “Ah computers, dere de comin‘ t’ing“. XBRL has been the coming thing for quite a while now.

Being in the business of Business Reporting, XBRL has always been on the radar, and of late, the radar is showing incoming fire. First the SEC, and now the UK’s HMRC, are mandating it as a filing method. Whether this is a good thing or not, is open to question. As this article puts it: “XBRL is a  case study in complexity” http://bit.ly/951oEY “the producer of the sample must have suffered a polymorphic recursive brain meltdown”.

But needs be; I’m in the business of shaping difficult data, so I’ve started to re-acquaint myself with the subject (last time I looked at XBRL in any depth was 2004). Part of that process will be to beef-up xLite’s XML capability, which, with me being on the table side of the “Tables Vs. XML; the data lingua franca debate“, will involve getting the data into a relational form at the earliest possible moment. For example, for discovery I would use a classic parent-child recursive structure, but not having something like Oracle’s Start-with, adding  a virtual table to make navigating such hierarchies easier with SQLite.

If anybody is peddling to you the concept that this brave new word of XBRL powered reporting will make your business reporting life easier, they’re either lying or don’t fully understand what they’re selling. As 19th century industrialists were wont to say: “Where there’s muck, there’s brass”; and with XBRL, you’ll be up to your oxters in muck, but with the brass all flowing to others, perhaps even to me!


→ 1 Comment

Posted in BI, ETL, Python, SQLite, VBA, excel, xLite

Tagged , , ,

Excel as a document-oriented NoSQL database

Posted on March 2, 2010 by gobansaor| 1 Comment

I’ve been a long time fan of CouchDB, one of the many NoSQL databases to appear in the last few years. CouchDB is a document-oriented database, which with solid B-tree indexing and easy replication, topped off by a MapReduce style view mechanism, puts it up there as a best-of-breed noSQL datastore.

Now it may seem strange that somebody whose SQL – does exactly what it says on the tin post clearly marks him out as an RDBMS fanboy, can also sing the praises of a noSQL database. Are they not mutually exclusive? To many, particularly in the noSQL world, this appears to be the case, with some clearly determined to re-invent the wheel, ignoring the lessons learned by relational database practitioners.

The main advantage to me of document-oriented databases, such as CouchDB, is the ease of setup and subsequent pain-free evolution of data models that comes with a schema-less database. The main disadvantage is the relative rigidity of downstream analysis built into most such databases. MapReduce, such as used by CouchDB, is fine for predefined views developed by programmers, but as we know, reporting never stops; datastores front-ended by a SQL interpreter open up the data within to a much wider audience (be that through hand-crafted SQL queries or more likley via reporting-tool generated SQL)

Of course document-oriented, noSQL, schema-less datastores have been all the rage with end-users for close on 30 years. They’re called spreadsheets. Excel has over the years added features (such as list handing & filtering) that have made the spreadsheet the database of choice for millions. Anybody who deals in corporate data is aware (sometimes painfully aware) of just how much data is stored in these Data Populi repositories.

I, as an IT professional, am aware that Excel workbooks as books-of-record, have been, and continue to be, the cause of many data quality problems. Yet, I’ve also seen, and am myself responsible for, many successful Excel ‘database implementations’. Take for example, my filing system.

I don’t have a filing cabinet, instead I use small stackable cardboard boxes to store documents. As I receive or generate documents I simply place them in the current open box. Every so often, usually prompted by a VAT or other tax return deadline looming, I record what’s in the box, and if the box is looking full or maybe it’s end-of-year, I’ll ‘close’ the box and open a new one.

Each box is represented by a separate workbook, each document by a separate worksheet. Some documents such as electronic Sales Invoices may not require a physical copy simply a link to a PDF, but I still tend to store a printed copy. Others, such as Purchase Invoices, have their details manually copied from the original paper based document, I usually also add a hyperlink to an image of the source document. (I no longer use my scanner, instead I use my phone camera to record paper documents).

Bank reconciliation involves recording the bank item ref against the appropriate document and linking back to the Bank Statement worksheet  (which as I still receive paper-statements consists simply of a link to a photo of the statement and basic info such as date of statement and whether or not I’ve reconciled it).

VAT Return documents are generated using links back to source documents and a link to an image of the completed paper return (not yet signed up for ROS). Similar documents are generated for year-end tax returns & accounts.

So my ‘filing system’ is also my ‘accounts system’. This is common practice amongst small (and not so small) businesses. The advantage of this approach rather than using a “proper accounts system”  is the simplicity and the in-depth knowledge it forces me to have of ‘my data’.

But can this type of thing scale, and what of the businesses that are using similar systems to manage thousands or indeed 10s of thousands of documents or transactions? The simple answer is no,  at least not without a semi-automated process and a cost-effective means of analysing the data; many such systems are on the road to disaster. That disaster may take the form of data quality issues or the significant (and often hidden) cost of operating such systems (often the operators are highly paid accounting staff or managers whose cost is buried in general overhead costs, unlike internal or external IT resources whose time tends to be project allocated).

But again, I and others, have managed to setup systems such as these that were  cost-effective (not just in initial construction but in ongoing running costs) and managed to maintain data quality. This usually involved building a simple work-flow process, automating to some degree but keeping the human touch as much as possible. My xLite datasmithing platform had its beginnings in such RSS (Really Simple Systems) scenarios. Many such “systems” were IT driven ETL processes or data cleansing initiatives, others, business initiatives such as sales planning/budgeting or customer surveys.

I haven’t used xLite to automate my filing system (my transactional volumes are too low and my motto when it comes to systems is, “good enough” will do), instead, relying on standard spreadsheet formulas and few bits of VBA, but if I suddenly found myself at the business end of a fire-hose of documents I could easily do so.

Much like CouchDB, I could  create ‘map’ views of my documents, but instead of MapReduce Javascript code, I’d  load the documents into SQLite tables (using a duck typing approach; if the document had the required data, e,g, Invoice No, etc. for Sales Invoices, load, otherwise ignore). The ‘reduce’ part would then be standard SUM(), Group By SQL statements.

I could also mine the documents for text and then use SQLite’s FTS full-text searching to create a free-format search index or use xLite’s TAG Cube functionality for a more formal, hierarchy supporting, tagging index.

If I needed to share the system with others in my organisation I could use a light simple distributed version control system such the SQLite based  Fossil. This would allow for many of the replication benefits that CouchDB offers.

In fact, if I wanted to backend the system with a server based database I could call in the services of CouchDB itself. Easily done as xLite has inbuilt Python support and the library that xLite uses to interact with SQLite on the Python side is APSW. And guess what, APSW now includes a virtual table implementation that lets you access CouchDB databases from SQLite. Excel as a front-end to CouchDB!

If the ‘it does exactly what it says on the tin‘  Ronseal catch-phrase epitomises SQL  then perhaps ‘ Simples‘ as Alexandr the Meerkat might say epitomises the potential of document-based databases.

Why not join me on Twitter at gobansaor?

→ 1 Comment

Posted in ETL, Python, SQLite, VBA, data, excel, xLite

Tagged , ,

Excel 2010 Application.Caller Bug

Posted on February 11, 2010 by gobansaor| 14 Comments

I’ve just released another xLite “introduction”, this time the xLiteWorkbookFunction function. I’ve had most of the now released functionality working (and in use) for quite a while but had delayed publishing until I’d installed Excel 2010 as I’d wished to test against a modern Excel version.

I’d not bothered with Excel 2007, as I couldn’t see the advantage over Excel 2003, but Excel’s new PowerPivot is one hell of a reason to upgrade to 2010. I’d preformed a quick test against 2007 by installing a trial version on an EC2 Window’s image and it had appeared to work fine; but it was a different story under 2010,strange things started to happen.

The core functionality, as tested by VBA code, worked OK but when I tested using xLite.SQL as a UDF (a user defined “formula”) things fell apart. For an explanation of what the xLite.SQL function is and why I wasn’t that surprised when it started to act up, see here. As xLite.SQL plays to the rules rather than the spirit of a UDF, I assumed it was pay back time  for my blasé ignoring of functional programming constraints and I set about tracking down the cause.

It turns out the cause is a change in behaviour (a bug) whereby in certain circumstances the cell range returned by Application.Caller is not, as one would expect, the cell hosting the called UDF but that cell more usually associated with Application.ActiveCell (i.e. most likely the cell where the cursor currently resides).

Why is this a problem and what is Application.Caller usually used for? The most common use I’ve made of Application.Caller is to determine whether a VBA function had been called from a cell as a UDF or from a menu, button or some VBA code. This is important because when called in UDF mode, a function must be side-effect free, i.e. its only affect on the workbook is the return value; attempting anything else will silently fail (or in extreme cases, abort Excel). This functionality is not affected by “the bug” as the usual method of achieving this is by …

If IsObject(Application.Caller) Then

… this will work even if Application.Caller returns .ActiveCell, as both are objects.

If however, various properties of that range need to be interrogated (such as the actual address or the formula text that xLite.SQL requires) then Application.Caller in certain circumstances returning the .ActiceCell range rather than the calling cell’s range causes problems. I’ve managed to get around these problems by adding an extra parameter (homeCell) to xLite.SQL which the function will auto populate on first entry (when Application.Caller and .ActiveCell are guaranteed to be the same object). For example, a SQL call entered in cell A2 of Sheet1 as ..

=SQL(“Select name from sqlite_master”)

is automatically rewritten as …

=SQL(“Select name from sqlite_master”,,,,,,”[test.xls]Sheet1!$A$2″)

Not ideal but it gets around the problem in the short term.  Long term I may do a version for sub-2010 reverting back to original dependence on Application.Caller. As xLite studiously avoids Excel’s UI features such as menus/ribbons I’d hope to avoid different versions for pre/post ribbon editions, but needs must.

The xLiteWorkbook example (test_call_workbook_function.xls calling test_workbook_function.xls) consistently generates the bug (there’s a SQL call against the log database at the end to test for this). Executing the same logic manually on the called workbook (test_workbook_function.xls) generally doesn’t, but it has done so occasionally!

I’ve messed around changing the .xls files to .xlsm in case of compatibility problems but it doesn’t appear to affect the outcome.

If anybody else has come across this problem or has an alternative to using Application.Caller to return a UDF’s calling cell’s range, do let me know.

UPDATE:

Peter Hoadley suggests a workaround by using Application.ThisCell, see comments below …

UPDATE: March 19th 2010

Danny Khen from Microsoft has emailed me to say …
“This is a known Beta bug which has since been fixed. So you should expect it to go away when Office 2010 ships“

Why not join me on Twitter at gobansaor?

→ 14 Comments

Posted in ETL, SQLite, VBA, excel, xLite

Tagged , ,

xLite Beta Updated – adds Python as an Excel Scripting Language

Posted on February 7, 2010 by gobansaor| Leave a comment

I’ve updated the xLite Beta with bug fixes and added a new page introducing xLite’s Excel/VBA and Python extensions to SQLite.

See http://www.gobansaor.com/xlite

The u() function allows any VBA UDF (user defined functions) to be called from SQLite.

The x() function allows an inbuilt function or indeed most any formula (but not a UDF, use u() instead) to be called from SQLite.

The f() function allows for standard worksheet cascading formulas to be referenced by SQL, in effect, worksheet user defined functions.  Really useful in building & testing workbook code/models.

Finally, xLitePyScript is a UDF that allows Python to be used as an Excel scripting language.  Can either be inserted into a SQL statement wrapped by the u() function or called like a regular function from VBA or as a cell formula.

Have fun …

→ Leave a comment

Posted in BI, ETL, Python, excel, xLite

TAG Cubes – SQLite Star Query Part III

Posted on September 29, 2009 by gobansaor| 9 Comments

It’s no secret that I’m a huge fan of SQLite and Excel, particularly when used in combination. I also greatly admire the open source BI engines, Palo and Mondrian. Mondrian appeals because of its “ROLAP with a cache” architecture and its implementation of MS’s excellent MDX language. When I say MDX is excellent I’m talking with my professional programmer’s hat on, as an end-user tool it’s a non-runner. This is where Palo comes in, building on the hypercube concepts pioneered by the likes of  TM1 and ESSbase, it presents a designview that’s approachable by a vastly greater percentage of “civilians” than is the case with ROLAP-based solutions.

The trick behind TM1, Essbase, Palo etc. is the extension of the spreadsheet metaphor from two to multiple dimensions, while still binding the interface closely to the familiar spreadsheet (which for most of the business world is still Excel).

So where does SQLite come in all this?

At first glance, SQLite lacks the sophisticated join functionality to support star-queries, but of course, if the dataset is small then a full-table scan of a fact table, or better still, loading the fact table into memory negates any such short-comings.

In fact, all traditional ROLAP engines have problems with dimensional models, particularly when you reach the point of using summary tables or query re-writes, that’s why the emerging SQL-speaking columnar-databases are such a godsend for ROLAP data warehouses.

It was SQLite combined with Excel acting as a data prep platform that was originally my main interest, so for pivoting, Excel’s own pivot table would have to do.  Nevertheless, I felt the tool was incomplete without the ability to directly pivot the underlying SQLite database.

Why not use Palo or Mondrian as a pivot tool? Well yes, where a fixed permanent “solution” is required then the extra moving parts of either approach would be justified and indeed necessary but that is to miss the essence of what I call datasmithing.

Datasmithing is not data warehousing nor is it the provision of solutions (which, for example, Palo superbly enables in multi-user budgeting situations). Datasmithing, as a skill, is of course part of the process of both, but it’s on the edges, at perhaps the planning or consumption stages.

Datasmiths deal in the unknown, in change, in disaster recovery, in systems’ commissioning, in the never-ending barely-repeatable processes thrown up by daily business life.  For that, the toolset required must be as simple as possible (but no simpler), self-contained, document-oriented, secureable (is that a word?) and easily archived and retrieved. Excel and file-based DBMSs such as MSAccess or SQLite fit the bill nicely, server-based technologies such as DBA controlled database servers or IT installed “solutions”, less so.

Jedox has made Palo relatively easy to install (and likewise, Canada’s SQLPower has made Mondrian setup a painless exercise via their excellent Wabit reporting tool), but, the zero-install, email friendly document approach that spreadsheets are famous (and infamous) for, is preferable in many situations. This is something that Microsoft have recognised in their Gemini add-in for Excel 2010, but Excel 2010 is a not here yet and it’s likely to be five years or more before it’s as common as Excel 2003 is today.

The inclusion of FTS full-text searching with SQLite triggered an ah-ah moment with regards to pivot-enabling SQLite.

The usual method that hypercube-like excel-friendly OLAP tools use to return data is via a UDF like so…

=DATA(“CubeName”,”value1″,value2″,…)

…where valueN represents dimensional elements, so…

=DATA(“SalesCube”,”Beer”,”Profit”,”Jan 09″,”Actual”)

…is the Actual Profit for Beer sales in Jan 09. The dimensional elements act as “tags” to locate a particular value, there is of course much more to tools like Palo; hierarchies, intra-cube rules etc. but in essence most OLAP tools are like www.delicious.com for number crunchers. This method of retrieving data fits well with how people use Excel and not just for pivots, but for embedding OLAP aggregated cells in lists.  For example, a CRM scenario; a Sales Rep makes a list of her ‘best’ (subjective) customers, but needs hard (objective) stats, to be placed alongside the list to convince the boss or to track actuals against expectation.

Dimensional elements as tags; FTS3 virtual tables as fact table indexes; the concept of a TAG Cube was born.

In the above example “Profit” would most likely be described as a measure (Palo, a near pure hypercube does not distinguish between Measure and other Dimensional coordinates). Dimensions, measures and attributes are in reality interchangeable (a Customer ID can act as a dimension or an attribute, but by applying a  Count Distinct to it, it’s a measure) but most OLAP solutions treat “Measure Dimensions” as different, and so do TAG Cubes.

By using the default fact table structure (a single-columned table) and querying using the default measure (which translates to the SUM() of that single value) a ‘pure’ approach can be used. But, ROLAP is tightly bound to the concept of a fact table, and since SQLite is relational, TAG Cubes offer the ability to use a wide fact table approach and I think gains considerably in flexibility by so going.

The above example of using Count Distinct, or the simple creation of calculated measures are examples of this flexibility. Another, is a measure based on SQLite’s concat_group aggregate function to provide a drill-down facility, e.g.

=DATA(“SalesCUBE”,”ROWIDList,”Beer”,”Jan 09″,”Actual”)

…where “ROWIDList” would be setup as concat_group(rowid,’,') and will return a comma separated list of the underlying fact table ROWIDs.

A major reason for rolling my own pivot engine was to add a concept of “namespaces” and to separate the implementation of these namespaces from the actual pivot.  When a tag (or a predefined hierarchy of tags) is assigned to a cube, it’s also assigned to a namespace, in many cases namespace and cube would be synonymous, but in some cases a more sophisticated approach is required:

  • Multiple cubes sharing the same set of conformed dimensions would be best served by such cubes sharing a common namespace, and so they can.
  • Different consumers of the pivot may require the use of a different language, be that a spoken language or a different ‘business language’ e.g. Manufacturing Product Codes V Consumer Product Names. Again, easily done.
  • Sometimes identifying data can’t be shared with the datasmith or the numerical analyst working on a problem; in such cases being able to replace  the actual namespace with an obfuscated one can be very useful. Or, for added security, the namespace might only be issued to approved  PCs while the tag index and fact table are stored on a shared drive.  Needs some more work to make managing such scenarios secure and easy to use but the structure is there.

As hinted on above, the three elements of a Tag Cube, the namespace, tag index and fact table can be assigned to different databases (i.e. files). Due to the wonders of SQLite’s ATTACH statement and the backup API’s ability to quickly load/unload databases in/out of memory, it’s possible, for example, to load namespace and tag index (i.e. the ‘dimensions’) into a memory database, while a very large (i.e. too big to fit to memory) fact table remains on disk. Fast and cheap SSDs will add further configuration options.

Although most of the TAG Cube functionality is available only within Excel, I’ve built a C based SQLite Virtual Table (cFact) to allow the tag index to used outside xLite. This means that SQLite drivers for ODBC (for use as a Pivot Table source, for example) or JDBC (for use in  SQLPower Wabit perhaps) can efficiently access data models built using xLite.

I had to revert to using C rather than my preferred Python (did I mention that xLite now embeds Python in Excel, no, well it does, Python the newVBA ?), having failed to get around multi-threading issues with callbacks to Python in both the ODBC and JDBC drivers. I’d make a career promise to myself many years ago, not to having anything to do with printers or threads, and I think I’ll stick with it :)

TAG Cubes are the latest addition (still WIP actually) to be added to xLite, adding to:

  • VBA coded SQLite SQL functions.
  • Worksheet Functions; call out to a ‘function’ built using Excel formula, passing a parameter list and returning a value.
  • Workbook Functions; like Worksheet Functions, but loading a new Workbook, passing in parameters, passing back a value (or tables) and closing the Workbook when finished.
  • XLiteScript; xLite exposes its functionality via VBA coded UDFs, which can be called like any other formula, but data prep activities often require sequential procedural logic, xLiteScript is a table-oriented scripting mechanism offering basic flow-control logic.
  • pyScript; I embedded Python into xLite to take advantage of Python’s speed in developing Virtual Tables, SQL Functions and extensions to SQLite and to tap in the wonderful world of Python code. I’ve also added the ability to use Python from scripts defined within Excel (to indent, tab to the next cell!).
  • Fast load/unload to/from CSV.
  • Load from any ADO source.
  • Remove xLite formulae and rename and save Workbook, very handy when used via Workbook Functions to mass produce Excel “reports”.
  • Other WIP items are; load from SAP, load/unload to/from Amazon S3, use Palo cubes as TAG Cube “facts”, slot in/out Palo for TAG Cubes, auto-generate Mondrian XML based on TAG Cubes, write-back and splash, Python & VBA TAG Cube “rules”.

I’ve started the process of releasing the beta code here …

Why not join me on Twitter at gobansaor?

→ 9 Comments

Posted in ETL, Palo, SQLite, VBA, excel, olap, xLite

Tagged , ,

LiteBI, Heavy ETL

Posted on April 24, 2009 by gobansaor| 2 Comments

Although my major BI interest is in micro-BI (or is that  workgroup-BI?)  i.e. data, perhaps cleansed and packaged elsewhere, available locally on a datasmith’s PC,with most likely an in-memory OLAP as the analysis tool; the possibilities of the “cloud” as a BI platform have not escaped me.

From a micro-BI perspective, the ability to act as a backup/mirroring tool or as ETL/marshaling tool (anybody for Hadoop and SQLite?) attracts. I’ve yet to make up my mind on BI delivered as a cloud PaaS but obviously many others believe it has a future.

My main worry with PaaS is not lock-in (which exists equally for in-house proprietary solutions) but the dangers of a Coghead-like lock-out.  My other doubts are more technical; believing, as I do, that in-memory offers significant advantages over traditional ROLAP (simplicity been the main one) and multi-tenant in-memory architectures are not yet a runner.  But last week I had a demo of new Spanish BI PaaS service, LiteBI, which might just change my mind.

Javier Giménez Aznar and his team previously worked on delivering Pentaho based datawarehouses to large Spanish corporations and government agencies, so they have a deep understanding of Mondrian ROLAP and are using that knowledge to build the LiteBI service, but this time with SMBs as the target customers rather than corporates. Pricing starts at €145 per month and is based on number of concurrent users, number of analytical spaces and the data volumes, so it’s not for very small firms more for the Medium in SMB.

Impressions? The cube designer, dashboard builders and the general UI are all very good and I would think would appeal to end-user datasmiths and, as such, will be a major up-front aid to selling this product.  But it was LiteBIs approach to the thorny issue of ETL and data loading that impressed me and also helped ease some of my Coghead-induced-fears.

BI technology stacks consist of three elements:

  • The “fancy” front-end; graphs,animated dashboads and so on.
  • The pivot engine; ROLAP or MOLAP or both.
  • The ETL process.
  • (Many would say there’s an important 4th, the data-warehouse, but not every BI effort requires one, but that’s another issue)

LiteBI is continuing to build yet more functionality into their UI and this “fancy” front-end is essential as it’s their “shop window”.

Mondrian provides their pivot engine, and again they continue to work on optimisations such as column-based datastores to increase speed and automate responsiveness tuning (end-users are very unforgiving of slow pivots).

But it’s in the 3rd area, that of the ETL process, that you realise the LiteBI team has real-world BI experience.  Data is loaded into LiteBI via an API, but with the ETL process itself happening on the customer side.

“Well,so what?” you may ask. The extraction of data has to obviously happen customer-side (even though not in the case of data being sourced from the likes of SalesForce.com). Yes, but it’s the transformations and data cleansing that adds true value to the ETL process and subsequently determines the quality and usefulness (as opposed to the speed or the “prettiness” of delivery) of the solution.

Part of the process of adopting LiteBI, is an ETL consultancy stage where a LiteBI partner company will provide on-site services to build this ETL layer, handling not just transformations but initial load and automating the subsequent delta uploads.

So the cost mounts up, but in reality you can’t do BI without this investment; there’s no ETL magic bullet.  Even still, Javier says the typical go-live time for a LiteBI project would be in the order of 3-4 weeks rather than the 3-4 months of similar on-site Pentaho projects.

The end-user ‘owning’ the ETL process makes the prospect of a service lock-out slightly less worrying as, at least, one would still have a good starting point for moving to another provider or back in-house. What I would really like to see would be the option to self-host LiteBI, which I guess would involve open sourcing large parts of the service (the automated optimisation strategies could, for example, be excluded from this open source version).

The load API comes packaged as a plugin to Kettle (aka PDI) and the intention is to offer a similar add-on for Talend in the near future. LiteBI also offers a white-label offering whereby 3rd party OLTP solution providers can use the service as their product’s BI suite.

Like the Skibbereen Eagle keeping its eye on the Czar of Russia, I too will be keeping a watchful eye on LiteBI and the march of on-demand BI in general.

Why not join me on Twitter at gobansaor?

→ 2 Comments

Posted in BI, ETL, Talend, cloud, data, kettle, olap

Tagged

PowerPivot – XXL, Excel on Steroids

Posted on April 1, 2009 by gobansaor| 12 Comments

In my last post about why I use SQLite in combination with Excel for datasmithing tasks, I listed the more traditional backends (Excel itself, MS Access, RDBMs & MOLAP cubes) that one would expect to “compete” with such an idea.   But I suspect that if that same post appeared  two years or so into the future, there would be a fifth contender, PowerPivot cubes.

PowerPivot (at the time it was called ProjectGemini) is due to be delivered as a free add-in to the next version of Excel (2010) ,like the Analysis ToolPak or the Data Mining add-ins for Excel 2003.  (See this OLAP Report Project Gemini, Microsoft’s Brillaint Trojan Horse for a good overview of the tool).

Donald Farmer ,who works on the project, having seen the SQLite as the MP3 of data post and recognising that the use cases behind combining SQLite with Excel were similar to those of Project Gemini, kindly offered me a demo of the product.  Well, the phrase “Excel on steroids” has been much used in the past (in particular of add-ins such as Essbase, Palo or TM1) but this “ya gotta see”, Donald likes to call it XXL.

Millions of rows of data in-memory on a 4GB PC being “modeled” using a “user-friendly” pivot-table-like interface. And when I say, modelled, the user isn’t being confronted with concepts such as dimensions, levels, attributes, facts and so on, but a classic star schema model is nevertheless being built behind the scenes.  And it’s this model that allows PowerPivot to escape some of the inadequacies of pivot tables, e.g. allowing for rules and hierarchies to be defined.  The resulting model can then be saved and shared as a file (keeping to the document-centric ethos of Excel) but it can also be posted to and managed by SharePoint.

SharePoint will be extended to allow the IT function to manage and audit shared models to whatever degree the organisation requires, but the single file format will also allow smaller groups to share without the need for IT involvement (essential if bottom-up adoption is to be encouraged).  SharePoint will also add the “Web2.0 collaboration layer”.

How will MS make money from this if it’s free?  The first clue is the SharePoint backend, more functionality means more reasons to purchase and use MS’s server stack and the same applies to Excel itself. I, like many others, are very happy using Excel 2003 and look on Excel 2007 the same way the market in general has looked on Vista; i.e. pretty, but lacking a strong enough reason to upgrade unless forced to do so. (Excel 2007 also has the ribbon issue, not one I find a major problem myself, but others do).  But I would upgrade to a version Excel that offered Project Gemini capabilities and I’m sure others would follow (and more importantly to MS’s revenues, thousands of corporate accounts would too).

PowerPivot offers proof that MS realises, what those of us on the ground have know for years, that BI projects are in the main, Excel-centric; all the ‘hard sums’  and awkward decisions end-up back on the desktop.  MS has decided to publicly recognise that fact and profit from it. The timing is both economically and technically opportune; PC speed and cheap memory means that a huge chunk of even a large corporation’s datasets can be analysed by a PC (according to this, the median size of original data in OLAP datasets is about 5GB); and there’s obvious cost-benefits for companies facing difficult times requiring more to be done with fewer resources.

What will the effect be on tools such as Essbase, TM1, Palo etc. ?  Well, let me put it this way, if their owners are making strategic plans for 2010 onwards and they’re not taking account of the PowerPivot effect perhaps they should.  Most likely PowerPivot will help increase the overall market for OLAP tools, with the incumbents tending to specialise in their existing niches (e.g. Palo in Budgeting, with the added value of being free and open source, which has a premium over just being ‘free’).

So will I put away my Excel-SQLite fixation then? No, for two reasons:

  • PowerPivot is not here yet, and the proof of the pudding will be in the eating. Also, when it does appear it will only apply to Excel 2010 (or whatever) and as many companies are still on Office 2000 (and a few on 97!), it’ll be at least  5 years before a significant percentage of sites upgrade.
  • The SQLite addition to Excel offers not just BI capabilities but also makes a nimble ETL and data integration engine. I’m also experimenting with Amazon S3 integration to enable simple work-flows for small distributed teams (or even same-office groups where the WAN is the new LAN).

Whether you agree or not in the validity of  ”workgroup BI“, be aware that MS does and it thinks that BI is about to enter a new phase,  for proof see MS’s Nic Smith’s The History of Business Intelligence video.

UPDATE: 19th Nov 2009

Last evening I downloaded for the 1st time both Excel 2010 Beta and the PowerPivot (new name for Gemini) add-in.  First impressions; yep, in the flesh it’s just as impressive as the above demo led me to believe it would be.  As I said on Twitter last night Datasmiths of the world; download the Excel 2010 Beta and PowerPivot add-in; this ya gotta see!!!

Why not join me on Twitter at gobansaor?

→ 12 Comments

Posted in BI, ETL, PowerPivot, SQLite, excel

Tagged , , ,

SQLite as the MP3 of data

Posted on March 14, 2009 by gobansaor| 19 Comments

… and Excel as its “mixing desk”.

When I tell people that I use SQLite in combination with Excel (via xLite) as my datasmithing platform, many ask why SQLite? (Many others ask why Excel?  but “sin scéal eile”, that’s another discussion – Excel as the iPod of Downloaded Data.) Those that question my use of SQLite tend to cluster into four camps:

  • Pure Excel jocks.
  • MS Access fans.
  • The client server database brigade (SQL Server,Oracle; or if FOSS fans; MySQL, PostrgeSQL).
  • The MOLAP folks (Essbase, TM1, Palo).

Now while I have used and will continue to use/encounter all four ‘approaches’, I’ve come to believe over the last couple of years that SQLite brings something special to the datasmithing game. When I look back over nearly 30 years in the data handling business I keep thinking – “If only I had SQLite then, how much easier/quicker/cheaper that task would have been!”.

Just as “fractional horsepower” electrical motors revolutionised manufacturing and eventually all our lives (car starter-motors, fridge motors, washing machines etc.), “fractional horsepower” databases can do the same for data. Distributing data to where it is needed.

As operational local caches, this use of SQLite is already far advanced. SQLite is embedded in lots of every day software tools, everything from McAfee anti-virus to TweetDeck Twitter clients (best one IMHO). But my interest is more in SQLite’s potential as a micro-BI (or maybe more correctly a distributed-BI) platform. A sort of MP3 format for distributed structured data, if you like.

But why SQLite (and in particular SQLite in combination with Excel) as my datasmithing tool rather than the four other approaches?  First, what’s a datasmith?

Managing and manipulating datasets has become an integral part of many people’s job, not just accountants (the original of the species) but marketing executives, sales staff, pricing analysts, process engineers; different job titles, different roles but using a skill that they’ve likely never been formally trained in, a skill without a name; a skill I call datasmithing. I like to think of  myself as a master datasmith, or a datamith’s datasmith.

If you consider yourself a datasmith then most likely the tool you use to manage your datasets is Excel. And before you apologise, don’t. Excel is by far the best and most flexible end-user data manipulation tool out there. Everything from the current financial crisis downwards has at some stage being blamed on Excel, but you know and I know that many tasks would remain undone or under-done were it not for end-user generated spreadsheets.

Spreadsheets are not however optimal for some tasks, linked spreadsheets in particular are data disasters in waiting. While fantastic for data transformations and presentations, as books-of-record they’re rarely suitable. Other tools such as SQL based relational databases and in-memory OLAP offer much better and potentially much more cost-effective data modelling functionality, but also at a cost of extra complexity and ongoing technical support.

MS Access (which like SQLite, is a document-centric, non-client-server database; but unlike it, is also a forms/reporting development environment) would appear to be the natural local store database. My problem with MS Access has been its tendency to try to be all things to all men, ending up not fully satisfying anybody. Professional developers think it’s too limiting, non-techs find it too intimidating, even reporting, where it once showed promise left a big enough opening for Crystal Reports to evolve. It is also limited to Windows which might not seem to be a problem if combining with Excel, but, as it’s often necessary, due to scale or complexity of the data,  to use ‘proper’ ETL tools such as Talend, having an OS agnostic database format than can act as a distribution media (think MP3s again) between “mixing desks” can be very useful.

The big difference to MS Access for me is SQLite’s open source code; code that’s a pleasure to browse and with an approachable API that even I, with my very rusty C skills, can manipulate. Having access to that code allows me to tightly integrate it with Excel, so much so, that I can use Excel functions (built-in functions, VBA user-defined functions and 3rd party add-in functions) directly from SQLite’s SQL; and vice-versa, access SQL functionality via Excel “formula” calls. It is  also possible to  load most datasets into memory using SQLite’s in-memory mode enabling very fast processing  and near zero-latency when passing data to and from Excel/VBA. In the near future, cheap, large SSDs will enable non-memory databases to offer similar speed but also handle extremely large datasets (see this for a glimpse of that future).

What about the big beasts of the data world, the client-server databases? Having spent most of my professional life working with such tools I’m aware of the power of a well designed relational database. If SQLite is the MP3, then these are the master tapes, the DDD recordings. Most of the data that eventual ends up in SQLite for analysis and/or transformation will have originated in data-warehouses or be directly sourced  from OLTP systems built using relational technology. But for close-up analysis and transformation, the pure simplicity and convenience of SQLite is hard to beat. That simplicity is primarily due to its Excel-like ‘document’ nature, all code and data can be housed in a single folder (or true-crypt container for added security), ensuring that the ‘problem domain’ can be easily archived and/or shared with others without the need for professional IT resources.

And yes, I hear you, isn’t that the basis of Excel-hell? Yes it is, but over the years I’ve found that this is rarely a problem for datasmiths, they deal day-in day-out with document work-flows, they understand the risks and the benefits (mainly the simplicity) of the approach. Where the nightmare truly happens is when this approach is used as an alternative to an OLTP system i.e. using Excel and other document-like datastores as books-of-record in large multi-user environments – “there be monsters for sure”.

How about MOLAP? Wasn’t Essbase’s name derived from “extended spreadsheet database” and doesn’t Palo offer a truly excel-friendly multi-user database back-end? Again, having worked with Essbase for many years and now being a big fan of the open source Palo MOLAP tool, I fully appreciate the power that such tools brings to analysis and multi-user planning tasks. But for many situations, an Excel Pivot Table is “good enough” and even when it’s not, it is possible by utilising what I call a tOLAP cube (essentially, a fact table indexed via tags enabled by Google’s great addition to SQLite, the FTS3 virtual table) to build and access  powerful, yet simple, cube-like data structures.

By integrating SQLite with Excel, datasmiths can have the best of both worlds, familiar spreadsheet front-end combined with a fast and powerful SQL engine and datastore, in fact, everything that MS Access should have been.

Why not join me on Twitter at gobansaor?

→ 19 Comments

Posted in BI, ETL, Palo, SQLite, VBA, excel, olap

Tagged

Talend ETL Excel report generator

Posted on February 13, 2009 by gobansaor| Leave a comment

Hugo, who you may remember from his OLAP Cube as a Mind Map project, has struck again. This time something really useful, a component for the Talend ETL platform that generates Excel reports using templates and a JSP style TAG language to control the output.

I’ve in the past used the excellent Xlsgen to automate the production of Excel reports, but Hugo’s component has the benefit of being free (xlsgen now costs €390!) and open source and it also taps into the vast world of existing Talend ETL components.

Well done Hugo.

Why not join me on Twitter at gobansaor?

→ Leave a comment

Posted in ETL, Talend, excel

SQL – does exactly what it says on the tin

Posted on December 18, 2008 by gobansaor| 12 Comments

SQL how unloved it must feel sometimes, constantly being maligned, accused of being on the wrong side of the object-relational impedance mismatch,  lacking the glamour of OO programming languages that claim the moral high ground. Yet at the same time hewing and hauling most of the world’s structured data on its old but well fashioned back.

SQL is perhaps the world’s most popular DSL, a declarative language for the manipulation of tabular data, easy to learn yet capable of powerful (and sometimes complex) expressions.  And like the Ronseal ad, a SQL statement no matter how simple or complex, does exactly what it says, all the complexity of loops and iterations and the attendant errors, abstracted away, it just works!

SQL is both a programmer and an end-user tool; after Excel formulas, it’s the language most likely to be understood and used by “civilians”.  There are few enough such cross-over tools, so think twice before building a datastore that doesn’t offer a SQL API.  And I guess that’s what Amazon did. Although SimpleDB is not a relational database, they’ve decided to add a SQL API, following Google’s lead with its SQL front-end to the non relational big-table backed Google App datastore.

SQL is also the reason why I’ve integrated SQLite with Excel , leveraging SQL to manipulate tabular data with greater efficiency and fewer errors while still keeping the touchy-feely power of Excel.   I expose SQLite to Excel via UDFs rather than menu options or wizards, so that the transformation logic is visible and approachable (at least to those comfortable with excel formula “programming” and with basic SQL).

SQL is my weapon of choice because of my belief in the primacy of data. It is data that matters in the long run, not the algorithms or GUIs that temporarily use (and abuse) it.  In my time in Guinness Ireland I had the task of transferring master and historical transactional data from “legacy systems” into SAP ,Siebel and a new datawarehouse; data that had a decade and a half earlier been transferred by me  into those same legacy systems from even older systems. In fact, the data’s electronic lineage could be traced back to a 1960′s era ICL mainframe  (I have the original spec!) and I’m sure it existed in accountancy machine punch-cards  prior to that. Understand a business’s data and you’ll not just understand the business as it currently operates but also how it operated in the past and its future potential.

SQL abú.

Why not join me on Twitter at gobansaor?

→ 12 Comments

Posted in AmazonAWS, ETL, SQLite, data, excel

Tagged , ,

Pentaho Data Integration (Kettle) V Talend Benchmark

Posted on December 4, 2008 by gobansaor| 6 Comments

Pentaho’s Matt Caster has just published a benchmarking exercise comparing Kettle and Talend.  In it he admits he’s not a Talend expert and he advises that people should perform their own benchmarks where possible as requirements differ.  Nevertheless, unlike most other benchmarks we’ve seen on the subject he publishes not just the results but the actual transformation “code” used in the tests. 

For many people these benchmarks are of no real interest as long as the product does what is required within the time and resources available they’re content.  But it would be a mistake to think that benchmarks don’t matter, they do; people have and will make that final decision based on them.  Remember ETL is not life and death, the decision which tool (if any) to go with may not get the level of investigation that the developers behind such products expect of their potential clientele and this is particularly true of open source.  Busy people will use such reports to direct them down a path or to confirm their existing prejudices. So I’m really glad to see Matt responding and in particular, responding in the manner he has.

Databases vendors have for years played the benchmarking game, setting and breaking records either via real technological advances or simply gaming the process.  We as purchasers and users knew in many cases to take the results with a large dose of salt, but purchasing decisions where nevertheless made on the backs of these surveys.

Why not join me on Twitter at gobansaor?

→ 6 Comments

Posted in ETL, Talend, kettle

Tagged , , ,

Spending time on Excel-SQLite, C, VBA Callbacks & Twitter

Posted on November 20, 2008 by gobansaor| 3 Comments

Haven’t posted here in a while as my spare time has been soaked up programing, well actually refactoring would be more exact.  My xLite “SQLite empowered Excel” codebase has grown over the years and required a serious makeover to get rid of stuff I no longer use and to generally make it more robust.  I also decided to add some extra functionality to my VBA friendly C wrapper for SQLite (based on Pivotal Solutions’ pssqlite.dll) which meant I had to re-acquaint myself with my long lost C skills, so doing reminded me how much I like C. Close to the metal programing if not exactly super-productive is nevertheless super-powerful.

The new improved xLiteSQLite.dll now has a built-in CSV loader (both file based and string based – handy for loading Palo HTTP API responses into a table). It also returns a one columned variant array of CSV values for quick rendering via “text-to-columns” code (by far the quickest way of handling large dataset pasting into Excel).

I’ve also added the ability to create SQlite UDFs (user defined functions) in VBA (thanks to http://stackoverflow.com/users/4007/rpetrich).  This is a very powerful feature as it allows SQLite selects to act as a “loop controller” calling back to  Excel/VBA functions to process each row, really useful for ETL tasks. And not just scalar UDFs but aggregating (aka group-by) functions too, allowing the use of Excel’s powerful array functions in SQLite statements.

All in all, the changes to the xLite VBA code and the C wrapper makes Excel backed by SQLite a seriously good micro-ETL tool. Combined with Palo, the result in a truly wonderful micro-BI platform; a cost-effective toolset for these recessionary times.

Of course I’d be lying if I said code was the only reason I’ve been neglecting my blogging duties, I’m afraid I’ve a confession to make, Twitter has hooked yet another sucker, me! 

I’ve found I’ve settled in to the whole micro-blogging thing with ease, and have managed to make contact with people I would not have encountered otherwise, as well as reconnecting with others that I’d lost contact with.  So if you too are all-a-twitter then do please follow gobansaor-on-twitter.

→ 3 Comments

Posted in BI, ETL, Palo, SQLite, VBA, Web2.0, excel, xLite

Tagged ,

Open Source Metrics and Benchmarks

Posted on October 30, 2008 by gobansaor| 13 Comments

Marc Russel’s blog links to a Manapps ELT benchmark report comparing the performance of several leading ETL tools both proprietary (DataStage and Informatica) and OS (Talend and PDI (aka Kettle)).  As would be expected each tool has their own strengths and weaknesses, but one thing stands out, the venerable Kettle ETL aka PDI 3.0 is now a serious contender for handling very large datasets.  Oops, that’s what I get for wishing for a result and (mis-)reading the report early in the morning with a cold and bad sore throat, sadly PDI is still very much slower that its OS cousin Talend. In fact, Talend continues to play on the strength that comes from a code generated sloution, i.e. raw speed.  As a pure ETL play, Talend is well capable of playing on the same pitch as the “big kids”. 

Interestingly, the report is also “open source” as it’s released under a Creative Commons License, so I can link to it here.

UPDATE:

There’s now a new version of the report available (www.manapps.com, Topic Benchmark), it seems the original was just a work-in-progress and was not meant for public release.  The main difference appears to be a significant improvement in Informatica’s ‘score’, but I’m not sure as I was really only interested in comparing the two OSS products, Talend and Pentaho PDI, in that ‘battle’ Pentaho still comes out ‘slower’.

 The original Marc Russel blog entry and a subsequent one reporting the new updated report appear to have both been removed.  

Also, I was informed of the ‘updated’ report via this email from manapps, which assures vendors that they are happy to rerun any tests and provide any information re the running of such tests … 

Dear Sir,

You referred on your web site to the report called “Benchmark ETL” by Manapps, from November 2008. This draft report was not intended to be publicly released since just a working document.
We would like you (i) publish Asap the modified version (or its related link) that supersedes the former one (on our web site (www.manapps.com, Topic Benchmark), (ii) state that Manapps had no intend to release the former report and accordingly takes no responsibility on its content, (iii) state that Manapps holds all necessary elements at the disposal of all vendors so that they can rerun some tests if wished that will then be published.

Regards,
Philippe THOMAS

Time: Thursday March 5, 2009 at 5:10 pm

 

Another analysis of OSS in the wild this time from Chris Keene, WaveMaker CEO, on OSS as a marketing tool. Bottom line, 1% conversion rate, 700 paying customers in 9 months …   

WaveMaker OSS as a marketing tool

WaveMaker OSS as a marketing tool

 

Why not join me on Twitter at gobansaor?

→ 13 Comments

Posted in ETL, Talend, kettle

Tagged , ,

Why Larry hates the cloud, and my data trinity.

Posted on October 4, 2008 by gobansaor| Leave a comment

Last week Oracle certified Amazon EC2 as a supported platform, that same week Larry Elison attacked the concept of cloud computing as pure hype. Obviously, Larry is not happy with this whole cloud thing, and I think it’s not just the threat it poses to the software industry’s traditional licensing model that worries him, rather, as Robert X. Cringely points out in his “Cloud computing will change the way we look at databases” post, it’s the likelihood that it sounds the death-knell for large-scale traditional databases.

This new database paradigm is memory rather than disk centric, with the disk-based element acting as an archive/backup/restore mechanism which can easily be stored on commodity SAN devices ( e.g. Amazon’s ESB). Using MapReduce technology Google effectively holds the whole Internet in memory, not in one big super computer but in lots of cheap commodity servers.

But it’s not just in the realm of mega datasets that RAM based databases threaten traditional models. Excel is a memory-based database engine, so too in-memory OLAP tools such as Palo. Such products’ ability to handle large volumes of data has increased over the years, with the decrease in RAM costs and the appearance of cheap 64 bit machines (which are no longer limited to 2G/3G process working sets).

That doesn’t mean that we’ll throw away SQL databases in their entirety, SQL and the relational model will continue to be useful. But perhaps of greater use in local datastores/caches that as the building blocks for large scale datastores. For such local caches, less will be more; fewer features, easier to configure, more flexibility. That’s why I like SQLite; long after the dinosaurs of the database world have disappeared, I imagine SQLite databases will continue to survive, embedded in mobile phones, browsers, wherever a local datastore is required. And more than likely operating in memory rather than off disk.

By combining Excel with an in-memory SQLite database, linked to a Palo OLAP in-memory server, it’s possible to take advantage of three powerful data-processing technologies (spreadsheets, SQL, multi-dimensional cubes) all within your PC’s RAM. You could do serious datasmithing with such a combination on a pretty mediocre laptop, with most modern machines providing an excess of CPU power, no need for super fast disks, just as much memory as you can muster. And, with Windows on EC2, these three amigos will soon be capable of being used as a cloud bursting platform.

Excel, SQLite and Palo, my data trinity.

→ Leave a comment

Posted in AmazonAWS, ETL, Palo, SQLite, cloud, excel, olap

Tagged ,

Clouds no longer pass by Windows.

Posted on October 1, 2008 by gobansaor| 4 Comments

Amazon today announced that later this year, Windows Server woud be available on EC2. No details on cost and licensing etc. but this is major.  Up until now, that portion of the business world who are pure MS shops (a very large percentage especially amongst SMEs) were excluded from taking advantage of Amazon’s amazing (and getting more amazing everyday) EC2 platform. 

From my point of view, as with Oracle’s announcement last week, this releases yet more of my “legacy” skillset for deployment in the clouds. Although I’ve been involved with  *nix servers for 20 years or so, as corporate servers became more locked-down (and removed to the control of 3rd party data centres) I lost day-to-day experience of using them; in latter years my main ‘hands-on’ platform was Windows, either my own PC or local departmental NT servers. Windows on EC2 will allow me to use a whole new set of Windows only software (e.g. RSSBus or XLsgen) and of course SQLServer.

The lack of SQLServer on EC2 has been a major problem for me as a datasmith; there’s an awful lot of data out there sitting in SQLServer databases, but currently if I need to “cloud burst” such datasets I would have to first extract the data to, say, csv files and then load the data on to a Linux compatible database. But with a SQLServer instance running in the cloud, I could simply use SQLServer’s native backup/replication tools.  No more need to download data to my “ground-based” PCs resulting in quicker turnaround and fewer data security risks.

On the licensing front,  I’m presuming that the OS licence will be on a pay-as-you-go basis, but what about SQLServer and other server products?  Will MS do an Oracle on it, i.e. require a traditional upfront use-it-or-lose-it payment or will they the go the radical (but I thing inevitable) path of a licence-by-the-hour. 

First RedHat, then Sun, then Oracle and now Microsoft; the mighty beasts of our industry have acknowledged there’s a new mighty beast on the prowl, dressed as a humble bookseller no less!

→ 4 Comments

Posted in AmazonAWS, EC2, ETL, RSSBus, Web2.0, data, news

Tagged , , ,