Are you using Acumatica?
Have you heard about Power BI, but aren’t sure how to get started?
Query Quarry can help you leverage the tremendous power of Power BI without having to go through the steep learning curve.
With Query Quarry, you will receive ready-made Power BI analysis that I will embed right into your Acumatica environment without you having to deal with any of the hassle.
With Query Quarry, I will create the entire data model in Power BI for you, including setting up the Power BI environment and getting it embedded in your Acumatica application. All you have to do is enjoy the result.
You can still pursue learning Power BI on your own alongside Query Quarry and building your own things with Power BI, then embedding them in Acumatica. But Query Quarry will always be there as a fully managed environment that you can depend on, in addition to the stuff that you build on your own.
Why am I offering Query Quarry, when I know that you could just build analysis in Power BI on your own?
Well, I know that the learning curve is steep. It took me a long time to acquire the skills needed to build Query Quarry.
First, I had to get…
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 utilizes the Business Intelligence capabilities in Power BI to deliver pre-built analysis of your Acumatica data.
Think of it 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 complicated 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 so that’s how I can provide pre-built Power BI analysis that can hook right into your current Acumatica environment and deliver immediate results.
Query Quarry provides you with instant analysis, answering difficult questions that you may not have even thought of yet.
Publicly Available Examples
With Query Quarry, you know what you’re getting up front. No empty promises.
I will be blogging with pieces of 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 Query Quarry examples, click here.
Are You Interested?
Are you are interested in experiencing the power of Power BI?
Do you want to see immediate results?
Then contact me (click here) and I’d love to share more about Query Quarry with you, including how you can experience it in your current Acumatica environment.