Category Archives: PowerPivot

Death of the Star Schema?

Posted on January 19, 2012 | 10 Comments

With the release of the next version of PowerPivot around the corner (mid March I think), I’ve been re-acquainting myself with its new features. Most of the current version’s annoyances have been remedied (no drill-thru, no hierarchy support for example); and the additional enhancements to the DAX language (crossjoins, alternate relationships etc) make modelling m0st any problem possible (and generally easy).

The more I come to know PowerPivot, the more I believe that modelled data warehouses‘ days are numbered. I didn’t say data warehouses per se, rather those that attempt to centrally model end user reporting structures (usually as star-schemas).

There will continue to be a need for centrally controlled data warehouses (or at least simplified data views (and/or copies) of operational datasets, either provided by system vendors of by in-house IT) to bridge the raw-to-actionable data gap. But I suspect the emphasis will change from providing finished goods to providing semi-processed raw materials.

So, will the star-schema become redundant? No, as it’s still a valid method of modelling a reporting requirement in order to make many queries simpler to phrase (this obviously applies to SQL , but also to DAX queries). But, those who build them will be doing so closer to the problem at hand, and specific to that problem (I’ve discussed this before in http://blog.gobansaor.com/2011/01/08/slowly-changing-dimensions-time-to-stop-worrying/).

For many reports the barely modified operational data model will be all that’s required (for example, DAX doesn’t require “fact” header/detail tables to be flattened to detail level, as would be the case with a classic star).

“Good Enough” models will become the norm; classic “Everything You Ever Wanted to Know” centralised models a luxury for most (especially as such models tend to “age” very quickly).

If you’re about to invest or re-furbish your data warehouse or your reporting data sub-systems, don’t do so without first taking a serious look at PowerPivot. This is a game-changer, not just for full-stack Microsoft BI shops, but for any business that finds that their reporting datasets invariably end-up in Excel.

If you need any help evaluating PowerPivot or modelling your reporting needs in PowerPivot,  I’m for hire.

Update:

Just in case you think I’m an dimensional-model un-believer or likely to abandon my star-schema roots read this….

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

→ 10 Comments

Posted in BI, excel, PowerPivot

Tagged ,

PowerPivot VBA Refresh Code – Bug Fix

Posted on November 22, 2011 | 4 Comments

Just a quick post to alert those of you using my PowerPivot Refresh code to a bug in its “refresh a single table” logic. Under certain circumstances, linked tables (i.e. those on the “many” side of a relationship) will fail to refresh if specified individually (fine when part of a refresh all). See here for the details behind the bug, and thanks again to Rob Parker for bringing it to my attention. I’ve updated the sample code with the fix, download it here.

I’ve also tested the code against the just released SQLServer 2010 RC0 version of PowerPivot and it appears to work.

I’ve updated the equivalent PPREFRESH code in HAMMER and this is now part of the tool’s latest release (V1.3.4(Beta)). My previous post, Excel as a Book of Record, previewed the most important new commands available in this release. Alongside those, I’ve also added the following:

  • ISTABLE, if the previous COMMAND’s result  or the previous argument is not a table, this will abend the command sequence.
  • ISARG, as above, but this time checks for an argument (a HAMMER parameter may either be a table, a command or an argument- aka, an ARG).
  • ISOK, previous argument must be the string value “OK”.
  • TABLESARETHESAME, will fail if the last two tables are not identical. Intended mainly for automated regression testing.
  • ARGSARETHESAME, as above. but this time for ARGS.
  • _GUID, will return a globally unique identifier.

Download the latest version of HAMMER from here …

→ 4 Comments

Posted in ETL, excel, HAMMER, PowerPivot, VBA

Tagged , ,

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 | 3 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 …



→ 3 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