How To Change Connection Type In Power Pivot

Every day, day in and day out, the most popular posting I have here is this one:

and this one:

I personally use these techniques all the time, for years now and it is my standard modus-operandi for Power Pivot development in Excel.

Yet despite the fact that everyone comes here every day and looks at this page, I HAVE¬†NEVER heard anything back from anyone whether they actually use this, if it works for them or not .. Not once…

That’s pretty frustrating people … ūüôā


p.s. go HERE … ūüôā

Posted in Excel, Excel 2013, Office Automation, OLAP, Power BI, Power BI Desktop, PowerPivot, Process Automation, Spreadsheets, Uncategorized | 19 Comments

Is Power BI For Real ?

Thought of the day …

I have been messing around with Power BI for a while now and while it “looks” great and does “good demo” I’m finding it almost impossible to build anything that I can rely on¬† .. it has a lot of supposed features that really don’t exist (vaporware) and frankly it’s VERY complicated to the point where I’d be reluctant to put a price on a Power BI implementation and I’d be worried that in the end it would let me down to the point where I would look bad for suggesting it.

I am TRYING to come up with a set of best practices that work reliably and consistently but to this point haven’t found that sweet-spot.

Also remember that Microsoft is selling this as an end user tool for analysts … and they have no idea that this will mostly NOT work because these “Analysts” will run into so many walls (data access, data quality, networking issues, security, internal data politics, etc., etc., etc.) that it should be done by professionals … But by Microsoft pricing it and selling it as an end-user product, companies will just never allocate the money needed to make ¬†Power BI Solutions thing really work (if that is actually possible) ….

Oh yeah, sure Microsoft’s line is “We’re working on it and shipping new incremental improvements every day” … but what that means is that Power BI will NEVER be stable enough to host anything but demos .. How can I rely on a technology that will change regularly and is very likely to make things that work for me now stop working at some time in the future for no apparent reason and without warning… ¬†?

And if IT gets involved, none of these Power BI installations will ever see the light of day .. it’ll be just too fragile, too needy and too prone to embarrassing IT in the eyes of their users .. So IT will shut it down.

I am thinking that Power BI better level off technologically and soon and that Microsoft needs to work on making it SOLID, explaining what it REALLY can do rather than writing endless crap on the web about stuff that they’re “working on” as if those features really exist and begin spinning the fact that Power BI is NOT EVER going to be a tool for the amateur.

But that in the end Power BI COULD be a great product and that Microsoft is working to make it so.

No I’m NOT just in a bad mood today btw ūüôā


Posted in Analysis Services, Business Intelligence, Cloud, Excel 2013, Microsoft Access, Office Automation, OLAP, Power BI, Power BI Desktop, PowerPivot, Process Automation, Spreadsheets, the Cloud | Tagged , | 13 Comments

This post is about the Windows 10 Start Menu Corruption BS ….

I am going to talk about something that most users of Windows 10 have not run into yet, so most will probably be inclined to just write this post off as irrelevant to them .. Just wait !!

This post is about the Windows 10 Start Menu Corruption PROBLEM.

It WILL¬†come to you some day – unless Microsoft somehow finds a way to fix this most pernicious of bugs in their Flagship operating system ! ¬†Which they have failed to do in over a year (?). ¬†So please read on….

I just recently decided that it was time to move my world to Windows 10 finally – for real. For a week I just loved the new feel and the general environment. ¬†Then suddenly my Start Menu stopped working and a dialog came up to the effect that my Start menu was corrupted and when you reboot it will “probably fix it” – or words to that effect. ¬†So I restarted and it was the exact same… I could no longer use my Start menu .. it was pooched !!

So¬†I searched the Internet and I found thousands and thousands of inquiries about this same exact issue and the usual thousands of mostly useless “fixes” posted by either well-meaning idiots or self-promoting a-holes. ¬†Frankly while the apparently well-documented solutions do seem to work INITIALLY they do not tell you that within hours or days at most the problem will happen yet again and despite all your efforts, while you might get it back … it will happen again once it starts happening.

It appears to me that this issue cropped up after I customized the “Tiles” on my Start Menu to move my favourite apps front and centre. ¬†It also in the end appears that the most likely ultimate solution is to reinstall Windows 10 from scratch and then DO NOT EDIT YOUR START MENU !! ¬†Although this is only my guess because I just couldn’t bring myself to do that.

Instead I found a free download called “Classic Shell” (¬† ), installed it from my desktop and up came a very slick looking solution that not only gives me a Start menu that looks and works exactly like the one in Windows 7 (which I can live with) , but one that seems to work and work and work !

I have found zero complaints about Classic Shell out there, nothing but praise. ¬†I have no idea why this is a free app … but I guess that’s not my problem. ¬†But it seems to work for me. ¬†We’ll see.

So what possible way can Microsoft explain why some freeware solution seems to be the only way to reliably resolve an bug in their operation system that has been known for over a year? ¬†I just ran Windows update and am running Version 1511 OS Build 10586.218 which was installed from Windows Update this very morning … And the Native Start Menu does not work .. So I installed Classic Shell.

What bothers me is that I have seen zero response from Microsoft on this … it makes me think that Microsoft is unable to solve this issue themselves and have so little shame that they can’t even admit it… And yet how come some third-party startup has a solution and not the maker of the product itself?

This makes me nervous about everything Microsoft is up to with their never-ending incessant upgrades of all their software all the time !! ¬†How can anyone make any decisions on Microsoft¬†products if they never stand still long enough to be assessed? Further, if they don’t stay still long enough for us to actual implement solutions and for the clients to actually get some value out of them and what happens when inevitably Microsoft breaks something in one of these iterations (and they are going to) ? ¬†How can these new incremental changes possibly be adequately tested before they ship… ? ¬†Oh I get it .. we’re ALL BETA Testers now … ¬†But sadly it looks more like we’re all ALPHA Testers instead. ¬†¬†

How can anyone make any decisions to implement serious solutions with this tech in this environment of a constantly moving floor? ¬† What does this Start Menu bug say about how Microsoft is going to respond to this inevitability ? ¬†I guess as long as only a few people are left out to swing in the wind then they’ll just look the other way. ¬†Where is that going to leave Microsoft when people just simply stay away?

Where does that leave us all?



Posted in Access, Access Solutions, Business Intelligence, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, PowerPivot, Process Automation, Spreadsheets, the Cloud, Windows 10, Windows 10 Start Menu | 6 Comments

My Rules For Solution Engagements …

I’m baaaack … again …

I have decided that I just cannot not blog …

I have too many thoughts and I sure would hate to see all the knowledge I have accumulated disappear with me into retirement someday (or that other thing that shall not be discussed).  30+ years has taught me a lot and I hope that it is useful to somebody out there.

I would be glad to hear directly from you if you really read this btw .. But I do not consider this a discussion forum so if you disagree with what I say here please just go somewhere else and think what you want. ¬†I admit I will¬†not always be right but this is always my blog ūüôā ….

So take my thoughts or leave them please .. ¬†I would however like to hear from you if you see what I say is valuable. ¬†If not then ok contact me too – but be nice – please. ¬†I do not really feel like a cat vs dog people argument anymore … this is what I think and that’s the way it is … unless I specifically have an error that you see.


My Rules For Solution Engagements …

Somebody asked this question recently on Linkedin:

“Hello, for those of you who develop Access db’s and apps for small and med businesses – How hard is to establish a common ground on which you and your customers establish a set of requirements?”

Interesting question that I simply felt I had some ideas about so I posted the following up there (slightly edited I’m afraid). ¬†I thought maybe this deserved some kind of Blog posting.

Bottom line for me is that the main pain-point of development in Medium to Small (and also usually large) clients is not REQUIREMENTS “per se” but rather managing the engagement both at the beginning and throughout. ¬†I have found that without appreciating the facts that I am detailing below nearly every project WILL FAIL to satisfy probably everybody involved. ¬†Yeah sure you’ll likely get paid but they’ll likely never implement the solution or worse you will end up supporting a “wheezing dog” solution for a client that will get more and more pissed off as time goes on. ¬†You MUST produce the right solution for the client period .. You cannot let the client force you to create a piece of carp that no one likes and doesn’t do the job for them…

Here we go – not totally random thoughts on this question …

Thirty years of doing solutions for Large, Medium and Small business has taught me that in many ways Medium and especially Small businesses are the toughest to satisfy. These businesses are most often owned and micro-managed by a person who is used to making all the decisions (as opposed to Corporate clients where there people are more used to compromise and collaboration).

To satisfy a Medium/Small business’s requests for a solution I find there is one key component I look for and that is a specific “Champion” in the business (sometimes the owner but not always) who “gets” what you are offering. By “get” I mean that they appreciate your skills as a business analyst and as a developer. He/she MUST respect you and especially respect that you are there to bring your skills in application development to their business need and they must have risked their careers on the success of this solution (really)..

The next consideration whether this is a NEW implementation or a spreadsheet replacement or a “Reno” of an existing Access database.

There are few opportunities anymore to create a database where there isn’t already some kind of automation in place. The first thing you have to do is determine who will fight that change and whether they are going to be an insurmountable problem. An effort has to be made to get ALL of them on-side. The more there are the faster you get to a failed opportunity. Regardless of the enthusiasm from the Boss or the “Champion” these folks will kill you if they are part of the decision-making process.Either they have to be won over or they have to be told that this is happening and they have to “get used to it”. Sad but true.

You have to insist on one rule without exception Saying “That’s the way we’ve always done it ” is NOT acceptable. Contd ‚Ķ

As developer you will, be doing your client and yourself no good if you let your database design be bastardize by their definition of some business process or some strangely laid out form or report that goes against good design practices and yet would deliver exactly the same info and result but in the right way. But at the same time you have to realize that you are just the developer and the business process has to be satisfied. So you often have to suggest alternative ways to accomplish something and fight for it. Again a reason why you need a “Champion” on your side.

After all these considerations then every solution goes it’s own way as every business requirement is different .. like snowflakes.

But without the relationships mentioned above and without respect for you and you skills as a solution developer (justified or not ūüėČ ) no project will succeed.

Always keep in mind the old saying that you have to NEVER say “I gave you what you asked for but it’s not what you need …” Understanding the roadblocks in place and managing those relationships is ultimately way more important than any technical or best practices in the building of the actual solution.Sorry to say this (and I know it will sound self-serving) but it is only through EXPERIENCE with people and organizations that you can fine-tune these skills. But when I was younger I learned these rules early and have just fine-tuned them over time.

But one of the problems ends up that once you get to a certain age the temptation to tell clients what you really think about the way they are approaching the project can be hard to suppress ūüôā … If you know what I mean ;-).

A very important part of any successful solution is NOT showing your work as you develop it to any more than a small number of people and most importantly 1. The people who will actually USE the solution and 2. NOT anyone Director-level or higher (if you can avoid it). If you ever show a solution partially finished to a Director or a VP I guarantee that they will suggest something be added .. EVERY TIME. ….

AND that something will likely have absolutely nothing to do with the original purpose of the solution and is very likely to mean going back the beginning in your design and development .

AND this one thing (or worse multiple things) will be the ONLY part of the solution they will care about and if you do not deliver it they will become negative on you and your solution and will very possibly kill the project.

I know you don’t always control this but if you can avoid this scenario TRY

If forced into it I suggest you be open and honest about the implications of these suggestions to the project right then and there and/or get your Champion to push back or manage this issue away.

I know that this is hard and I KNOW that the customer is always right …

– these extra people aren’t your customers ‚Ķ

– you have to keep the feature suggestions down to the least people who are most genuinely involved in the business process you are trying to automate ..

– AND you have to focus on delivering something that works and is not a dangerous piece of bad design and compromised spaghetti code to make it look good but that will tip over soon and will be impossible to revise or even maintain going forward..

One last thing … These “special requests” invariably are added without any revision to the budget or the timing. That is a BIG risk you have to manage immediately … The longer you delay that chat the more likely that YOU will end up eating the extra cost …

Talk soon ….


Posted in Access, Access Solutions, Business Intelligence, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, PowerPivot, Process Automation, Spreadsheets, VBA | 3 Comments

Here We Go … REAL JavaScript Macros For Excel ?

I have been completely open and honest (as usual) about my contempt for the useless offerings that Microsoft has tried to pass for “Automation” in Excel using JavaScript. ¬†Their concept of “Add-Ins” which do little more than allow you to add Web content inside your spreadsheet that interacts with the values in some cells in the file and exposes some inane answer in the Pane (pain?) on the right taking up even more of the screen already grabbed too much by the Effluent UI “Ribbon” at the top…

I have commented many times here, there and everywhere that what is REALLy needed is some kind of true Macro capability for the “Automation” of actual spreadsheets. ¬†What a strange idea. ¬†Well finally it appears as if someone at Microsoft has come to the same conclusion after years of apparently aimless brain-storming. ¬†Excellent !!

Here is a link to my buddy Maarten Van Stam (a genuine Excel MVP btw) that details the recent “coming out” of this technology …

and also here is more:

and especially here:

(this last one you have to get into it until the interview with Rob Howard …)

Rob says some really interesting things … This MIGHT be a big thing but I am seen so many BIG THINGS over the years that have turned to nothing but smoke in the end. ¬†But this is different I think … Is this a breakthrough in Microsoft’s approach to Excel? ¬†ACTUALLY¬†caring about people who ACTUALLY use the product ?

Hope so..

Posted in Analysis Services, Cloud, Excel, Office Automation, OLAP, PowerPivot, Process Automation, Spreadsheets, the Cloud, Uncategorized, VBA | 7 Comments

My Thoughts On Excel TV

Earlier this week I appeared on the Excel TV broadcast with Rick Grantham, Jordan Goldmeier and Oz du Soleil … ¬†It was great fun for sure and I hope it was somewhat valuable to the broad Excel community (and even the Access community). ¬†I would really like to thank these guys for what they’re doing with Excel TV and I sure hope that they keep it up !!

As the guest I took it upon myself to cover as many things as I could and in the end I’m pleased with the discussion. ¬† It seems like I talked a lot but as the guest I thought that was expected … ¬†Hopefully what I said provided value regardless..

These guys, like me, want to get Excel the best, broadest, use in businesses around the world. ¬†Anyone who comes here knows that I don’t pull my punches on what I think about the current status of Excel. ¬†I sure wish I could just talk about the great things we can do with the product rather than the problems that are in front of us in trying to advance the use of the product, and maybe at the same time make us all some money either as a better employee or as a Consultant. ¬†Some day maybe …

Anyway .. it’s pretty long (an hour and a half) but I understand that Rick chops the sessions up into subsections soon after so you can enjoy it in bite-size pieces.

Hope you enjoy it !!




Posted in Access, Access Solutions, Business Intelligence, Cloud, Excel, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, OLAP, PowerPivot, Process Automation, Spreadsheets, the Cloud, Uncategorized, VBA | 8 Comments

Changes In Procurement Practices Are The Death Of Us All …

  • Just replied to someone on LinkedIn in a ¬†conversation about Access work getting really thin lately … ¬†Thought my response might be a good blog .. (additions in blue) ..

    Jeff asked …”Has the market shifted to hiring contract programmers? “

  • My response …

    Jeff …

    From my observations Corporations have switched to a “Contractor-Only” model (and the “Preferred Vendor” model). ¬†There is the possibility of project-base work but that seems to only be available to the big consulting firms now. I have several times recently worked on contracts or turned down contracts to do what is basically a project because the procurement folks simply could not conceive of the idea of me working on a “pay as you play” or a fixed price model for a specific project in Access or Excel. I was “Dick Moffat” so as a “person” they could only think of me as a potential contractor – even though I am actually an incorporated company. Once they knew I was areal “person” their heads exploded when trying to fit me into their model…

    So I’ve had to go to the clients’ physical office (which sometimes mean being away from home for months at a time btw) every day for 8 hours for weeks until the contract ends at which time there is no on-going arrangement to even support the work I did for them going forward. So the users keep asking me to help them for free because they are frankly screwed… Eventually they have to simply stop using the solution because there is no support and no changes or revisions. The corporation simply can’t conceive of this model anymore no matter how hard I try to explain it to them.

    This is a totally dysfunctional protocol but it seems to have become locked in and I cannot see this changing. This is not good for us “gypsy developers” nor is it good for medium-sized consultancies nor for the customers themselves. But that doesn’t seem to matter .. Once procurement gets ahold of the process it will never give up .. sorta like when IT began to clamp down on PC work in the name of security but moreso in the interest of keeping their jobs.

    In the end the losers are the Companies (we really don’t matter I’m afraid) as they end up paying more and more for less and less, while the supply of capable developers shrinks away to nothing over the next 10 years and then they are left totally dependent on technology but with no one skilled enough to help them keep it going – except maybe someone 12 time zones away ūüėČ …

    But they’ve have lotsa cool, useless stuff to view on their cell-phones ūüėČ ..

    I went into this biz because I could see the need for these technologies to be forever (and still do) and that enhancements in technology would only allow us to do more and better for the same clients. It wasn’t supposed to end up this way.


Posted in Access, Access Solutions, Analysis Services, Business Intelligence, Excel, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, PowerPivot, Process Automation, Spreadsheets, the Cloud, VBA | 2 Comments