In this blog post, we are excited to share a comprehensive tutorial on one of our powerful integrations. We will guide you through the process of extracting incredible visual insights from your MYOB Advanced data using the powerful combination of Microsoft Power BI and OData.
Imagine having all your financial, distribution, CRM, project, and other business data stored in a single repository with easy access through reports, screens, and dashboards. However, wouldn't it be even better if you could visualise this data in a visually appealing and effortless manner?
Fortunately, there are several business intelligence tools available that provide advanced visualisation technology to help you achieve just that. By leveraging MYOB Advanced Inquiries, you can expose any MYOB Advanced data using the Open Data Interface (OData) and integrate it seamlessly with multiple BI tools.
Business Intelligence Overview
When it comes to business intelligence, there is a wide range of tools available to help you gather valuable information and draw conclusions from your data. On one end of the spectrum, you have spreadsheets and reports that provide basic insights. On the other end, there are advanced data warehousing applications that collect data from various sources, process it into a semantic layer based on data relationships, and present it through visually engaging visualisations.
In this blog post, we will focus on mid-tier solutions that offer the perfect balance between simplicity and functionality. These solutions allow you to gather data from multiple sources and establish basic relationships, without the need for complex data warehousing or semantic layers.
By exploring these solutions, you can unlock the power of your MYOB Advanced data and transform it into visually appealing and effortless visual insights.
Power BI versus Excel
When it comes to creating visualisations for your MYOB Advanced data, you have the option of using either Excel or Power BI. Excel offers a familiar interface and the ability to apply formulas to your MYOB Advanced data quickly.
However, Power BI offers several advantages over Excel:
- Access and share data online: With MYOB Advanced and Power BI, you can operate both tools from multiple devices, allowing for easy access and sharing of data.
- Additional visualisations: Power BI offers a wider range of visualisations, including speedometers and maps, that can help you present your MYOB Advanced data in a more visually engaging way.
- Language-driven queries and ease of use: Power BI allows you to use language-driven queries to interact with your data, making it easier to explore and analyse your MYOB Advanced data.
Overall, Power BI is the perfect complement to MYOB Advanced because both tools provide web-based access using only a browser. This makes it simple to share authorised data across different geographies and devices.
Linking MYOB Advanced and Power BI
MYOB Advanced Generic Inquiries and OData
MYOB Advanced data can be easily accessed and exposed using a variety of tools such as web services, integration tools, and reporting tools. These tools allow you to gather data from specific MYOB Advanced screens and make it available using standard data classes. Additionally, reporting tools enable you to mix and match data tables to expose any data you require.
One such powerful reporting tool is the Generic Inquiry writer, which provides access to any data stored in MYOB Advanced, including data stored in customised fields. This tool allows you to publish the data and make it readily available for analysis. Furthermore, by utilising the OData formatting option, you can expose the data using the Open Data Format, which is fully supported by popular business intelligence tools like Microsoft Power BI.
MYOB Advanced Generic Inquiries can publish data using the OData standard
Checking the “Expose via OData” box on an MYOB Advanced 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
An OData endpoint allows an external system to request data from MYOB Advanced. Each request requires authorisation 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 MYOB Advanced 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.
For example, if the URL of your MYOB Advanced instance is https://iloveclouderp.myobadvanced.com and you want to view generic inquiries exposed in the I Love Cloud ERP tenant, you use the https://ineedclouderp.myobadvanced.com/OData/I need Cloud ERP URL in your browser or in an OData client. (If you type this into a browser, you will notice that the browser automatically replaces each space with %20 in the URL.)
Also, you can view the login name of the tenant to which you are currently signed in by viewing the User menu (as shown in the following screenshot), which you access by clicking the User menu button on the top pane of the MYOB Advanced screen.
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
Through OData, you can view the contents of any exposed inquiry. You compose the data by doing one of the following:
- If your MYOB Advanced instance contains a single tenant, append /OData/<GI_Name> to the URL of your instance.
- If your MYOB Advanced instance contains multiple tenants or a single tenant, append /OData/<TenantName>/<GI_Name> to the URL of your instance.
We recommend that you use the approach with the login name of the tenant specified in the URL for an instance with a single tenant if you may add more tenants to the instance in the future.
In the examples above, <TenantName> is the login name of the tenant in the MYOB Advanced instance for which you have configured inquiries, and <GI_Name> is the case-sensitive title of the exposed generic inquiry.
MYOB Advanced supports JSON and ATOM notations; by default, the data is displayed on the screen in ATOM notation. You can directly select a notation by appending the $format parameter to the URL of an inquiry. The parameter can have one of the following values, which reflect how the data is displayed:
- json: In JSON notation
- atom: In ATOM notation
- jsonverbose: In JSON Verbose notation
For example, if the URL of your MYOB Advanced instance is https://iloveclouderp.myobadvanced.com, the instance contains one tenant, and you want to obtain the contents of the BI-Customer generic inquiry in JSON format, you use the https://iloveclouderp.myobadvanced.com/OData/BI-Customer?$format=json URL (either in your browser or in an OData client).
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 MYOB Advanced. You can sign up at http://powerbi.microsoft.com.
You can try Power BI for free.
Linking MYOB Advanced 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:
1. Select the option to Get Data
2. Select the OData Feed as your data source
3. Enter the MYOB Advanced endpoint in the URL box. This is the endpoint to get a list of available inquiries.
4. MYOB Advanced 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.
5. After selecting the queries, the system will connect and display a preview of the data in the Query section.
6. 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.
Now you can use the Power BI tools to create charts, graphs, KPIs, maps, and other items to help you visualise your data.
