Planning Application Diagrams

Diagram 01. Application Architecture

The application has the following logic blocks:

- Master data
- Calculation rules
- Source cube
- String values
- Calculated cube
- Form settings
- Forms
- User permissions
- Tracking changes

Master data include dimensions, dimension members and its properties.

The application creates calculation rules to calculate aggregates based on master data and source cube.

The source cube contains number data entered by users or loaded from external systems.

The block of string values contains string values entered by users. The application does not aggregate such values.

The calculated cube allows getting the source and aggregated data in forms.

Form settings define available forms including their layouts, rows, columns, page data, and parameters.

The Forms block is an engine that executes configured forms to allow getting data and saving data back to a database.

User permissions is a block that allows managing user permissions on forms and dimension members.

Tracking changes is an optional block implemented with the Change Tracking Framework.

Diagram 02. Master Data

The master data includes:

- Currencies and units in the dbo25.units table;
- Tax rates in the dbo25.tax_rates table;
- Dimensions in the dbo25.dimensions table;
- Dimension members in the dbo25.members table;
- Dimension member parents in the dbo25.parents table.

Use the following procedures to edit master data:
xls25.usp_units, xls25.usp_tax_rates, xls25.usp_dimensions, xls25.usp_members.

The dbo25.calc_types and dbo25.name_formats table contains application defined values.

The dbo25.dimensions table contains seven dimensions.
The first four (Accounts, Times, Categories, and Entities) have specific built-in support and must be active.
Analysts may activate or deactivate the last three dimensions, and change their meaning.

Adding additional dimensions requires changes in the application code.

The dbo25.members table contains members of all dimensions.
It contains columns of the member calculation and display properties.

The application supports multiple hierarchies. Each member can have multiple parents.
The dbo25.parents table contains such relations.
The application allows editing parents and its factors using the xls25.usp_members procedure.

Diagram 03. Calculation Rules

Based on child-parent relations, calculation type, and previous periods,
the xls25.xl_actions_update_hierarchies procedure updates the dbo25.factors table.

This table is used to calculate aggregates and balances.

For example, for member parents A1 - A2 and A2 - A3 the factor table contains A1 - A2, A2 - A3, and A1 - A3.

Use the xls25.view_hierarchies view to check the generated rules.

Diagram 04. Member Hierarchies

Based on child-parent relations, the xls25.xl_actions_update_hierarchies procedure also updates the dbo25.hierarchies table.

This table is used to select members of the specified parent in forms.

For example, for member parents A1 - A2 and A2 - A3 the hierarchy table contains A1 - A1, A2 - A2, A3 - A3, A1 - A2, A2 - A3, and A1 - A3.

Use the xls25.view_hierarchies view to check the generated rules.

Diagram 05. String Values

The application stores string values in the dbo25.strings table.

The application does not aggregate such values.
So, it stores values for members at any hierarchy level.

The dbo25.strings table contains member id values in columns id1-id7.

If the dimension is not active, its column contains the dbo25.dimensions.default_member_id value.

To store string values, specify the string calculation type of the target account (dimension 1).

Diagram 06. Source Cube

The application stores number values in the dbo25.facts table.

The dbo25.facts table contains member id values in columns id1-id7.

If the dimension is not active, its column contains the dbo25.dimensions.default_member_id value.

The table contains source values of the user input, in the account currency or unit, in the source_value column.

The application calculates the cube values, in the system currency or base unit, and updates the value column immediately on input.

It uses the dbo25.currency_rates table values for currency accounts and just a dbo25.units.factor value for other accounts.

Use the xls25.xl_actions_update_facts procedure to update cube values after currency rate changes.

Note that the application uses hard-coded dimension id values:

1 - Accounts
2 - Times
3 - Categories
4 - Entities

Diagram 07. Calculated Cube

The application calculates aggregates and balances on the fly.

It uses the source and calculated cube values of the dbo25.facts table and calculation relations and factors from the dbo25.factors table.

You may use the code of the dbo25.view_facts view to learn the calculation rules.

The application uses the dbo25.members.previous_period_id and dbo25.member.same_period_id fields to select values of the referred periods.

Also, the forms output Excel formulas as is. Such values are calculated in Microsoft Excel.

Diagram 08. Form Layouts

The application allows creating any number of forms in the dbo25.forms table.

Analysts have to define the form layouts including:

- the dimension axis (row, column, page),
- the dimension order in the axis (header_order),
- and the parameter order at the ribbon (parameter_index).

Analysts may test any form using the xls25.usp_run_form and xls25.usp_run_offline_form procedures.

End users use the inherited forms with the predefined sets of the parameters.

They have to use the xls25.view_query_list_forms and xls25.view_query_list_offline_forms views in the SaveToDB Connection Wizard.

By default, the application shows child members of the dbo25.dimensions.root_member_id member in the parameters and axes.

Analysts may configure member lists using the feature described below.

Diagram 09. Form Rowsets and Parameters

Analysts may customize member lists for parameters, rows, and columns.

The fist option is to redefine dbo25.form_dimensions.root_member_id.

For example, you may set the "Sales" account as a parent for the "Sales" form.

The second option is to limit a member list in parameters by levels using the parameter_start_level and parameter_end_level parameters.

For example, the time dimension has a hierarchy: Times - Years - Year - Month.
If you need to show only years in the parameter, set Years as a root_member_id and 1 as parameter_start_level and parameter_end_level.

You may use the same technique for rows and columns.
Just use the form_start_level and form_end_level parameters instead.

For example, to show years only in columns, set Years as a root_member_id and 1 as form_start_level and form_end_level parameters.

And the final option is to define a member list completely manually.

Use it, for example, if you need to have selected rows from multiple hierarchies, add empty lines, or change codes, names, or Excel look of members.

To do this, create a member with calc_type_id = 9 (rowset) and add rows into the dbo25.form_rows table (you copy and paste rows in Excel).
Then rewrite any property of the underlying member in the row.

The application shows rowsets in the ribbon parameters automatically.
If you need to use the rowset only and to disable other options, set the rowset as the root_member_id value and clear the parameter_index value.

Diagram 10. Member Relations

The dbo25.member_relations table contains relations between members of different dimensions.

The first use case is eliminating rows in forms.

For example, the "Sales" account is not applicable for all entities.
So, you may set the select_permission to 0 for such pairs of the account and the required entities.

The second case is disabling data changes, for example, for closed periods.

In this case, you may set the update_permission to 0 for the pair of time and category members.

These settings are common for all users of the application.

Diagram 11. Member Permissions

The dbo25.member_permissions table contains user permissions for dimension members.

Administrators (members of the planning_app_administrators role) may allow:
- reading (select_permission = 1, update_permission = 0),
- reading and writing (select_permission = 1, update_permission = 1),
or deny reading and writing (select_permission = 0, update_permission = 0).

These modes have mnemonic letters R, W, and D accordingly.

When an administrator sets the permission, the application applies it to children also with the is_inherited = 1 flag.

For example, if the administrator disables the "Sales" account, the user will not have access to the child members of the "Sales" account also.

The administrator can set different permissions for children in this case.
Such direct child permissions have a higher priority.

Analysts and developers may enable or disable verifying permissions using the dbo25.dimensions.is_protected field.

Also, analysts may define the default permissions for dimension members in the dbo25.dimensions table.

The application uses the USER_NAME() function to get the username.

The application checks the permissions for members of the planning_app_users role only.
So, the developers and analysts always see the complete sets of members.

The application does not implement permission management by roles.

Diagram 12. Form Permissions

The dbo25.form_permissions table contains user permissions on forms.

The application does not allow executing disabled forms and does not show such forms
in the xls25.view_query_list_forms and xls25.view_query_list_offline_forms views used in the SaveToDB Connection Wizard.

The application uses the USER_NAME() function to get the username.

The application checks the permissions for members of the planning_app_users role only.
So, the developers and analysts always see the complete sets of forms.

The application does not implement permission management by roles.