Planning Application Tables

Table	Description
dbo25.axis_types	This system table contains axis types.
dbo25.calc_types	This system table contains calculation types.
dbo25.companies	This user table contains companies.
dbo25.currency_rates	This user table contains currency rates.
dbo25.dimension_calc_types	This system table contains available dimension calculation types.
dbo25.dimension_properties	This user table contains company-related dimension properties.
dbo25.dimensions	This system table contains dimensions.
dbo25.factors	This application table contains calculation factors for child-parent relations.
dbo25.facts	This application table contains source cube number values.
dbo25.form_dimensions	This user table contains form dimension properties.
dbo25.form_permissions	This user table contains form select permissions by users.
dbo25.form_rows	This user table contains rows of the custom form rowsets.
dbo25.form_subtotals	This user table contains form subtotal settings.
dbo25.formats	This user table contains company-related form formats.
dbo25.forms	This user table contains a list of forms.
dbo25.hierarchies	This application table contains child-parent relations.
dbo25.member_permissions	This application table contains member permissions by users.
dbo25.member_relations	This application table contains select and update permissions for cross-dimension members.
dbo25.members	This user table contains dimension members.
dbo25.name_formats	This system table contains variants of displaying members.
dbo25.parents	This application table contains source factors for child-parent relations.
dbo25.strings	This application table contains source string values.
dbo25.tax_rates	This user table contains tax rates.
dbo25.translated_tables	This system table contains tables that support the translation of elements.
dbo25.translations	This user table contains company-related translations.
dbo25.units	This user table contains units.
dbo25.users	This user table contains application users.

dbo25.axis_types

This system table contains axis types.

Trigger	Type	Comment
tr_axis_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_axis_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	tinyint		The application uses id values directly. Do not change the id values.
	code	nvarchar(50)		You may change the code. However, keep the axis meaning.
	name	nvarchar(100)		You may change the name. However, keep the axis meaning.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	page	Page
2	rows	Rows
3	columns	Columns

Do not add or delete rows. Do not change the id values.

dbo25.calc_types

This system table contains calculation types.

Trigger	Type	Comment
tr_calc_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_calc_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	tinyint		The application uses id values directly. Do not change the id values.
	code	nvarchar(50)		You may change the code. However, keep the calculation type meaning.
	name	nvarchar(100)		You may change the name. However, keep the calculation type meaning.
	sort_order	tinyint	✓	You may change the sort order to select rows in the required order in Excel.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name	sort_order
0	none	None	0
1	total	Total	1
2	average	Average	2
3	open	Opening Balance	3
4	close	Closing Balance	4
5	ytd	YTD	5
6	same_period	Same Period	6
7	excel	Excel Formula	7
8	string	String	8
9	rowset	Rowset	9
10	rate	Rate	10
11	revaluation	Revaluation	11
12	difference	Difference	12
13	percent	Percent	13

Do not add or delete rows. Do not change the id values.

dbo25.companies

This user table contains companies.

Trigger	Type	Comment
tr_companies_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_companies_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	int		Identity
	code	nvarchar(50)		Company code
	name	nvarchar(100)		Company name
	default_language	varchar(10)	✓	The language of company elements like accounts, members, etc.
	sort_order	int	✓	Sort order
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application allows planning for multiple companies in the same database.

All user tables use direct or indirect company_id as a foreign key from this table.

Use xls25.usp_companies to check available companies and add new ones.

dbo25.currency_rates

This user table contains currency rates.

Trigger	Type	Comment
tr_currency_rates_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_currency_rates_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	base_currency_id	int		dbo25.units.id
✓	quote_currency_id	int		dbo25.units.id
✓	time_id	int		dbo25.members.id This is a time member.
✓	category_id	int		dbo25.members.id This is a category member.
	currency_rate	float		Currency rate
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application calculates dbo25.facts.value values using the dbo25.facts.source_value values and currency rates.

Use the xls25.usp_rates procedure to select and edit currency rates.

Use the xls25.xl_actions_update_facts procedure to recalculate facts after currency rate changes.

dbo25.dimension_calc_types

This system table contains available dimension calculation types.

Trigger	Type	Comment
tr_dimension_calc_types_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimension_calc_types_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	dimension_id	tinyint		dbo25.dimensions.id
✓	calc_type_id	tinyint		dbo25.calc_types.id
	is_active	bit		Set 1 to activate and 0 to deactivate the calculation type for the dimension.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Do not add or delete rows. The table contains all the available settings.

Change is_active to activate or deactivate calculation types.

dbo25.dimension_properties

This user table contains company-related dimension properties.

Trigger	Type	Comment
tr_dimension_properties_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimension_properties_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_dimension_properties_delete	DELETE	This trigger sets NULL in related columns and removes the element translations.

	Column	DataType	Null	Comment
✓	company_id	int		dbo25.companies.id
✓	id	tinyint		dbo25.dimensions.id
	code	nvarchar(50)		You may change the dimension code. However, do not change the meaning of the first four dimensions.
	name	nvarchar(50)		You may change the dimension name. However, do not change the meaning of the first four dimensions.
	parameter_name	nvarchar(50)		Use this field to set the name of the ribbon parameter name for the dimension members.
	sort_order	tinyint		You may change the sort order to select dimensions in the required order in Excel.
	join_order	tinyint		This field defines the JOIN order in the dynamic procedures. Do not change it.
	name_format_id	tinyint		dbo25.name_formats.id This field defines the name format used in the ribbon parameters and validation lists for dimension members. For example, you may select id, code, name, or their pairs.
	root_member_id	int	✓	dbo25.members.id Set this member to the root member of the dimension members.
	default_member_id	int	✓	dbo25.members.id Set this member to the member used as the default value in the cube. The default member must not contain children.
	is_protected	bit		Set 1 to activate checking SELECT AND UPDATE permissions for dimension members. Note that in this case, you have to set member permissions directly. Otherwise, users may not see new members.
	default_select_permission	bit		Set 0 to hide and 1 to show the cube values of the dimension members by default. Keep 1 at least for the Times and Categories dimensions.
	default_update_permission	bit		Set 0 to disable and 1 to enable updating cube values of the dimension members by default. Keep 1 at least for the Times and Categories dimensions.
	is_active	bit		Set 1 to activate or 0 to deactivate dimensions 5-7.
	external_id	int	✓	Use it with custom import-export procedures.
	external_code	nvarchar(50)	✓	Use it with custom import-export procedures.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This company contains company-related settings of the dimensions.

The application has seven built-in dimensions. Adding more dimensions requires application changes.

The first four dimensions (Accounts, Times, Categories, and Entities) have built-in business logic. Do not deactivate them and keep their meaning.

You may activate and deactivate dimensions 5-7, and change their meaning.

Use the xls25.usp_dimensions procedure to select and edit dimensions in Microsoft Excel.

dbo25.dimensions

This system table contains dimensions.

Trigger	Type	Comment
tr_dimensions_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimensions_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	tinyint		The application uses dimension id values directly. Do not change the id values.
	code	nvarchar(50)		You may change the dimension code. However, do not change the meaning of the first four dimensions.
	name	nvarchar(100)		You may change the dimension name. However, do not change the meaning of the first four dimensions.
	parameter_name	nvarchar(50)		Use this field to set the name of the ribbon parameter name for the dimension members.
	sort_order	tinyint		You may change the sort order to select dimensions in the required order in Excel.
	join_order	tinyint		This field defines the JOIN order in the dynamic procedures. Do not change it.
	name_format_id	tinyint		dbo25.name_formats.id This field defines the name format used in the ribbon parameters and validation lists for dimension members. For example, you may select id, code, name, or their pairs.
	is_protected	bit		Set 1 to activate checking SELECT AND UPDATE permissions for dimension members. Note that in this case, you have to set member permissions directly. Otherwise, users may not see new members.
	default_select_permission	bit		Set 0 to hide and 1 to show the cube values of the dimension members by default. Keep 1 at least for the Times and Categories dimensions.
	default_update_permission	bit		Set 0 to disable and 1 to enable updating cube values of the dimension members by default. Keep 1 at least for the Times and Categories dimensions.
	is_active	bit		Set 1 to activate or 0 to deactivate dimensions 5-7.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name	parameter_name	sort_order	join_order	name_format_id	is_protected	default_select_permission	default_update_permission	is_active
1	accounts	Accounts	Account	1	5	4	1	0	0	1
2	times	Times	Period	2	6	1	1	1	1	1
3	categories	Categories	Category	3	3	1	1	1	1	1
4	entities	Entities	Entity	4	4	1	1	0	0	1
5	dim5	Regions	Region	5	1	1	0	1	1	0
6	dim6	Products	Product	6	2	1	0	1	1	0
7	dim7	Sub_Accounts	Sub.Accounts	7	7	1	0	1	1	0

This table contains default settings of dimensions.
The company-related settings are stored in dbo25.dimension_properties.

The application has seven built-in dimensions. Adding more dimensions requires application changes.

The first four dimensions (Accounts, Times, Categories, and Entities) have built-in business logic. Do not deactivate them and keep their meaning.

dbo25.factors

This application table contains calculation factors for child-parent relations.

Trigger	Type	Comment
tr_factors_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_factors_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	member_id	int		dbo25.members.id
✓	parent_id	int		dbo25.members.id
✓	calc_type_id	tinyint		dbo25.calc_types.id
	factor	float		Factor to calculate the parent value
	is_calculated	int		1 - the value is calculated 0 - the value is loaded from the source
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application uses this table to calculate aggregated cube values.

Use the xls25.xl_actions_update_hierarchies procedure to update this table.
Do not change the table data manually.

Use the xls25.view_hierarchies view to select factors.

dbo25.facts

This application table contains source cube number values.

	Column	DataType	Null	Comment
✓	id	int		Identity
	id1	int		dbo25.members.id Accounts
	id2	int		dbo25.members.id Times
	id3	int		dbo25.members.id Categories
	id4	int		dbo25.members.id Entities
	id5	int		dbo25.members.id Dimension 5
	id6	int		dbo25.members.id Dimension 6
	id7	int		dbo25.members.id Dimension 7
	value	float	✓	This field contains calculated values that depend on unit factors and currency rates.
	source_value	float	✓	This field contains user input values as is.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Fields id1-id7 must have member id values. The application uses the dbo25.dimensions.default_member_id field value for inactive dimensions.

The application keeps the uniqueness of id1-id7 key values programmatically.

Use the dbo25.view_data view to select the table data.
Use the dbo25.view_facts view to select the calculated cube data.

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

dbo25.form_dimensions

This user table contains form dimension properties.

Trigger	Type	Comment
tr_form_dimensions_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_form_dimensions_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	form_id	int		dbo25.forms.id
✓	dimension_id	tinyint		dbo25.dimensions.id
	axis_type_id	tinyint		dbo25.axis_types.id
	parameter_index	tinyint	✓	Use this field to set the order of the parameter at the ribbon. The index must be unique within the form scope and have values between 1 and 7.
	header_order	tinyint	✓	Use this field to set the order of the dimension in the form rows and columns. The index must be unique within the form axis scope and have values between 1 and 3.
	root_member_id	int	✓	dbo25.members.id Set this member to replace the dbo25.dimensions.root_member_id value for the form. For example, if you create a form like "Sales", then you may set the root account member as "Sales". The application uses this member as a base for the following fields.
	parameter_start_level	tinyint	✓	Set this optional value to show child members of the root member in the ribbon parameters starting at the specified level.
	parameter_end_level	tinyint	✓	Set this optional value to show child members of the root member in the ribbon parameters ending at the specified level.
	form_start_level	tinyint	✓	Set this optional value to show child members of the root member in the form rows and columns starting at the specified level.
	form_end_level	tinyint	✓	Set this optional value to show child members of the root member in the form rows and columns ending at the specified level.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains dimension settings for forms executed with the xls25.usp_run_form and xls25.usp_run_offline_form procedures.

Use the xls25.usp_form_dimensions procedure to select and edit settings in Microsoft Excel.

dbo25.form_permissions

This user table contains form select permissions by users.

Trigger	Type	Comment
tr_form_permissions_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_form_permissions_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	form_id	int		dbo25.forms.id
✓	username	nvarchar(128)		The application uses the USER_NAME() function to get the username.
	select_permission	bit		Set 1 to allow the user to select the form.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application applies these form permissions for users of the planning_app_users role only.

Use the xls25.usp_form_permissions procedure to select and edit permissions in Microsoft Excel.

dbo25.form_rows

This user table contains rows of the custom form rowsets.

Trigger	Type	Comment
tr_form_rows_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_form_rows_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_form_rows_delete	DELETE	This trigger removes the element translations from dbo25.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	rowset_id	int		dbo25.members.id Rowset members must have the Rowset calculation type. The application shows only such members.
	sort_order	int		Set the sort order to select rows in the required order in Microsoft Excel.
	member_id	int	✓	dbo25.members.id You may omit the member to create a blank row.
	code	nvarchar(50)	✓	Set this value to replace the member code.
	name	nvarchar(400)	✓	Set this value to replace the member name.
	decimal_places	tinyint	✓	Set this value to replace the member decimal_places value.
	is_percent	bit	✓	Set this value to replace the member is_percent value.
	row_color	tinyint	✓	Set this value to replace the member row_color value.
	row_bold	tinyint	✓	Set this value to replace the member row_bold value.
	row_indent	tinyint	✓	Set this value to replace the member row_indent value.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

You may create custom form rowsets for any dimension.

Just add a new member to the dbo25.members table and specify the Rowset calculation type. Then specify form rows in this table.

You may specify a member and replace its values to change the member row look in Microsoft Excel.

The application selects the decimal_places, is_percent, row_color, row_bold, and row_indent fields as is.
So, you may customize the form look in Excel using the conditional formatting.

Use the xls25.usp_form_rows procedure to select and edit rows in Microsoft Excel.

dbo25.form_subtotals

This user table contains form subtotal settings.

	Column	DataType	Null	Comment
✓	id	int		Identity
	form_id	int		dbo25.forms.id
	dimension_id1	tinyint	✓	dbo25.dimensions.id
	member_id1	int	✓	dbo25.members.id
	dimension_id2	tinyint	✓	dbo25.dimensions.id
	member_id2	int	✓	dbo25.members.id
	dimension_id3	tinyint	✓	dbo25.dimensions.id
	member_id3	int	✓	dbo25.members.id
	show	bit		Set 1 to show and 0 to hide subtotals.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The forms may have up to three dimensions in rows.

The application shows subtotals for every dimension automatically.
Using this table, you may hide subtotals that you do not need.

For example, the form shows accounts ("Sales"), customers, and products. To disable all subtotals, specify the account, customer, and product dimensions in the columns.

To disable specific member subtotals, specify the dimension member.
You may create several rows to combine conditions.

Use the xls25.usp_form_subtotals procedure to select and edit subtotal settings in Microsoft Excel.

dbo25.formats

This user table contains company-related form formats.

Trigger	Type	Comment
tr_formats_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_formats_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	ID	int		Identity
	TABLE_SCHEMA	nvarchar(128)		Table schema
	TABLE_NAME	nvarchar(128)		Table name
	TABLE_EXCEL_FORMAT_XML	xml	✓	Excel table format in XML. The SaveToDB add-in packs and unpacks this format internally.
	company_id	int	✓	dbo25.companies.id
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The SaveToDB add-in uses xls25.usp_update_table_format to save formats.

dbo25.forms

This user table contains a list of forms.

Trigger	Type	Comment
tr_forms_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_forms_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_forms_delete	DELETE	This trigger removes the element translations from dbo25.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo25.companies.id
	code	nvarchar(50)		The code field values must not contain spaces.
	name	nvarchar(100)		Try to keep names short.
	sort_order	int	✓	Set the sort order to select forms in the required order in the ribbon Query List.
	is_active	bit		Set 0 to exclude the form the ribbon Query List.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

You may create and customize any number of editable forms with zero coding.

The SaveToDB add-in shows such forms in the ribbon Query List when you select the xls25.view_query_list_forms or xls25.view_query_list_offline_forms views in the Connection Wizard.

Analysts may add new forms anytime. Users should just update the ribbon Query List.

The application uses the xls25.usp_run_form and xls25.usp_run_offline_form procedures to run forms.

dbo25.hierarchies

This application table contains child-parent relations.

Trigger	Type	Comment
tr_hierarchies_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_hierarchies_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	member_id	int		dbo25.members.id
✓	parent_id	int		dbo25.members.id
	level	int		The field contains a number of levels between members.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application uses this table to select hierarchies and detect levels between related members.

Use the xls25.xl_actions_update_hierarchies procedure to update this table. Do not change the table data manually.

Use the xls25.view_hierarchies view to select the table data.

dbo25.member_permissions

This application table contains member permissions by users.

Trigger	Type	Comment
tr_member_permissions_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_permissions_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	member_id	int		dbo25.members.id
✓	username	nvarchar(128)		The application uses the USER_NAME() function to get the username.
	select_permission	bit		SELECT permission values: 1 - yes, 0 - no
	update_permission	bit		UPDATE permission values: 1 - yes, 0 - no
	is_inherited	bit		When you set the permission for the parent member, the application applies this permission to the children. This field contains 1 for such inherited permissions. You may set the direct permission for any child to replace the inherited permissions.
	permission	varchar(1)		This field contains user input permission values (R - read, W - Read/Write, D - Deny) and inherited member permissions (r - read, w - read/write, d - deny).
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application applies these member permissions for users of the planning_app_users role only.

The application updates this table programmatically. Do not edit it manually.

Use the xls25.usp_member_permissions procedure to select and edit permissions in Microsoft Excel.

dbo25.member_relations

This application table contains select and update permissions for cross-dimension members.

Trigger	Type	Comment
tr_member_relations_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_relations_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	member_id	int		dbo25.members.id
✓	partner_id	int		dbo25.members.id
	select_permission	bit	✓	SELECT permission values: 1 - yes, 0 - no
	update_permission	bit	✓	UPDATE permission values: 1 - yes, 0 - no
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table allows eliminating form rows for pairs of different dimension members.
For example, you may exclude certain accounts for certain entities.

Use the xls25.usp_member_relations procedure to edit relation permissions.

Also, this table allows disabling updating data for the desired pairs.
The most common case is closing the period for changes for the specified category.
To close periods, use the xls25.usp_closed_periods procedure.

dbo25.members

This user table contains dimension members.

Trigger	Type	Comment
tr_members_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_members_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_members_delete	DELETE	This trigger sets NULL in related columns and removes the element translations.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo25.companies.id
	dimension_id	tinyint		dbo25.dimensions.id
	sort_order	int	✓	Set the sort order to select members in the required order in Microsoft Excel.
	code	nvarchar(50)		The code values are the most important. The application uses codes in forms and import-export procedures to find member id values. Try to keep the codes constant. Do not use spaces in the codes.
	name	nvarchar(400)		You may change member names anytime.
	comment	nvarchar(1000)	✓	Use this field to add extended comments for business users.
	tax_rate_id	int	✓	dbo25.tax_rates.id This field is for informational purposes only. Use it to separate the same accounts with different tax rates.
	unit_id	int	✓	dbo25.units.id The application uses the dbo25.units.factor value to calculate the dbo25.facts.value value based on dbo25.facts.source_value.
	calc_type_id	tinyint		dbo25.calc_types.id The application uses this field value to calculate member aggregates.
	previous_period_id	int	✓	dbo25.members.id This field contains a member of the previous period for the Time dimension. For example, the February member must contain the id of the January member.
	same_period_id	int	✓	dbo25.members.id This field contains a member of the same period for the Time dimension. For example, the February member must contain the id of the February member of the previous year.
	decimal_places	tinyint	✓	Use this field to customize form look in Microsoft Excel using conditional formatting.
	is_percent	bit	✓	Use this field to customize form look in Microsoft Excel using conditional formatting.
	row_color	tinyint	✓	Use this field to customize form look in Microsoft Excel using conditional formatting.
	row_bold	tinyint	✓	Use this field to customize form look in Microsoft Excel using conditional formatting.
	row_indent	tinyint	✓	Use this field to customize form look in Microsoft Excel using conditional formatting.
	show_line	bit	✓	1 - show the row 0 - hide the row
	show_line_before	bit	✓	1 - add a row before 0 - do not add a row before
	show_line_after	bit	✓	1 - add a row after 0 - do not add a row after
	is_translatable	bit	✓	The element supports translation
	is_active	bit		Set 0 to hide the member.
	external_id	int	✓	Use this field with custom import-export procedures.
	external_code	nvarchar(50)	✓	Use this field with custom import-export procedures.
	excel_formula	nvarchar(400)	✓	You may specify an Excel formula to calculate member aggregates in Microsoft Excel. To use this feature, the form must select cells used as arguments in the correct order.
	cube_formula	nvarchar(max)	✓	This field is for informational purposes only. The application updates formulas automatically with the xls25.xl_actions_update_hierarchies procedure.
	is_calculated	tinyint	✓	1 - the member is calculated 0 - the member is not calculated
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use the xls25.usp_members procedure to select and edit dimension members in Microsoft Excel.

Do not use spaces in member codes.

dbo25.name_formats

This system table contains variants of displaying members.

Trigger	Type	Comment
tr_name_formats_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_name_formats_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	tinyint		The application uses id values directly. Do not change the id values.
	code	nvarchar(50)		You may change the code. However, keep the name format meaning.
	name	nvarchar(50)		You may change the name. However, keep the name format meaning.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
0	code	code
1	name	name
2	id	id
3	id_name	id - name
4	code_name	code - name

You may change the display format at any time.
Reload data and configuration in Microsoft Excel to reload ribbon parameters and validation lists with new values.

Do not add or delete rows. Do not change the id values.

dbo25.parents

This application table contains source factors for child-parent relations.

Trigger	Type	Comment
tr_parents_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_parents_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	member_id	int		dbo25.members.id
✓	parent_id	int		dbo25.members.id
	factor	float		The field contains source factors specified in the xls25.usp_members form.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table contains source input values.

The xls25.xl_actions_update_hierarchies procedure updates the dbo25.hierarchies table used in forms. Execute it after each change to update related factors and hierarchies.

Use the xls25.usp_members form to set member parents.
You may check child-parent pairs using the xls25.view_hierarchies view.

dbo25.strings

This application table contains source string values.

	Column	DataType	Null	Comment
✓	id	int		Identity
	id1	int		dbo25.members.id Accounts
	id2	int		dbo25.members.id Times
	id3	int		dbo25.members.id Categories
	id4	int		dbo25.members.id Entities
	id5	int		dbo25.members.id Dimension 5
	id6	int		dbo25.members.id Dimension 6
	id7	int		dbo25.members.id Dimension 7
	value	nvarchar(4000)	✓	This field contains user input string values as is.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

To allow input string values, set the account member calculation type as String.

The application does not "aggregate" string values for parents.
So, the table may contain source values for any level of the hierarchy, unlike the dbo25.facts table that contains values for children only.

Fields id1-id7 must have member id values. The application uses the dbo25.dimensions.default_member_id field value for inactive dimensions.

The application keeps the uniqueness of id1-id7 key values programmatically.

Use the dbo25.view_strings view to select the table data.

dbo25.tax_rates

This user table contains tax rates.

Trigger	Type	Comment
tr_tax_rates_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_tax_rates_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_tax_rates_delete	DELETE	This trigger removes the element translations from dbo25.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity Do not delete the default id 0.
	company_id	int		dbo25.companies.id
	code	nvarchar(50)		You may change codes.
	name	nvarchar(50)		You may change names.
	sort_order	tinyint	✓	Set the sort order to select members in the required order in Microsoft Excel.
	factor	float	✓	The field is reserved for future use.
	is_translatable	bit	✓	The element supports translation
	is_active	bit		Set 0 to hide the member.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

The application shows tax rates for informational purposes only and does not use the values for calculation.

Use separate accounts with appropriate parent factors to calculate taxes.

You may add the required tax rate members in this table directly.

dbo25.translated_tables

This system table contains tables that support the translation of elements.

Trigger	Type	Comment
tr_translated_tables_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translated_tables_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	tinyint		Id
	code	nvarchar(50)		Code
	name	nvarchar(100)		Name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Values:

id	code	name
1	members	members
2	companies	companies
3	units	units
4	tax_rates	tax_rates
5	dimensions	dimensions
6	dimension_parameters	dimension_parameters
7	forms	forms
8	form_rows	form_rows

Do not change this table.

dbo25.translations

This user table contains company-related translations.

Trigger	Type	Comment
tr_translations_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translations_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	company_id	int		dbo25.companies.id
✓	table_id	tinyint		dbo25.translated_tables.id
✓	member_id	int		Id of the translated element in its table
✓	language	varchar(10)		Language
	name	nvarchar(400)	✓	Translated element name
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls25.usp_translations to select and edit data.

dbo25.units

This user table contains units.

Trigger	Type	Comment
tr_units_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_units_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_units_delete	DELETE	This trigger removes the element translations from dbo25.translations.

	Column	DataType	Null	Comment
✓	id	int		Identity Do not delete the row with id 0 used as the system currency.
	company_id	int		dbo25.companies.id
	code	nvarchar(50)		Specify meaningful codes. For example, USD or ton. Do not use spaces in the codes.
	name	nvarchar(100)		Specify meaningful names. For example, USD or ton.
	sort_order	tinyint	✓	Set the sort order to select members in the required order in Microsoft Excel.
	factor	float		Specify the factor to calculate dbo25.facts.value based on dbo25.facts.source_value. So, the forms show source values while the cube contains both.
	is_currency	bit		Set 1 for currency units and 0 for others.
	is_functional_currency	bit		1 - use this item as the functional currency (must be a single item) 0 - all others
	is_translatable	bit	✓	The element supports translation
	is_active	bit		Set 0 to hide the member.
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

This table is an important part of the calculations.
Add and configure its members in the first steps.

The member with id 0 is the system currency used to calculate values of the dbo25.facts.value column.

You may change the system currency using the xls25.xl_actions_set_system_currency procedure (from the context menu in Excel).

Use the xls25.usp_units form to edit and customize units.

dbo25.users

This user table contains application users.

Trigger	Type	Comment
tr_users_insert	INSERT	This trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_users_update	UPDATE	This trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.

	Column	DataType	Null	Comment
✓	id	int		Identity
	company_id	int		dbo25.companies.id
	username	nvarchar(128)		A database name of the user acquired using the USER_NAME() function. The application uses this username to check user's permissions.
	name	nvarchar(100)		User name
	is_admin	bit		1 - the user has administrator privileges 0 - the user has no administrator privileges
	is_default	bit		1 - use this company by default when @company_id is NULL 0 - do not use this company by default
	is_active	bit		1 - the user can work with the company data 0 - the user cannot work with the company data
	created_by	nvarchar(128)	✓	Username of the user who created the record
	created_on	datetime	✓	The creation time
	modified_by	nvarchar(128)	✓	Username of the last user who modified the record
	modified_on	datetime	✓	The last modification time

Use xls25.usp_users to select and edit data.