Tag Archives: microETL

Steam Powered PowerPivot

In previous posts I described various options for enabling PowerPivot models (or indeed any Excel model) as the hub in a hub’n’spoke application. This thin’n’core approach is the norm for web apps as it was for the client server applications that preceded them.

But as we know, Excel “applications” rarely follow this pattern.

They tend to follow a more peer-to-peer topology, often with each player being an “equal”; collaboration being the primary aim rather than service provision.

But even in such scenarios there usually is a hub at the centre providing the communication linkages. Most likely the company’s email system or a “shared drive” of some sort. In fact, on many corporate teams or projects, you know you’ve been accepted into the fold when the project manager says the magic words “I’ll get you access to the shared drive”.

But sometimes, hub-like service provision or a single controller role is required and in such situations one of the spokes takes over that responsibility. For example, in a budgeting scenario one spoke may be the source of historical stats, in effect, performing a service role.

If classic client-server uses the powered-hub-driving-the-client-spokes like the driven wheels of a car as an analogy, then many Excel systems could be compared to a steam engine’s driven wheels; where the hub provides a centre to rotate about but it’s a particular spoke that does the driving due to its linkages to the piston arm.

Such systems built around the sending back and forth of workbooks or CSV files via email or shared drives are common and most of us have seen them in action. Most tend to be manual, in that they require user action on both client and server side, but with a bit of VBA magic one or both can be automated. Options for doing so on the server side are:

  • A VBA loop that sleeps and checks for change, process any changes and sleeps …
  • Trigger a microETL.SQLScript using a COM automation call from a external program like that used in this scenario.
  • Trigger a script using an RTD Server that checks for new activity.
  • Use a “Javascript” timer as per Excel Hero’s neat trick.

RTD servers are rarely encountered in the wild as they only appeared on the scene in Excel XP and were not the easiest things to code or understand but now new tools such as ExcelDNA makes coding RTDs much more approachable and useful.

Daniel Ferry‘s JSTimer idea is a simpler VBA alternative to an RTD and is non-blocking unlike a pure VBA sleep-check-sleep loop. It also has the advantage that no add-in is required so the client-side could be a single-file macro-enabled workbook.

Using a shared folder or email as the conduit for passing data back and forth and using Excel files or CSVs as the data artefacts may be the obvious choices but other “more modern” alternatives exist.

For example, for situations where a shared folder isn’t an option why not use something like Amazon’s S3? S3 is secure (much more secure than external email), reliable, simple to use and extremely cheap. A modern-day alternative to an FTP server. Ideal for big corporates,SMEs and sole-traders.

Orchestrating the processing of files on S3 is very simple using tools such as Python’s Boto (another good reason for having Python in-built in microETL). For the client-side, pure VBA could  be used, see my xlAWS code.

Likewise, using workbooks as the medium of data exchange is usually the first option, with CSV files a close second. But since Excel 2003 Professional, Excel has had excellent support for the exchange of XML files via XML Maps. Although not useful for highly complex schemas or very large datasets (both of which are anyway the work of the devil 😉 ), XML Maps are really good at handling relatively simple dialog and configuration inter-change requirements.

The process of importing/exporting data via XML can be easily automated using basic VBA but can also be safely left under manual control. XML files can also be generated and read by non-Excel components.

So when you’re confronted with a PowerPivot model that is not suitable for direct sharing, due to:

  • technical constraints e.g. non-2010 clients
  • or business reasons e.g. detail data behind model, or the model itself, needing to be kept secret
  • or model complexity reasons i.e. too complex for a single PowerPivot model …

… investing in a top-of-the-range SharePoint farm is not your only option. By utilising some of the enormous power that comes as standard with Excel such as VBA, XML Maps, RTDs etc. further empowered by an Excel-focused ETL tool such as microETL a lot can be achieved on a relatively small budget.

If you need help designing or configuring such a scenario or need help in understanding where PowerPivot can fit in to your decision making and information distribution requirements, contact me. My job in life is to provide than help …

Download latest version of microETL from http://www.gobansaor.com/microetl