There are several ways to aggregate or compute values in Cube, and sometimes, multiple ways exist to perform the same calculation. Not sure what to use for your use case? Here are some examples.
When to use a dimension rollup
Dimensions are organized into a hierarchy of parents and children under each top-level dimension. Child dimensions automatically roll up into their parent dimension to produce a value at the parent level. Learn more about Cube's data model.
You can choose which time rollup type to use (sum, balance, or average) for Account dimensions. Children will automatically sum at the parent level for all other top-level dimension types.
Your dimension rollups will align with your Chart of Accounts and your custom data hierarchy and are your default way to calculate values in Cube.
Note: Reparenting or moving dimensions may affect your rollups and could cause tie-out issues. Consider using tags or formulas for calculations instead when you're not looking to make permanent changes to your dimension hierarchy.
When to use a tag
You can use tags to create an alternative rollup to your Chart of Accounts without changing your dimension hierarchy. Tags sum custom groupings of dimensions and could be used to define certain accounts as Fixed vs. Variable Costs or group departments by their leader.
Tags can be fetched like a dimension when working in your spreadsheets but are outside of your dimension hierarchy. This means they will not appear on your dimensions page in your Web portal and cannot be used in formulas.
Learn how to create tags.
When to use a formula
If you want to do more than sum values, you can perform complex calculations using formulas in your Chart of Accounts. Formulas are associated with a particular dimension and can calculate a value based on other Cube dimensions using several supported operations.
Formula dimensions can be used in other formulas, and because they sit in your dimension hierarchy, their values also roll up to their parent dimensions.
For instance, Gross Margin can be added as a formula in Cube by dividing the formula dimension "Revenue" by "Cost of Goods Sold."
The Formulas page in the Web portal shows all formula dimensions and the calculations they perform, saving you time. Instead of scrolling through the Dimensions page and clicking on each dimension to see the underlying formula, you can see it all on the Formulas page.