Another Big Success For The Microsoft Access Team

I know that this is supposed to be a Data-Centric Excel blog but at one time it included Microsoft Access.  I have been completely spent pissing about the way Microsoft is sending Access Client (soon into maintenance I assume – really).

But just today I stumbled on something I just could not leave without sharing .. and that is a post on the “Office Dev Center” site (which is an oxymoron if I ever heard one) .. while looking around for tools to upsize Access files to SQL Server inside a VM that only has Office 2013 Pro installed.  Microsoft dropped the Upsizing Wizard in Access 2013 because they decided to drop ADPs (which were a great example of a great technology that Microsoft quickly abandoned because it would have taken some effort to promote it) and the Upsizing Wizard apparently shared some code with ADPs.

I remember going all the way to Australia and doing a presentation on DAPs and one on ADPs, both well received and both gone … :-( .. unbelievable.

Sure I know how to move from Access to SQL Server other ways (SQL Server Migration Assistant for Access) but I was wondering if there is anything new out there.  And one of my Google results took me here:

You should all read the comments on this posting  - even if you’re only an Excel person.  Very sad to read.

THIS is what Microsoft is doing to those who have bought into their technologies and committed their business and their clients to serious solutions using Access.  The response from Microsoft is “Just kidding :-) !!

Excel VBA users and devs .. be afraid – be very afraid…. !



Posted in Excel 2013, Microsoft Excel 2013, Microsoft Access, VBA, Office 2013, Microsoft Access 2013, Access, Office Automation | 4 Comments

InfoPath Retirement Includes An Interesting Tidbit

My friend Simon Murphy noted yesterday that MS has decided to put InfoPath “down”: …

About time IMHO….

Not to take anything away from Simon’s post, I have to mention that he references an article in The Register ( that includes the following quote from the Microsoft announcement:

In an effort to streamline our investments and deliver a more integrated Office forms user experience, we’re retiring InfoPath and investing in new forms technology across SharePoint, Access, and Word, “

Yet another “Forms Initiative” ? … hmmmmm.  I will be watching this closely for the next while to see if this is going to be a game-changer Pro or Con.  We must all remember that while it’s all well and good to have data-centric spreadsheets it’s all useless without good data to rely on.

Having a capable “Forms Package” might just be your best friend going forward to allow you to control your own “outlying” data or to manage your internal “departmental” databases that contribute to your Data-Centric Spreadsheets.

Stay tuned…


Posted in Access, Excel, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, PowerPivot, Spreadsheets | 20 Comments

How Many Versions Of My Data Do I Really Want?

I got into a bit of a disagreement with someone today over the idea of Pre-Aggregation of data before bringing it into PowerPivot in Excel as opposed to bringing in all the source data for all the tables to be analyzed and then doing all the necessary aggregations exclusively in Excel using PowerPivot.

Let me make it perfectly clear that I am not pleased that there has to be a disagreement .. the fact that it looks like it is becoming one is sad.   Unfortunately I feel the need to aggressively promote what I believe is true because there are people who are in my opinion limiting the prospects for PowerPivot and Excel by their promotion of a limited vision for PowerPivot.  I know in their hearts they believe they are right – But I beg to offer an alternative view.  I believe that those who do not share my vision and I are discussing 2 separate uses for PowerPivot and thus there is less disagreement than there would appear to be.

Here’s the nub of this thing … I have a vision of PowerPivot as a “Report Generator”.  I am personally not especially interested in the use of PowerPivot for ad-hoc analysis – the proverbial “ad-hoc slicing and dicing” of data.  Oh sure I do that from time to time but mostly I develop databases for clients and the reports that they need to see on a regular basis for management.  I have found PowerPivot when combined with VBA to be an extraordinary Report Generator – second to none IMHO.  I think people will benefit from knowing how I am using PowerPivot.  Frankly, if I was to keep it to myself (which I could), then I believe that MY business would suffer.  I believe we all would miss a BIG opportunity to get more companies using PowerPivot ever day.

I have a long experience with databases (for what that’s worth) and one of the most important things I learned a long time ago is “You do not want to accumulate multiple copies of your data all over the place!” In a perfect world there should be only one version of the “Raw” truth.  Yes Corporations have moved to a Data Warehouse model for the most part and that’s awesome but to me that’s where the raw data to be used for analysis should stay. This is important for several obvious reasons that I don’t need to go into here.

One of my big problems with PowerPivot, especially the Cloud versions of PowerPivot within PowerPivot SharePoint services and now PowerBI, has always been the fact that it ends up creating it’s own private data warehouse in addition to existing Corporate data warehouses, to say nothing of the data stores of the applications themselves that create and manage the data through business activity.

Now that does NOT mean that I do NOT believe in the value of the functionality of Power BI and Microsoft’s Cloud offering for data storage and use in Browser-based Analytics or  as a source for Client-Side Analysis.   I do, however, believe that I would rather store pre-aggregated and somewhat pre filtered data “up there” if necessary.  In this way I would not have a Data Warehouse but rather a “Reporting Data Store for PowerPivot Reports” all of which would be based on a moment in time – but would only include the smallest amount of data necessary.

I have also found that by using my technique with VBA I can develop powerful Client-Server solution against SQL Server (or other database servers) without going through SharePoint or PowerBI.  But by using Pre-Aggregation and the power of the database server I can deliver solutions that refresh in seconds and that are small enough files that they can be distributed in “old-fashioned” ways if so desired – or simply published to PDF files and then thrown away.  I have trouble seeing what is wrong with that.

When I use the techniques I have been promoting here, I am able to source data from as close to the source of the data as possible, in a  timely fashion through the Client Excel, thus removing the need for all the overhead and complexity of publishing and managing my reports in the Cloud and not recreating yet another data warehouse with it’s attendant issues around timeliness, maintenance, access and management in general.  I can create a stand-alone solution that I manage and that I can make as secure and reliable as I can.  This way MY users will get exactly what they want when they want it and can rely on that…..

I do believe also that there are a LOT of developers of Corporate reports that this is all they really want to do.

I must also add that this in no way means that I think any less of the capabilities of PowerPivot because I am promoting an alternative way of using it.  Quite the contrary – it is because I see what PowerPivot is capable of that I am promoting this as yet another way to use the product, to get better use out of Excel and to give our customers (internal or external) what they REALLY want … not what I think is good for them.

Why this has to create so much negativity from certain quarters disappoints me no end. And this from people I call friends or at least friendly fellow members of a community that wants the same thing in the end.  We’re all trying to do the same thing … provide better solutions for our customers to get better results with their efforts – and to get paid for doing so of course :-) … but paid for good results!  I believe that PowerPivot is THE tool to allow us to do that.


Posted in Excel, Excel 2013, OLAP, PowerPivot, Spreadsheets, VBA | 13 Comments

A PowerPivot Conversion Success

I have just completed a major conversion of one of my “traditional” spreadsheet reporting solutions to PowerPivot for a major coal mining company.  It has been one of the most satisfying solutions I have ever done and convinces me even more than before of the importance of PowerPivot to all Excel users.

The Problem

While the older solution relied on data gleaned from SQL Server, the presentation layer relied on standard Excel formulas.  This meant that if there was ever a change, deletion or addition to the levels of the model (i.e. new mine, closed mine, reorganization, etc.) I personally had to make a detailed and frightening change to the core report that impacted levels up and down, right and left throughout the model (new Sub-Sub-Totals, Sub-Totals, Totals and Grand Totals – new variance analyses up and down through the model).  This was a tedious and painful job and one that often cropped up at inconvenient times for me -  always when I was busy working somewhere else in the world.

The second problem was that this business model was just too complex for a standard Pivot Table solution – so that was not an answer.  One thing that always makes me shake my head is how complex businesses can make their business processes and how it is usually legitimate.

PowerPivot on the other hand is capable of multi-table, DAX-driven calculations that can then be dropped into the enhanced PowerPivot Pivot Tables in a way that accomplishes exactly what the original model did – but totally data-driven and needing zero maintenance by me if any of the things I mentioned earlier happen (additions deletions, reorgs, etc.)

What I Learned

I learned (or re-learned) the following very important things in this process:

  • This shit isn’t easy!!  Anyone who says that PowerPivot makes reporting in Excel easy should move beyond Adventureworks and try doing REAL solutions for REAL businesses
  • I could not have done any of this without my knowledge of relational databases in general and SQL Server in particular.
  • “Pre-Aggregation” is critical to a successful PowerPivot solution.  The idea of bringing millions of records up into Excel and then doing all the work there is not only just not practical but simply ridiculous.  Why would you want to bring detailed transactional data into the backend of an analytical solution? For example in my solution I only cared about how much of each type of coal was mined at each mine by day and only for the period I want to analyze.  In this case it shrunk a nearly million row data table of 4 years of truck data into 22,000 rows of aggregated data for the year 2013.  I had all the info I needed and the size was manageable and most importantly the time for refresh is acceptable.  It also means that my solution can live in client-side Excel and does not need to be promoted to SharePoint to succeed – although SharePoint is going to also be part of the solution as well.
  • SQL Server Functions are the most efficient way to return data to your PowerPivot solution.  A straightforward SQL statement like

select * from fnTruckSummary(’01/01/2013′,’12/31/2013′)

is efficient to work with as you move through all the various data sources involved.  I have also found that Functions are significantly faster than Views for reasons I have yet to explain.

  • My code to change the source data dynamically through VBA works as advertised and I am finding that my personal “Best Practices” around this technique work well for me.  For example is created a table on a separate spreadsheet like this:


If I need to feed a value chosen by the user on the spreadsheet through clicking a slicer to choose a particular year of data I use a formula. So for example underlying

spFcst_Plan 2013,F above is this formula =”spFcst_Plan “&CurrYear&”,F”.

Then my code to refresh all these connections is just

    For Each r In Range(“tblConnections[Connection]“)

        Connect r.Value, r.Offset(0, 1).Value, Range(“txtProvider”).Value

    Next r

Sub Connect(ConnName, SQLText, txtProvider)

    With ActiveWorkbook.Connections(ConnName).OLEDBConnection         .CommandText = Array(SQLText)         .Connection = txtProvider         .Refresh     End With    

End Sub

Notice that Range “txtProvider” in that code is a cell reference to a cell in the model where I manage the OLEDB provider for all these links.  This allows me to easily change the Provider for all these links in one place one time.

Once I have refreshed all the data sources I set the value of a cell that is visible to all users of the model to capture the data and time last refreshed

Range(“LastRefresh”).Value = Now()

Then I save the spreadsheet and announce this to the user\


    MsgBox “Refresh Complete … File Has Been Saved With The Current Data”, vbInformation + vbOKOnly, “Data Refresh”

In this way when a user opens this file they may not need to refresh it if, for example, the first user of each day runs this refresh code (driven by a button on the first Worksheet).  This way all other users only need to open and use the file (no one really cares about the changes in the data until the end of a day anyway so today’s data means nothing to them).


I know that there are those who think that my posts are too long (??) but the fact is that I could go much, much further into the details of all this but I won’t anymore then.  Instead I will give you highlights from now on and you gotta figure the rest out for yourself, I guess.

In the end every solution is a snowflake (they’re ALL different) and all I can do to help promote this technology is give you high-end things I have figured out.  I see a lot of folks coming here but very few comments to tell me whether I’m helping or not.  That frustrates me a lot I’m afraid :-(.

I will however keep posting here because I believe that this technology is important to us all and to our clients internal and external.

I also believe that most of those posting about PowerPivot and Excel futures are ignoring the capabilities available to client-side Excel users in a way that risk the acceptance of PowerPivot broadly.

Funny final story …. Whenever I mention to ANY of my clients that my analytical solutions in Excel using PowerPivot can be published to a SharePoint “Gallery” and shared easily by Senior management and others throughout the organization they always say “That’s the LAST thing I want to do !!! I want this available only to those who can get REAL business value out of this !! I can’t spend all my time answering stupid questions from people drawing stupid conclusions from all this !! “  I hear this EVERY time I mention the SharePoint Gallery option…..  Too good – and makes too much sense.  Data distribution has to be controlled as well.


Posted in Business Intelligence, Excel, Excel 2013, Microsoft Excel 2013, Office 2013, Office Automation, OLAP, PowerPivot, Spreadsheets, VBA | 12 Comments

Smurf Is Back

After a long time off the radar my friend and kindred spirit Simon Murphy is back posting about Excel issues again.

I am sooo glad that he is back and have supported his posts with a few comments of my own.  I hope my comments support his arguments about the troubling trends out of Microsoft in their offerings called “Office” (which is actually now really called “SharePoint” I guess).  This is especially true of Excel and Access and Office Automation in general.

Oh sure, I’m excited about the potential of PowerPivot, but without a strong and promoted client Excel and client Access, and without a commitment to either VBA or another equally capable (or even better alternative – unlike their JavaScript plans) I think even PowerPivot will not lead to better, smarter, more productive and more “used” Excel solutions.  Rather I worry that the downward trend in Excel acceptance is a big loss for the business world everywhere and can only continue until they end up moving even PowerPivot away from Excel into it’s own “Cloud” application.  The marginalization of Excel may not be reversible anymore though.

I place this downward trend directly at Microsoft’s doorstep.  Only they can fix this and they will have to accept responsibility when people look back at the result.  The resemblance to this situation to that of Lotus Development back in the late 80′s is scary.

Here is a link to Simon’s blog.  Please read the last few month’s posts there for sure:


Posted in Excel, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, PowerPivot, Spreadsheets, VBA | 7 Comments

The Case For Hierarchies in PowerPivot

It’s interesting to me how the definition of “Business Intelligence” (BI) seems to have evolved over the last few years as the various providers of data analytical technologies try to differentiate themselves from their competition. I think some companies have been playing fast and loose with the acronym “BI” to the point where it has muddied the waters quit e a bit – and that’s probably their plan.

I really don’t care what they want to call what they offer, but one thing I do know is that the PowerPivot Add-In for Excel and SharePoint is a “Traditional” OLAP tool designed strictly for the “Slicing and Dicing” of numeric data sourced from relational On-Line Data Processing (OLTP) database that live on corporate networks. PowerPivot takes that data and produces a virtual “Cube” within an Excel file (and ultimately up on SharePoint) of the data it imports. All this is exposed in Pivot Tables on Excel worksheets with the Outline form of presentation typical of Cube-based OLAP analysis.

I personally first got involved with OLAP data in a project 20 years ago wherein I used a Sybase OLAP server as the source for an Excel VBA-driven model for financial analysis. What struck me as the most significant feature of the OLAP database was how it intrinsically knew not only the structure of the business organization (corporate, region, country, district, city, location) I was analyzing, but it also knew the relationships between aggregate time periods from month to quarter to year) in such a way that I didn’t need to ever worry about what axis of my data was a parent for or a child of. In fact it was physically impossible for me to mistakenly lay out my hierarchies in an illogical way. In addition my users only needed to know that there was or was not a parent or a child for the level I was at in the database’s structure.

There are those who will say that this is a bad thing because it limits the user’s flexibility but that misses out on the fact that:

1. Hierarchies are just an additional feature of an OLAP cube and do not restrict users from doing free-form analysis of any kind and

2. By defining Hierarchies in an OLAP database or PowerPivot data model “The Business Model” across organization, products, time and date, etc. can be controlled, managed and most importantly changed at source and reflect immediately in your BI data summarizations and queries in a safe and consistent way.

2. If a user (or developer) takes advantage of hierarchies in their OLAP database they will be able to get the information out of the database much quicker, much easier and with consistency in reports. Believe me, not only is consistency what senior manages want but that is what they really need. If I’m an EVP or Director and every month the report I’m getting is designed differently because somebody decided to produce some innovative new report using free-form PowerPivot I’m going to freak right out and get someone else to get me the report I REALLY need. It is legitimately important to compare apples to apples every day or month.

So it is my opinion that Hierarchies are a key capability of PowerPivot and that every user or developer needs to know how to use them.

I will discuss this more in the future but in the meantime give Hierarchies and Star Schemas a look …   You will be better for it :-).


Posted in Analysis Services, Business Intelligence, Excel, Excel 2013, Hierarchies, Microsoft Excel 2013, OLAP, PowerPivot, Snowflake Schema, Spreadsheets, Star Schema | 1 Comment

Awkward Spreadsheet Snafus From BBC News …..

Today the BBC News website has a little feature on spreadsheet “snafus”.

I doubt if the writer of that headline realized how well the acronym “snafu” applies to spreadsheet usage everywhere (… Too true.

It’s about time people took the risks of spreadsheets seriously (although most still won’t).  I only hope that some corporations or governments (and especially Microsoft) will take this issue to heart and rather than banning all spreadsheets (which wouldn’t surprise me – until they realize they really can’t) make the effort to develop the protocols, best practices and skills to get the safest results out of this irreplaceable technology.

If you need help, and you see a value in getting it, there are many of us out there who can help you with this risk – for a respectable fee of course.  

A large part of the problem IMHO is that because Excel is considered a “loose cannon” in organizations and as such has not had any value placed on getting genuine professional help on their spreadsheets.  Corporations do no training past the intro level and never bring in spreadsheet “experts” to upgrade their models and the skills of their staff.

To me it is this attitude that is the biggest threat to Microsoft’s whole BI “story” wrapped around using Excel as the source for Enterprise BI solutions.  Although it’s undeniably great technology, it’s going to be a tough sell IMHO because it is in the Excel spreadsheet. Frankly Excel is the Rodney Dangerfield of the technology world .. it gets no respect.

This is a serious problem that smart companies must address – and soon – before their auditors shut down all spreadsheets and before anyone makes a SERIOUS commitment to PowerPivot and SharePoint’s PowerPivot based BI offering.  Sarbanes-Oxley was a shock to the spreadsheet ecosystem but didn’t have the impact it should have IMHO. There is however a rising level of heat on this issue now (thanx largely to the Reinhart and Rogoff  “Austerity SNAFU” – look it up)

If you love your spreadsheets it’s time to get serious about them or you will lose them and the result will be chaos – even worse than the chaos that some see in today’s spreadsheet world.


Posted in BBC News Spreadsheets, Excel, Excel 2013, Microsoft Excel 2013, Office 2013, PowerPivot, Spreadsheets, VBA | 1 Comment