In any business it’s critical to be able to easily analyse your customer ledger transactions and have a repeatable process that allows you to have real time accurate analytics.

Customer insights and key sales drivers can be generated from your ledger transactions but you need to develop a data model that will hold the characteristics of that data.

For example, you might want to analyse customers sales by country, currency, project,  job, sales person or some other identifying dimension specific to your reporting goal.

This is where Microsoft’s Power BI software can be leveraged against any data source.

No matter what accounting system and processes you adopt, the principals around the building a customer ledger data model is essentially the same.

In this blog I touch on the main components of my customer ledger model and illustrate how I have grouped fact and dimension tables.

When building any model I start by writing down the reporting goals, what data characteristics are required and where to source and store the data.

In my example below, I’m extracting customer data from MYOB’s Account Right data file which has an API that allows you to interact with any of the ledgers stored in the data file.

Note that most accounting solutions like Xero, Quick Books, SAP, Microsoft Dynamics etc will have similar table structures for accessing your accounting and customer data.

In some cases, accounting solutions can also contain summary data tables (for example by financial year, by job, by division etc) which provide even faster access to your data.

Here are some links to some major accounting developer sites in Australia that contains key table structure information.

Xero Accounting

MYOB AccountRight

Quick Books

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

Here’s an example of a customer ledger data model using a MYOB Account Right data file as my data source.

Using the API, I entered the relevant power query to retrieve the dimension tables required and lined them up at the top of the model. At his stage I also added in the calendar table as a data flow so I can perform time intelligence functions later on.

You can see below the dimension tables I have retrieved are based on the need to report on Customer sales by Product, Region , Job and currency.

With the customer fact tables, I needed to extracted two control ledgers. One table for customer sales and one table for customer payments. These control ledgers hold just basic details about each invoice and payment made by customers such as the name of the customer, the amount and the date.

I also added two sub-ledger tables. The first subsidiary ledger holds customer product sales lines and the second sub ledger holds the service based sale transaction lines.

Sub ledgers contain a lot more dimensional characteristics about each sale made to a customer and its generally a lot larger and wider data table so be cautious about exactly what data you really need and extract for your reports. Choosing incorrectly could slow down your model a lot!

Configuring your fact tables is probably one of the harder things to work out with the modelling part of Power BI and given the various accounting systems on the market. I suggest always consult your documentation and draw out a plan to ascertain exactly which data fields and transformations are required to deliver reporting objectives and meet user expectations.

Once the fact and dimension tables are created, you can then build a star schema relationship between the facts and dimensions as shown above by the vertical lines shown with a 1 and *.

Most of the hard work is now done and the remaining tasks revolve around writing measures that can deliver insights about your customer ledger transactions.

Stay tuned for more customer ledger insights in future blogs where I do a deep dive into writing some key customer analytic measures.