The Microsoft Rapture

Read this link before reading my comments here:

http://www.geekwire.com/2014/internal-memo-microsoft-cut-external-staff-18-months-requiring-six-month-break/

I believe that this (even more than the 18000 announced last week – most of whom are at Nokia) will force a HUGE reorg of Microsoft before the end of 2014 or at the latest the end of their Fiscal 2015 (June 30, 2015).

I know a lot of “V-”s there and have worked with them for ten years now.  Take all of them away on Jan 1, 2016 and the company will simply stop.  Like after the Rapture ….

So watch out for something BIG before then.

But this cannot be good for the parts of Office that I mostly care about (Excel and Access, data connectivity and automation) as this may just be a great opportunity to further the diminishment of Excel and Access under cover of the reorg.  I think there are few left in there who will speak up now for client versions or for “real” automation and the great BI “story” will likely get seriously watered down.

The insecurity in the Office team that this will bring can only mean more cuts and the remaining staff focused even more exclusively on “Office in the Cloud”. Hmmmm …

It’d be nice to hear someone other than just Tim Rodman to comment on this :-) ,,

Posted in Access, Analysis Services, Business Intelligence, Excel, Excel 2013, Microsoft "V-", Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, OLAP, PowerPivot, Spreadsheets, Uncategorized, V-, VBA | 4 Comments

Data Access Is STILL The Problem

Below is a replay of a posting I made here waaaay back in 2011.  It discusses an issue that is not only STILL a problem for people trying to help businesses analyze their data but that is now exponentially worse and is likely never going to change except in some Corporations who “get it”.  Even those companies however are just one CIO away from going backward in a big hurry so that there is really no happy ending here.

The fact is that while Microsoft has gone out and muddied the waters with all their BI offerings since 2010 (PowerPivot, PowerQuery, PowerView, Power BI and of course whatever the heck Office 365 really is ;-) ).

At the same time they have made a decision to promote the concept of “Self-Service BI” where the poor misguided end-user will create their own “Solutions” by hand … as opposed to promoting a more “Professional” approach to report development based on the theory that most consumers of Corporate data actually have REAL jobs to do rather than building models and changing them annually, every month or quarter or year and only have a peripheral knowledge of data, relational databases, OLAP, etc.

Then there is the little issue of getting at the data.  I have spent the last three years doing project in the U.S., Canada and Europe where EVERY single project ended up over-budget in time and money strictly because of a complete lack of cooperation (usually expressed as contempt) from the “Gate Keepers” I referred to in 2011.

1. I have been told that I cannot have the data because my client would use the data against the owner of the database.

2. I have been sworn at and told to piss off

3. I have been told how I should build my solution by little people who know nothing about what I’m trying to accomplish or that I have 30 years of experience designing databases myself and am only asking for their data not their advice.

4. I have spent days and days designing the necessary SQL to clean the crappy data they gave me to get the best possible result only to have the source changed on my arbitrarily with no warning or advisement.

5. I have been told by clients that after all that the data is “good enough” when it definitely is NOT…

6. I introduced one developer of a huge, slow MYSQL database to the concept of Indexes but had to jump through hoops to get him to even let me TRY one. He became a true believer immediately of course.

7. On the other hand I have been often unable to use corporate data because of poor database design, lack of indexes and simply because those who designed that magical Data Warehouse decided that the important data they would collect did not include the data I really needed and there was no way to get the data I wanted added to the DW … thus end of project.

8. I have been contracted knowing that if IT ever found out what we were doing it would be over.  Under the Radar doesn’t work when integrating Corporate data I’m afraid.

No, things are not getting better, they are getting MUCH, MUCH worse.

And in their inimitable way Microsoft continues to live in a dream world where everyone has rights to perfectly organized corporate data at a whim, where the user themselves can figure out all the twists and turns of a complex data-driven analytical solution with no help from anyone with any experience in how to do such a thing.

In fact the trend is now towards Departments and end-users being refused the budget to contract any external resource to help them … meanwhile thanks to Microsoft they really believe they can do it all themselves with zero help from IT (actually obstruction) or from any outside resource.  Many are taking useless training courses that cannot possibly fill a user with all the knowledge they need and more likely will set the user back to their company with lots of enthusiasm and promising self-service solutions that are doomed to fail – This stuff is NOT easy !!.  Sorry :-(

So please read what I said in 2011 and think about how this can all be worked out if the powers that be could only stop living in dream-land talking to CIO’s and Directors of IT and instead talk to the real people with real needs or real skills and encourage an environment where this whole BI thing can actually work.  And don’t get me going on how much all this BI noise has actually marginalized Excel development overall, stopped any effort to promote Excel Best-Practices to the point where Excel itself is actually at risk of becoming an unacceptable technology for use for any serious business analysis.

And then what?

Dick

Data Access IS The Problem

2011/10/26 

I have harped on this here before, but once again my blood-pressure is rising because I have seen a simple, efficient use of the powers of Excel marginalized by a client’s inability to get support and cooperation from the “Gate-Keepers” (“Crypt-Keepers” ?)  of corporate data stores.

The Gate-Keepers are often corporate DBA’s who are deathly afraid that users might hurt themselves if they actually get the data they need to help them analyze performance and plan their business futures (even though they are already doing it mostly in “crappy”, dangerous, silos of user-designed and maintained spreadsheets already anyway).  The rest are the makers of third-party accounting or BI software who’s sole motivation is simply to get total control of the data and the process so the business is forced to keep paying them forever and ever … By forcing BDMs to flail along with stand-alone spreadsheets without direct or reliable integration with REAL corporate FACTS they are exposing the business to risks that may or may not be large. 

Regardless, these processes are inefficient at best and frustrating to users and their bosses no end.  The efforts to eliminate spreadsheets altogether (which is the unspoken goal of most IT “Professionals” anyway)  simply has not and will not work.  That is unless they can succeed in moving everyone to the browser-based “calculators” being offered as Excel alternatives by Google and even Microsoft and eliminate the powerful Office Client Excel that has so much power and capability.  That would be unconscionably stupid.

In the case that has me worked up today it is data being collected by a 3rd party software provider who takes simple, straightforward collected data and presents it to the user in an awkward, complex and generally useless GUI in a browser that takes otherwise logical and straight-forward data and makes the export of the data to Excel a painful, inefficient, and in the end unusable, process – thus making use of their data outside of their interface (which they will sell the client and reporting module BTW :-) ) impossible. I have run into this in the BI “Universe” as well where at one client we have been trying to get the data we need for a critical business analysis tool in Excel for TWO YEARS to no avail. 

I have found more and more cases where DBA’s will simply not allow departmental users or developers access to the very data they need to run their businesses while it’s being collected and collected but is being used for absolutely nothing. To me the efforts made by DBA’s and 3rd Party software developers to prevent the effective import of corporate data into Excel is a major impediment to Corporations.  Tragically these Corporations don’t even realize it’s true or how much inefficiency and extra cost they are accepting as normal.  BDM’s as a rule defer to the “Professionals” in their IT brain’s trust or worse they are afraid to cross IT (like you might not to complain to the Police for fear of repercussions).  

It is truly a tragedy. We should all remember that it is THE BUSINESS that matters, not the careers of the IT “professionals”.  And we are supposed to be working at using technology to make business processes better and more flexible not less.  We are servants to the Business not the other way around and whenever that gets forgotten we all lose.

Dick

Posted in PowerPivot, Excel 2013, Microsoft Excel 2013, Microsoft Access, VBA, Office 2013, Microsoft Access 2013, Access, Excel, Spreadsheets, Business Intelligence, OLAP, Analysis Services, Office Automation | 5 Comments

Sorry Mr. Nadella, But What We REALLY Need Is Automation

I just spent a couple of my valuable hours perusing the presentations of the recent Build Seminar in San Fran ….

http://www.buildwindows.com/

I am going to try to be as concise as possible on my take-away.  I could write pages and pages explaining how I really feel about the direction Microsoft is taking but I will try to be brief ..

1. The Office content consisted of one session

Build3http://channel9.msdn.com/Events/Build/2014/3-568

and apparently it was the final session of the event (quite a statement there)

2. The content of this presentation reflects exclusively the ability to append functionality to the Task Pane in Excel or in an Access Web App (as opposed to client) using Apps for Office.

3. This content consists of in-situ add-ins that can be included inside the Window of any Office app (PowerPoint, Outlook, Excel. Word and Access Web Apps)

4. I have been here before several times – starting in the late 80′s the first time a Microsoft rep showed me how I could embed an Excel Worksheet inside a Word Document (a technology that still sucks btw ;-)) – In the late 90′s there was a big push on adding functionality to the Word and Excel Task Bars exactly like this.  This went absolutely nowhere.  I guess all the people who were involved in that attempt have left the company and the new people have decided to try once again.  Isn’t there an old saying about that :-) ?

5. On top of all that, implementing this new technology involves implementing these “solutions” using Apps For Office in Visual Studio.  Visual Studio, a haven for Excel lovers if I’ve ever seen one…  What is wrong with an in-file automation technology or at least an add-in like capability that can travel with a file or be referenced externally?

6. There is a reference at the last minute to “old VBA” and its limitations and how this new exciting capability opens the World to developers in Office - apparently.

So if VBA is considered an old and obsolete “file-based” technology I would REALLY like to hear what they are planning for true “Automation” of Excel?  By that I mean a true macro language that is designed specifically ONLY for automation of the functionality that is inherent to Excel… not a loose binding of a bunch of “cutesy” external apps nobody is asking for that I would refer to as Add-Ins that do good “Demo”,  rather than Real Business.

If Microsoft has decided that VBA is dead because they have fired everyone who ever worked on it all those years ago then I call for them to replace it with something comparable asap.  I was recently surprised to find that a VBA app I wrote on my PC in Excel 2013 works just fine on my client’s Mac using Office for the Mac …  If they’re capable of writing a version of VBA for the Mac (which I assume is not COM-based) then Id like to know what’s stopping them from building a version to run anywhere?  My instincts tell me it is not technical as much as it is a conscious decision on the part of Microsoft  to downgrade the capability of Excel to something little more than a grid app to produce nice demos with pretty graphs.  I have no idea where the PowerPivot and PowerQuery technologies fit in the Automation story of Excel and they just scream out for that in my opinion.

IMHO, while there are many out there who individually manipulate spreadsheets doing ad-hoc reporting and analysis, I believe that there is actually an even bigger need for well-designed models built to be robust and reliable and able to perform repetitive tasks without a need for someone who understands all the complexity of the model to manually edit the file’s design every day or month or year. I also believe that the ability to push one button and have it run a major analysis importing data, manipulating formats, creating reports an publishing them to PDFs or to an Excel Web based file for consumption by many more people who have no desire to do it themselves (or have actual JOBS that don’t mean Excel development) has HUGE value.

All this requires the automation that is currently only available in VBA.  Without that (or an equivalent) capability Excel will just fall away to be replaced by more expensive, less capable technologies (that just might not be from Microsoft btw). Alternatively it will all fall back to user-maintained reporting with the attendant non-productivity and problems that can create.

It appears to me that the people making the decisions on Excel (and don’t get me going on Access) have no history nor do they understand or care about all the good and reasonable ways that businesses everywhere use and could use VBA (or XLM or whatever) to Automate their business processes.  The fact that they don’t get it makes me shake my head in despair.

Dick

p.s. You comments would be greatly appreciated :-) ….

 

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

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:

http://msdn.microsoft.com/en-us/library/office/jj618413(v=office.15).aspx

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…. !

Dick

 

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

InfoPath Retirement Includes An Interesting Tidbit

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

http://smurfonspreadsheets.wordpress.com/2014/02/06/infopap-re-envisioned/) …

About time IMHO….

Not to take anything away from Simon’s post, I have to mention that he references an article in The Register (http://www.theregister.co.uk/2014/01/31/infopath_retired/) 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…

Dick

Posted in PowerPivot, Excel 2013, Microsoft Excel 2013, Microsoft Access, Office 2013, Microsoft Access 2013, Access, Excel, Spreadsheets, Office Automation | 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.

Dick

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:

Table

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\

   ThisWorkbook.Save

    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).

Conclusion

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.

Dick

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