What are the Reporting Database tables?

This article explains the commonly used reporting tables in the EPM Live Reporting Database, also referred to as the EPM Live Content Database.

Note: The Reporting Database tables are somewhat dependent on the site configuration. The List App Reporting Database tables as dynamic - specific to your site's configuration.  Any mapped List Apps in your site collection will have corresponding "LST" tables in the database. Additionally, there are some standard reporting tables that are present in most/all customer site collections.

LST Tables

For each mapped List App, two reporting tables are created.  One is a "refresh" table, which is the live table.  The other is a "Snapshot" table, which stores any saved snapshots for time-phased/trending reporting. For example, for the Issues List App, there are LSTIssues and LSTIssuesSnapshot. All fields in the List App would be mapped within that List App, so a report can be created to include data from any data in those fields for the List App.

  • Live Table: Any item added, edited, or deleted will be updated right away in the LST table.
  • Snapshot Table: This table is empty initially.  If a snapshot is taken (either manually or via a snapshot schedule), then a record for that snapshot is added to the table.  In order for a snapshot report to be created, the LST Snapshot table must be joined with the RPTPeriods table.  This would allow for the data to be displayed for each applicable period/timeframe.

Workspace Tables

If there are mapped lists from a workspace, the tables will show as the workspace name and the list name. There are two LST tables for each List App: the live table and the snapshot table.  Ex: Team Workspace_LSTDiscussion and Team Workspace_LSTDiscussionSnapshot.

EPG Tables

For the Portfolio Resource and Cost Planning tools, there are "EPG" tables. These tables include all data for the cost planning and resource planning tools.

Note: The data in these tables is NOT live.  When the Reporting Refresh runs, the EPG tables are populated/updated from the PortfolioEngine Database.  The Reporting Refresh is scheduled every 24 hours, and can also be run on-demand.  

  • EPG_RPT_ADMIN: This table includes the last refresh date & timestamp from the last time the Reporting Refresh.
  • EPG_RPT_Availability: This table includes the resource availability (their available work hours).
  • EPG_RPT_Calendar: This table includes all the calendar periods that are used for entering cost planner & resource planner data.
  • EPG_RPT_CapacityPlanner: This table includes the resource plans.
  • EPG_RPT_Cost: This table includes the cost plans.
  • EPG_RPT_CostDetail: This table includes the cost plans with additional details if the Details feature is in use.
  • EPG_RPT_List_CostCategories: This table includes the cost categories for cost planning.
  • EPG_RPT_List_CostTypes: This table includes the cost types for cost planning.
  • EPG_RPT_List_Departments: This table includes the resource departments for resource planning.
  • EPG_RPT_List_Roles: This table includes the resource roles for resource planning.
  • EPG_RPT_Projects: This table includes the list of projects and any other portfolio items from lists mapped to the PortfolioEngine Database.  
  • EPG_RPT_Resources: This table includes the list of resources in the Resource Pool.
  • EPG_RPT_TABLES: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.

RPT Tables

The majority of the RPT tables are used for back-end code purposes for the functionality and features of EPM Live.  The tables highlighted above are used more frequently for creating reports.

  • RPTColumn: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.  
  • RPTGROUPUSER: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTITEMGROUPS: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTList: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTListInfo: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTLog: This table is used for tracking the mappings of lists to the Reporting Database into a log.  You would not typically not use this table for creating reports.
  • RPTPeriods: This table is used for time-phased reporting.  If you are using snapshots, you would build a join between the snapshot table and periods table.
  • RPTReslink: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTSettings: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTTSData: This table includes the timesheet data.  
  • RPTWeb: This table includes all webs (sites/workspaces) in your site collection.
  • RPTWEBGROUPS: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.
  • RPTWork: This table includes each List App that has been specified as a "Work" List.

RPTTSData Table

The RPTTSData table includes the timesheet data from the EPM Live Database, also referred to as the Timesheet Database.

Note: The data in this tables is NOT live.  When the Reporting Refresh runs, the RPTTSData table is populated/updated from the EPM Live Database.  The Reporting Refresh is scheduled every 24 hours, and can also be run on-demand.  

RPTWork Table

The RPTWork table includes each List App that has been specified as a "Work" List.  To specify a List App as a work list, the following must be configured:

  1. The WorkEngine Work List Feature must be enabled (List Settings > General Settings).  
  2. In the reporting mapping, the list must be checked as a Resource List.  In order to be a Resource List, the following 5 fields are required to exist in the list: Title, AssignedTo, DueDate, Work, and StartDate.

To create a "work" report, you would use the RPTWork table, and then also join the LST Tables for any/all work lists to also include in the report.

SS Tables

The SS tables are for use by the Social Stream web part, meaning these tables serve as the data source for the Social Stream.  You would not typically use this table for creating reports.  

  • SS_Activities
  • SS_AssociatedThreads
  • SS_Logs
  • SS_Streams
  • SS_Stream_Threads
  • SS_StreamUsers
  • SS_Threads
  • SS_ThreadUsers
  • SS_Transactions

Other Tables & Views

  • ReportListIds: This table includes all the mapped lists' unique list IDs.  You would not typically use this table for creating reports.
  • Version: This table shows your current code version of EPM Live.
  • VWRPTLastProcessed: This view tracks details of the most recently run Reporting Refresh.  
  • VWRPTListSummary: This view includes all the mapped lists, with some additional flags for information.  You would not typically  use this table for creating reports.
  • VWRPTLogSummary: This view includes data from the RPTLog table. You would not typically use this table for creating reports.
  • VWRPTMaxErrorLevel: This table is used for back-end code purposes.  You would not typically not use this table for creating reports.

Comments

0 comments

Please sign in to leave a comment.