Contents Diagrams Roles Schemas Tables Views Procedures Functions

Planning Application
for Microsoft Excel and SQL Server

Tables

TableDescription
dbo25.axis_typesThis system table contains axis types.
dbo25.calc_typesThis system table contains calculation types.
dbo25.currency_ratesThis user table contains currency rates.
dbo25.dimension_calc_typesThis system table contains available dimension calculation types.
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.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.unitsThis user table contains units.

dbo25.axis_types

This system table contains axis types.

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(50)You may change the name. However, keep the axis meaning.

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.

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(400)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.

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.currency_rates

This user table contains currency rates.

ColumnDataTypeNullComment
base_currency_idtinyintdbo25.units.id
quote_currency_idtinyintdbo25.units.id
time_idintdbo25.members.id

This is a time member.
category_idintdbo25.members.id

This is a category member.
currency_ratefloatCurrency rate

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.

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.

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

Change is_active to activate or deactivate calculation types.

dbo25.dimensions

This system table contains dimensions.

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(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 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.

Values:

idcodenameparameter_namesort_orderjoin_ordername_format_idroot_member_iddefault_member_idis_protecteddefault_select_permissiondefault_update_permissionis_activeexternal_idexternal_code
1accountsAccountsAccount15411001
2timesTimesPeriod26121111
3categoriesCategoriesCategory33131111
4entitiesEntitiesEntity44141001
5dim5RegionsRegion5115460110
6dim6ProductsProduct6216480110
7dim7SubaccountsSubaccount7717500110

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.factors

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

ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
calc_type_idtinyintdbo25.calc_types.id
factorfloatFactor to calculate the parent value

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.

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.

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.

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.

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.

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.

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.

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.

The forms may have up to thee 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.forms

This user table contains a list of forms.

ColumnDataTypeNullComment
idintIdentity
codenvarchar(50)The code field values must not contain spaces.
namenvarchar(255)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.

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.

ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
levelintThe field contains a number of levels between members.

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.

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).

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.

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

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.

ColumnDataTypeNullComment
idintIdentity
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_idtinyintdbo25.tax_rates.id

This field is for informational purposes only.

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

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.

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.

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.

ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
factorfloatThe field contains source factors specified in the xls25.usp_members form.

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.

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.

ColumnDataTypeNullComment
idtinyintDo not delete the default id 0.
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_activebitSet 0 to hide the member.

Values:

idcodenamesort_orderfactoris_active
0default11

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.units

This user table contains units.

ColumnDataTypeNullComment
idtinyintDo not delete the row with id 0 used as the system currency.
codenvarchar(50)Specify meaningful codes. For example, USD or ton. Do not use spaces in the codes.
namenvarchar(50)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_base_currencybitSet 1 for the base currency and 0 for the quote currencies.
For example, set 1 for EUR and 0 for USD in the EUR/USD pair.
is_activebitSet 0 to hide the member.

Values:

idcodenamesort_orderfactoris_currencyis_base_currencyis_active
0USDUSD11101
1EUREUR21101
11unitsUnits111001

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.

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.