Matrix versus Rules

In Board is is for example possible to use a Chart of Accounts based on multiple tables in a relationship, see also the section about the Unbalanced Chart of Accounts. 

The advantage of using multiple tables is that amounts are automatically aggregated to a higher level.


A typical Chart of Accounts based on tables and relationships will look like this:


The disadvantage of tables and relations is that you have limited control over the formatting, what if you want for certain sections to show all details and for other sections you want to show only the aggregated lines.


This is why you often see that customer - consultants use a single table, in which all the accounts are stored. This is often referred to as flat table. The advantage of a flat table is that you have more control over the layout, you can use a template and a matrix to show or hide certain accounts.


This is a sample of a flat table, as you can see you can use different fonts and colors to format your table.


 


In a flat table you have accounts which contain imported data and accounts, which are used for subtotals or totals. You will need a process to calculate the subtotals and totals and often consultants use rules for this.


Rules

Board offers a function called rules, you will find this function in the data model section (Yellow B button). 

You can create rules for your entity tables and the rules contain calculation for certain entity members. 

Below you see an example and in these rules we define calculation, rules, for certain GL Accounts. 

In this example you see that the account B1070 is the sum of the accounts B1020 + B1030 + B1040 and B1050



After you have created a rule you can use the rule in a data view, it is important that the entity for which you created the rule in on your rows or columns in your data view.


In the example below you see a data view. For the rows I selected the entity GL_Account_Report. I selected the Cube GL Transactions and in the options on the right side I activated the option rules and selected the rule.



When you run your report you will see that your totals and subtotals are now calculated based on the rules you defined in your rules.


So the advantage of using a flat table in combination with rules are:

  • You have options to format your report
  • The concept of rules is simple and users who have advanced user or admin license can maintain rules.


Now there are also some disadvantages of using rules:

  • Rules don't work if you filter your entities for example if you put a filter on your accounts and only show the totals, then the rules will not work.
  • It is not possible to use the drill anywhere function on a entity member, which contains a rule.


Using a matrix as alternative to rules

 An alternative for the rules is using a matrix. If you want to use a matrix then you will need two entities and 3 cubes.

Entities:

  • Source entity on which you import your data, e.g. your accounts in which you import your data.
  • Target entity e.g. your reporting entity with all your accounts


Cubes:

  • A matrix cube, which is linked to your source and your target entity.
  • A source cube on which you import your source data
  • A target cube, which is linked to your source and target data, and this cube will be used in your reports.


In a matrix screen you will define the relation between your source and target accounts, and this screen could look more or less like this: In the example below you see your reporting entity in the rows and your source entity on the columns. 

With a matrix you define the relationship between your source and your target entity.



On first impression you might think that a matrix is significant more maintenance than using rules. But with the right setup, there is no difference between maintaining rules and maintaining a matrix. 


These are some guidelines for a a good setup.


In your target entity you use the same keys as in your source table, for example if you have account 7020100 in your source table, then you make sure that you have the same account in your target table. Now you can use a simple data reader to link your source accounts to your target accounts on the lowest level.


For your subtotals and totals you need to setup a the right key format in your entity keys. In the example below you see that my total sales has the ID 1010, the second level have the format 1010-XX and the third level have the format 1010-XX-XX. 


So now you only need to configure which accounts belong to level 3, which you also need to do when you would use rules. All the levels above can be generated automatically using a process with a data reader.


The advantages of using a matrix are:

  • Users with a lite plus, advanced user or admin license can maintain the matrix.
  • You can also change your structure over time,  while maintaining your history, simply by adding a time member your your matrix.
  • You can filter your accounts in your screen and still see the correct totals.
  • You can use the drill anywhere function on your totals and sub totals.