Problem statement:
The end client has more than 156 cinema locations for which the GST data needs to be consolidated for monthly GST filing. The problems that the end client desires to solve are as follows:
· Automation of the GST loads process.
· Error handling and data auditing
Tasks achieved
Full load of all 156 locations:
The ETL team tried single load and 2 parallel load approach for implementation of the same.
The performance parameters of the ETL process was gauged by the following parameters:
· Time of load
· Error logging and status logging
· Types of errors
· Validation issues
Process flow: The end client has a set of stored procedures running at remote locations. The procedures are of 2(two) types: 2 parameter and 3 parameters. The ETL load accommodated both the types of stored procedures into the final solution.
The ETL team has done an initial load for 2 parameter(only the date from and date to are given as input parameters) and 3 parameter locations(only the cinema_operator,date from and date to are given as input parameters).
Attached is the entire process flow at the process task level:
In the initial load package, the initial load of 2 parameters and 3 parameters happens in parallel.
Hereafter there could be some locations that are not loaded (of 2 parameters and 3 parameters). These locations are loaded in the incremental set up.
Condition for the incremental set up is derived from the variable value:
@[user::count]>0 condition is given for add missing value in data for the process is running when count Value is greater then 0.
The given variable @usercount is derived from the execution of the sql transform as follows:
Mapping of the result output to the variable:
Package explanation of initial load :
3 parameters and 2 parameters are loaded in parallel in initial load package.
Explanation of two parameters load
Fetch of the maximum row_id from audit_table. The table name audit_table has all the locations with 2(two) parameter load.
Set the parameters for loop increments.
The variable @inc is set to 1.
@max variable has been set to the maximum number of rows in the audit table.
For each location a dynamic connection string is used for connecting to the remote location and extracting data from the remote location.
Fetching of connection string for dynamic location: the audit table has the connection parameters or all locations. These connection parameters are extracted one by one using the execute sql command.
Sqlcommand: =
" select Cinema_strCode code, Accessed_user id, Channel_Address ip, convert(varchar(30), DECRYPTBYPASSPHRASE('8', password)) pass, SUBSTRING(tableSynonym, 0, CHARINDEX('.', tableSynonym, 1))dbname from tblCinema_Master where Cinema_strCode in (select code from audit_table where row_id="+(dt_wstr,4) @[User::inc]+" );"
The data is set to result set
Dynamic Location is set to the static location by setting the expression connection string
Con1:(setting up the connection string)
"Data Source="+ @[User::ip1] +";User ID="+ @[User::id1] +";Initial Catalog="+ @[User::dbname1] +";Provider=SQLNCLI11.1"+";Password="+ @[User::pass1] +";"
Now the Sql Execute Task connection string assigned dynamic connection.
Fetching the data from remote location using dynamic connection string:
Updated Intermediate_Test
Validation rules: the end client asked the ETL team to apply validation rules to check the quality of the data. the End client had input percentages of 18,12,11,14,0 and 5. Any transactions not in the given percentage ranges were classified as having wrong data.
Rule Apply first Check (18,12,11,14,0,5)
Checked Rule Second : this rule entailed that any entry having 0% GST should not have total value less than -2 or more than 2.
Update the Intermediate location with covered date and location.
Executed the exjoinexceptiontable for apply both rules.
Truncated the intermediate tables
truncate table tblGstIntermediate;
truncate table [dbo].[AuditTable1];
truncate table [dbo].[auditTable2];
Incremental load of two parameters; this module indicates the approach for 2 parameter incremental load.
The incremental load for 2 parameters was required since there could be some locations that have been missed out of the initial load due to the connection errors. In such a scenario the incremental load for 2 parameter locations needs to be done.
ALTER procedure [dbo].[sp_missing_2_parameter]
as
begin
truncate table missing_2_parameters;
--drop index idx_cinema_str on tblGst_Test11;
with cte1 as(select distinct cinema_strcode from [dbo].[tblGst_Test11])
select * into #cte1 from cte1;
create index #idx_cte1 on #cte1(cinema_strcode);
with
cte2
as( select * from [dbo].[audit_table]
where code not in (select distinct cinema_strcode from #cte1))
insert into missing_2_parameters
select * from cte2;
--create index idx_cinema_str on tblGst_Test11(cinema_strcode);
end;
We will find the missing location from missing_2_parameters, and we
Start loading the data from missing_2_parameters table.
Executed the sp_missing_2_parameter for load the missing location
exec sp_missing_2_parameter;
The find out the maximum missing_id from missing_2_parameters.
select max( missing_id) from missing_2_parameters
Setting the value of for loop parameters
For loop will iterate till the max_inc meet.
Note :
Then we have taken sequence container
Inside taken Execute SQL task (Taking Dynamic connection string one by one)
The ETL team replicated the same process for 3 parameter load.
Note: this blog covers only the serial execution. The process of parallel execution(running multiple dataflows simultaneously for 2 parameter data load was work in progress).
APPENDIX
Activity
1.) Logging : the logging mode was enabled for package data load.
1. Set the error log for the package.
Steps:-
Set the Provider type and configure the type of data base where to store.
Then set the event to store the error points
In case of any GST automation requirements, please contact the following:
Name: Apoorv Chaturvedi
email: support@mndatasolutions.com;support@turbodatatool.com
Phone; +91-8802466356
Website: https://mn-business-intelligence-india.business.site/
No comments:
Post a Comment