Introduction
You can refer to your application tables through SQL Server Manage Studio to learn more about your application and apps. This article provides definitions for all the application tables used by Kepion.
Major tables
|
Prefix of the Table Name |
Description |
|---|---|
|
D_ |
[Dimension] – Each record represents a dimension member, including all its related attribute data. |
|
DO_ |
[Dimension Order] – Stores the member ordering of a dimension. |
|
F_ |
[Fact] – Store both numeric and text data, sliced by the dimensionality of a model. |
|
H_ |
[Hierarchy] – Stores the set of members that define a member list. Parent-child relationships can also be defined here. |
|
HO_ |
[Hierarchy Order] – Stores the member order for a member list. |
Staging tables
|
Prefix of the Table Name |
Description |
|---|---|
|
DS_ |
[Dimension Staging] – Used with staging SP to merge dimension data from the DS_ table into the D_ table. |
|
DSE_ |
[Dimension Staging Error] – Reporting any errors during a dimension merge from staging to main. |
|
Staging_F |
[Staging Fact] – An optional table that can be used for staging fact records. |
|
Staging_H |
[Staging Hierarchy] – An option table that can be used for staging member list records. |
Generated tables
|
Prefix of the Table Name |
Description |
|---|---|
| DefinedSet_ | [Defined Set] – Each record represents a dimension member used to define the defined set of type MEMBERS. |
|
HC_ |
[Hierarchy Calculation] – Each record represents a member formulas for a member. |
|
FH_ |
[Fact History] – Stores the historical changes to the associated F_ table from the writeback process. Can be used for audit trail to track data changes to a model. |
|
Map_ |
[Mapping] – Defines the data for a form drop-down that is of type dynamic-list. |
|
MF_ |
[Mapping Filter] – Used to define multi-filter selections for dashboard apps. |
|
NS_ |
[Nested Sets] – Holds hierarchy information for quick query searches on a parent-child hierachy. |
|
SysCache_V_ |
[System Cached View] – Table generated by Kepion to materialized complex views as records in a table. Used internally for improved performance. |
Metadata info tables
|
Table Name |
Description |
|---|---|
| AllModelDimensions | List of all model dimensions used by each model (including model dimensions from the linked models). |
| DashboardForms | List of dashboard forms by dashboard within the application. |
| DashboardPages | List of dashboard pages by dashboard within the application. |
| Dimensions | List of dimensions within the application. |
| FormAxis | List of the member lists/hierarchies used by a form on rows, columns, or filters. |
| Hierarchies | List of member lists/hierarchies within the application. |
| ModelDimensions | List of model dimensions by model within the application. |
| Models | List of models within the application. |
| Partitions | List of partitions within the application. |
Metadata definition tables
|
Table Name |
Description |
|---|---|
| ApplicationDefinedSets | Metadata table for defined sets. |
| ApplicationDrillthroughDefinitions | Metadata table for transactional drill-through definitions. |
| ApplicationMemberVariableMembers | Metadata table for application member variable values. |
| ApplicationMemberVariables | Metadata table for application member variable definitions. |
| ApplicationInfo | Metadata table for the application, including the database version number. |
| ApplicationVariables | Metadata table for application standard variable definitions and values. |
| Authorizations | Metadata table for apps that are configured through the Administrator module. |
| AuthorizationsNotificationTemplates | Metadata table for notification email templates for the application. |
| Dashboards | Metadata table for dashboards. |
| FileContainers | Metadata table stored image content in the application. |
| FileContents | Metadata table stored image content in the application. |
| Files | Metadata table stored image content in the application. |
| FileStores | Metadata table for file stores. |
| Forms | Metadata table for forms. |
| HistoricForms | Metadata table storing the historical record of form edits. |
| HistoricRules | Metadata table storing the historical record of rule edits. |
| Htmls | Metadata table for HTML definitions. |
| Rules | Metadata table for rules. |
| Scripts | Metadata table for deployment scripts. |
| MappingFilters | Metadata table for mapping filters. |
| Submissions | Metadata table for app instances. |
System tables
|
Table Name |
Description |
|---|---|
| AccountLogic_AccountType | Used in Chart of Account rollup logic. |
| AccountLogic_DebitCredit | Used in Chart of Account rollup logic. |
| AccountLogic_DebitCredit_Aggregation | Used in Chart of Account rollup logic. Determines the aggregation to use when aggregating from a child member to a parent member. |
| Activities | Writeback and rule activities are logged to this table. |
| ApplicationDeployment | Application deployment log. |
| ApplicationSettings | Internal use. |
| ppointedApprovers | Approvers for an App instance. |
| ApproverAssignments | Approver chain defined for an app workflow. |
| AuthorizationsDashboardRules | Rules associated to a dashboard app. |
| AuthorizationsDimensionPermission | Control write access to dimension member at the app level. |
| AuthorizationsFilterGroups | Global filter logic for apps. |
| AuthorizationsFilterGroupsToForms | Global filter logic for apps. |
| AuthorizationsFormRestrictions | Control access to forms within an app. |
| AuthorizationsForms | Forms associated to an app. |
| AuthorizationsPageRestrictions | Page permission settings for an app. |
| AuthorizationsRules | Rules associated to an app. |
| AuthorizationsUsers | Users associated to an app. |
| Constants | Internal use. |
| ContextOverrideFilter | Defines context override details. |
| ContextOverrideSettings | Defines context override settings, such as specifying the selected page for an app. |
| DataPermissions | Data level permissions. |
| DimensionDataPermissions | Dimension member permissions. |
| DummyAllMembers | Internal use. |
| ErrorCodes | Internal use. |
| ExtendedDataPermissions | Expanded data permissions. |
| ExtendedDimensionPermissions | Expanded dimension member permissions. |
| ExtendedModelAccesses | Expanded model access. |
| Filter_Modeler | Default form filter value defined in the Modeler. |
| Filter_Planning | User-saved form filters by app instance. |
| Filter_Planning_Empty | User-save form filters that are empty by app instance. |
| Formula_CoordinateDefinition | Internal use. |
| Formula_Expression | Internal use. |
| Formula_RHSTupleReference | Internal use. |
| Formula_UniqueName | Internal use. |
| Log | Application logs for performance analysis. |
| LogExtension | Details associated with the application logs. |
| LoggingDashboardPageAccess | Log of user access by dashboard page. |
| ModelAccesses | Security table defining model access by user. |
| PartitionTracker | Internal use. |
| ProcessDetails | pplication process log. |
| RuleComponentProgress | Internal use. |
| RuleProgress | Internal use. |
| Session | Internal use. |
| SubmissionSettings | Override settings for submissions, e.g. starting page. |
| SysCache_Rules | Internal use. |
| SystemCheck | Internal use. |
| UpgradeSettings | Internal use. |
| UserAffiliations | User to group/role mapping. |
| UserRestrictions | Stores the restrictions defined for a user. |
| Users |
List of all users within the application. |
System views
| View Name | Description |
|---|---|
| [pub].[Dimension_Permission_{DimensionName}] | List of users and their associated dimension permissions when dimension security is enabled |