Introducing xlDAX - an add-in for modern Excel
In this article, I introduce my latest Excel utility add-in: xlDAX.
Before modern Excel I developed a .NET add-in called HAMMER to do many of the things that are now standard within Excel. And before that again, I used a mainly pure VBA add-in called microETL to do the same thing. When I say mainly VBA, it also used a number of C dlls (SQLite and Python) to add extra needed functionality, resulting in a lot of extra files accompanying the core VBA code.
HAMMER solved this explosion of files by wrapping the same functionality in a single-file, no-install, xll, but it was still a dll (an xll is a specialist form of a windows dll for loading by Excel), and in many corporate sites dlls of any nature are not welcome.
The post 2010 beefing up of Excel’s data mangling capabilities, with Power Pivot, Power Query and MS Access driver, offered me a way out of my own dll hell. I did this by essentially re-engineering the microETL code base, to replace SQLite with a combination of Power Pivot, MS Access and Excel Tables, and replacing the need for a data scripting language with Power Query, the result is xlDAX.
xlDAX is pure VBA add-in, no non-Excel external dependencies (its code could also be extracted from the add-in and added to a workbook’s own VBA project, doing away with the need even for a separate .xlam file). This makes its use in corporate tactical reporting solutions easier technically and, more importantly, politically.
xlDAX adds the ability to use VBA to perform automation in a familiar way while leveraging modern Excel’s newly acquiring data crunching power.
xlDAX has a simple DAX formatter and editor for handling DAX queries outside the Power Pivot add-in (right-click context men, xlDAX->DAX Editor).
xlDAX can, if HAMMER accompanies it, link into the power of .NET (via IronPython or JINT scripting) if the need is there. An example of where .NET could be useful: using AMO to do cool things with Power Pivot, such as scripting the creation of Measures!).
Over the next few weeks, I’ll delve into xlDAX’s bag of goodies. By the end, hopefully I’ll have produced a manual of sorts, so stay tuned.