Seven weeks ago I started working for Acumatica. During the past seven weeks, I have been considering whether or not to continue posting on this blog.
I have a lot of ideas for posts (some written down, some in my head), but I wanted to see how the first seven weeks would go as an Acumatica employee before deciding whether or not to continue blogging here.
Here is what my Evernote blog post ideas document looks like at the moment:
- Power BI Top 5 Customers by Revenue
- Creating a data source based on ARTran
- Connecting to Power BI and adding a monthly bucket column, hardcoding the revenue account filter
- Add monthly filter and drilldown to item
- Top 5 mobile
- Add an alert if top 5 are more than 50% of total
- Power Update Introduction
- Mainly needed if you want a simple way to “mashup” with on-prem data sources
- Tableau Public map visualization of sales by state connected to OData
- Power Query Intro in Power BI Desktop
- Add custom calculated column -> PT/PC
- Data shaping (more than just rows and columns) – IT tools vs new Business tool called Power Query
- Doug Johnson SQL View vs Power Query
- Link to Miguel Escobar training course
- Power Pivot Intro and link to Rob course
- Chandoo dashboard stuff
- Why ERP summary tables are obsolete
- Talk about the experience with a CFO where he was using a report based on summary tables and they didn’t have enough detail when he wanted to slice and dice
- Power BI lets you filter or pivot on anything you want. You don’t get handcuffed. You are free to ask whatever questions you want about your data.
- Talk about pivoting with Power Query, basically on-the-fly summary tables
- What lurks behind CSV?
- What is CSV and why does it exist?
- Rob Collie’s comment that most of his engagements are sourced from legacy CSV files
- ERP (French) and Excel (Chinese)
- Until now, you had to hire a translator named CSV
- Acumatica decided to go out and learn how to speak Chinese (OData)
- Acumatica Reporting – Where to Start?
- Graphic with Report Designer logo and arrows to “no options”
- Graphic with Excel logo and arrows to Power BI, Office 365, Power Update, Report Designer
- Excel is the best prototyping tool and many times good enough for the end user
- Acumatica and Microsoft are a winning combination
- I noted a while back that Acumatica is aligned with Microsoft while NetSuite is aligned with Oracle.
- Since Satya took over the reigns at Microsoft, they have sped ahead with their technology. So much so that NetSuite recently ditched Amazon for Microsoft Azure.
- Of course, it pays to go with Acumatica which allows you to deploy with SaaS or on Azure, Amazon, 3rd party hosting, or on-premise.
- Simple, low-powered devices that display location-relevant KPI metrics throughout an office:
- Steve Jobs design of Apple headquarters intentionally to help people run into each other and interact
- The importance of collaborative working space and displaying relevant information throughout a three dimensional environment, not just on a screen in a cubicle
- The danger of the all-in-one remote or the cell phone screen that does too many things
- The reliability of the digital stadium scoreboard being due to it’s fixed geographical location and that it only delivers one piece of information and delivers it well (without the interruption of commercials like on the jumbotron)
- The human brain’s ability to remember things based on their geographical context
- Binary vs Analog
- Human beings will always be analog
- Implementing ERP is more about the analog side of things than the binary side of things
- FRx and multiple data source reporting
- Remodeling vs building from scratch
- Building up vs tearing down
- There are two philosophies: fit your business to the ERP software or fit the ERP software to your business
- Pick a product that can meet your current needs or pick a product that can conform to your current and future needs
- Acumatica and the SSRS Rumor
- Bye bye paper
- Paper separates Gen X from Millennials
- Living Information vs Information Corpses
- Almost want to replace the word Report with the phrase “Information Delivery Vehicle” (IDV)
- Organic, biological, static, inorganic, DNA
- The unchangeable trust factor: why paper lives on
- Paper is illicit, like cash to a drug dealer
- I still use paper occasionally, not because I’m old but because I’m human. Human beings are analog, not digital, beings.
- Democrats vs Republicans and Domo vs Excel
- Are we destined for 50/50?
- Does this same debate apply to Hosted vs On-Premise
- Waterfall Income Statement
- A Pivot Table Income Statement
- My Story (Part 1)
- Started at Deloitte and Touche in audit. Clients always had trouble pulling information from their systems to give to us in a usable format.
- Serendipitously landed in ERP in 2005 where I learned SQL and how to pull whatever information I wanted out of the system. A dream come true!
- I eventually was introduced to the world of Business Intelligence, Data Warehousing, and Data Discovery tools. So much better than Crystal reports!
- My Story (Part 2)
- From “focus on tools” to “focus on people”. The real Business Intelligence is people. Reports don’t make decisions, people make decisions.
- From working with reporting tools that were like “working with concrete” to those that were like “working with clay”
- From focusing on “how to” geeky technical nuances to philosophical musings which is how we got to where we are today with this website
- The importance of numbers
- The numbers themselves tell a story: Height, Weight, Age, Grade Point Average, Batting Average, Salary, Annual Revenue, Headcount, Outside Temperature. All of these numbers paint a picture and tell a story.
- Data viz is the cherry on top, not the ice cream sundae.
- The importance of data visualization
- The importance of infographics
- Trust – The foundation of enterprise reporting
- A number queried directly from the ERP system versus a number from the Excel spreadsheet on a VP’s laptop. The VP’s laptop will always win because there is a greater level of trust.
- Report Designer MICR
- Dynamically splitting the GL segment using Power Query in Excel
- Integrating Power BI tiles into Acumatica screens
- Building ERP-specific visualizations with D3 and the Power BI open framework
- Why Power BI makes sense for Mid-market ERP consulting firms
- Zero price barrier to entry for clients
- Allows you to have a conversation with EVERY SINGLE ONE of your clients, not just the big spenders
- Easy to prove massive value in a short amount of time
- Most clients are already reporting in Excel, the BI Trojan Horse (at least to some extent).
So, ya, have a lot of ideas for future blog posts.
However, after a lot of careful consideration, I have decided to hit the pause button.
Things are much busier at Acumatica than they were at my last job and I just don’t have the time or energy at the moment to continue blogging. I really want to, but I’m just not able to at this time.
When I started work at Acumatica, I also started going to the gym again (it’s been a while), in the morning before work, and I LOVE it. If I were to continue late night blogging, it would kill my morning gym time and I’m just not willing to give it up.
In order to hit the gym in the morning, I have to go to bed at a reasonable time (10ish to 10:30ish). Basically I get home, play with the kids for a little bit, help put them to bed (a long process), and then it’s time for me to go to bed.
So, that’s it. Things will go quiet here on the blog.
I might pick it up again at some point in the future, but we’ll have to wait and see.
It’s a shame because there are many, many exciting things going on with Power BI and I would have loved to tie them in with Acumatica and the world of ERP. I firmly believe that paper-based reports will continue to give way to reports on your laptop screen, on your phone, or on the tv monitor in the conference room. Power BI is in a great position to deliver these kinds of reports and Acumatica is in a great position to feed Power BI with the kind of ERP data it needs to be valuable.
Blogging was a very fun hobby for me and I have enjoyed it very much. Regarding hobbies, I have always liked Mark Twain’s explanation of a hobby in the book The Adventures of Tom Sawyer:
If he had been a great and wise philosopher, like the writer of this book, he would now have comprehended that Work consists of whatever a body is obliged to do, and that Play consists of whatever a body is not obliged to do. And this would help him to understand why constructing artificial flowers or performing on a tread-mill is work, while rolling ten-pins or climbing Mont Blanc is only amusement. There are wealthy gentlemen in England who drive four-horse passenger-coaches twenty or thirty miles on a daily line, in the summer, because the privilege costs them considerable money; but if they were offered wages for the service, that would turn it into work and then they would resign.
If I were a wealthy gentleman in England, then I would probably continue blogging about Acumatica and Power BI.
But, I’m not.
I will focus instead on hitting the gym in the morning, keeping up with my new job responsibilities, and getting to bed at a reasonable hour.
Thanks to all of you who have stopped by over the last two years.
It’s been fun.
Guest post by Brad Kostreva
Quick intro from Tim: I was thrilled when Brad asked me last month about doing some guest posts here on the blog. We first connected about a year ago through the blog and have kept in touch ever since. I mentioned him recently on the bottom of the What is Power BI post. Unlike me, an Acumatica reporting hobbyist, Brad is actually using Acumatica, Excel, and Power BI in the real world. I’m looking forward to hearing his real world experiences in this and future posts. Take it away Brad!
Of Acumatica and Power BI – A Tale Loosely Inspired by the title of a classic novel by John Steinbeck
Tomorrow, September 17th, I will become an employee of Acumatica, filling the ERP Solutions Consultant position in the Columbus, OH office.
Acumatica has been a fun side project / hobby for me since April 30th, 2013. But I’m happy that I will now be working with it full-time. Of course, I’m also happy that I will now be getting paid too 🙂
Am I Excited?
Currently, Acumatica is the only ERP product listed on PowerBI.com:
But Power BI is a fairly new thing and many people aren’t exactly sure what it is.
So, in this post I’ll attempt to answer the question, “what is Power BI?” [Read more…]
The Current Excel Online Problem
Three weeks ago we looked at using Excel Online to deliver your awesome Excel reports to people without them having to know that you made it in Excel.
However, there currently is a problem with Excel Online. The problem is that there are actually different versions of Excel Online. There is a Free version, there is a Business version, and there is an Enterprise version. For more details, checkout this post by Mike Alexander (click here).
I personally do not understand all the differences between the versions or even if these are indeed all the versions of Excel Online. There might be others that I am not even aware of yet. This is the downside of the Microsoft BI strategy at this time. It can be tricky to navigate all of the different options.
Wow, I can’t believe that it’s been two months since my last substantial blog post! But it is Summertime and family activities take priority over the blog. We did some camping, made a trip to California to see family, and have generally been having a blast enjoying the nice Summer weather.
Now it’s time for a long overdue blog post.
Two months ago we built a very simple example of a dashboard in Excel (click here). Since we built it from an Acumatica OData connection, we were able to refresh it with live, up-to-date data by simply clicking a button in Excel.
Of course, I am a big proponent of Excel because it has a long history of being a mature reporting product. It has been built over three decades and has over 800 million users. These users have used Excel for all sorts of different applications in the real world. They of course generate feedback which has in turn steadily transformed Excel into a very mature reporting solution.
Currently, in my opinion, Microsoft Excel is the best application for consuming the OData connection that is available in Acumatica for two main reasons:
- Most companies already own Excel
- Excel is already a robust calculation and reporting software application
How robust is it? Consider this, if you go to business school, the chances are that you are going to be using Excel heavily in order to solve real world business problems. If you go to work on Wall St., you will not be able to survive, unless you acquire advanced Microsoft Excel skills. There is even an annual competition called ModelOff (click here) designed for these Wall St. types and the competition attracts over 4,000 people.
Schools like UT Austin in Texas have entire business courses that are dedicated to teaching their students Microsoft Excel. One such course is taught by professor Clint Tuttle, a man who is so passionate about Excel, that he even makes music videos like this one (click the picture to see the video):
I already mentioned the over 800 million users who are constantly placing demands for more and more features. When you consider that Excel has been around for 30 years, you realize that there are a lot of features available. It is a very mature application. Even Bill Jelen (aka Mr. Excel) admits that he rarely walks into a room to give a seminar about Excel without learning something new himself.
Bottom line, even though Excel is not an all-encompassing reporting solution, it is a lot more powerful than most people realize.
The Excel Problem for Acumatica Users
This is all great, but we have a problem.
With the simple dashboard that we built two months ago, someone has to open Microsoft Excel on their desktop computer in order to use it.
This is especially problematic for an Acumatica user because an Acumatica user is accustomed to being free to access Acumatica anywhere they want. They can access it from their computer, they can access it from their phone, and they are not tied to their Microsoft Windows desktop environment.
I’ll admit that it can seem archaic to an Acumatica user when they are told that they need to use Desktop Microsoft Excel in order to do some reporting.
Wouldn’t it be nice if I could open an Excel file in my web browser just like I open a regular web page, just like I open Acumatica?
The good news is that there already is such an application and it is called Excel Online.
Excel Online is web-based Excel.
Applications like Excel Online have received a lot of attention from Satya Nadella, the new CEO at Microsoft. Satya has talked a lot about making Microsoft a cloud company, a company that delivers their software in the cloud, in the browser, on mobile devices, and not just on Desktop computers.
What has been interesting is to see how quickly he is taking Microsoft in this new direction and I think many are surprised to see how quickly Microsoft is actually executing on the vision outlined by Satya.
Excel Online is one of many examples which demonstrate what the future looks like for Microsoft applications. As time goes on, Excel Online is beginning to feel more and more like Desktop Excel. Interestingly enough, Desktop Excel is beginning to feel more like Excel Online. For example, you can now embed web apps into Desktop Excel documents by inserting them from the ribbon.
This just goes to show that the entire Excel experience, both with Excel Online and with Desktop Excel, is beginning to feel like one giant web application. This is not an accident, it is simply the execution of Microsoft’s new strategy. It seems like eventually there will only be one Excel product with two versions: a Web version and a Desktop version.
Microsoft has already accomplished this with Windows. The next version of Windows, Windows 10, is one Windows code base. It is just one collection of computer code, but it can be deployed on a Desktop computer, on a Tablet, on a Phone, or even in the Cloud. I believe that we will eventually see the same thing happen with Microsoft Excel.
If this “one code base, any device” philosophy sounds familiar, it should. Acumatica has been doing this since it began in 2008 and it is a part of the Acumatica DNA.
Web-based Acumatica, meet Web-based Excel (Excel Online)
Recently I realized that, since OData is a secure way to deliver Acumatica data over the internet, it might be possible to connect to an Acumatica data source from the Excel Online web application, not just from the Excel Desktop application.
I was pleasantly surprised to find that this is indeed possible using Excel Online and Office 365.
In the video below, I would like to show you how to take the dashboard that we created two months ago, upload it to Excel Online, and actually refresh the Excel Online dashboard with live data from Acumatica, all within your web browser, without ever having to open the Desktop Excel application.
Then, as a final touch, we can take our Excel Online dashboard and embed it within Acumatica, completely hiding the fact that we used Excel to make the report! We just need to use the following secret code: &action=embedview&wdbipreview=true
Checkout the video!
Last week we built a very simple dashboard in Excel by connecting Acumatica data to Excel using OData.
This week I have been super busy so I wasn’t able to put together a video post.
However, I did find a couple of helpful links that demonstrate the power of Microsoft Excel Dashboards in Acumatica ERP:
Easy to Use and Massive Community
There is a reason that Microsoft Excel is the world’s most popular BI reporting tool. It’s easy to use and you can find tons how-to information online.
If you aren’t sure how to do something in Excel, just type your question into Google and the chances are that you will find an answer.
Excel formulas are very powerful and there are myriads of ways that you can combine them together to create powerful calculations.
Update February 5th, 2017: Alas, the TryAcumatica.com links don’t work anymore since that site has been taken down. So, I removed the links.
Then, at the very end, we checked a box to enable it for OData.
Checking that box allows us to connect to the data in our Generic Inquiry screen using other applications, including the world’s most popular business intelligence application: Microsoft Excel.
Two weeks ago I posted about why it’s such a big deal that Acumatica has connected to Microsoft Excel and Power BI via OData.
But how do we tap into this new magic “power”?
Everything begins with building a Generic Inquiry screen in Acumatica. In order to use an OData connection in Acumatica, you first have to build a Generic Inquiry screen.
In this post, I’d like to walk through building a simple Generic Inquiry screen and enabling it for OData.
Last week I mentioned the recent announcement by Acumatica which connects Acumatica to Microsoft Excel, Power BI, Power Pivot, and Power Query.
Then I made my case for why I think that Excel and the “Power” tools are a big deal.
What about Report Designer?
But where does the Report Designer in Acumatica fit into all of this?
Hold onto your seats, the world’s fastest growing Cloud ERP software (Acumatica) is about to collide with the world’s most popular Reporting and Business Intelligence software (Microsoft Excel).
Acumatica made the announcement this morning (click here) at Microsoft Build, the annual conference aimed at developers who use Microsoft’s next generation technologies. I just watched the event live at www.buildwindows.com and the Acumatica OData solution occupied the stage for 5 minutes which is a lot of time considering how much they cram into the keynote (watch and you’ll see what I mean).
The OData feature in Acumatica allows users to connect Excel to virtually any Acumatica data that they want. No more manual exporting and importing. You can now build an Excel report with Acumatica data and have it refreshed with the click of a button.
So what’s the big deal about Excel?
“Excel,” I hear you asking, “isn’t that just a spreadsheet program?”
Ah, yes, it is a spreadsheet program, but it is also the most popular Reporting and Business Intelligence software on the planet.
Wall Street would collapse without Excel, the government’s budget and census projections would be nonexistent without Excel, and university research would be nothing more than data, lacking any analysis, without Microsoft Excel.
Ok, maybe I exaggerate a little, but take a look at the following video featuring the two creators of VisiCalc, essentially the first version of Microsoft Excel, and maybe you’ll agree with me.
Click the picture to see the video.
In the June 1981 issue of Fortune magazine, VisiCalc was featured in an article entitled, “Software’s Greatest hits.”
The January 1982 issue of Inc. magazine featured the creators of VisiCalc on the cover highlighting the birth of the new computer industry. Notably, Bill Gates was pictured on the inside story, but not prominently on the cover.
In September 1985, The Wall Street Journal made the following comment in an editorial:
We’ve been reading stories this week about all the returning Members of Congress who say that virtually none of their constituents are interested in Ronald Reagan’s tax reforms. Could be, but we doubt it. Our guess is that people everywhere have by now filled Visicalc spreadsheets, endless pages of eight-column accountant’s paper, yellow legal pads, blank stationery and envelope backs with calculations of how they’d fare with the president’s tax-revision proposals…
It’s notable that in 1985 the spreadsheet had already become synonymous with important decision-making.
VisiCalc eventually became Microsoft Excel and Microsoft Excel has now been installed on over a billion computers worldwide.
We have the same situation today that we had in the 1980s. Walk the halls of any company, duck into a meeting, and look at the reports that are being used to make critical business decisions. You will probably find that the majority of those reports were created in Microsoft Excel.
The hidden truth about Excel
By now, all the reporting professionals are nodding their heads. Why? Because they share a common experience with Mr Excel (aka Bill Jelen).
Click the image below and listen from the 2 minute mark to the 5 minute mark as Bill tells his story of how he used spreadsheets to do what a $100,000 reporting software package couldn’t do.
Bill discovered what every reporting person eventually discovers. That is, the most popular button in any reporting software:
I personally have experienced this many times, whether it was during my consulting days or now at the manufacturing company that I work for. As an IT person, I used software like Crystal, SSRS, and FRx to create reports. But, time after time, people would ask me how they could export the reports to Excel.
Eventually I started to pay attention to the reports that were being brought to meetings. It was then I discovered that my reports were simply exported to Excel where the “real” reporting actually took place.
Fancy Reporting Software
These days, the Reporting and Business Intelligence software market has become very crowded: Tableau, Qlikview, Domo, Logi Analytics, Solver, BizNet, Renovo, and the list goes on…
Some of these vendors (notably Tableau, Qlikview, and Domo) try to promote what I call “out of the box analysis” where they imply that, if you buy their software, you will instantly have insightful dashboards at your fingertips.
It’s as if their software can somehow magically understand your business and spit out meaningful reports without you having to do any thinking.
Rob Collie sums this up with the 3 Big Lies of Data which are:
A new era for Excel
In late 2006, a Microsoft employee launched a secret incubation project called “Gemini” with a goal to make SSAS (SQL Server Analysis Services) available to users of Microsoft Excel.
Microsoft SQL Server Analysis Services is one of the most popular analysis software solutions for medium sized businesses in the world.
It’s important to note that “project Gemini” was initiated by someone from the SSAS team, not the Excel team, at Microsoft.
“Project Gemini” was released as Power Pivot in 2009 as a free add-in for Excel. The goal was achieved in that Excel users now had access to the power of SSAS, but in an interface that was familiar to them.
Power Pivot gained popularity very quickly and it eventually became the centerpiece of the entire Microsoft BI strategy.
This strategy eventually landed them in a very prominent position on the Gartner Magic Quadrant for Business Intelligence and Analytics Platforms:
After Power Pivot, Microsoft added Power Map, Power Query, and Power View to the stack. All these solutions were built as add-ins for Excel.
More recently, Microsoft launched Power BI which makes the stack available in your web-browser or on your phone, without the need for Excel. You can also query your data by typing sentences and the software will create a graphical report for you based on what you typed.
These tools basically amount to “Excel on steroids” and they have taken what was already the world’s most popular Reporting and Business Intelligence software and made it 100 times more compelling.
I personally have been using these “power” tools since 2012 at the company I work for and have found them to be better than any reporting software that I have ever worked with.
Acumatica and Excel with Power Pivot, Power Query, and Power BI
Most ERP packages for the mid-market have their own proprietary reporting tools.
Or, you can pay for a 3rd party solution.
By connecting directly to Excel, Acumatica just picked up 30 years of reporting experience (Excel was introduced in 1985).
The Acumatica reporting tools just went from a bicycle (Report Designer) to a Lamborghini (Excel with Power Pivot, Power Query, and Power BI) overnight.
My Personal Direction
When I started PerpetualAcumaticaLearner.com in April 2013, I was just interested in learning about Acumatica in general.
The idea to connect Acumatica to Excel first occurred to me in January 2014 when I was trying to do it with Power Query (click here), but Gabriel from Acumatica had a much better idea to use OData (click here).
When I moved things here to AUGForums.com in January of this year, I did it with the expectation that Acumatica would eventually be connecting to Excel with OData.
I had become very excited about Power Pivot because of what I experienced at the company that I work for. For more on that experience, click here.
I figured I would learn Report Designer while I waited for Acumatica to implement OData.
I just never thought that they could implement it so quickly. I think that part of the reason why they were able to do it so fast was because they partnered with the Microsoft engineers. This is a pattern with Acumatica that I have noticed in that their development team doesn’t operate as a closed unit. They aren’t afraid to reach out and build on top of other solutions. This is a philosophical difference between a synergistic web-based vendor like Acumatica and the siloed approach that most traditional ERP vendors have become enslaved to. And it’s another reason why I think Acumatica is more of a platform than just an application.
I have already used OData to deploy Excel reports for two of my clients and it works great.
The Future of This Website
I think that Report Designer is only for 5% of the reports that a company needs. It’s great for things like Checks, Purchase Orders, Sales Orders, Invoices, Customer Statements, etc.
As for the other 95%, I believe that Excel with Power Pivot, Power Query, and Power BI is going to become the go to solution.
With this in view, my posts going forward will focus on Excel with Power Pivot, Power Query, and Power BI. However, I will continue my review of the standard reports on Thursdays because they give me insight into DAC which is critical to building the Generic Inquiry screens that the Acumatica OData connections depend on. But more on that in future posts.
Suffice to say, I am very happy about the OData announcement today, and thrilled that it happened on the 2 year anniversary of my first post (click here).
There are a lot of new possibilities to explore and it’s going to be a fun adventure.
Please feel free to comment with your thoughts and let’s keep learning together.
Currently, you can’t build Microsoft Excel reports in Acumatica. However, if Acumatica implements OData in the future, this will become a possibility.
In the meantime, I created a simple Excel report as an example of the powerful things that you can do with Excel. This report was built for Sage 500 and is being sold through The Report Store (click here), but it would look exactly the same for Acumatica if the OData option is included at some point.
So, maybe this is a glimpse into future Acumatica? I hope so
Here is a quick 5 minute video showing the report:
I was planning to do a post this week on the email features in Acumatica, but the announcement by Lebron James last Friday was such HUGE news here in Cleveland that I thought this post would be better. I’ll pickup the email features post next week. [Read more…]
After taking a detour last week and exploring how to add new fields to existing screens in Acumatica (click here), I decided to return to the task of learning to create Excel reports that point directly to the Acumatica database. I began with this idea three weeks ago (click here) and continued two weeks ago by taking some baby steps to create a very simple Excel report (click here). This week I decided to pickup with where I left off a couple weeks ago and attempt to continue reproducing the Vendor Summary (AP401000) screen in an Excel report.
Continuing With Baby Steps
I ended the post a couple of weeks ago (click here) with the following report in Excel:
I still have a lot of hope for learning how the Data Access Classes in Acumatica can contribute to better reporting. As far as I can tell, I think the Data Access Classes function like the Acumatica Reporting??? side of the picture below. However, I think that I’m going to revert to my comfort zone for a little while to see how well I can create reports directly against the Acumatica database like in the Traditional Reporting side of the picture below. In more traditional ERP systems, your only option is to report directly on the database so this is what I’m used to. [Read more…]
I wasn’t able to spend much time on direct Acumatica learning this week, but I did get to entertain an Excel reporting idea that I think would be very powerful if I can get it to work.
First, a word about Power Pivot. I began using Power Pivot early last year and have become a big fan. At my company, we now do a lot of our reporting in Power Pivot. Recently, over the past few months, I have been attending the Cleveland Excel User Group which focusses on Power Pivot and is organized by a guy named Rob Collie. Rob is the most vocal Power Pivot voice on the planet. If you haven’t heard of Power Pivot, Rob has a nice overview on his blog. He also wrote a book on Power Pivot which does a great job of introducing the Power Pivot formula language (called DAX). Rob has a rare gift for writing about technical stuff in an entertaining way.
Microsoft is structuring their whole BI strategy around Power Pivot and they are adding components to the “Power” family. One of these components is Power Query. Now, Excel has had data connections for a while. You can use data connections by going to Data -> Get External Data on the Excel ribbon. Here is a screenshot from the latest version of Excel (Excel 2013).
Power Query is like a revamp of the Excel data connection feature. Actually, the end result of using Power Query is that an Excel data connection still gets created, but Power Query makes it MUCH easier while giving you A LOT more functionality. You can download Power Query for free here. When it installs, it will show up on the Excel ribbon like this:
There are some interesting options in the From Other Sources menu drop-down which include SharePoint, Email (Exchange only currently), Active Directory, and Facebook. Microsoft continues to add features to Power Query so I would expect this list to continue to grow.
Now, you might ask, “what does all this have to do with Acumatica?” Well, I’m glad you asked. In the most recent version of Acumatica (4.1), an Excel Connectivity feature was introduced. This feature allows you to export the results of an explore query to Excel without losing the live link back to Acumatica. During the export to Excel, Acumatica creates an Excel data connection which allows you to refresh Excel with live Acumatica data by simply clicking the Refresh All button on the Data ribbon.
Now you might ask, “what does this have to do with Power Query?” Great question! Power Query has the ability to connect to web pages and extract the data that is currently being displayed in tables on those web pages.
For example, you could connect to this Wikipedia page (http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population) to bring the list of largest US cities into Excel using the From Web button in Power Query. Once you do this, you have created a link to the webpage. Now, if Wikipedia ever updates the data in the table on that webpage, a simple click of the Refresh All button in Excel will instantly grab the current contents of that table and bring them into Excel.
Why not just use the Excel Connectivity feature that Acumatica worked so hard to develop? What’s the big deal about Power Query? The reason to use the From Web button in Power Query rather than the old From Web button under Data -> Get External Data on the Excel ribbon is that Power Query provides much more functionality. You can remove columns, filter, group, insert calculated columns, etc. all before the data even comes into Excel. Power Query has it’s own language so you can do even more, but I haven’t looked into it too much yet. Also, the Excel Connectivity feature loads the Acumatica data into your Excel file, while Power Query gives you the option to load the data directly into the Power Pivot data model by checking the Load to Data Model box. I hope to write a future power about the benefit of bringing the data into Power Pivot rather than into a normal Excel worksheet.
The Excel Connectivity feature that was recently introduced in Acumatica version 4.1 utilizes the old Excel data connection From Web feature. When you open an Excel file that was exported from an explore query in Acumatica, you can go to Data -> Connections on the Excel ribbon to see the Excel data connection that Acumatica creates during the export. I tried exporting the Account Summary explore screen to Excel and the Data -> Connections screen looks like this:
and finally the Edit Query… button. A login prompt appears and I entered my Acumatica credentials. Don’t forget to enter both your username and the company that you want to connect to with an @ symbol in between like this:
Now, for some reason, I get a The webpage cannot be found error. However, I am able to refresh the Excel file so something must be working. This puzzles me, but more on that later.
I can click the disk icon in the upper right next to the Options button to save the data connection web query definition to my desktop as a .iqy file. I then open the file using Notepad to see the details of the data connection definition:
And this is where I get stuck. I think that I should be able to take the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL and paste it into the Power Query From Web feature like this:
I actually had an exchange with a very helpful Microsoft employee named Curt on the Microsoft forum:
He pointed out that res://ieframe.dll/navcancl.html# portion before the URL in the data connection definition file looks funny.
I also tried pasting the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL into my Chrome web browser, but I get a You can’t be here right now!!! error which seems similar to me to the 404 error that Power Query gives.
So, basically I’m stuck because I don’t understand the Excel Connectivity feature well enough and I’m wondering if someone out there can help me out.
My next step will be to see if I can get myself into the Acumatica forum to get help on this issue.
I definitely think that the combination of Acumatica, Power Query, and Power Pivot would be great for reporting.