Fact Type Table
This page describes the steps needed to use Generate to produce EDFacts Files for the Staff Fact Type.
Last updated
Was this helpful?
This page describes the steps needed to use Generate to produce EDFacts Files for the Staff Fact Type.
Last updated
Was this helpful?
Fact Type refers to a categorization or classification of data that is used to organize and manage information within a data warehouse or data management system. It essentially represents a specific type of factual data that is collected, stored, and processed within the system.
A Generate Fact Type is a specific type of Fact Type that is used within the Generate ETL (Extract, Transform, Load) Stored Procedures. These procedures are organized based on different types of factual data, each representing a distinct aspect or category of information.
Generate ETL Stored Procedures are organized by Fact Types.
Many EDFacts file specifications have shared timelines, reporting requirements, and/or a high degree of overlap in source system field mappings. When this happens, the data are organized into the same Fact Type to make data migration, testing, and file submission more efficient.
The Fact Type determines where the data is stored in the Generate Reporting Data Store (RDS). For example, the Directory data are stored in RDS.FactOrganizationCounts
. Generate has a series of tables used by the application where more information about Fact Types can be found. See the queries below.
The process of generating reports within our system involves identifying and associating specific Fact Types from numerous data sources to ensure accurate and relevant data representation. Fact Types are crucial for categorizing data across different dimensions such as assessments, discipline, membership, etc. This document outlines the architecture and processes used to manage, migrate, and utilize fact types in report generation.
Source to Staging ETL Stored Procedures: [Source].[Source-to-Staging_Assessments]
Staging to CEDS Data Warehouse ETL Stored Procedures: [App].[Wrapper_Migrate_Assessments_to_RDS]
CEDS Data Warehouse to Report Tables:
Current procedure: [RDS].[Get_FactTypeByReport]
Note: This procedure will be replaced by the GenerateReports_FactType
table.
Example: TBD (this hasn't been built as of 3/26/2024)
Staging Validation:
Example values from of ReportGroupOrCodes field in StagingValidationRules: "Directory, ChildCount, Exiting, Discipline, Personnel, Assessment, Membership"
Debug views for verifying ETL to Staging: [debug].[vwAssessments_StagingTables]
Debug views for verifying ETL to CEDS Data Warehouse: [debug].[vwAssessments_FactTable]
App.DataMigrationTasks
Names stored in the StoredProcedureName field contain fact type labels
For example RDS.
Wrapper_Migrate_[FactTypeLable]_to_RDS (RDS.vwDimAssessments)
Names used for views contain fact type labels
For example
Source.Source-to-Staging_[FactTypeLable] (Source.Source-to-Staging_vwDimAssessments)
Descriptions of Staging to CEDS Data Warehouse Wrapper scripts contain EDFacts file specification numbers for the Fact Type
For example RDS.
Wrapper_Migrate_[FactTypeLable]_to_RDS (RDS.
Wrapper_Migrate_vwDimAssessments
_to_RDS)