Using Power BI to Build Customer Insights Part 1

 In Accounting Systems, Power BI

Its really important to be able to analyse your customer transactions to ensure you understand the key drivers to your income generating activities.

No matter what accounting system you use, the principals around the model build process are essentially the same.

Once you have written down your reporting goals and ascertained what data you require and where to source it, you can start to extract data and build your model.

In my example below, I’m using MYOB’s Account Right data file which has an API that is easily accessible if your file is stored locally on your own hardware or via a power query connector if your business use MYOB’s cloud to host your data on their cloud.

Note that most packages like Xero, Quick Books etc will have similar table structures for accessing your accounting data.

Here is the link to the setups for some of the SME packages available in Australia.

Xero Accounting
https://developer.xero.com/documentation/

MYOB Accounting
https://developer.myob.com/api/accountright/v2/

Quick Books https://developer.intuit.com/app/developer/qbo/docs/develop

The documentation for each provider lists which tables you need to query along with examples of how to do it.

Here’s an example of my customer model from a MYOB data source which illustrates how I have configured the relationships between the fact tables and dimension tables stored in the accounting package.

In this scenario I want to report on Customer transactions facts by product, job, country and currency dimensions.

Customer Model

Customer Table Design

Note that I have extracted two fact table control ledgers for Customer Sales and Customer Payments.

In addition I have added the two fact sub-ledgers (one for products sales and one for service sales) so we can report on the detail of the transactions not held in the control tables.

Which tables you retrieve and how many transformations you are required to make will depend on your reporting goals and what data is available to you.

The dimensions are pretty much taken directly from the accounting system except for the date table which I built as a data flow so i can re-use in many models.

So once you have your fact and dimension tables and created the star schema relationships I have shown above your ready to start building measures against your data tables.

A couple of points here related to dates and whether you want to say analyse data by more than one date. If you have say multiple dates like Order Date, Sales Date and Delivery date then you need to adopt some additional techniques to satisfy this requirement. You can use DAX’s userelationship function or adopt seperate date tables for each type of date you need analysis on.

Stay tuned for part two where I examine common measures to use when building customer insights and how your date table can help you deliver these insights.

Recommended Posts

Leave a Comment

Start typing and press Enter to search

Foreign Currency In MYOB