AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Courses
  • Register

Getting Started with Acumatica and Power BI – Part 3

March 31, 2018 by Tim Rodman

Getting Started with Acumatica and Power BI. In this example, analyzing your Acumatica Monthly Commercial Transactions and finding the highest Transaction Type Category across multiple Instances and Companies.

Are you using Acumatica?

Have you heard about Power BI, but aren’t sure how to get started?

In this blog post series, my goal is to help you get started.

Here’s the plan:

  1. I create a working Power BI example that is connected to Acumatica via Query Quarry.
  2. If you want to try it on your own Acumatica data, you can install Query Quarry together with the Power BI Desktop file at the bottom of this post.

Sound like a good plan?

Great! Let’s get started.

 

Today’s Example – Analyzing Monthly Commercial Transactions

This example is geared towards Acumatica partners who have multiple clients across multiple Instances and Companies, but it can also be used by Acumatica customers in their own environment.

Acumatica recently shifted their pricing strategy to include a consumption component. I guess they had to do this because the old “per server core” licensing model was confusing. It wasn’t that clear how much load a server core could actually handle, but it’s a lot easier to understand number of transactions.

The only other viable option that I can think of would have been to charge per user, but I’m glad that they didn’t go in that direction since unlimited users has very much become a part of Acumatica’s identity.

The main factor in the new consumption component of the pricing is something called Monthly Commercial Transactions.

What exactly qualifies as a Commercial Transaction? Currently, here is the list: Sales Orders, Shipments, AR Invoices, Customer Payments, Purchase Orders, Purchase Receipts, AP Bills, and Vendor Payments.

But what about Monthly Commercial Transactions? What does that mean? Here is where things get tricky. And I’m not sure I completely understand it. But my current understanding is that you take the highest number from the categories listed in the previous paragraph each month. So, you count all of the transactions in each category, then take the largest category for that month. That number is now a big factor in the price that you pay Acumatica.

Where things get tricky is how often you can change your price level. What if you do tons of business during the holidays, but not that much the rest of the year? Can you pay for an increased Monthly Commercial Transaction bucket just for the month of December, then scale back down in January? I think so, but I don’t understand the specific mechanics of it yet.

Bottom line though, you need to know your Monthly Commercial Transactions numbers to gauge how this new pricing will impact you.

In order to help with this for customers who are currently on Acumatica 2017 R2, Acumatica has provided a Customization Project which basically is nothing more than a way to deploy a SQL View query to a Generic Inquiry. Hey, that’s basically what my Query Quarry project is: SQL Views deployed as Generic Inquiries. The trouble is that this Customization Project from Acumatica isn’t enabled for OData by default. So I just included the SQL View code in my Query Quarry project. Then I built today’s Power BI example using the regular Query Quarry customization project which now has this new SQL View added to it.

When counting your Monthly Commercial Transactions, it’s kind of annoying to have to find the largest category in each month. One month the largest category might be Sales Orders, then the next month it might be Shipments, etc. But your eye has to manually look through all of the categories to find the largest one each month.

To make it more annoying, what if you are an Acumatica partner and you want to analyze Monthly Commercial Transactions across all of your clients? Now you have to do a lot of manual work to get the largest Monthly Commercial Transactions category each month.

This is the kind of thing that Power BI is good at. Here is my solution with some sample data.

For some reason, this Power BI chart isn’t working in all web browsers. If you don’t see anything in the space below, then click here to see the chart.

Some Notes:

  1. Notice how the largest category can change from month to month. And notice how the largest category at the Instance level may be different than at the Company level.
  2. It’s setup to look at the previous month and prior. So, if you refresh the file in May, the first column will now become 2018-04.
  3. You can scroll to the right to go as far back as you’d like.
  4. The important numbers are really the ones at the Instance level since the pricing is based on the Monthly Commercial Transactions for the whole Instance. But, it’s kind of interesting to also look at the breakdown per Company so I included Company in this analysis.
  5. I don’t think the Customization Project provided by Acumatica is correct. For example, if you look at the SQL code in it, there is nothing for Shipments. But Shipments are one of the categories listed. Also, I have no idea why AP Pymt Adj would be a legitimate transaction type category (that’s weird). So I have a feeling that Acumatica will eventually provide an updated Customization Project. If that happens, I’ll update Query Quarry with the new SQL code.

The real brains in this example are in the measures. There are three measures being used by this report, with the Top TranType Amount and TranType being the one that is displayed above:

  1. Total Transactions = SUM(‘Acumatica Data'[Quantity])
  2. Top TranType Amount = MAXX( TOPN( 1, SUMMARIZE(‘Acumatica Data’,’Acumatica Data'[TranType],”Document Count”,[Total Transactions]), [Document Count] ), [Document Count])
  3. Top TranType Amount and TranType = FORMAT([Top TranType Amount],”#,###”)&” – “&[Top TranType]

Credit where credit is due, I did not come up with these measures on my own. I referenced this very useful blog post (click here).

The other trick was to get Power BI to loop through multiple Instances and Companies. For that I used the Function trick described in this very useful blog post (click here).

Many thanks to the authors of these two blog posts.

 

Connect This to Your Acumatica Data

Are you curious to see what this Power BI example looks like on your Acumatica data?

First, you need to download and install Query Quarry since this example is based on Query Quarry. Click here and scroll to the bottom for instructions. Note that you need to install Query Quarry in each Company in each Instance that you want to analyze.

Second, if you don’t already have Power BI Desktop installed, you need to download it from Microsoft. Click here and then click the DOWNLOAD FREE button. Run the install after it’s downloaded.

Third, you need to download the .pbix Power BI Desktop file used in this example. Click here to download.

Fourth, open the .pbix Power BI Desktop file used in this example in Power BI Desktop and put in the Instances and Companies that you want to connect to. You do this by listing them in a table like this:

 

Click Edit Queries

Power BI and Query Quarry

 

 

Click the gear icon on the right on the Source line. This should open a Table.

Power BI and Query Quarry

 

 

Here is what the table looks like with some sample values:

InstanceName is where you put the friendly instance name that you want to display in the report.

CompanyName is where you put the friendly company name that you want to display in the report.

AcumaticaURL is where you put the URL to this Acumatica instance.

AcumaticaCompany is where you put the Acumatica Company Name. If there is only one Company in this Acumatica Instance, then you need to leave AcumaticaCompany empty like you can see in the last row of the screenshot above.

Power BI and Query Quarry

 

 

 

Click OK

Click Close & Apply which should cause Power BI to load data from all the Instances and Companies you just listed (this could take a while)

Power BI and Query Quarry

 

 

 

Click Refresh when you want updated data

Power BI and Query Quarry

 

If prompted, choose Basic, then enter your User name and Password, then click Connect

Power BI and Query Quarry

 

You should now be looking at this Power BI example with your own data. Cool huh?

Filed Under: Query Quarry Tagged With: Acumatica, Acumatica Blog, Acumatica Learning, Acumatica Reporting, Acumatica Training, Getting Started with Power BI, Power BI

By using this website, you agree to our Terms of Use (click here)

Online Members

Recent Blog Posts

  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
  • EP 35: Ideas for AUGForums.com Live with Ryan Brown (Podcast) December 12, 2020
  • EP 34: Talking Acumatica eCommerce and the Next Normal for Distribution, Wholesale, and Manufacturing with Ted Stenstrom (Podcast) December 11, 2020
Acumatica Learning Resources

Recent Forum Posts

  • Acumatica Payroll PTO Banks adjustment

    Has anyone figured out how you would make a manual entr...

    By MSylvia, 3 hours ago

  • Best Practices/Suggested Processes for Before and After Acumatica Version Upgrade

    We have a process in Sage 100 where we will run various...

    By CLove26, 4 hours ago

  • Denominated Currency Accounts, Foreign Currency and Project Accounting

    I'm noting this here for some future reference. I've s...

    By megan.friesen, 4 hours ago

  • RE: Importing Acumatica User Roles / User Security Permissions from Excel

    I tried a simple test in 2020R1 and 2020R2 and it doesn...

    By AhmedBahar, 6 hours ago

  • Combining Bill Retainage Invoices to one AR Invoice in Construction

    Hi was wondering if anyone has come up with a good work...

    By Mike Baio, 11 hours ago

  • RE: Generic Inquiry RESULTS GRID Column Order

    On the Results Grid tab you can drag and drop the items...

    By Michael Roszkowski, 12 hours ago

  • RE: Acumatica 2020 R2 OwnerID Field

    Thank you, Tim. Just faced this problem in a customized...

    By Anna Borisova, 1 day ago

  • Import Azure AD Groups to User Roles Screen

    Hi AUG, I'm cross posting this from the community as I ...

    By Michael.Barker, 1 day ago

  • Link EPApproval to APInvoice

    I am trying to write a GI that will link an Invoice to ...

    By Michael Roszkowski, 1 day ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.