Staging.RUN_DMC
Overview & Purpose
This stored procedure retrieves and formats membership report data for educational institutions. It dynamically constructs SQL queries based on input parameters to extract data from fact tables and apply various transformations including category filtering, zero count handling, and missing data obscuration. The procedure supports multiple report types and organizational levels.
Main Functions:
Dynamic SQL Generation
Constructs SQL queries at runtime based on input parameters to retrieve appropriate report data
Zero Count Handling
Includes or excludes records with zero counts based on parameters and business rules
Missing Category Obscuration
Replaces or removes records with missing category values based on configuration
Key Calculations:
Dynamic SQL Generation: Creates flexible queries that adapt to different report types and filtering requirements
Formula:
Dynamic SQL construction with parameter substitutionBusiness Significance: Enables a single procedure to handle multiple report formats and data structures
Example: Building SQL statements with different category sets and report levels
Zero Count Handling: Controls visibility of zero-value data points in reports
Formula:
Conditional inclusion of zero count recordsBusiness Significance: Ensures reports show complete data sets when required or cleaner reports when zeros are not meaningful
Example: Using @includeZeroCounts parameter to determine if zero count records should be included
Missing Category Obscuration: Handles missing data in a consistent way across reports
Formula:
UPDATE @reportData SET factField = -1 WHERE CategorySetCode = X AND ReportField = 'MISSING'Business Significance: Ensures data quality and consistency in reporting
Example: Setting student counts to -1 for missing category values when appropriate
Data Transformations:
Conversion of category codes to friendly names when requested
Filtering data based on report code, level, year, and category set
Handling of missing category values through obscuration or removal
Dynamic inclusion of zero count records based on parameters and business rules
Pagination of results using start record and number of records parameters
Expected Output:
The procedure returns two result sets: 1) A paginated set of report data with organization details, categories, and count values, and 2) A count of total records matching the criteria before pagination.
Business Context
System: Educational Data Reporting System
Necessity: Provides flexible, consistent access to educational membership data across different organizational levels and reporting requirements
Business Rules:
Some reports require special handling for zero counts based on report code and level
Missing category values may be obscured with -1 values when specified
Performance-related reports (c175, c178, c179) have special handling when not at SEA level
Reports can be filtered by organization level (SEA, LEA, school)
Category sets control which dimensions are included in reports
Result Usage:
The data is used to generate standardized educational reports for compliance, analysis, and decision-making
Execution Frequency:
On-demand when reports are requested
Critical Periods:
End of school year reporting periods
Federal and state reporting deadlines
Parameters
@reportCode
varchar(50)
Identifies the specific report to generate
True
@reportLevel
varchar(50)
Specifies the organizational level for the report
True
@reportYear
varchar(50)
Specifies the school year for the report data
True
@categorySetCode
varchar(50)
Specifies which category set to use for the report
False
@includeZeroCounts
bit
Controls whether records with zero counts are included
True
@includeFriendlyCaptions
bit
Controls whether category codes are translated to friendly names
True
@obscureMissingCategoryCounts
bit
Controls handling of records with missing category values
True
@isOnlineReport
bit
Indicates if the report is being generated for online viewing
False
@startRecord
int
Starting record number for pagination
True
@numberOfRecords
int
Number of records to return for pagination
True
Source Tables
rds.FactCustomCounts
Business Purpose: Stores custom count data for specialized reports
Columns:
FactCustomCountId
Unknown
Primary key for the custom count record
ReportCode
Unknown
Identifies which report the data belongs to
app.GenerateReport_FactType
Business Purpose: Maps reports to fact types
Columns:
GenerateReportId
Unknown
Links to the report definition
FactTypeId
Unknown
Links to the fact type definition
rds.DimFactTypes
Business Purpose: Stores fact type definitions
Columns:
DimFactTypeId
Unknown
Primary key for fact type
FactTypeCode
Unknown
Code identifying the fact type
rds.DimSchoolYears
Business Purpose: Stores school year definitions
Columns:
SchoolYear
Unknown
Identifies a specific school year
app.FactTables
Business Purpose: Stores metadata about fact tables in the system
Columns:
FactTableId
Unknown
Primary key for fact table metadata
FactTableName
Unknown
Name of the physical fact table
FactFieldName
Unknown
Name of the main fact field in the table
FactReportTableName
Unknown
Name of the report table associated with the fact
FactReportTableIdName
Unknown
Name of the ID field in the report table
FactReportDtoIdName
Unknown
Name of the DTO ID field
app.GenerateReports
Business Purpose: Stores report definitions
Columns:
GenerateReportId
Unknown
Primary key for report definition
ReportCode
Unknown
Code identifying the report
FactTableId
Unknown
Links to the fact table used by the report
GenerateReportTypeId
Unknown
Links to the report type
app.Dimensions
Business Purpose: Stores dimension definitions
Columns:
DimensionId
Unknown
Primary key for dimension definition
DimensionFieldName
Unknown
Name of the dimension field
DimensionTableId
Unknown
Links to the dimension table
app.CategorySets
Business Purpose: Stores category set definitions
Columns:
CategorySetId
Unknown
Primary key for category set
CategorySetCode
Unknown
Code identifying the category set
CategorySetName
Unknown
Name of the category set
GenerateReportId
Unknown
Links to the report definition
OrganizationLevelId
Unknown
Links to the organization level
SubmissionYear
Unknown
Year the category set applies to
TableTypeId
Unknown
Links to the table type
CategorySetSequence
Unknown
Order of the category set
Temporary Tables
@reportData
Purpose: Stores the processed report data before final output
Columns:
Various including StateANSICode, OrganizationName, etc.
Various
Direct assignment from source data
@ReportFieldsInFactTable
Purpose: Stores the report fields available in the fact table
Columns:
ReportField
nvarchar(100)
Direct assignment from app.Dimensions
@ReportFieldsInCategorySet
Purpose: Stores the report fields used in the selected category set
Columns:
ReportField
varchar(100)
Direct assignment from app.Dimensions
#CAT_PerformanceLevel
Purpose: Stores performance level category options for performance reports
Columns:
Code
varchar(50)
Direct assignment from app.CategoryOptions
#performanceData_*
Purpose: Stores performance data for each category set in performance reports
Columns:
Various including StateANSICode, OrganizationName, etc.
Various
Generated by RDS.Get_CountSQL
Potential Improvements
Error Handling
Description: Add explicit error handling for invalid parameters and SQL execution failures
Benefits: Improved reliability and easier troubleshooting
Priority: High
Performance
Description: Optimize dynamic SQL generation to reduce string concatenation
Benefits: Improved execution time for complex reports
Priority: Medium
Code Maintainability
Description: Refactor into smaller, more focused stored procedures
Benefits: Improved maintainability and easier debugging
Priority: Medium
Security
Description: Add parameter validation to prevent SQL injection
Benefits: Improved security and data protection
Priority: High
Documentation
Description: Add comprehensive inline documentation
Benefits: Improved maintainability and knowledge transfer
Priority: Medium
Execution Steps
Step 1: Initialize variables and determine fact/report tables
Input Data: Input parameters
Transformations: None
select @factTable = ft.FactTableName, @factField = ft.FactFieldName, @factReportTable = ft.FactReportTableName, @factReportId = ft.FactReportTableIdName, @factReportDtoId = ft.FactReportDtoIdName from app.FactTables ft inner join app.GenerateReports r on ft.FactTableId = r.FactTableId where r.ReportCode = @reportCode
Step 2: Handle custom counts if applicable
Input Data: Fact table information
Transformations: None
if @factTable = 'FactCustomCounts' begin select FactCustomCountId, ReportCode, ... from rds.FactCustomCounts where ReportCode = @reportCode and ReportLevel = @reportLevel and ReportYear = @reportYear and CategorySetCode = isnull(@categorySetCode, CategorySetCode) return end
Step 3: Identify report fields in fact table and category set
Input Data: Fact table information
Transformations: None
insert into @ReportFieldsInFactTable (ReportField) select distinct upper(d.DimensionFieldName) as ReportField from App.Dimensions d inner join App.FactTable_DimensionTables fd on d.DimensionTableId = fd.DimensionTableId inner join App.FactTables ft on fd.FactTableId = ft.FactTableId where ft.FactTableName = @factTable and upper(d.DimensionFieldName) <> 'YEAR'
Step 4: Build dynamic SQL for report data retrieval
Input Data: Report fields and parameters
Transformations: Complex SQL string building
set @sql = @sql + 'declare @reportData as table (...)'
Step 5: Handle performance reports if applicable
Input Data: Report code, level, and year
Transformations: Complex SQL string building
if(@isPerformanceSql = 1) begin ... end
Step 6: Handle zero counts if applicable
Input Data: includeZeroCounts parameter
Transformations: Complex SQL string building
if @includeZeroCounts = 1 begin ... end
Step 7: Handle missing category obscuration if applicable
Input Data: obscureMissingCategoryCounts parameter
Transformations: Complex SQL string building
if @obscureMissingCategoryCounts = 1 begin ... end
Step 8: Finalize and execute dynamic SQL
Input Data: Constructed SQL string
Transformations: None
EXECUTE sp_executesql @sql, @ParmDefinition, @reportCode = @reportCode, @reportYear = @reportYear, @reportLevel = @reportLevel, @categorySetCode = @categorySetCode, @isOnlineReport=@isOnlineReport;
Was this helpful?