Power Query refresh and Solver add_in

[Paul_Christie]

Ken

I've got a model that uses Power Query to get collection and delivery data. The Power Query parts of this model have been checked and double checked and produce what is expected.

Added to this are a number of fields and calculations that are then used as parameters in an optimisation model that I'm controlling with vba.

I'm getting slightly inconcistent results from the Solver Add-In. In particular it has a habit of always saying there isn't a viable solution for whichever is the first optimisation when manually it works. I get the feeling there is a timing problem here. I've tried reducing the length of the calculation tree that feeds into the Solver parameters and I'm using your function to force a refresh of the relevent query. Of course the Solver isn't supported by Microsoft or Frontline so I'm struggling to find any intelligence or what problems might exist and how I might solve them.

Any clues would be much appreciated, even a link to another forum where Solver issues are discussed. I could use the current Frontline Solver Add-In but I need to get the current model working as best I can as a pre-cursor to justifying the cost of the current Frontline Solver.

Paul

 

[Paul_Christie]

Ken

In the now time honoured tradition of answering my own queries I've solved the problem with code like this

shtOpti.Range("CurRound") = strCurRnd
shtStops.Activate
UpdatePowerQuery ("Query - Stops")
shtStops.Calculate
shtOpti.Activate
wbkTW.Sheets("Round_Dashboard").Calculate
shtOpti.Calculate

This might seem a bit of an overkill but I inherited the reporting side of this model from elsewhere and changed the data import from VBA to PowerQuery. Reducing the length of the calculation tree and removing a number of duplications has helped but I still needed to force the calculation to occur before running the Solver code.

Paul

 

[Ken Puls]

Hey Paul,

Sorry for the late reply, I've been busy with work, and am just getting back here myself for the first time in too long. I'm trying to decide if I'm doing you a favour though by NOT answering your questions, as you seem to be answering them all yourself!

 

[Paul_Christie]

Ken

You may have been doing me a favour by not answering it but I'm finding some interesting things that I'm happy to share.

One of the issues with doing things with KPI's is that you start off with the raw data from which you generate the KPI's and then you get into a situation with users where KPI's breed KPI's and if you don't watch out you end up creating what in pure Excel would flag as a circular reference. If you are using Power Query to drive getting the raw data and then creating the KPI's it's possible to create a circle of calculation between Power Query and Excel but it won't show as a circular reference. Somehow I've done this and what I'm seeing is that in certain circumstances you have to refresh twice to get some KPI's to show correctly. I don't want to hand this over to my user like this.

Add to this the use of the Solver Add-In, which is effectively unsupported by Microsoft and Frontline Solvers, because the Solver requires that all it's parameters are calculated before you invoke it, especially under vba control, you can imagine it's possible to get in to a bit of a mess.

I was engaged to fix something that had a warning on it. It was 14 MB spreadsheet that alledgedly could take up to an hour to optimise. I've got it down to 2 MB and it calculates in under 5 to optimise so I'm on to a winner. There was also no specification/documentation/User Requirement document. However, I've got this last litle glitch that because I'm a perfectionist I have to resolve. I took the decision yesterday to rebuild it, shortening the query tree and the internal calculation tree whereever possible.

When I've finished I'm going to recommend the use of a paid for and supported copy the current Solver from Frontline.

Regards
Paul