Update (January 23rd, 2019): I’m keeping the post on this page in place for now, but click here for the latest on Query Quarry.
Are you using Acumatica?
Have you heard about Power BI, but aren’t sure how to get started?
Query Quarry can help.
There are many books written about learning Power BI. Some of my favorites:
- Power Pivot & Power BI
- Power Pivot Alchemy
- Introducing Microsoft Power BI
- Analyzing Data with Power BI and Power Pivot for Excel
If you like videos rather than books, I highly recommend LearnPowerBI.com.
But using Power BI with Acumatica depends on Acumatica Generic Inquiries. You have to create the appropriate Generic Inquiries and check the Expose via OData checkbox to make them available to Power BI.
And you have to build those Generic Inquiries.
Query Quarry is an open source project aimed at building a library of Generic Inquiries that are designed for Power BI.
Many of my future blog posts will be focused on building Power BI examples that leverage Query Quarry.
With Query Quarry, you can focus on learning Power BI rather than wasting time learning the Acumatica database schema.
Why? Because building Generic Inquiries really requires…
A background in Structured Query Language (SQL), Relational Databases, Tables, Primary Keys, and the Acumatica Schema
Sorry for the long title to this section 🙂
I personally got introduced to SQL back in college when I worked on an open source project using the LAMP stack (Linux, Apache, MySQL, and PHP). The project ran all of the class websites for the social science departments at UCLA.
We used SQL to talk to the database and retrieve data from the database.
Then, when I later got into ERP, I discovered how powerful SQL really can be. You can basically grab data from anywhere in the ERP system and combine it together for amazing insights.
But it takes some time to learn how to write SQL. A basic query is easy, but things can get tricky real fast. Then there are performance problems that could be introduced by writing inefficient queries.
Creating a simple Generic Inquiry in Acumatica is easier than writing SQL because you do things graphically. But it’s a slippery slope and things can quickly go from simple to complex as you start to need more advanced queries.
Another challenge when building Generic Inquiries is understanding the Acumatica Schema. At the time of this writing, there still isn’t an official published “schema” from Acumatica. It’s a lot of hunt-and-peck to navigate the database and learn how things are structured. Do you think there should be an Acumatica Schema? Click here to vote on that idea.
The Inspect Element feature in Acumatica is really nice and it makes building Generic Inquiries A LOT easier than in older ERP systems. Click here for more on that.
But you still have to understand relational databases, tables, and primary keys if Inspect Element is telling you to pull data from more than one table because combining the tables together can be tricky for beginners.
I worked at Acumatica for one-and-a-half years and, during that time, I got to know the database schema very well.
But knowing SQL and a Database Schema is just the start, just the foundation.
The real power of Power BI is that it is a true Business Intelligence tool.
Business Intelligence is much more than “a report” in that it can answer very difficult questions, some of which you haven’t even thought of yet.
Business Intelligence software is designed to “analyze” data, not just “report” on data.
Query Quarry allows you to jump right into Business Intelligence.
Think of Business Intelligence as “reporting on steroids” which might sound a little embellished. I used to think the same thing, until I got into Business Intelligence.
This is how I got into Business Intelligence…
My Personal Business Intelligence Experience at The Robbins Company
I worked for a manufacturing company in Cleveland, Ohio for a few years. It was a cool company to work for.
We built large tunnel boring machines that dig tunnels. The “chunnel” from England to France was one of our projects.
These machines are very large (often longer than a football field) and very complicated (requiring a coordinated effort from many engineers with expertise in multiple engineering fields).
When I first started, they had recently implemented a new ERP system and my time was consumed with making the system fit the business better and helping people get comfortable with it.
However, as time went on and the dust began to settle on the implementation, I discovered that there was a constant need that kept coming back up again and again.
There was a constant need for reporting.
It seemed to be that the appetite for information was insatiable. The more reports I delivered, the more they wanted.
My approach to delivering reports in the beginning was to use SQL to query the database.
SQL is really the most powerful and flexible way to query a database. You can get to all of the data and you can then basically do anything that you want with it, especially when you wrap your logic in a stored procedure.
But eventually we got into a situation where there were hundreds of reports floating around.
Yes, hundreds of reports, all delivering various information in various formats.
It was then that I discovered the importance of having a reporting strategy, not just attacking each individual request as an isolated thing, but linking each request back to the overall reporting strategy.
That’s when I started to get into Business Intelligence and Data Warehousing.
“Business Intelligence” is simply the next step that you naturally find yourself taking after you discover that you have too many “Reports”.
A reporting strategy is not a flat list of isolated reports individually hitting the database.
A reporting strategy is more like a pyramid where you have reports feeding into other reports that might then feed into still more reports.
The point is that you aren’t reinventing the wheel every time you create a report. You are leveraging the work that was done on previous reports. Leveraging other reports keeps the core logic in one place and allows you to take another step closer to that elusive goal of “one version of the truth”.
Have you ever been in a meeting where the CFO distributes a report around the table?
And there is a total dollar amount for inventory at the bottom of the report.
Then the COO distributes their report with a more detailed breakdown of inventory, but there is also a total dollar amount for inventory at the bottom.
AND THE TWO TOTALS DON’T MATCH!!!
I’m pretty sure that this is a fairly common occurrence in many companies.
This is one of the problems that Business Intelligence and “one version of the truth” is designed to solve.
Hey, speaking of meetings and tables, I experienced something really cool at The Robbins Company.
A transition from “paper” to “digital” reporting.
Before I got into Business Intelligence, it would be common to walk passed a conference room and see people sitting around a table, having a meeting, and holding paper in their hands.
Lots of paper, lots of “does everyone have a copy?”, and lots of “let’s all turn to page 5.”
After I got into Business Intelligence, and we began delivering more reports digitally, we purchased some nice flat-screen monitors for the conference rooms.
After that, it would be common to walk passed a conference room and see people sitting around a table, all looking at the same digital screen.
Site note: Flat-screen monitors work so much better than projectors because the display is MUCH crisper and clearer. The older employees who would complain about not being able to read the projector screens (and rightly so) were much happier with the Flat-screen monitors. And those Flat-screen monitors are very affordable now too.
So, now we had people making decisions while everyone was looking at the same information.
And there was another big benefit to this. Maybe someone comes a little late to the meeting and, as they are looking at the numbers on the screen, they might say, “hey, I just fixed that problem in the system right before I came into this meeting.” With paper reports, in this situation, everyone’s paper copy would have suddenly become painfully out-of-date. But, now that they were all looking at the same digital screen, the person controlling the mouse simply had to press the Refresh button and, BAM, their report was updated with live information showing the corrected numbers.
Once you live in the digital display world, you wonder how you ever survived on paper.
But Business Intelligence is more than just “one version of the truth” and moving from paper to digital.
Business Intelligence is also about…
Asking Difficult Questions
Many times we are happy simply to get a report out of an ERP system like Acumatica. It’s challenging enough just to get the data!
Once you have the data though, what are you going to do with it?
Power BI allows you to take it to another level. You can really start to ask difficult questions.
Answering those difficult questions involves writing formulas in a language called DAX. It’s very similar to Excel formulas on the surface, but MUCH MUCH more powerful the more you get into it.
But many times you don’t even know where to begin. You don’t know what questions to ask because it’s such a battle just to get the data out of your ERP system.
Acumatica makes getting the data out easy with Generic Inquiries and OData. The trickiness of course is understanding Structured Query Language (SQL), Relational Databases, Tables, Primary Keys, and the Acumatica Schema like I mentioned earlier.
Power BI knows how to consume OData connections and that’s basically what Query Quarry is: a pre-built library of OData connections.
Power BI allows you to answer difficult questions, questions that you may not have even thought of yet.
And Query Quarry gives you the foundation you need to put the Power BI “difficult question answering” layer on top.
Publicly Available Power BI Examples
Query Quarry is just the foundation.
The real value is what you build on top of Query Quarry using Power BI.
To demonstrate that value, I will be blogging with Power BI examples that are built on Query Quarry, one example at a time.
To see the first post, click here. You can even scroll down to interact with the Power BI dashboard.
For all of the Power BI examples built on Query Quarry, click here.
Downloading and Installing Query Quarry
Are you are interested in experiencing the power of Power BI?
Do you want to build on the Query Quarry foundation?
Follow these instructions to install Query Quarry in your Acumatica instance:
- Download the Query Quarry Customization Project
- Go to the Customization Projects screen in Acumatica and click the IMPORT button
- Choose the QueryQuarry.zip file that you just downloaded in step 1 and click the UPLOAD button
- Check off the QueryQuarry customization project and click the PUBLISH button
- Once you see the Validation has been finished successfully message, click the PUBLISH button underneath the message
- Once you see the Website has been updated message, you have successfully finished installing the Query Quarry generic inquiries