none
Power Query and Acumatica

    Question

  • UPDATE: I summarized this issue on a blog post:
    http://perpetualacumaticalearner.com/acumatica-power-query-and-power-pivot/

    When you export data from Acumatica ERP into Excel, it creates a connection to the Excel file so you can refresh the data without having to leave Excel. The data connection that it creates in Excel in order to accomplish this has a Connection type: of Web Query. When I go to Edit Query... and click the Save Query button in the upper right, it will create a .iqy file. Opening the .iqy file in Notepad looks like this (I'm running Acumatica on my laptop even though it's a cloud ERP product):

    ---------------------------------------------

    WEB
    1
    res://ieframe.dll/navcancl.htm#http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination

    Selection=EntirePage
    Formatting=All
    PreFormattedTextToColumns=False
    ConsecutiveDelimitersAsOne=False
    SingleBlockTextImport=True
    DisableDateRecognition=False
    DisableRedirections=True

    ---------------------------------------------

    I'm not sure how to replicate this connection in Power Query though. It would be nice if I could because I would be able to take advantage of all the data transformation options that Power Query has to offer. Can anyone provide any insight?


    • Edited by Tim Rodman Monday, April 28, 2014 2:58 PM
    Wednesday, January 15, 2014 2:16 PM

Answers

  • HTTP vs HTTPS would be a function of the server, not the client. PQ definitely supports basic authentication over both.

    Taking a second look at the contents of the .IQY file, I think there's already a problem there: the "res://ieframe.dll/navcancl.htm#" part of the URL suggests that browsing was canceled. Is there an Acumatica forum where you could ask about this?

    • Marked as answer by Tim Rodman Saturday, January 18, 2014 3:09 AM
    Thursday, January 16, 2014 4:59 PM
    Owner

All replies

  • It looks like the actual URL of the data is http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination. If you use "From Web" in Power Query and specify that as the URL, do you get anything useful?

    Our ability to parse data out of web pages is extremely primitive, and it can require a lot of playing and tweaking to get anything useful, depending on how the web page is organized.

    Wednesday, January 15, 2014 8:55 PM
    Owner
  • Hi,

    I am new to this form but saw that you were using Acumatica.  Are you telling me that when you need to run reports and manipulate data it must be exported and then a link is created in excel that keeps the data synced?

    Also how do you like Acumatica?  I am looking at Acumatica and also Netsuite.  Netsuite being my favorite right now but any light on the topic would be great! 

    Anyone else have opinions? 

    Thursday, January 16, 2014 4:26 AM
  • Curt,

    Thanks for the response. Putting this response together with your response on my Power Query and Xero post, I think I might be in another OAuth situation (although I don't know exactly what that means) since this data connection does actually require me to input my username and password when I click Refresh.

    Maybe I can only connect to sites like Facebook since they store my login info in a cookie, but I can't login to more secure sites like a bank or an ERP application that will require me to enter my username and password every time?

    If this is correct, I think that Power Query is still great, but it would be even better if it could interact with login prompts. That would open the doors to an even larger amount of relevant corporate data that is increasingly found in the cloud.

    Tim

    Thursday, January 16, 2014 4:47 AM
  • GBohus13,

    Acumatica definitely doesn't require Excel for reporting. They have their own reporting tools that you can use to build pretty much any report that you can think of. Their reports are deployed in the web-based Acumatica application. I'm just a big Excel fan (especially with the introduction of Power Pivot and Power BI) which is why their Excel data connection caught my attention. Power Query would provide me with even more functionality.

    I've never seen NetSuite, so I'm not really qualified to say anything about it. My professional experience is with Dynamics GP and Sage 500 ERP, but I have been learning Acumatica in my spare time with a view towards the future. Even though I'm not really qualified to say anything about NetSuite, I did attempt a post on my blog which makes a few comparisons between the two. This whole endeavor, including the blog, is one big learning experience for me.

    A couple big things I like about Acumatica:

    • The leadership at Acumatica has strong ties to Dynamics SL (Solomon) which was one of the dominate ERP packages in the 1980s and 1990s. This allows them to tap into a large existing partner base rather than try to build something from scratch.
    • Their approach is to build a platform and rely on 3rd party developers to extend it rather than trying to appeal to all industry verticals. This kind of approach will take longer to mature, but will be more solid when it does in my opinion. This was the approach that lead Dynamics GP (Great Plains) to such prominence in the mid-market.

    My take: if you see your company doing a lot of customization and wanting a lot of control over your ERP system, I would consider Acumatica. Otherwise, at this point, I would probably lean towards NetSuite.

    Tim

    Thursday, January 16, 2014 5:09 AM
  • Tim,

    Thank you for your insight.  I have been doing research on ERP systems for about year now and never came across Acumatica until someone mentioned it to me.  My business is in the wholesale distribution industry with some retail.  What drew me to NetSuite other than everyone saying it was number 1 is that it all centers around the customer database (like salesforce) so that every record is tied back to the customer so you have full visibility about that person.  every transaction, every payment, every call etc. 

    I am not sure if Acumatica is set up that way or not?  It sounds to me and I could be wrong but it sounds like they give you a platform to build on and you go out and find vendors out there that you can work with to create software for you?  kind of like the salesforce platform bc essentially you can do the same with that.

    What drew me to NetSuite is that they have been in the business awhile, have a very developed product, have a great database of applications, tons of people are out there making "add-ons" if you require.

    To me NetSuite has such a strong product and is the industry leader its hard not to go with their product and choose someone that has been in the game for only 6-7 years and is still developing their product.  I know my company is also looking to do ecommerce and EDI reporting and many other things so that is another reason my pull for NetSuite....Thoughts?

    Thursday, January 16, 2014 3:33 PM
  • Is it Excel that's prompting you for a user name and password or are you directed to a web site? If this looks like an Excel prompt, then the site is probably using HTTP "basic" authentication which is supported from inside Power Query.

    Thursday, January 16, 2014 3:59 PM
    Owner
  • I am using HTTP authentication since it's just running on my local laptop. HTTPS is not yet supported?

    Here is the login prompt that I get with the Acumatica Excel connection which confirms basic authentication:

    So I try basic authentication in Power Query using the From Web option and the URL that you mentioned earlier in this thread, but I get the following error message:

    I also get a 404 error message if I try to go to the page in my browser, but somehow the Acumatica Excel data connection is able to access the page.

    Thursday, January 16, 2014 4:15 PM
  • HTTP vs HTTPS would be a function of the server, not the client. PQ definitely supports basic authentication over both.

    Taking a second look at the contents of the .IQY file, I think there's already a problem there: the "res://ieframe.dll/navcancl.htm#" part of the URL suggests that browsing was canceled. Is there an Acumatica forum where you could ask about this?

    • Marked as answer by Tim Rodman Saturday, January 18, 2014 3:09 AM
    Thursday, January 16, 2014 4:59 PM
    Owner
  • Curt,

    Good catch on the URL. It indicates that the Excel file is getting something similar to what I get when I try that URL in the browser. It's baffling that the Excel data connection refreshes correctly (I even try different companies and the data changes), yet the link appears to be broken.

    I'll take your advice and try an Acumatica forum. Thanks again for your help.

    Tim

    Saturday, January 18, 2014 3:08 AM
  • GBohus13,

    NetSuite has definitely been around longer so it's more mature. I think they started around 2000 when the product was still called NetLedger. Acumatica released their first version in 2008 so they are much newer to the game.

    Acumatica does have a CRM piece that was built-in from the beginning as far as I know. I don't know what CRM features people look for though because I'm more focussed on the back-office ERP stuff.

    I have the latest Acumatica installation on my laptop that I'm using to learn from. We could do a screen-sharing session and I could let you take it for a test drive if you'd like. Feel free to connect with me on LinkedIn if you're interested:
    http://www.linkedin.com/in/timrodman

    Tim

    Saturday, January 18, 2014 4:25 AM
  • GBohus13,

    I've been poking around the CRM piece in Acumatica a little more recently and it looks like you can do the following: track leads, support cases, opportunities, and conduct mass email marketing campaigns.

    One of the features that caught my attention is the email feature which is baked into the entire application, not just the CRM portion. Here is an excerpt from the training manual:
    "Acumatica ERP provides email management functionality for employees involved in customer relationship management (CRM) activities, such as working with leads and contacts, organizing campaigns, and handling cases. By using system email accounts, employees can centrally send mass emails they have created or schedule sending them. The system also enables automatic processing of incoming mail, which involves creating leads and activities based on replies to campaign-related emails and routing of emails related to cases to employees involved in processing and generating new cases. Acumatica ERP tracks email history and processes incoming emails based on email exchange history."

    You can also setup email notification templates in which you setup pre-formatted email messages to do things like: auto-respond to generic inbound email letting the customer know that someone will be contacting them within say 24 hours, automatically notify customers of expiring credit cards, internally nag employees to complete their Acumatica timecards, request W4 information from vendors  (who will remain inactive until W4 is received), etc.

    Anyways, I was reading about the email piece and it reminded me of this post so I thought I'd post the info here.

    Sunday, May 18, 2014 11:15 PM