¶ 1. Abstract
The manual describes the structure and operation of a database in T6 Planning, a planning system used to store and analyze data related to different applications. The database contains dimension tables (such as Account, Time, Entity, Scenario) and fact tables to store actual application-related data.
¶ 2. Application Structure
A T6 Planning database can contain multiple applications numerically indexed with an increment of 1. Each application has its own "Fact" tables and various metadata tables describing the "Fact" table data.
By default, each new application created within T6 Planning will always have four basic tables:
d_account_app#d_time_app#d_scenario_app#d_entity_app#
For each new dimension created within the application (i.e., custom dimensions), the system will create a new table with the nomenclature d_<dimension identifier>_app#. The numeric symbol(#) indicates the location of the application number. As you can see below, each application has its own dimension tables and Fact tables, each indicated by the application number.
¶ 2.1 Table Structure
The tables below are necessary to populate during the installation and deployment of T6 Planning.
| TABLE NAME | DESCRIPTION |
|---|---|
d_account_app## |
Details of members and structure of the Account dimension. |
d_entity_app## |
Details of members and structure of the Entity dimension. |
d_scenario_app## |
Details of members and structure of the Scenario dimension. |
d_time_app## |
Details of members and structure of the Time dimension. |
d_dimension_id_app## |
Details of any custom dimensions added to the database. |
f_app## |
Fact table for all real data related to the application. |
f_app#_audit |
Stores the audit trail (e.g., history) of changes in the fact. |
f_app#_detail |
Stores Cell Details and Value Details, which can be included in application form cells. |
f_app#_detail_audit |
Stores the change history made in the Details and Value Details. |
f_app#_scenario |
Stores the types of scenarios (indicates whether it is Planned, Closed, or Actual). |
f_app#_scenario_audit |
Stores the change history made in the corresponding table (f_app#_scenario). |
¶ 2.2 Dimension Table Structure
In addition to loading this information via ETL, dimension tables can be filled directly or through an Excel spreadsheet, using the T6 Planning interface.
All dimensions except the Time dimension have a functionality that makes them multi-hierarchy dimensions. In these cases, some fields will be added to the table and will be named with the suffix "_h#", where the numeric symbol (#) represents the hierarchy code.
¶ 2.2.1 d_account_app##
| FIELD NAME | FIELD PROPERTIES | FIELD DESCRIPTION | **MULTI-HIERARCHY * ** | SHOW DATA |
|---|---|---|---|---|
sk_account (automatically generated) |
PK, int, not null | Primary key of dimension members. | X | --- |
sk_parent (Optional) |
FK, int, null | Primary key for parent member. | Yes | --- |
flgBelongs (Mandatory) |
bit, null | Specifies the hierarchy to which the member belongs. | Yes | 0 or 1 |
Account (Mandatory) |
Varchar(100), null | Member Name. | X | Cost of goods sold |
AccountType (Mandatory) |
Varchar(500), null | Account balance classification. Impacts on aggregation across time. | X | Flow options, Revenue, Asset, Liability, Expense, Driver |
DataType (Mandatory) |
Varchar(500), null | Member data format. Changes the way data is presented. | X | Currency options, Non-currency, Percent, Date, List, Text |
AggregationType (Mandatory) |
Varchar(500), null | Specifies that the member should aggregate above in the hierarchy. | Yes | Options +, - |
Description [description] (Optional) |
Varchar(max), null | Detailed description of the member. | X | Cost of goods sold |
aggregation_signal (Optional) |
numeric, null | Specifies how the member signal should aggregate above in the hierarchy. | Yes | -1 or 1 |
aggregationFunction [Aggregation Function] |
Varchar(500), null | Classifies the account aggregation function. Possible values depending on the account type. | X | |
dividendAccount [Dividend Account] |
Int, null | Which account will be the dividend in the case of weighted average aggregation function. | X | |
divisorAccount [Divisor Account] |
Int, null | Which account will be the divisor in the case of weighted average aggregation function. | X | |
Level (Optional) |
int, null | Specifies the member level. | Yes | 1, 2, 3... |
order_members (Optional) |
int, null | Specifies the order of presentation of members. | Yes | |
linkedList (Optional) |
int, null | Specifies a linked list of values for this member. | X | --- |
| Other attributes with the same name ending with "_h#" | Same as the original | Attributes replicated to allow hierarchy leveling when it is a multi-hierarchical dimension. | Yes | --- |
| Other attribute with the same name ending with "_l#" | Same as the original | Attributes replicated to allow hierarchy leveling. | Yes (has "_l#" when there are other levels) | --- |
| Other Attributes (Optional) | Various types, null | User-created custom attributes. | X | Other attributes ending with "_l#", Same as the original, Other attributes (optional), Various types, null |
¶ 2.2.2 d_entity_app##
| FIELD NAME | FIELD PROPERTIES | FIELD DESCRIPTION | SHOW DATA |
|---|---|---|---|
sk_entity (automatically generated) |
PK, int, not null | Primary key of dimension members. | --- |
sk_parent (Optional) |
FK, int, null | Primary key for parent member. | --- |
flgBelongs (Mandatory) |
bit, null | Specifies the hierarchy to which the member belongs. | 0 or 1 |
Entity (Mandatory) |
Varchar(100), null | Member Name. | Department 101 |
Level (Optional) |
int, null | Specifies the member level. | 1, 2, 3... |
| Other attributes with the same name ending with "_h#" | Same as the original | Attributes replicated to allow hierarchy leveling when it is a multi-hierarchical dimension. | --- |
order_members (Optional) |
int, null | Specifies the order of presentation of members. | |
| Other attribute with the same name ending with "_l#" | Same as the original | Attributes replicated to allow hierarchy leveling. | --- |
| Other Attributes (Optional) | Various types, null | User-created custom attributes. | --- |
¶ 2.2.3 d_scenario_app##
| FIELD NAME | FIELD PROPERTIES | FIELD DESCRIPTION | SHOW DATA |
|---|---|---|---|
sk_scenario (automatically generated) |
PK, int, not null | Primary key of dimension members. | --- |
sk_parent (Optional) |
FK, int, null | Primary key for parent member. | --- |
scenario (Mandatory) |
Varchar(100), null | Member Name. | Actual, Plan, Forecast |
ScenarioType (Mandatory) |
Varchar(500), null | --- |
Options Actual, Planned |
flgBelongs (Mandatory) |
bit, null | Specifies the hierarchy to which the member belongs. | 0 or 1 |
level (Optional) |
int, null | Specifies the member level. | 1, 2, 3... |
date_start |
datetime, null | Scenario start date. | '2012-01-01 00:00:00.000' |
date_start_dateValue |
numeric, null | Scenario start date in numeric format. | 15340.00000000000000 |
date_end |
datetime, null | Scenario end date in numeric format. | '2013-12-31 00:00:00.000' |
date_end_dateValue |
numeric, null | Scenario end date in numeric format. | 16070.00000000000000 |
| Other attributes with the same name ending with "_h#" | Same as the original | Attributes replicated to allow hierarchy leveling when it is a multi-hierarchical dimension. | --- |
¶ 2.2.4 d_time_app## *
| FIELD NAME | FIELD PROPERTIES | FIELD DESCRIPTION | SHOW DATA |
|---|---|---|---|
sk_time (automatically generated) |
PK, int, not null | Primary key of the time dimension database. | --- |
time_ID (automatically generated) |
Varchar(100), null | Primary key of the database for the parent member. | --- |
id_time_0 (automatically generated) |
bigint, null | Year of the previous period. | 2009 |
prev_id_time_0 (automatically generated) |
bigint, null | Year of the previous period. | 2009 |
time_1 (automatically generated) |
bigint, null | Month of the current period. | January, February... |
id_time_1 (automatically generated) |
bigint, null | Year and Month of the current period. | 200901 |
prev_id_time_1 (automatically generated) |
bigint, null | Year and Month of the previous period. | 200812 |
monthName (automatically generated) |
Varchar(100), not null | Month of the current period. | January, February… |
shortMonthName (automatically generated) |
Varchar(100), not null | Month name in abbreviated form. | Jan, Feb, Mar… |
date (automatically generated) |
Datetime, not null | End date and time. | 1/31/2008 12:00:00 PM |
** *This table is never filled using ETL. To fill it, use the time dimension wizard within the T6 Planning administration menu.**
¶ 2.2.5 d_<dimension_name>_app##
Used to store dimensions customized by the client (products, clients, etc).
| FIELD NAME | FIELD PROPERTIES | FIELD DESCRIPTION | SHOW DATA |
|---|---|---|---|
sk_dimension_name (automatically generated) |
PK, int, not null | Primary key of dimension members. | --- |
sk_parent (Optional) |
FK, int, null | Primary key for parent member. | --- |
dimension_name (Mandatory) |
Varchar(100), null | Name of the Member. | Product A, USD, Employee 123 |
| Other Attributes (Optional) | Varchar(500), null | User-created custom attributes. | --- |
flgBelongs (Mandatory) |
bit, null | Specifies the hierarchy to which the member belongs. | 1, 2, 3... |
level (Optional) |
int, null | Specifies the member level. | 1, 2, 3 ... |
order_members (Optional) |
int, null | Specifies the order of presentation of members. | --- |
| Other attributes with the same name ending with "_l#" | Equal to the original | Attributes replicated to allow hierarchy leveling. | --- |
| Other attributes with the same name ending with "_h#" | Same as the original | Attributes replicated to allow hierarchy leveling when it is a multi-hierarchical dimension. | --- |
| Other Attributes (Optional) | Several types, null. | User-created custom attributes. | --- |
¶ 2.3 Fact Tables Structure (f_app##)
Data loaded through ETL (such as actual values) is loaded into this table.
Include the columns:
- valueText: nvarchar(max) null, stores the value of accounts whose type is Text.
- hashData: varchar(max) null, stores a unique identifier that allows identifying values loaded via Fact Data Load.
| FIELD NAME | FIELD PROPERTIES | FIELD DESCRIPTION | SAMPLE DATA |
|---|---|---|---|
| sk_conta (Required) | PK,FK, int, not null | Primary database key of Account dimension. | --- |
| sk_tempo (Required) | PK,FK, int, not null | Primary database key of Entity dimension. | --- |
| sk_cenario (Required) | PK,FK, int, not null | Primary database key of Scenario dimension. | --- |
| sk_tempo (Required) | PK,FK, int, not null | Primary database key of Time dimension. | --- |
| sk_dimension_name (Required if custom dimensions exist) | PK,FK, int, not null | Primary database key of another dimension. The primary key for each additional dimension must be included in the Fact values definition. | --- |
| value (Required) | Numeric (28,14), null | Data value. | --- |
| cod_user (system-generated) | Varchar(20), null | User ID of the user running the last field update. For loads, use the fixed 'ETL'. | --- |
| dat_update (system-generated) | datetime, null | Date of the last field update. | --- |
| type_update (system-generated) | char(1), not null | Code that identifies whether the data was updated via ETL or other options generated by the system. | --- |
¶ 3. Extracting Data via SQL
In some situations, it is necessary to access application data tables directly through SQL queries. When doing so, we face the challenge of unbalanced parent-child relationships. This difficulty arises because we need the total numbers to obtain the value of the selected member, which can be a leaf or a parent at any level.
The simplest and most generic way to achieve this is by using one of the existing views created by T6 Planning. For example, for the Entity dimension of an application, we have the view "vw_entity_app1_children". The SQL command defining this view is as follows:
CREATE VIEW vw_entity_app1_children AS
WITH parent_child(sk_parent,sk_child) AS (
SELECT sk_parent,sk_entity
FROM d_entity_app1
WHERE sk_parent IS NOT NULL
UNION ALL
SELECT P.sk_parent,C.sk_entity
FROM d_entity_app1 AS C INNER JOIN
parent_child AS P ON C.sk_parent = P.sk_child)
SELECT * FROM parent_child
GO
This new visualization will bring all children of all members of the dimension so that we can connect this visualization with the Fact using the sk_child column (performing an inner join of the sk_child column with the sk_entity column of the Fact table). To filter a specific entity, we have to use a WHERE clause on the sk_parent column, thus bringing all data below the selected entity, including data members. Here is an example:
SELECT * FROM f_app1 f INNER JOIN
vw_entidade_app1_tree e ON (f.sk_entidade = e.sk_child)
WHERE e.sk_parent = <entity PK to be filtered>
¶ 4. Integration Parameter
In T6 Planning from version 10 onwards, it is possible to enable a parameter called Integration in each application. This parameter generates certain database views to facilitate data retrieval, specifically for BI tools to extract data more efficiently.
To use this parameter and enable the generation of these views, it is necessary to create a new Property for each dimension you want to use for data extraction by BI tools external to T6 Planning.
To do this, in the T6 Planning menu, go to Explorer > Navigator.
On the Explorer screen:
- The features of the HOME tab will be displayed automatically.
- On the left side of the screen, you will find the system folder listing.
- Select the DIMENSIONS folder.
- In the list grid of dimensions, find the dimension of the desired application account. Click on it to select.
Then, in the options bar, click on Edit.
Next, on the Edit Dimension screen, in the bottom right corner, click the add button.
The Dimension Attribute window will appear. In the Attribute Name field, enter "BI," in the Type field, enter "Selection." In value, enter the values YES and NO, and click OK.
Back in the T6 Planning menu, go to Modeling > Designer.
In the Designer screen, in the Tools menu, click on Show Properties.
Then, select some members and mark the BI property with the value YES. It is important to note that at least one YES account must exist, or the process below will fail.
Once done, save and publish in the model.
¶ 4.1 Enabling the Integration Parameter
In the T6 Planning menu, go to Settings > Parameters.
On the Application Parameters screen, choose the desired application and change the Integration value from NO to YES, then save and publish in the model.
Next, a new option will be displayed that allows you to disable integration; leave it unchecked and click Publish.
This way, the database will present 3 new ways of viewing data:
- vw_f_appX: We will have the correct value, but the sign may still be incorrect, as this view does not consider the level as mentioned above, so ignore this table.
- vw_f_appX_bi: For each account marked with the BI property as Yes, we will have an extra column in this table. In other words, accounts become columns with the correct value, aggregation, and sign. This would be one of the best options for data visualization.
- vw_f_appX_account: This viewing mode has data for all accounts in rows, regardless of whether the BI attribute is marked as Yes or No. These accounts will have the aggregated value and correct sign.
This would be the best data visualization as performance is good, and there are no restrictions on the number of columns.
