This is the multi-page printable view of this section. Click here to print.
Dimensions
1 - Dimension Functions for Expressions and Aggregations
Functions for use in Dimension Hierarchy Expressions
Within the Dimension Hierarchy screen it is possible to add 'Aggregations' and 'Expressions'. A description for these is included below.
Aggregations
An Aggregation is used to display an aggregated value from a table (which can be 'Sum', 'Count', 'Min' or 'Max') The following image shows an Aggregation that has been configured to pull values from a 'Line Item Values' table so that values can be displayed for each 'Period' in the hierarchy.
Aggregations can be filtered so that only items matching the filter are displayed. In the following image we have set up the aggregation to show values for a selected item in the 'Account' dimension.
If these filters are left blank then the data can be filtered by using the dimension filter bar at the top of the screen, as can be seen in the following image:
Expressions
Using Expressions it is possible to display values which are calculated based on values from Aggregations displayed for the dimension. Expressions are built using mathematical formulae, which can contain many kinds of operators, and some special functions. The list of operators available can be found here. The functions available are described below
Functions
column(<column_name>)
Fetch a value from a named column for the current row/node.
Below we see an example of an Expression being defined to display the result of multiplying the Line Item Value by 2.
childCount()
Returns the number of children for the current row/node. If the current row/node is a leaf item this will return 0.
In the following example this is being used to return the average value for the child nodes of a parent node.
leafCount()
Returns the number of leaf items found in the tree for the current row/node. If the current row/node is a leaf item this will return 1.
descendantCount()
Returns the total number of items found in the tree for the current row/node. If the current row/node is a leaf item this will return 0.
siblingCount()
Returns the number of sibling items for the current row/node. The value returned includes the current node.
nodeValue("<node_name>","<column_name>")
Returns the value from a named column for a named node. Here's an example which is used to show the percentage of the "LIV" total for each row/node.
parentValue("<column_name")
Returns the value from a given column for the parent of the current node. This example shows the percentage of the value from a parent node being used by a child node.
columnTextCompare("<column_name", "")
Returns a numerical result representing if the text in a named column is greater than, less to, or equal to a provided value.
If the text from the column equals the provided text then this function returns 0.
If the text from the column is less than the provided text then this function returns -1.
If the text from the column is greater than the provided text then this function returns 0.
The following example compares the name of the Period to "Jun"
Conditional Expressions
The examples shown above are fairly simplistic. By using conditionals within expressions it is possible to create more complex expressions.
Within Expressions conditionals take the following form:
By combining expressions containing both conditionals and functions we can build more complex expressions, such as this example where 100,000 is added to a Line Item Value if the month is "Jun"
Another example: Simple Allocation
This example shows the amount of a parent's Line Item Value consumed by using the Resource Driver Value for a leaf node.
Limitations:
It is currently not possible to build Expressions which are based on values from other Expressions. Expressions can only be built using values from Aggregations.
2 - Loading and Unloading Dimensions
Dimensions can be maintained from workflow operations by loading data. In addition, dimensional data can be flattened into tabular data and stored in tables. This is often useful for enriching reporting and analytics data.
Loading Dimensions
Since dimensions represent hierarchical data structures, the load process must convey the relationships in the data. PlaidCloud supports two different data structures for loading dimensions:
- Parent-Child - The data is organized vertically with a Parent column and Child column defining each parent of a child throughout the structure
- Levels - The data is organized horizontally with each column representing a level in the hierarchy from left to right
In addition to structure, other dimension information can be included in the load process such as values, aliases, and properties.
See the Workflow Step for Dimension Load for more information.
Unloading (Exporting) Dimensions
Exporting dimensions to tables supports two structural approaches:
- Parent-Child - The data is organized vertically with a Parent column and Child column defining each parent of a child throughout the structure
- Levels - The data is organized horizontally with each column representing a level in the hierarchy from left to right
Properties and values can also be included in the flattened tabular data.
See the Workflow Step for Dimension Unload for more information.
3 - Using Dimensions (Hierarchies)
PlaidCloud natively manages dimension (i.e. hierarchical) data through our proprietary hierarchy storage system. We decided to construct our own from purpose-built solution because other commercial and open-source solutions seem to present limitations that were not easily overcome.
The hierarchy storage supports not only hierarchical relationships but also properties, aliases, attributes, and values. It is also designed to operate on large structures and perform operations quickly including complex branch and leaf navigation.
Dimensions are managed in the Dimensions tab within each PlaidCloud project configuration area.
Main Hierarchy
Each dimension (i.e. hierarchical dataset) always consists of a main hierarchy. Every member of the hierarchy is represented here.
Having a main hierarchy helps establish the complete set of leaf nodes in the dimension.
Alternate or Attribute Hierarchies
Alternate hierarchies are different representations of the main hierarchy leaf nodes. Alternate hierarchies can consist of a subset of both
leaf nodes and roll-up (i.e. folders) in the main hierarchy as well as its own set of unique roll-ups.
This provides for the maximum amount of flexibility by automatically updating alternate hierarchies when children of a roll-up change or to strictly control the alternate hierarchy members by specifying only the leaf nodes required.
main hierarchy have attribute labels showing alternate hierarchies for which they also belongManaging Dimensions
Creating a Dimension
From the New button in the toolbar, select New Dimension. Enter in the desired name, directory, and a descriptive memo.
Once you press the Create button the dimension will be created and ready for immediate use.
You can also create a dimension from a workflow using the Dimension Create workflow step.
Deleting a Dimension
To delete an existing dimension, select the dimension record and open the Actions menu in the upper right. Select Delete Dimension.
This will delete the dimension and all underlying data.
You can also delete a dimension from a workflow using the Dimension Delete workflow step.
It is also possible to clear the dimension of all structure, values, aliases, properties, and alternate hierarchies without deleting the dimension by using the Dimension Clear workflow step.
Copying a Dimension
To copy an existing dimension, select the dimension record and open the Actions menu in the upper right. Select Copy Dimension.
This will open a dialog where you can specify the name of the copy. Click the Create Copy button to make a copy of the dimension
including values, aliases, properties, and alternate hierarchies.
Sorting a Dimension
The dimension management area makes it easy to move hierarchy members up and down as well as changing parents. It also makes it easy to create and delete members.
However, it can get tedious when manually moving hierarchy items around so you can sort a dimension from a workflow using the Dimension Sort workflow step. This can be a big time saver especially after data loads or major changes.
Dimension Property Inheritance
A dimension may inherit a property from an ancestor. To enable inheritance, click the dropdown next to Properties and select Inherited Properties. All child nodes in the dimension will now inherit the propties of its parents.
Usage Notes:
- Inheritance will happen for all properties in a dimension. You cannot set inheritance on one property but not another.
- If you change and then delete the value of a child property, it will default back to the parent value. You cannot have a null value when the parent has a value.
- If you set the value of a child property, its children will inherit the child property instead of the parent.
- Inheritance will go all the way down to the leaf node.