Basic concepts
Rules are sets of formulas defined on members of the same Entity. Using a Rule, you can define the value of a Cube cell for a specific Entity member as the result of a formula involving other members of the same Entity, regardless of any Selection that may be active on that Entity.
A Rule is always associated with a single Entity and can be used with (or applied to) all Cubes that have that Entity as a dimension in their Structure.
A typical example where Rules come in handy is for profit & loss statements (P&Ls), where some P&L lines are calculated from other lines. In this case, the P&L can be considered as an Entity in which the P&L lines are its members, as in the following table:
Member code | Member description | Rule |
L01 | Gross Sales | |
L02 | Cost of goods sold | |
L03 | Gross Margin | = L01 - L02 |
L04 | % on GS | = (L03 / L01) * 100 |
L05 | Distribution costs | |
L06 | Inventory | |
L07 | Total distribution costs | = L05 + L06 |
L08 | % on Gross Margin | = (L07 / L03) * 100 |
L09 | Marketing costs | |
L10 | Advertising | |
L11 | Total marketing costs | |
L12 | % | |
L13 | R&D | |
L14 | IT costs | |
L15 | Gen & Admin | |
L16 | Other Operating Expenses | |
L17 | Total Operating Expenses | |
L18 | % | |
L19 | Net Operating Income |
As you can see in the table, the rules set on members L03, L04, L07, and L08 will calculate the corresponding values based on other members of the P&L Entity when said Entity is in the Structure of a numeric Cube.
When your Data model includes a large amount of Rules it might be difficult locating a specific Rule or understanding the purpose of all Rules listed in the table. In this case, we strongly recommend that you logically group them using the Group field in the Entity metadata panel.
Groups are not part of the multidimensional Data model (i.e. they cannot be used in reports or Procedures): the only purpose of groups is to improve reading and searching through the list of Rules.
To access the Rules section of a Data model, access the designer space of the desired Data model and click on the Rules tile.
In the Rules page, you can see all existing Rules of the Data model and their main information: the table is sortable and searchable using the interactive header fields. You can also show or hide columns to your liking, by clicking the Column chooser button in the upper right corner of the table.
You can perform different actions on one or more Rules by selecting them and by clicking on the different buttons above the table.
From this page, you can:
- Create new Rules
- Edit existing Rules
- Clear formulas of one or more Rules
- Delete Rules
- Import and export Rules