Category Archives: HAMMER

The Model of a very modern PowerPivot – without the add-in

My last post demonstrated a method by which explicit PowerPivot DAX measures could be created in any version of Excel 2013 without  the PowerPivot add-in. Here’s another method, this time using Excel 2010’s PowerPivot add-in (available for all versions of 2010) to design the model and the OpenXML SDK to copy the resulting model to an Excel 2013 workbook.

I had thought originally that this wasn’t possible; when I first tried it, the model copied OK but the workbook connection couldn’t access it. Turned out, this was due to using a V1 model that although upgraded to V2 still used the “Sandbox” cube name rather than the now expected “Model” name. So, all models must be created in PP V2 for this to work.

Also, the “receiving” Excel 2013 workbook must be primed with a data model (easiest way without the add-in is to create two tables and link via relationships option, or just use the Model_2013_template workbook provided).

You can download a POC workbook Example_CopyModels_no_Addin demonstrating this method from here. 

Note:

You’ll need to modify the paths of the example workbooks to suit your environment.

Also, if you copy the workbook out of the downloaded folder, make sure to copy the datasmith xll of the right “bitness” and copy the DocumentFormat.OpenXml.dll or ensure one is available on the GAC or PATH.

Before using the datasmith xll for the first time, right-click and unblock, this can also be a good idea for workbooks with external connections, otherwise Excel can sometimes keep certain functionality “locked” 1st time you access such files.

Having copied the model into the Excel 2013 workbook, save & re-open before making any further changes.

If you look  at the code you’ll see it’s also possible to copy models for 2010 to 2010 and from 2013 to 2013 or indeed from 2013 back to 2010. This general coping of models from workbook to workbook was the use-case I had in mind when I first discovered this (not as a way of getting around lack of add-in). Very useful during model development allowing different views to be tried against a first developed (or commonly used) data model. Could also be used when live to allow a single model-refresh to be used by many “view” workbooks.

Have fun.

Advertisements

DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in

I mentioned in my last post that it was possible to create DAX measures (and indeed Calculated Columns, KPIs etc.) without using the PowerPivot add-in but instead using AMO to do so. The facility has always existed in HAMMER but only as a hidden option that I often used to iterate quickly through a set of measure variations (or load standard sets) during model development; but I wasn’t 100% convinced it was worth the effort to make it more production quality in order to add it to HAMMER’s public interface. But, with the extraordinary removal of PowerPivot add-in as an option for all retail versions and all O365 SME versions of Office 2013, I decided to revisit the code and make it available as a new HAMMER command “PPMAKEMEASURES”.

This zip file contains an example of an Excel 2013 workbook (drawing data from a Northwind oData public feed) with DAX measures created by HAMMER rather than via the PP add-in.

The HAMMER formula and associated “list-of-measures” table can be found within.To use the formula on this, or another workbook, you need to activate HAMMER.xll (or HAMMER-64.xll, if using Excel 64bit) by either opening like a normal workbook (will not be installed as a permanent add-in) or put it in your add-ins folder and register as per a normal XLL (you can also use VBA’s registerDll functionality for automatic only-this-instance registering).

The first time you apply this method of DAX measure creation to a workbook, you should save, close & re-open the workbook; otherwise, you run the risk of the created measures being overwritten if you subsequently create an implicit measure (by moving a field to the Values section of a pivot table). If saved, this will no longer be a problem. If you use this in Excel 2010 you’ll always need to save,close & reopen in order to see the new measures. (Also, in 2010 you’ll need to precede any PP commands with the PPCONNECTION command e.g. =HAMMER(“PPCONNECTION”,Table2[#All],”PPMAKEMEASURES”) UPDATE: It appears you also need the PPCONNECTION in 2013 although I don’t appear to need it in my O365 version!).

To get a list of DAX Measures within a workbook use the PPLISTMEASURES command (e.g. =HAMMERToRange(“PPCONNECTION”,”PPLISTMEASURES,”Sheet2!A1″) will paste a table to Sheet2 starting at cell A1). The table will be in the format required by the PPMAKEMEASURES command and is intended as a quick way of transferring measures from one workbook (even if 2010) to another. (Note: this will list both implicit and explicit measures, best avoid creating implicit measures using this method!)

The table is position based (i.e. heading values not important, just as long as there is a heading line); columns represent – Table Name, Measure Name, DAX, Format and Visible. If the 3rd column, i.e. the DAX expression, is blank, the measure will be deleted, otherwise, any existing measures will be deleted and replaced with the new definitions.

This has only been tested against  O365 versiond of Excel 2013, retail versions may be different. I needed to hard-code the location of the AMO & ADOMD dlls (unlike 2010 they’re no longer in GAC and are not on a PATH – well, likely they are, but only in the virtual file system now used by Office).

The location for the AMO library for 32bit O365 Home Premium is  “C:/Program Files/Microsoft Office 15/root/vfs/ProgramFilesCommonX86/Microsoft Shared/OFFICE15/DataModel/Microsoft.Excel.AMO.dll” let me know if your setup is different. (UPDATE: As Marco discovered, enterprise and most likely retail versions do not use VFS, I‘ll post a fix to this tomorrow). UPDATE: PATH issues with none O365 versions should now be fixed.

If you’re a SME or sole-trader and you intend to use PowerPivot extensively you really need to purchase (as I have) the O365 Professional Pro subscription for your “datasmiths”. The rest of your crew can use Retail or O365 offerings (as they can still “consume” DAX powered models). But if, for some reason, you can’t or won’t purchase ProPlus, or you just want to take advantage of the fast build-redesign-build cycle offered by PPMAKEMEASURES, then hopefully, this will be of use.

Oh, and Happy St. Patrick’s day!

SAP RFC_READ_TABLE functionality in HAMMER

The code below is a typical VBA routine used to fetch data from SAP into Excel.

It uses the “SAP.Functions” COM object as exposed by the SAP GUI Client, fetching the data via RFC_READ_TABLE; an automated SE16 in effect.

The credentials required are the same as those you would use to log into your desktop client, and whatever internal tables you can see via SE16, those same tables will be fetchable via RFC_READ_TABLE.

This automated fetching of data is ideal when some self-service reporting is a requirement (you know, standard DW extracts offer most of what you need, but there’s always something missing 🙂 ).

I figured this would be a good candidate as a HAMMER command. Not just to take advantage of HAMMER’s natural table handling but also its multi-threading capability. Being able to spawn one or more background threads (or delegate to HAMMER.exe command line process(es) ) would be very handy for SAP datasmiths.

Problem is, the code below works, and I’ve converted it to VB.NET, made it more generic and added it as a HAMMER command; but I can’t test it, as I no longer have access to a SAP R3 Instance!

The command SAPREADTABLE takes three parameters:

  • a CSV list of SAP logon credentials: System,Client,User,Password,Language
  • a CSV list of table information, 1st argument the table name, the rest field names e.g. KNA1,KUNNR,NAME1,NAME2,LAND1
  • a filter statement (like a SQL where) e.g. LAND1 in (‘DE’,’NL’)

Example:

“Test SYS,600,tom,pwd,EN”,”KNA1,KUNNR,NAME1″,”LAND1 = ‘DE'”

UPDATE: April 29, 2012

Could somebody with access to SAP R3 test this out for me?  Done, tested (found a small bug, now fixed) and working (thanks to a kind person who allowed me access to a test server, you know who are, thanks again).

Fetch the modified latest version below (fixed bug that produced an extra blank column and extra blank row in result table, my typical “1 off” bug when converting from VBA to VB.NET, obviously I’ll never learn 🙂 )

If you get a scary “ABEND – SYSTEM FAILURE” error, don’t panic you haven’t broken the company ERP system, it’s usually due to a malformed filter statement e.g. LAND1=’NL’ (no spaces) rather than LAND1 = ‘NL’.

To download the latest version of the code, go to this page on my website.

Follow the HAMMER tag on  this blog for information on commands and examples (best start with the oldest and work forward …)

Need a pure VBA version, here it is :

SAP RFC_READ_TABLE VBA Example:


Option Explicit
Option Base 0

Public Function RFC_READ_TABLE(tableName, columnNames, filter)

Dim R3 As Object, MyFunc As Object, App As Object

' Define the objects to hold IMPORT parameters
Dim QUERY_TABLE As Object
Dim DELIMITER   As Object
Dim NO_DATA     As Object
Dim ROWSKIPS    As Object
Dim ROWCOUNT    As Object
' Where clause
Dim OPTIONS As Object
' Fill with fields to return.  After function call will hold
' detailed information about the columns of data (start position
' of each field, length, etc.
Dim FIELDS  As Object
' Holds the data returned by the function
Dim DATA    As Object
' Use to write out results
Dim ROW As Object

Dim Result As Boolean
Dim i As Long, j As Long, iRow As Long
Dim iColumn As Long, iStart As Long, iStartRow As Long, iField As Long, iLength As Long
Dim outArray, vArray, vField
Dim iLine As Long
Dim noOfElements As Long

'**********************************************
'Create Server object and Setup the connection
'use same credentials as SAP GUI login
On Error GoTo abend:
  Set R3 = CreateObject("SAP.Functions")
  R3.Connection.SYSTEM = ""
  R3.Connection.Client = ""
  R3.Connection.User = ""
  R3.Connection.Password = ""
  R3.Connection.Language = "EN"

  If R3.Connection.logon(0, True) <> True Then
   RFC_READ_TABLE = "ERROR - logon to SAP Failed"
   Exit Function
  End If
'**********************************************

'*****************************************************
'Call RFC function RFC_READ_TABLE
'*****************************************************

  Set MyFunc = R3.Add("RFC_READ_TABLE")
   Set QUERY_TABLE = MyFunc.exports("QUERY_TABLE")
   Set DELIMITER = MyFunc.exports("DELIMITER")
   Set NO_DATA = MyFunc.exports("NO_DATA")
   Set ROWSKIPS = MyFunc.exports("ROWSKIPS")
   Set ROWCOUNT = MyFunc.exports("ROWCOUNT")

   Set OPTIONS = MyFunc.Tables("OPTIONS")
   Set FIELDS = MyFunc.Tables("FIELDS")

   QUERY_TABLE.Value = tableName
   DELIMITER.Value = ""
   NO_DATA = ""
   ROWSKIPS = "0"
   ROWCOUNT = "0"
   OPTIONS.Rows.Add
   OPTIONS.Value(1, "TEXT") = filter ' where filter

    vArray = Split(columnNames, ",") ' columns
    j = 1
    For Each vField In vArray
        If vField <> "" Then
            FIELDS.Rows.Add
            FIELDS.Value(j, "FIELDNAME") = vField
            j = j + 1
        End If
    Next

   Result = MyFunc.CALL

   If Result = True Then
     Set DATA = MyFunc.Tables("DATA")
     Set FIELDS = MyFunc.Tables("FIELDS")
     Set OPTIONS = MyFunc.Tables("OPTIONS")
     R3.Connection.LOGOFF
   Else
     R3.Connection.LOGOFF
     MsgBox MyFunc.EXCEPTION
     Exit Function
   End If

  noOfElements = FIELDS.ROWCOUNT
  iRow = 0
  iColumn = 0
  ReDim outArray(0 To DATA.ROWCOUNT, 0 To noOfElements - 1)
  For Each ROW In FIELDS.Rows
    outArray(iRow, iColumn) = ROW("FIELDNAME")
    iColumn = iColumn + 1
  Next

'Display Contents of the table
'**************************************
iRow = 1
iColumn = 1

For iLine = 1 To DATA.ROWCOUNT

       For iColumn = 1 To FIELDS.ROWCOUNT
         iStart = FIELDS(iColumn, "OFFSET") + 1
    '       If this is the last column, calculate the length differently than the other columns
         If iColumn = FIELDS.ROWCOUNT Then
            iLength = Len(DATA(iLine, "WA")) - iStart + 1
         Else
             iLength = FIELDS(iColumn + 1, "OFFSET") - FIELDS(iColumn, "OFFSET")
        End If
    '       If the fields at the end of the record are blank, then explicitly set the value
        If iStart > Len(DATA(iLine, "WA")) Then
             outArray(iRow, iColumn - 1) = Null
        Else
            outArray(iRow, iColumn - 1) = Mid(DATA(iLine, "WA"), iStart, iLength)
        End If

       Next

       iRow = iRow + 1
Next

RFC_READ_TABLE = outArray
Exit Function

abend:

RFC_READ_TABLE = Err.Description

End Function

Public Sub Paste_sheet1()
Dim lArray
Dim lAdjust As Long

lArray = RFC_READ_TABLE("KNA1", "KUNNR,NAME1,NAME2", "LAND1 = 'DE'")
If TypeName(lArray) = "String" Then
    MsgBox "Problem calling RFC is it here " & CStr(lArray)
Else

    ' adjust if zero based array
        If LBound(lArray, 1) = 0 Then lAdjust = 1 Else lAdjust = 0

    [Sheet1!A1].Resize(UBound(lArray, 1) + lAdjust, UBound(lArray, 2) + lAdjust) = lArray

End If

End Sub

Excel – as a fractional horsepower HTML5 server

You may have been wondering what’s the driving force behind the various changes I’ve made to HAMMER over the last few weeks,  namely threading support, a simple HTTP server and JavaScript. The driving force is to better position HAMMER (and through it, Excel) as a fractional horsepower HTTP server (see this post for more on fractional horsepower engines). Features such as threading and JavaScript are useful for many things; threading, for example, makes debugging scripts easier (see the Debug sheet and code in the sample InProcess_oData workbook) and also makes long running ETL processes easier to control and monitor. But, enabling the set-up of simple task-specific behind-the-firewall data servers, with as little ceremony as possible, is the ultimate goal.

But why, what purpose do these mini servers serve?

They’re obviously not intended as beyond-the-firewall public servers, they wouldn’t scale or be secure enough for such a task. Providing in-house feeds to other web enabled clients would be a more sutable task. For example, providing a feed from a “hub” workbook containing a PowerPivot model to other “spoke” workbooks (PowerPivot enabled or not) – a poor man’s alternative to doing the same via a SharePoint farm, if you like.

But it’s another seemingly unrelated technology that’s really sparked my interest in perfecting the fractional horsepower server: HTML5.

Generally when people think of HTML5 (if indeed they think of it at all), it’s mobile platforms that come to mind. (As it’s primarily Apple and Google, through their shared WebKit browser core, that have driven the development and adoption of HTML5). So what has this to do with Excel and the boring, but oh so profitable world, of corporate IT? Well, next time you’re in any spot where the global mobile workforce gathers, airport waiting lounges, hotel lobbies, etc. look at the technology kit that they’re using.

Only a few years back, the vast majority would have had a Windows laptop, if indeed they had any “data processing” device. Now, many, if not all, will either be using a smart-phone or a tablet device (iPhone or iPad but also increasingly Android powered phones/pads). All of these workers are still likely to have a laptop in a carry case or back in the hotel room, and certainly will have a laptop/desktop or their workplace desks. But on the move mobile is where it’s going.

So how do front-line datasmiths respond to this? Currently many of us build reporting solutions and really-simple-systems using Excel as the delivery agent, moving all or part of this to a mobile delivery agent will inevitably become increasingly attractive and/or demanded.

MS is already responding to this, e.g. PowerPivot and standard Excel spreadsheets are capable of being rendered via SharePoint’s Excel Services. But what if you don’t have access to a SharePoint farm, or you need a more robust UI, such as could currently be built using a VBA/.NET add-in? This is where HTML5 and fractional horsepower servers come in.

For me, there are two aspects of HTML5 that I think will make developing and deploying such “systems” possible and relatively easy:

It’s HTML5’s local storage, that’s makes a fraction horsepower server scenario possible. In traditional web apps, it’s assumed that:

  • 1st the client is always connected to a server,
  • and that the server provides both the layout (html, javascript, css) and all the data (REST APIs etc.) that the web app consumes.

Now with HTML5 apps, the client doesn’t need to be always connected to its main server or to its data server(s). It can go offline, or it can stay connected to its main server (perhaps a public-internet-facing S3 hosted domain), and every now and then make contact with one or more data servers (which can be safely positioned behind the firm’s firewall).

An example:

A firm’s Sales Reps come into the office every Friday for wash-up meetings, to record sale completions and to get their journey-plans for the following week.

Each rep has a desktop computer, where they interface with the firm’s various systems. One such set of “systems” are PowerPivot based models that report on the year’s forecasts and actual sales.  Part of the process of preparing for a sales visit is creating a set of sales reports for each customer to be visited, last year sales, this year’s targets, and so on, sourced from the various PowerPivot models. Although the production of the reports is largely automated via Excel macros, currently the resulting sheets have to be printed.

There’s been talk of company supplied laptops for years and the budget for them has now at long last materialised. The reps however, have expressed a preference for using iPads when customer-facing, mainly because the sales conversation often require not only presenting the prepared sales reports and charts but also flicking through many of the 100 odd product manuals. Being able to hand around an iPad with high quality glossy images (and videos) of this year’s new products, plus a sales projection chart for the same products, is, they contend, a winner.

A simple mobile sales reporting app is therefore developed (using the JoApp framework and Google’s Chart API and this pure JavaScript columnar database) to cater for the type of sales reports the reps require. The existing Excel automation code is enhanced with a HAMMER server. The reps new iPads’ web-apps are configured to automatically download prepared and ad-hoc reports when they log-in to the office network.

This has worked so well, the reps now want the ability to feed back sales target changes, that they also wish to record on their iPads via another really-simple-system, to their personal Sales Plan workbooks.

Is this as simple as using a “pure” spreadsheets solution, no, but it’s nearly as simple as building a VBA/.NET powered Excel application to do the same. The problem with many Excel “applications” is that they often push Excel beyond its “comfort zone”. The benefit of a hybrid solution like above, is that Excel gets used where it’s really useful and powerful (reports and models, data gathering and dispersal) while at the same time taking advantage of the freedom and cost-benefits (and fun!) of the emerging mobile web.

oData in-process Server – auto refreshing PowerPivot linked Excel tables

To test out the new threading facilities in HAMMER I picked on that perennial question that I get asked “Can your PowerPivot refresh code, refresh linked tables?” to provide me with a suitable task.

As regards the original code, the answer is no; linked tables cannot be refreshed via the XMLA method I use. You could of course, simply automate the export of the Excel tables to, say, a CSV format and then refresh from the disk. Biggest problem with that approach is PowerPivot’s insistence on using absolute file addresses, which makes sharing the resulting workbook more cumbersome (requires that the saving folder on each machine be the same). Another solution is to use some variation on SendKeys to automate the “button push”, can be done, but pretty it’s not.

So what’s an automate’r to do? Use PowerPivot’s oData import facility, that’s what.

In a previous example I had demonstrated using oData to provide a feed from a master-workbook to client work-books, so I already had code in Python to generate a simple oData feed (it’s essentially an ATOM feed; yeah, the same format used by many blog engines (usually alongside RSS2.0), and offered as a output format by many web services, Twitter and Google Docs, for example).

Rather than using HTTPlistener (which is very powerful, but requires Admin privileges) this time I used HAMMER’s internal simple HHTP/1.0 socket based server (which is multi-threaded and uses callbacks to communicate with either Python or Javascript GET/POST handling functions). Using a PYTHONTHREAD command enabled me to spin of the oData-generating-code as an in-process server running on an independent thread; leaving the main Excel thread to fetch the necessary ranges from the workbook and then issue a PowerPivot refresh command (both of theses operations require being run single-threaded and in the main thread).

In PowerPivot itself, the range tables are fetched using a “127.0.0.1:8081/range/” endpoint.

So, for example, to fetch a table from a range whose top-left-hand-corner is Sheet2!A1, use the URL “127.0.0.1:8081/range/Sheet2!A1”.

For an Excel 2007/2010 table named InvoiceHeaders use “127.0.0.1:8081/range/InvoiceHeaders[%35All]” (the %35 is URL encoded “#”).

While the workbook is in “manual mode”, the in-process server will fetch on-demand any valid tabular range (but be careful not to “lock” the workbook, by, for example, editing a cell, while this is happening).

When in “automated refresh mode”, the VBA code will lock out any on-demand refreshes (it’s best in any case, to close PowerPivot’s window before doing any XMLA refreshes); then use the list of ranges provided to fetch the tables required, and initiate an auto-refresh.

Everything should go smoothly as long as the list matches exactly (case sensitive) the URLs assigned to tables with PowerPivot.

To try this out, download the latest version of HAMMER, go to the distribution folder, open the InProcess_oData workbook.

In the Control sheet, click the Enable HAMMER button, followed by the Start oData Server button. If you think port 8081 is likely to be already in use on your PC, go to the PythonCode sheet and change to a free port.

Then go to the Data sheet, change the tables, and refresh PowerPivot using the “arrow” button provided.

Make sure to click the Stop Server button, before exiting Excel, otherwise the port-listening thread will keep Excel alive in the background.

Of course, auto refreshing same-workbook “linked” tables is but one use this could be put to. Other possible uses would be taking advantage of Excel powers that the PowerPivot import facility still lack, such as XML Maps. Or, using 3rd party libraries or bespoke code to access “non-standard” (i.e. the majority that don’t use ATOM!) web services or data sources (such as XBRL).

Have fun…