ease20022002
Technical User
Hi, I have been searching for weeks on attempting to find the best practices for running a process that was orignally developed in MS Access, that I had to deconstruct the entire database, implement a Star Schema, and then replace the processing portion.
Essentially, what I did was break-up all the parts that could be reused separately into different stored procedures b.c I don't know if they will be called separately in the future (I suspect they will). So basically I have one stored procedure that executes a bunch of remaining stored procedures. I also put in Try Catch error handling and Transaction mangement in each stored procedure. In each stored procedure listed, they all have the same Try Catch errorhandling and the same Transaction management.
Here is the SP that executes the entire process. If you guys could give me feed back on any practices that I should use (besides dynamic sql...lol, which I did change to the sp_executesql with QuoteName and dbo safeties) I would greatly appreciate it:
Thanks a bunch for your guys help. This forum is awesome.
FYI, the entire process works and balances.
Paul
Essentially, what I did was break-up all the parts that could be reused separately into different stored procedures b.c I don't know if they will be called separately in the future (I suspect they will). So basically I have one stored procedure that executes a bunch of remaining stored procedures. I also put in Try Catch error handling and Transaction mangement in each stored procedure. In each stored procedure listed, they all have the same Try Catch errorhandling and the same Transaction management.
Here is the SP that executes the entire process. If you guys could give me feed back on any practices that I should use (besides dynamic sql...lol, which I did change to the sp_executesql with QuoteName and dbo safeties) I would greatly appreciate it:
Code:
USE [FinancialDB]
GO
/****** Object: StoredProcedure [dbo].[uspTimeDetailProcess] Script Date: 10/13/2009 10:56:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspTimeDetailProcess]
AS
BEGIN
--Holds info on whether or not this proc originated the
--transaction.
DECLARE @v_trans_started BIT
BEGIN TRY
PRINT 'Executing uspRunTimeDetailProcess.'
SET @v_trans_started = 0
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION;
SET @v_trans_started = 1
END
ELSE
SET @v_trans_started = 0
Declare @SelectTable Varchar(20)
Set @SelectTable = 'ExtractCognos'
EXEC uspTimeDetailDeleteWeek --Deletes the week's records from the TimeDetail table that are in the ExtractCognos table
EXEC uspExtractCognosTrim --Trims all fields from import (Move to ETL package)
EXEC uspExtractCognosQuotes --Removes quotes from all fields (Move to ETL package)
EXEC uspExtractCognosCreateProject --Creates missing Project Codes
EXEC uspExtractCognosCreatePASDProject --Creates missing PASD Project Codes
EXEC uspExtractCognosCreateProgram --Creates Program codes
EXEC uspExtractCognosNZ --Removes all Nulls, varchar = 0 length strings, Date = 1/1/9999 and Numeric = 0
EXEC uspExtractCognosResTypChargeCostFTECPs --Calculates Resource Type, Chargeable, Cost, FTEs and Contract Programmers
-- The following SP Inserts new values from ExtractCognos tbl into Dimension tables (Star Schema)
EXEC uspInsertDimensionValues 'DimYears', 'Year', @SelectTable, 'Year'
EXEC uspInsertDimensionValues 'DimMonths', 'Month', @SelectTable, 'Month'
EXEC uspInsertDimensionValues 'DimWeeks', 'Week', @SelectTable, 'Week'
EXEC uspInsertDimensionValues 'DimPrograms', 'Program', @SelectTable, 'Program'
EXEC uspInsertDimensionValues 'DimProjects', 'Project', @SelectTable, 'Project'
EXEC uspInsertDimensionValues 'DimProjectDescriptions', 'ProjectDescription', @SelectTable, 'ProjectDescription'
EXEC uspInsertDimensionValues 'DimUserRespCodes', 'UserRespCode', @SelectTable, 'UserRespCode'
EXEC uspInsertDimensionValues 'DimResources', 'Resource', @SelectTable, 'Resource'
EXEC uspInsertDimensionValues 'DimShortNames', 'ShortName', @SelectTable, 'ShortName'
EXEC uspInsertDimensionValues 'DimCompCodes', 'CompCode', @SelectTable, 'CompCode'
EXEC uspInsertDimensionValues 'DimResourceSources', 'ResourceSource', @SelectTable, 'ResourceSource'
EXEC uspInsertDimensionValues 'DimResourceTypes', 'ResourceType', @SelectTable, 'ResourceType'
EXEC uspInsertDimensionValues 'DimResourceRoles', 'ResourceRole', @SelectTable, 'ResourceRole'
EXEC uspInsertDimensionValues 'DimActivities', 'Activity', @SelectTable, 'Activity'
EXEC uspTimeDetailInsertExtractCognos --Inserts ExtractCognos records with Foreignkeys into the FactTimeDetail table
IF @v_trans_started = 1
BEGIN
SET @v_trans_started = 0
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
IF @v_trans_started = 1
BEGIN
SET @v_trans_started = 0
ROLLBACK TRANSACTION;
END
RAISERROR ('Error in uspRunTimeDetailProcess!',16,1)
END CATCH
END
Thanks a bunch for your guys help. This forum is awesome.
FYI, the entire process works and balances.
Paul