Category Archives: PowerPivot

HAMMER Alongside, as a COM Server

Posted on October 7, 2011 | 9 Comments

Although it has always been possible to call HAMMER from within VBA via the Application.Run method, this is a somewhat clunky way of doing so and it can also be very inefficient, particularly for tight loops. But now, with this release (V 1.2.0 (Beta) ) core functionality is exposed as a COM Server, which means easier and more efficient interfacing between VBA and the .NET multi-threaded enabled world of the datasmith’s HAMMER.

I call this “HAMMER Alongside”, to differentiate it from HAMMER Inside where I use HAMMER internals to craft stand alone XLLs. With this COM Server method, the standard HAMMER add-in (installed or just-in-time registered) is needed alongside either a VBA add-in or a VBA enabled workbook. The extra “moving parts” are easily justified by the extra flexibility that this method allows (particularly to those with a reasonable grasp of VBA, but perhaps lacking any familiarity with the .NET world).

By learning a small amount of IronPython (here’s a good starting place) it’s relatively easy to hook up VBA to any .NET library through HAMMER, without having to invest time and money in learning heavy duty development environments such as Visual Studio. Using “Internal Threads, it’s also possible to take advantage of .NETs multi-threading capability from VBA. (Note: both these options require .NET4).

So with VBA becoming a first-class language with regards to HAMMER, why offer Python as an alternative scripting choice? Well, one reason is to offer a means to access .NET’s power as per the last paragraph. But, the primary reason, is related to the ability of HAMMER transformations to be “detached” from Excel and run via HAMMER’s command line tools. The .NET 4 version of the command line tool supports Python, so it is possible to initially build out a micro-ETL transformation within Excel (using Python to perform the business logic alongside SQL) and then transfer that logic to the command-line tools with a minimum of modification.

This could be useful , for example, when a PowerPivot model moves to the server, its related HAMMER-powered micro-ETL processing could do likewise (most enterprise ETL tools support call-outs to command-line processes).

The COM server can be accessed from VBA only via late-binding like so:

Set comServ = CreateObject(“hammerCOMServerV1″)

The server’s methods are:

  • hammer(…) – works the same way as the UDF version.
  • hammerVersion() – returns HAMMER version, again the same as UDF version.
  • hammerVersionOK(version) – e.g.  isOK= comServ.hammerVersionOK(120) will return TRUE if the current version is >= V1.2.0
  • arrayResize(anArray) enables the creation of “toFit” UDFs.
  • arrayToSheet(anArray) likewise for “toSheet” UDFs.
  • arrayToRange(anArray,pasteToWhereString) enables “toRange” UDFs.
  • hammer_ppRefresh_inline(optional table,optional timeout) – refreshes a PowerPivot model, again the same as its UDF equivalent.

Download the latest version of HAMMER from here …

→ 9 Comments

Posted in ETL, excel, HAMMER, PowerPivot, Python, VBA

Tagged ,

Exposing an Excel PowerPivot model as a Web Service

Posted on September 21, 2011 | 5 Comments

In my last post I demonstrated a simple way to interrogate an Excel based PowerPivot model using either MDX,DMV or the new DAX Query Table commands. This is a great way to dig deep into a PowerPivot model, for example, this new Denali DMV discover_calc_dependency can be use to  describe the dependencies between a model’s DAX measures.

Being able to directly access the PowerPivot model opens up all sorts of interesting options, one such is a web service.

In the past I’ve written about exposing Excel as a simple DIY web service, see here for a JavaScript example and here for a Python version. Both were capable of serving PowerPivot data but they both had to trigger events in Excel to do so.

With this newly discovered ability to query the model directly, I figured a third attempt at a PowerPivot Web Service was overdue.  This time I’m using the new kid on the block, HAMMER, and in particular using its in-built IronPython interpreter.

I could have built a simple server using VB.NET or C# and added it as a HAMMER command, but the nature of a web service makes it hard to frame as a parametrised command. It was for this type of requirement that I added the ability to script using Python, i.e. situations where a generic command would always come up  short, for example:

  • Should the service allow only local connections?
  • Should it use authentication, and if so, what type?
  • Should it allow free-format commands or offer only packaged commands?
  • Should it use HTTPListener (which is in-built and very powerful, but requires Admin privileges) or use a more basic socket based utility that can run without Admin privileges.
  • Should it block or handle asynchronous requests. (For services that access the Excel Object Model, it has to be blocking, but perhaps if the service simply accessed the AS engine, it night support multi-threaded queries? Must try it sometime.)

For this example I decided to use HTTPListener (in blocking mode and without authentication) and to expose services that accept free-format MDX,DMV or (if Denali) DAX Table Queries.

See the PPWebService workbook example in the latest (V0.5.0) version of HAMMER. To activate, make sure latest version of HAMMER is installed (or use activate button on Hammer sheet) and click the Expose PowerPivot Model as Web Service button (again on Hammer sheet). This will start a server on port 8070.

The service exposes 6 endpoints:
  • /exit – this will shutdown the service.
  • /html – this will return data as a HTML table (ideal for importing into Excel using a Web Query).
  • /xml – this returns a ‘canonical’ XML table (ideal for importing to Excel as an XML Map).
  • /json – returns a JSON table (as a list of lists).
  • /odata – returns a ATOM-base ODATA feed suitable for direct import into PowerPivot using its Data Feed import facility.
  • /csv – returns in CSV format.

Each service (except /exit) expects to be followed by a MDX, DMV or DAX command. e.g. /html/evaluate(InvoiceHeaders). In the case of /csv an optional file name may be specified e.g. /csv/invhead.csv/evaluate(InvoiceHeaders).

It’s also possible to issue a POST request with the command in the message body.

As with all URL requests, the commands may need to be URL encoded (use the HAMMER command “_URLENCODE” if need be).

For the latest versions and articles on HAMMER follow the HAMMER tag on my blog …



→ 5 Comments

Posted in DAX, Denali CTP3, ETL, excel, HAMMER, PowerPivot, Python

Tagged , ,

DAX Table Queries in Excel

Posted on September 15, 2011 | 4 Comments

Although the discovery of what Rob Collie calls a live specimen of Bigfoot itself i.e. automated refresh of PowerPivot models from VBA is indeed welcome, the activities that led to its discovery were intended to serve a different purpose, namely the use of DAX table queries in Excel.

Dax table queries are a new feature of DAX (part of the Denali PowerPivot upgrade) that in my opinion takes the DAX language away from its (very useful) pivot table birth-place and makes it a truly powerful alternative to SQL/MDX for analytical reporting.

It was Chris Webb’s excellent  series of articles on DAX that first wetted my appetite for this feature and then hinted that it could be accessed from Excel and ultimately automated if need be.

An so it can, the VBA code that I used to query DMVs and send XMLA process commands to refresh the PowerPivot model can also be used for MDX and DAX tabular queries.

And it gets better; you don’t need to use code to do this, there’s a very simple trick which will expose this functionality in plain Excel.

This trick only applies to Denali as it takes advantage of one of Denali’s most welcome new features, show detail (aka drill-thru). (An alternative method which can be used in PowerPivot V1 is detailed here  - if using V1, only DMV and MDX queries would be possible).

First thing to do is (in a PowerPivot Denali powered workbook) make a pivot table (doesn’t matter of what), such as this …

… then right-click and show-detail on any measure cell, which should result in a new sheet with a Excel table showing the drill-through, like so …

… if you then right-click, pick Tables and then Edit Query, you’ll see the MDX query associated with the table …

… you’re now free to edit this query, replacing it with whatever takes your fancy, here’s an MDX tabular query …

… you can also issue DMV queries such as “Select * from [Model].[$InvoiceHeaders]” or its equivalent DAX table query …

Note: both the DMV and DAX ‘dump a dimension’ are unlikely to work for very large tables (such as a multi-million row facts) unless you have loads of free memory and the ability to use it (i.e. 64 bit), without triggering a “could not allocate memory error”.

DAX is not limited to DMV type queries but can be used to express very complex OLAP queries, Being able to define new  temporary just-in-time Measures (and Variables) for use within a particular queries will help not just to produce the result required, but will make DAX easier to master for newcomers.

The current version of HAMMER only allows DMV and MDX queries as it restricts the “ADO” command  to “SELECT” statements; the next release will remedy that.

I’m at present working on an example of using HAMMER to build a DMV/MDX/DAX Web Service such as I did with MicroETL in Python-Powered PowerPivot. This time using IronPython and the .NET’s HttpListener and simply passing the queries straight-through to PowerPivot without any need for Excel Pivot Table involvement.  So stay tuned … Update: (here it is)

→ 4 Comments

Posted in DAX, DAX v SQL, Denali CTP3, ETL, excel, olap, PowerPivot

Tagged

HAMMERing away at Automated PowerPivot Refresh

Posted on September 9, 2011 | 24 Comments

See below for an updated version of the VBA code that automates the refresh of PowerPivot models. Having spent the last week delving deeper into the process I’ve made a few changes.

The original code doesn’t work if more than one PowerPivot model is opened; the new method uses a combination of DMV (tabular views of AS engine’s metadata) Sessions and Object Activity (in particular  looking for ‘Permissions’ issued to a workbook’s sessions).

I’ve also allowed for a single table refresh. Again, this required some more DMV queries to determine the DimensionID associated with the table.

Excel linked tables can be refreshed (i.e. the XMLA will run OK) but it has no effect, as this doesn’t trigger a fetch of new data from the workbook. To automate linked table refreshes means using the dreaded SendKeys, nasty, but works after a fashion.

Another interesting observation, the refresh operation works fine without the PowerPivot add-in being enabled (but the associated DLLs would have been still visible and presumably used). Not sure what use that knowledge is, but interesting none the less.

So far so good, seems to be working; but as Marco pointed out, this is not supported  (but is documented and would be perfectly valid to use against a ‘normal’ AS model). So, test, test again and make sure you have a backup of any important workbooks.

I’ve also managed to get the code working in VB.NET and have ported it into my HAMMER micro ETL tool. In fact, having the ability to quickly and easily fetch and render DMV views using HAMMER helped enormously in identifying what DMV queries would help with the multi-model and DimensionID problems.

The xll (a 32bit and a 64bit version) of the next HAMMER release is included alongside the sample workbook (go to Hammer sheet and press “Enable HAMMER” button, or use the setup xls if you wish to install). You’ll need .NET 4 to run this version of HAMMER.

I’ve tested against Denali CPT3  (but should work with PowerPivot V1) XP SP3 32bit; 64 bit should work too (let me know if not).

To enable actions such as PowerPivot refreshes (which require access to the Excel Object model) I’ve made a few breaking changes and added some new commands.

  • BREAKING CHANGE, the main HAMMER function is no longer thread enabled (accessing the Excel Object model from within a threading UDF is not to be recommended). If you’re sure you’re thread-safe use the hammerThreadEnabled function, if you don’t know what I’m talking about, don’t :) Internal Threads are not affected by this.
  • BREAKING CHANGE, commands are now case-senstive, nothing really to do with the PowerPivot changes, just something I’ve been meaning to do for sometime.
  • PPCONNECTION (New command) – will set up an ADODB connection within HAMMER to enable ADO queries against PowerPivot DMVs. Once established can  be used by subsequent HAMMER calls.
  • ADOCONECTION (New Command) – like above but for any ADODB connection, requires a valid connection string. Also, once established, can  be used by subsequent HAMMER calls.
  • ADO (Modified Command) – can still use a connection string for once-off connections but if  an ADOCONNECTION is in force will use that instead.
  • HAMMER_ppREFRESH (New Helper function) – spawns a background thread to refresh the PowerPivot model and refresh associated pivots. Background thread may be still be running when function returns. Takes two optional arguments, table (if a single refresh is required) and timeout (in seconds, to allow for long running refresh).
  •  HAMMER_ppREFRESH_inline (New Helper function) – like above but operates as a normal UDF, will not return until action is complete. In theory breaks the UDF no-side-effects rule, but appears to work fine! Also, this is the function to use if calling via VBA’s Application.Run command.
  • PPREFRESH (New command) – performs a PowerPivot model refresh but doesn’t do a connection refresh.  Main use-case is for use within VBA code to allow for finer control.

Download the latest Power Pivot refresh code.

For the latest versions and articles on HAMMER follow the HAMMER tag on my blog …


→ 24 Comments

Posted in Denali CTP3, ETL, HAMMER, PowerPivot, VBA

Tagged ,

Automating PowerPivot Refresh operation from VBA – The Code

Posted on September 1, 2011 | 56 Comments

In my last post I explained how I ‘d managed to automate the refresh of a PowerPivot model from within VBA. The example workbook below contains the test  code I used and a PowerPivot model based on two CSV files.

To try it out you’ll need to generate the CSV files first  and then adjust the Text connections within the PowerPivot model to point at your default Excel folder (usually MyDocuments).

This was developed against the latest PowerPivot CTP3 release, under XP SP3

Download the example workbook from here …  UPDATE: New version of code available here …

ERROR: -2147467259 (80004005)’  -  If you get such a error see here for potential causes and fixes 

UPDATE: Mar 17th 2012

If you need to automate the refreshing of “linked” Excel tables to PowerPivot see here…

→ 56 Comments

Posted in PowerPivot, VBA

Tagged ,