Author Archives: gobansaor

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 ,

Automating PowerPivot Refresh operation from VBA

Posted on August 31, 2011 | 19 Comments

Although the latest CTP3 release of PowerPivot has addressed many of the short-comings of the original release (allowing drill-thru for example), there’s still one major missing: a published API accessible from VBA (or even .NET).

Like Marco Russo, I would settle for the ability to automate the PowerPivot refresh function. What’s doubly annoying about this is that there’s obviously an undocumented method of doing this as Predixion are doing it and so are PowerPlanner.

Encouraged by the fact that it’s obviously possible, armed with hints from the comments on Macro’s post, and from this Chris Webb post and a late night twitter conversation with Mark Stacey, I decided to try.

I used VBA, but this could also be done within a .NET add-in.

The first thing to do is get an ADODB connection to the local embedded AS engine. I tried using the connection string details used by the Excel PowerPivot model connection (see under Data->Connections) but couldn’t get past an authentication error. There may be a way around this, but I decided to short circuit the problem by using the Excel Object Model to directly fetch the already established connection’s ADODB handle via ..

ActiveWorkbook.Connections(“PowerPivot Data”).OLEDBConnection.ADOConnection

One problem with this method is that when a workbook is 1st opened the default PowerPivot Data connection will not yet be established, needs something like a PivotTable refresh or a Cube formula call to fire it up. But again this can be automated.

Next step is to issue an XMLA command like the one in this post (no need for the CubeID property, but if want, you can specify it as “Model” or  ”Sandbox” depending on the version of PowerPivot; “Model” seems to be the new name of the cube in the latest version).

But how to get DatabaseID? I used a “select distinct object_parent_path from $system.discover_object_activity” DMVcall to get a list of database objects and parsed out the DatabaseID from this object…

GHOME1300\LocalCube.Databases.CBBB19B2CD9B4017A8A0

… where GHOME1300 is my machine name and the DatabaseID is CBBB19B2CD9B4017A8A0

The DatabaseID can also be seen in the un-zipped Excel file but it appears to change when the workbook is loaded so will need to be refreshed each time.

The important bit of the XMLA command is this ..

<Object><DatabaseID>CBBB19B2CD9B4017A8A0</DatabaseID></Object><Type>ProcessFull</Type>

Having issued the command, the PowerPivot model will refresh all its external connections and rebuild the Model (aka Sandbox) cube. Linked Excel tables however, appear not to be affected by this, which is a pity. [UPDATE: Mar 2012, but here's a solution to the linked table refresh problem]

The workbooks pivot tables still require to be refreshed separately, but this too can be automated via an ActiveWorkbook.Connections(“PowerPivot Data”).Refresh or a PivotTable refresh. One thing to be aware of, is that the PowerPivot Refresh happens asynchronously (i.e. command will potentially return before the refresh has finished) therefore a delay may be required before issuing a connection or pivot table refresh. This asynchronous behaviour may be the reason why an API has not yet been provided (similar problems arise when automating CUBE formulas from VBA). There are various last_updated datetime attributes available on DMV tables, perhaps these could be used to signal when a refresh has completed.

As I said, this issues a complete refresh, I’ve not been able to restrict to a single table refresh (using the DataSourceID, the XMLA was accepted but it generated a DLL error). Those with more knowledge of XLMA processing commands might have more luck.

UPDATE: Both the asynchronous nature of the Process call (which turns out not to be asynchronous, the call actually blocks, must have been imagining things during my late night experimenting ;) ) and the lack of a single table refresh are no longer problems, see comments on next post. Will produce an updated example in due course. Here’s an updated version of the code.

So not quite a full blown PowerPivot API, but I’ve always said I’d be happy with a simple Refresh ability from VBA, looks like I might have it.

This is not yet fully proven, so proceed with caution. Hopefully over the next week or so I’ll attempt to put together a version of this for inclusion in HAMMER and/or microETL.  UPDATE: Here’s a HAMMER version.

UPDATE:

To see an example in code see http://blog.gobansaor.com/2011/09/01/automating-powerpivot-refresh-operation-from-vba-the-code/

UPDATE:

Ideally the PowerPivot window should be closed during the refresh as the tables displayed by it will not show as updated, no problem in that the model that the workbook’s pivot see will be, but can be confusing.

→ 19 Comments

Posted in ETL, PowerPivot

Tagged , ,

Hammer Inside

Posted on August 14, 2011 | 6 Comments

As I explained in a previous post my main reason for developing HAMMER was to provide me with the same productivity boost in .NET as that provided by MicroETL when working using VBA.

Although the resulting tool is proving to be very useful in its standalone formats (as a UDF formula, VBA tool via Application.Run and as a command-line EXE) its use as  a powerful utility library within VB.NET or C# add-ins is still what justifies the cost of its development.

Over the last few weeks I’ve used HAMMER in the development of several ETL-focused add-ins with great success. That success is not just to be measured in speed and accuracy of development but in the extra features I can now deliver for little or no cost (caching of API or database calls for example). I term them my “HAMMER Inside” add-ins.

The final destination is to allow me to offer fixed-price (or at least transparent price) development of  ETL-focused add-ins. Not just the classic database or flat file backends, but WEB APIs and calculation engines (such as pricing /discounting tables) etc.).

Such add-ins would be “opinionated software”, in that they’d have a limited look’n'feel, with a concentration on the hewing and hauling of data, not on fine-detail Excel automation or advanced/pretty UI features.

In the meantime, HAMMER has been updated with a series of new COMMANDs:

  • JSON – Load a JSON document into a table.
  • TOJSON – Convert a table to a JSON document. If a single row table, will generate a JSON “dictionary” object with the column headers as the Keys, and the column data as the Values. If a multi-row table will generate a JSON List of Lists.
  • TOJSONLOL  - force a JSON List of Lists (when a single row table is really a table not a dictionary).
  • TOJSONLOO – force tables to be represented as a List of (Dictionary) Objects rather than a List of Lists.
  • TEXTTOFILE – output an argument as an UTF8 encoded text files e.g. HAMMER(myTable,”TOJSON”,”C:\files\myTable.json”,”TEXTTOFILE”)
  • TABLETOSCALAR – converts a table to a scalar value by picking the first column of the first row.
  • SQLRAW – Like SQL, but doesn’t perform any token substitutions. The range of tokens that can be substituted by SQL has also been increased. The existing “:n” tokens and “from tablen”, where n is the argument number, continue to be supported; but new formats of :ARGn ,:<ARGn ,:TABLEn, and :<TABLEn have been added.  As before, n will be the argument number for :ARG and :TABLE but in the case of :<ARG and :<TABLE, n will represent the previous nth argument or table (think “<’ pointing backwards).
  • URLGET and URLPOST – fetch or post data to a URL.
  • There’s also a series of “_” functions, mainly intended for use within VBA or .NET but could be useful as formula calls too, I guess. These are: _URLENCODE (encode a URL), _HAMCSHA1 (calculate a HMACSHA1 hash),_MD5 (MD5 hash),_MD5FILE (same but for a file),_GETCONFIG (reads addin’s .config file’s HAMMER JSON section) and _GETCONFIGNOERROR (same but doesn’t complain if no .CONFIG file or no JSON section found).

→ 6 Comments

Posted in ETL, excel, HAMMER

Tagged

The Civilian Datasmith – a live specimen.

Posted on July 30, 2011 | 15 Comments

A live specimen - not a datasmith but a related data consuming species: The PacMan

If you’ve been following my ramblings posts for any length of time you’ll have come across the term “civilian datasmith” quite a lot. For example, in my last two posts on DAX I’ve been looking at whether they’re likely to use PowerPivot’s DAX.

Professional datasmiths, like myself, will have little choice but to learn and adopt DAX if we intend to continue to service either the high-end Excel management information market or the general MS BI tools market, but will our civilian peers follow suit? I think many will, particularly those who are already comfortable with high-end “excel formula programming” or those who already have extensive SQL and/or data modelling experience. But who are these non-IT datasmiths?

Well, they’re very unlikely to have data anywhere in their job titles and often share their job titles with dataphobic colleagues. But you’ll know them, they’re usually the “go-to-guys/gals” for all sort of data analysis/integration/discovery tasks and they tend to favour making decisions based on hard facts rather than gut instinct. They’ve learned to make use of whatever data and whatever tools are at hand, while others complain about tools/systems not doing what they want or IT not reading their minds, datasmiths just get on with making things happen.

An example of a real live civilian datasmith in action is Richard Baxter of SEOGadget. I know Richard from Excel development work he’s commissioned from me, including this Google Adwords Excel Add-in that appears to have been all the rage at this year’s MozCon conference.

I was at first somewhat surprised that an SEO professional was  commissioning Excel add-ins, as the typical SEO tools tend to be web-based (usually by default I think,rather than by rational choice), but I soon realised I’d found yet another datasmith enabled profession (to add to the more typical accountants, engineers, sales & marketing managers etc.).

Richard’s a classic datasmith.  To see him in action  check out the slides of his well received MozCon presentation. Note how he sources data from multiple sources but uses Excel to merge and shape the data (one other tool I’d suggest he should check out would be my HAMMER, ideal when multiple tables need to be queried,merged, cross-joined etc.).

So if you’re an SEO pro (or indeed any data dependent professional) struggling with too much data, or worrying that decisions are been made based on too little data, check out the following:

  1. Microsoft Excel for SEO http://www.distilled.net/excel-for-seo/ not just for SEO types, an excellent run down on the Excel skills any would-be datasmith must have.
  2. See Richard’s Adwords Add-in for Excel, a good example of the sort of thing Excel can do through the power of custom-coded add-ins. (UPDATE: Here’s a video of the tool in action)
  3. Then check out HAMMER, my swiss-army-knife-like  datasmithing tool for those whose working lives revolve around  data tables (primarily an Excel add-in for use as an array formula, but can also be called from VBA and via the command-line (aka DOS box)) .



→ 15 Comments

Posted in data, ETL, excel, HAMMER

Tagged , , ,