Planning Application Tables

Planning Application Tables

TableDescription
dbo25.axis_typesThis system table contains axis types.
dbo25.calc_typesThis system table contains calculation types.
dbo25.companiesThis user table contains companies.
dbo25.currency_ratesThis user table contains currency rates.
dbo25.dimension_calc_typesThis system table contains available dimension calculation types.
dbo25.dimension_propertiesThis user table contains company-related dimension properties.
dbo25.dimensionsThis system table contains dimensions.
dbo25.factorsThis application table contains calculation factors for child-parent relations.
dbo25.factsThis application table contains source cube number values.
dbo25.form_dimensionsThis user table contains form dimension properties.
dbo25.form_permissionsThis user table contains form select permissions by users.
dbo25.form_rowsThis user table contains rows of the custom form rowsets.
dbo25.form_subtotalsThis user table contains form subtotal settings.
dbo25.formatsThis user table contains company-related form formats.
dbo25.formsThis user table contains a list of forms.
dbo25.hierarchiesThis application table contains child-parent relations.
dbo25.member_permissionsThis application table contains member permissions by users.
dbo25.member_relationsThis application table contains select and update permissions for cross-dimension members.
dbo25.membersThis user table contains dimension members.
dbo25.name_formatsThis system table contains variants of displaying members.
dbo25.parentsThis application table contains source factors for child-parent relations.
dbo25.stringsThis application table contains source string values.
dbo25.tax_ratesThis user table contains tax rates.
dbo25.translated_tablesThis system table contains tables that support the translation of elements.
dbo25.translationsThis user table contains company-related translations.
dbo25.unitsThis user table contains units.
dbo25.usersThis user table contains application users.

dbo25.axis_types

This system table contains axis types.

TriggerTypeComment
tr_axis_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_axis_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idtinyintThe application uses id values directly. Do not change the id values.
codenvarchar(50)You may change the code. However, keep the axis meaning.
namenvarchar(100)You may change the name. However, keep the axis meaning.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1pagePage
2rowsRows
3columnsColumns

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

dbo25.calc_types

This system table contains calculation types.

TriggerTypeComment
tr_calc_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_calc_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idtinyintThe application uses id values directly. Do not change the id values.
codenvarchar(50)You may change the code. However, keep the calculation type meaning.
namenvarchar(100)You may change the name. However, keep the calculation type meaning.
sort_ordertinyintYou may change the sort order to select rows in the required order in Excel.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodenamesort_order
0noneNone0
1totalTotal1
2averageAverage2
3openOpening Balance3
4closeClosing Balance4
5ytdYTD5
6same_periodSame Period6
7excelExcel Formula7
8stringString8
9rowsetRowset9
10rateRate10
11revaluationRevaluation11
12differenceDifference12
13percentPercent13

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

dbo25.companies

This user table contains companies.

TriggerTypeComment
tr_companies_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_companies_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idintIdentity
codenvarchar(50)Company code
namenvarchar(100)Company name
default_languagechar(2)The language of company elements like accounts, members, etc.
sort_orderintSort order
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_currency_rates_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_currency_rates_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
base_currency_idintdbo25.units.id
quote_currency_idintdbo25.units.id
time_idintdbo25.members.id

This is a time member.
category_idintdbo25.members.id

This is a category member.
currency_ratefloatCurrency rate
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_dimension_calc_types_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimension_calc_types_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
dimension_idtinyintdbo25.dimensions.id
calc_type_idtinyintdbo25.calc_types.id
is_activebitSet 1 to activate and 0 to deactivate the calculation type for the dimension.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_dimension_properties_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimension_properties_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_dimension_properties_deleteDELETEThis trigger sets NULL in related columns and removes the element translations.
ColumnDataTypeNullComment
company_idintdbo25.companies.id
idtinyintdbo25.dimensions.id
codenvarchar(50)You may change the dimension code. However, do not change the meaning of the first four dimensions.
namenvarchar(50)You may change the dimension name. However, do not change the meaning of the first four dimensions.
parameter_namenvarchar(50)Use this field to set the name of the ribbon parameter name for the dimension members.
sort_ordertinyintYou may change the sort order to select dimensions in the required order in Excel.
join_ordertinyintThis field defines the JOIN order in the dynamic procedures. Do not change it.
name_format_idtinyintdbo25.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_idintdbo25.members.id

Set this member to the root member of the dimension members.
default_member_idintdbo25.members.id

Set this member to the member used as the default value in the cube.
The default member must not contain children.
is_protectedbitSet 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_permissionbitSet 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_permissionbitSet 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_activebitSet 1 to activate or 0 to deactivate dimensions 5-7.
external_idintUse it with custom import-export procedures.
external_codenvarchar(50)Use it with custom import-export procedures.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_dimensions_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_dimensions_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idtinyintThe application uses dimension id values directly. Do not change the id values.
codenvarchar(50)You may change the dimension code. However, do not change the meaning of the first four dimensions.
namenvarchar(100)You may change the dimension name. However, do not change the meaning of the first four dimensions.
parameter_namenvarchar(50)Use this field to set the name of the ribbon parameter name for the dimension members.
sort_ordertinyintYou may change the sort order to select dimensions in the required order in Excel.
join_ordertinyintThis field defines the JOIN order in the dynamic procedures. Do not change it.
name_format_idtinyintdbo25.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_protectedbitSet 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_permissionbitSet 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_permissionbitSet 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_activebitSet 1 to activate or 0 to deactivate dimensions 5-7.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodenameparameter_namesort_orderjoin_ordername_format_idis_protecteddefault_select_permissiondefault_update_permissionis_active
1accountsAccountsAccount1541001
2timesTimesPeriod2611111
3categoriesCategoriesCategory3311111
4entitiesEntitiesEntity4411001
5dim5RegionsRegion5110110
6dim6ProductsProduct6210110
7dim7Sub_AccountsSub.Accounts7710110

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.

TriggerTypeComment
tr_factors_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_factors_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
calc_type_idtinyintdbo25.calc_types.id
factorfloatFactor to calculate the parent value
is_calculatedint1 - the value is calculated
0 - the value is loaded from the source
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

ColumnDataTypeNullComment
idintIdentity
id1intdbo25.members.id

Accounts
id2intdbo25.members.id

Times
id3intdbo25.members.id

Categories
id4intdbo25.members.id

Entities
id5intdbo25.members.id

Dimension 5
id6intdbo25.members.id

Dimension 6
id7intdbo25.members.id

Dimension 7
valuefloatThis field contains calculated values that depend on unit factors and currency rates.
source_valuefloatThis field contains user input values as is.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_form_dimensions_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_form_dimensions_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
form_idintdbo25.forms.id
dimension_idtinyintdbo25.dimensions.id
axis_type_idtinyintdbo25.axis_types.id
parameter_indextinyintUse 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_ordertinyintUse 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_idintdbo25.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_leveltinyintSet this optional value to show child members of the root member in the ribbon parameters starting at the specified level.
parameter_end_leveltinyintSet this optional value to show child members of the root member in the ribbon parameters ending at the specified level.
form_start_leveltinyintSet this optional value to show child members of the root member in the form rows and columns starting at the specified level.
form_end_leveltinyintSet this optional value to show child members of the root member in the form rows and columns ending at the specified level.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_form_permissions_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_form_permissions_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
form_idintdbo25.forms.id
usernamenvarchar(128)The application uses the USER_NAME() function to get the username.
select_permissionbitSet 1 to allow the user to select the form.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_form_rows_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_form_rows_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_form_rows_deleteDELETEThis trigger removes the element translations from dbo25.translations.
ColumnDataTypeNullComment
idintIdentity
rowset_idintdbo25.members.id

Rowset members must have the Rowset calculation type.
The application shows only such members.
sort_orderintSet the sort order to select rows in the required order in Microsoft Excel.
member_idintdbo25.members.id

You may omit the member to create a blank row.
codenvarchar(50)Set this value to replace the member code.
namenvarchar(400)Set this value to replace the member name.
decimal_placestinyintSet this value to replace the member decimal_places value.
is_percentbitSet this value to replace the member is_percent value.
row_colortinyintSet this value to replace the member row_color value.
row_boldtinyintSet this value to replace the member row_bold value.
row_indenttinyintSet this value to replace the member row_indent value.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

ColumnDataTypeNullComment
idintIdentity
form_idintdbo25.forms.id
dimension_id1tinyintdbo25.dimensions.id
member_id1intdbo25.members.id
dimension_id2tinyintdbo25.dimensions.id
member_id2intdbo25.members.id
dimension_id3tinyintdbo25.dimensions.id
member_id3intdbo25.members.id
showbitSet 1 to show and 0 to hide subtotals.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_formats_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_formats_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Table schema
TABLE_NAMEnvarchar(128)Table name
TABLE_EXCEL_FORMAT_XMLxmlExcel table format in XML.

The SaveToDB add-in packs and unpacks this format internally.
company_idintdbo25.companies.id
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_forms_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_forms_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_forms_deleteDELETEThis trigger removes the element translations from dbo25.translations.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo25.companies.id
codenvarchar(50)The code field values must not contain spaces.
namenvarchar(100)Try to keep names short.
sort_orderintSet the sort order to select forms in the required order in the ribbon Query List.
is_activebitSet 0 to exclude the form the ribbon Query List.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_hierarchies_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_hierarchies_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
levelintThe field contains a number of levels between members.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_member_permissions_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_permissions_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
member_idintdbo25.members.id
usernamenvarchar(128)The application uses the USER_NAME() function to get the username.
select_permissionbitSELECT permission values: 1 - yes, 0 - no
update_permissionbitUPDATE permission values: 1 - yes, 0 - no
is_inheritedbitWhen 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.
permissionvarchar(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_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_member_relations_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_member_relations_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
member_idintdbo25.members.id
partner_idintdbo25.members.id
select_permissionbitSELECT permission values: 1 - yes, 0 - no
update_permissionbitUPDATE permission values: 1 - yes, 0 - no
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_members_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_members_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_members_deleteDELETEThis trigger sets NULL in related columns and removes the element translations.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo25.companies.id
dimension_idtinyintdbo25.dimensions.id
sort_orderintSet the sort order to select members in the required order in Microsoft Excel.
codenvarchar(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.
namenvarchar(400)You may change member names anytime.
commentnvarchar(1000)Use this field to add extended comments for business users.
tax_rate_idintdbo25.tax_rates.id

This field is for informational purposes only.

Use it to separate the same accounts with different tax rates.
unit_idintdbo25.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_idtinyintdbo25.calc_types.id

The application uses this field value to calculate member aggregates.
previous_period_idintdbo25.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_idintdbo25.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_placestinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
is_percentbitUse this field to customize form look in Microsoft Excel using conditional formatting.
row_colortinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
row_boldtinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
row_indenttinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
show_linebit1 - show the row
0 - hide the row
show_line_beforebit1 - add a row before
0 - do not add a row before
show_line_afterbit1 - add a row after
0 - do not add a row after
is_translatablebitThe element supports translation
is_activebitSet 0 to hide the member.
external_idintUse this field with custom import-export procedures.
external_codenvarchar(50)Use this field with custom import-export procedures.
excel_formulanvarchar(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_formulanvarchar(max)This field is for informational purposes only.

The application updates formulas automatically with the xls25.xl_actions_update_hierarchies procedure.
is_calculatedtinyint1 - the member is calculated
0 - the member is not calculated
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_name_formats_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_name_formats_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idtinyintThe application uses id values directly. Do not change the id values.
codenvarchar(50)You may change the code. However, keep the name format meaning.
namenvarchar(50)You may change the name. However, keep the name format meaning.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
0codecode
1namename
2idid
3id_nameid - name
4code_namecode - 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.

TriggerTypeComment
tr_parents_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_parents_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
factorfloatThe field contains source factors specified in the xls25.usp_members form.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

ColumnDataTypeNullComment
idintIdentity
id1intdbo25.members.id

Accounts
id2intdbo25.members.id

Times
id3intdbo25.members.id

Categories
id4intdbo25.members.id

Entities
id5intdbo25.members.id

Dimension 5
id6intdbo25.members.id

Dimension 6
id7intdbo25.members.id

Dimension 7
valuenvarchar(4000)This field contains user input string values as is.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_tax_rates_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_tax_rates_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_tax_rates_deleteDELETEThis trigger removes the element translations from dbo25.translations.
ColumnDataTypeNullComment
idintIdentity
Do not delete the default id 0.
company_idintdbo25.companies.id
codenvarchar(50)You may change codes.
namenvarchar(50)You may change names.
sort_ordertinyintSet the sort order to select members in the required order in Microsoft Excel.
factorfloatThe field is reserved for future use.
is_translatablebitThe element supports translation
is_activebitSet 0 to hide the member.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_translated_tables_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translated_tables_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idtinyintId
codenvarchar(50)Code
namenvarchar(100)Name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Values:

idcodename
1membersmembers
2companiescompanies
3unitsunits
4tax_ratestax_rates
5dimensionsdimensions
6dimension_parametersdimension_parameters
7formsforms
8form_rowsform_rows

Do not change this table.

dbo25.translations

This user table contains company-related translations.

TriggerTypeComment
tr_translations_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_translations_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
company_idintdbo25.companies.id
table_idtinyintdbo25.translated_tables.id
member_idintId of the translated element in its table
languagechar(2)Language
namenvarchar(400)Translated element name
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls25.usp_translations to select and edit data.

dbo25.units

This user table contains units.

TriggerTypeComment
tr_units_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_units_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
tr_units_deleteDELETEThis trigger removes the element translations from dbo25.translations.
ColumnDataTypeNullComment
idintIdentity
Do not delete the row with id 0 used as the system currency.
company_idintdbo25.companies.id
codenvarchar(50)Specify meaningful codes. For example, USD or ton. Do not use spaces in the codes.
namenvarchar(100)Specify meaningful names. For example, USD or ton.
sort_ordertinyintSet the sort order to select members in the required order in Microsoft Excel.
factorfloatSpecify 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_currencybitSet 1 for currency units and 0 for others.
is_functional_currencybit1 - use this item as the functional currency (must be a single item)
0 - all others
is_translatablebitThe element supports translation
is_activebitSet 0 to hide the member.
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe 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.

TriggerTypeComment
tr_users_insertINSERTThis trigger updates created_by, created_on, modified_by, and modified_on after INSERT.
tr_users_updateUPDATEThis trigger updates created_by, created_on, modified_by, and modified_on after UPDATE and updates ids in related columns.
ColumnDataTypeNullComment
idintIdentity
company_idintdbo25.companies.id
usernamenvarchar(128)A database name of the user acquired using the USER_NAME() function.
The application uses this username to check user's permissions.
namenvarchar(100)User name
is_adminbit1 - the user has administrator privileges
0 - the user has no administrator privileges
is_defaultbit1 - use this company by default when @company_id is NULL
0 - do not use this company by default
is_activebit1 - the user can work with the company data
0 - the user cannot work with the company data
created_bynvarchar(128)Username of the user who created the record
created_ondatetimeThe creation time
modified_bynvarchar(128)Username of the last user who modified the record
modified_ondatetimeThe last modification time

Use xls25.usp_users to select and edit data.