AcumatiCaresIn this “Technical Tuesday” post, Doug Johnson is sharing an end-to-end tutorial covering one of our powerful new integrations by showing you how to pull fantastic visual data insights from your Acumatica data using Microsoft Power BI and OData.
Scenario: Acumatica provides a single repository for your financial, distribution, CRM, project, and other business data. Gaining access to this data is easy via reports, screens, and dashboards–but it would be great if I could see data in a visually pleasing, easy-to-produce, format.”
Solution: Several business intelligence tools provide advanced visualization technology to assist with this process. Using Acumatica inquiries, you can expose any Acumatica data via an Open Data Interface (OData) to several BI tools, but we’re going to feature Microsoft Power BI.
Business Intelligence Overview
The term “business intelligence” encompasses many different tools that help people gather information and make conclusions about data. At the low end, there are spreadsheets and reports. At the high end there are data warehousing applications that gather information from multiple sources, process it into a semantic layer based on data relationships, and then present it via a visualization layer.
In this article, we discuss mid-tier solutions that gather data from multiple sources and build some simple relationships, but do not create a data warehouse or semantic layer.
Create Acumatica data visualizations using OData and Microsoft Power BI
Power BI versus Excel
You can create Acumatica data visualizations using Excel or Power BI. Excel provides a familiar interface as well as the ability to quickly apply formulas to Acumatica data.
Power BI allows for some other advantages over Excel:
- Access and share data online
Acumatica and Power BI can be operated from multiple devices - Additional visualizations (speedometer, maps)
- Language-driven queries and general ease of use
Power BI is a perfect complement to Acumatica because both provide web-based access using only a browser. This makes it easy to share authorized data across geographies and on different devices.
Linking Acumatica and Power BI
Acumatica Generic Inquiries and OData
Acumatica data can be exposed using web services, integration tools, and reporting tools. Web services and integration tools expose data from specific Acumatica screens – so they make data available using the standard data classes. Reporting tools allow you to mix and match data tables to expose any data you need.
The generic inquiry writer is a reporting tool that can access any data in Acumatica, including data stored in customized fields, and publish it. The OData formatting option exposes the data in the Open Data Format supported by business intelligence tools like Microsoft Power BI.
Acumatica Generic Inquiries can publish data using the OData standard
Checking the “Expose via OData” box on an Acumatica generic inquiry establishes a data ‘endpoint’ that can be used to request the data. You must add the inquiry to the site map in order to provide the proper security access permissions for the data.
OData and Security
Create an OData Endpoint in Acumatica
An OData endpoint allows an external system to request data from Acumatica. Each request requires authorization so only data accessible to a specific user is made available. For example, in the screen below, we establish access rights for the inquiry called Invoiced Items. If a user submits a request for this data, they must have administrator permissions.
OData Inquiry Access
List of Available Inquiries
You can view a list of available Acumatica inquiries with OData endpoints by submitting an HTML request. The request is formatted as follows:
- http://<application site domain>/odata/<company name>
The Company Name is only required if you have a multi-company deployment.
Specific Generic Inquiry Access
To view a specific inquiry, add the name of the generic inquiry to the request above:
- http://<application site domain>/odata/<company name>/<Generic Inquiry Name>
The generic inquiry name is the value in the Inquiry Title. In the screen above, this is ‘InvoicedItems’.
OData Format
Acumatica supports different notations (formats) for exposing OData. The default is the JSON notation. You can select a different notation by appending the text “$format=<format>” to the end of the query using the following values for <format>:
- json: Data displayed in JSON notation (default)
- atom: Data displayed in ATOM notation
- jsonverbose: Data displayed in JASON Verbose notation
For example, BI data can be exposed to Tableau using the ATOM notation.
Receiving the Data with Power BI
Background
Power BI is an online service. The first step is to get a subscription from Microsoft, through the Power BI website. There is a free trial service that you can use to demonstrate data consumption from Acumatica. You can sign up at http://powerbi.microsoft.com.
You can currently try Power BI for free
Linking Acumatica OData Content
After you have a Power BI account, you can import several different types of data such as Excel workbooks, SQL server databases, Google Analytics statistics, and many more.
There is no direct import of an OData endpoint, so you have to create a Power BI Designer File using a desktop utility called the Microsoft Power BI Designer. This tool is currently available for free.
The Power BI Designer can consume OData formatted data with the JSON notation.
Power BI Designer
After installing the Microsoft Power BI Designer perform the following steps:
- Select the option to Get Data
- Select the OData Feed as your data source
- Enter the Acumatica endpoint in the URL box. This is the endpoint to get a list of available inquiries.
OData Feed - Acumatica returns the list of available generic inquiries. Select the inquiries that you want to use to build your visual reports. If you select multiple inquiries, Power BI will require that you specify a way to join them if you use data elements from both in a report or graph.
Power BI Navigator - After selecting the queries, the system will connect and display a preview of the data in the Query section. Also note that any steps you apply to process the data will be tracked in the right hand ‘Applied Steps’ pane.
Power BI Applied Steps Pane - As this point you can save your file or begin creating reports and queries using the Power BI Designer. If you create reports in the Power BI Designer, they will become available to the Power BI online tools.
Power BI
The last step is to import your Power BI Designer file (.pbix) to the Power BI web tool.
Power BI Designer File Preview Pane
After following the simple wizard, your data is available. As you can see below, there are two items created. The first is a dataset consisting of your Acumatica queries. The second is a report containing all the items you created.
Power BI Preview Screen
Now you can use the Power BI tools to create charts, graphs, KPIs, maps, and other items to help you visualize your data.
Some Best Practices
There are a few things that will help you get the most from your data visualization.
Tip 1: Date File
Specifying date ranges in various formats is something needed for your queries. Acumatica includes a DateTable that you can use to create a simple Generic Inquiry. The inquiry allows you to specify date ranges and allows you to add quarters, month names, days of week, etc. into your queries.
The inquiry is available in the Acumatica Sample Data available through the partner portal (http://partners.acumatica.com). The inquiry is called BI-CreationDate in the sample data.
Tip 2: Simple KPIs
Use Excel spreadsheets to import data that is not available through an Acumatica inquiry. In many examples, I use a spreadsheet (included in Acumatica demo data) that includes budgeted sales and budgeted profits.
Budgeted Sales Spreadsheet sample
This data is frequently useful for exposing data in the gauge format.
2015 Sales Target
Tip 3: Data Calculations
Data calculations can be made by the Acumatica generic inquiries or by the business intelligence tools. A couple suggestions based on my limited experience are:
Record Level Calculations
Adding, subtracting, multiplying fields within a record are easily performed within Acumatica. An example would be Total Sales – Total Cost = Margin. The margin is easily calculated within the Results Grid of the Acumatica Inquiry in the Data Field column.
Summing, Averaging Calculation
Power BI tools should be used for summing, averaging, or selecting max/min from a selected group of records. If these computations are done by the Acumatica generic inquiry, then all data will not be passed to Power BI. If you want to limit the amount of data passed to Power BI, then these calculations should be performed inside Acumatica.
