Contents Diagrams Roles Schemas Tables Views Procedures Functions

Planning Application
for Microsoft Excel and SQL Server

Version 2.5, September 10, 2018

Contents

Introduction

Planning Application for Microsoft Excel and SQL Server

This ready-to-use application allows planning in Microsoft Excel and getting reports from a SQL Server database.

Key features:

- Customizable dimensions and dimension members;
- Customizable calculation rules with multiple hierarchies;
- Customizable edit forms and reports;
- Built-in permission management;
- Built-in change tracking.

Here is an example of a customizable form:

The application includes complete source code and a set of user workbooks available through the SaveToDB Application Workbooks wizard.

You may read more in the e-book Planning Application with Excel and SQL Server.

This paper contains the descriptions of the database objects.

The application also uses the following frameworks:

- SaveToDB Framework
- Database Help Framework
- Change Tracking Framework

Feel free to contact us.

We may arrange a meeting to show the application and answer to your questions.

Custom developement and commercial support are also available.

Change History

Version 2.5, September 10, 2018

New Features:

Impovements:

Diagrams

DiagramDescription
Diagram 01Application Architecture
Diagram 02Master Data
Diagram 03Calculation Rules
Diagram 04Member Hierarchies
Diagram 05String Values
Diagram 06Source Cube
Diagram 07Calculated Cube
Diagram 08Form Layouts
Diagram 09Form Rowsets and Parameters
Diagram 10Member Relations
Diagram 11Member Permissions
Diagram 12Form Permissions

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.

Roles

RoleDescription
planning_app_administratorsThe role includes permissions for planning application administrators.
planning_app_analystsThe role includes permissions for planning application analysts.
planning_app_developersThe role includes permissions for planning application developers.
planning_app_usersThe role includes permissions for planning application users.

planning_app_administrators

The role includes permissions for planning application administrators.

Assign this role to users who manage business user permissions.

This role members have permissions to execute permission management forms and procedures and do not have permissions to execute business reports.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

planning_app_analysts

The role includes permissions for planning application analysts.

Assign this role to users who configure dimensions, members, reports, and other business-related objects.

This role members have full control over any business-related objects and do not have permissions to manage user permissions and change the application configuration.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

planning_app_developers

The role includes permissions for planning application developers.

Assign this role to users who configure or develop the application.

This role members have full read and write permissions on any object in the dbo25 and xls25 schemas.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

planning_app_users

The role includes permissions for planning application users.

Assign this role to business users who work with configured forms only.

This role members may have additional restrictions configured at the application level.

Do not assign this role to users that are members of the roles listed above.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
dbo25The schema contains data tables and technical views and procedures of the planning application.
xls25The schema contains end-user views and stored procedures used in the Excel planning application.

dbo25

The schema contains data tables and technical views and procedures of the planning application.

Only business analysts and developers have select permissions on schema objects.

xls25

The schema contains end-user views and stored procedures used in the Excel planning application.

Business users have access to a limited set of schema objects.

See default permissions in the dbo25.xl_actions_set_role_permissions procedure.

Contents

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.