integrating acl and sql server. agenda intros/bios intros/bios definition of terms used definition...
Post on 26-Mar-2015
220 Views
Preview:
TRANSCRIPT
Integrating ACL and Integrating ACL and SQL ServerSQL Server
AGENDAAGENDA Intros/BiosIntros/Bios Definition of Terms UsedDefinition of Terms Used Architecture (Before & After)Architecture (Before & After) Business Reason for upgradeBusiness Reason for upgrade Factors Considered in solutionFactors Considered in solution Case Study:Case Study:
– Problem/objectiveProblem/objective– SolutionSolution
Lessons LearnedLessons Learned Q & AQ & A
Background of PresenterBackground of Presenter
ReneRene– 2 years EHI experience2 years EHI experience– 9 years audit experience (6 yrs. CAATS)9 years audit experience (6 yrs. CAATS)– Toolset: ACL, T-SQL, PLSQL, SAS, SSAS, Toolset: ACL, T-SQL, PLSQL, SAS, SSAS,
SSRS, SSIS, SQL Management StudioSSRS, SSIS, SQL Management Studio NickNick
– 13 years EHI Experience13 years EHI Experience– 8 years audit experience8 years audit experience
Definition of Terms UsedDefinition of Terms Used
SSIS – SQL Server Integration Services, SSIS – SQL Server Integration Services, provides ETL of data and automation of provides ETL of data and automation of starting ACL. It is part of the Business starting ACL. It is part of the Business Intelligence Developer Studio (BIDS) Intelligence Developer Studio (BIDS) software. software.
SMS- SQL Server Management Studio, the SMS- SQL Server Management Studio, the more “dba” centric tool used for adding, more “dba” centric tool used for adding, deleting, updating tables on the server.deleting, updating tables on the server.
Definition of Terms Used (cont.)Definition of Terms Used (cont.)
SSRS – SQL Server Reporting SSRS – SQL Server Reporting Services, the reporting engine that Services, the reporting engine that hosts the reports on a web server, hosts the reports on a web server, which the users access via internet.which the users access via internet.
Before Our Client/Server SolutionBefore Our Client/Server Solution
DATASOURCE
ACL PRODServer
ACL PRODServer
EMAIL DISTRIBUTION -NOTIFY
MANUAL POSTING OF REPORTS TO SITE
ACL
Infrastructure InvestmentInfrastructure Investment Efficient exception management solutionEfficient exception management solution
Repository for external data we Repository for external data we accumulateaccumulate
Flexibility of being able to access reports Flexibility of being able to access reports without having to be “ACL” proficientwithout having to be “ACL” proficient
Needed to be able to query/store large Needed to be able to query/store large amounts of dataamounts of data
Factors ConsideredFactors Considered In house ExpertiseIn house Expertise
Scalability Across the EnterpriseScalability Across the Enterprise
Cost of OwnershipCost of Ownership
Familiarity with solutionsFamiliarity with solutions
Technical Support Resources AvailabilityTechnical Support Resources Availability
Out of box connectors for ETLOut of box connectors for ETL
Internal Audit SolutionInternal Audit Solution
ACL -PROD
SQL -db Server
CORP USER
ACL -DEVELOPMENT
ORACLE TERADATA SQL SERVER
INBOUND DATA LOADS
FTP SERVER
VENDOR DATA
.FILs NEEDED FOR PROJECTS
ACL CODE PROMOTION
SQL db Server
SSRS web server
IA USER
FRAUD USER
OUTBOUND REPORT/DATA POSTS
ReportBuilder
VIA ODBC /EXCEL
VIA ODBC/ACL
After Our Client/Server SolutionAfter Our Client/Server Solution
DATASOURCE
ACL
SQL ServerSQL Server
SSIS
ACCESS TOREPORT VIA ACL
ACCESS TOREPORT VIA EXCEL
POSTING TO WEB SERVER
MANUAL POSTING TO SITE
AUTOMATED EMAILS VIA NOTIFY
Case Study: ACL and SQL ServerCase Study: ACL and SQL Server
Problem: Wanted to leverage our Problem: Wanted to leverage our existing ACL CM projects and allow existing ACL CM projects and allow our end users to consume the reports our end users to consume the reports in a variety of ways (i.e. ACL, excel or in a variety of ways (i.e. ACL, excel or via the web).via the web).
Solution: Use the built in Solution: Use the built in functionality of ACL to trigger SSIS functionality of ACL to trigger SSIS into performing certain functions.into performing certain functions.
Solution- Using an Audit ObjectiveSolution- Using an Audit Objective
DATA SOURCE ACLApp ServerSTART ACL
Is the Audit Threshold exeeded?
EXPORT & INDICATE
Script to create a 1 row table to be read by SQL Server
ACL SCRIPTS
DIFFERENT SUB-SCRIPTS
EMAIL COMPLETION TO MAILER
END
More than X number of cars not on rent in > 5 days?
SSIS and ACL are on same
server
RUN ACL
Post to ServerPost to Server
REPORT DELIVERY
Entire Process
Controlled via SSIS
YES
NO
Access via Security application and/or
Audit Approval
Access via Security application and/or
Audit Approval
Solution –Step by StepSolution –Step by Step
Step 1: Add a final script to existing Step 1: Add a final script to existing ACL script stack (Indicator_script) ACL script stack (Indicator_script)
Step 2: Create the Indicator script to Step 2: Create the Indicator script to capture “threshold needed” (i.e. capture “threshold needed” (i.e. COUNTN variable)COUNTN variable)
Step1: Indicator ScriptStep1: Indicator Script this syntax is done in the master- just to illustrate the SSIS process its done herethis syntax is done in the master- just to illustrate the SSIS process its done here ************************************************************************************ SET SAFETY OFFSET SAFETY OFF SET SESSION initialze_SYSDATESET SESSION initialze_SYSDATE SET DATE 'YYYYMMDD'SET DATE 'YYYYMMDD' v_sys_date = ALLTRIM(DATE())v_sys_date = ALLTRIM(DATE()) SET LOG "LOG_%v_sys_date%"SET LOG "LOG_%v_sys_date%" ASSIGN v_end_date = ALLTRIM(DATE(CTOD(%v_sys_date%) - 34))ASSIGN v_end_date = ALLTRIM(DATE(CTOD(%v_sys_date%) - 34)) ASSIGN v_ana_date = CTOD(%v_end_date%)ASSIGN v_ana_date = CTOD(%v_end_date%)
SET SESSION calcuate_daysSET SESSION calcuate_days COMMCOMM ************************************** set script vars, note variable pathset script vars, note variable path **********************************************
ASSIGN v_path = "D:\FY12_RK_UnitsUnrented\EXPORTS"ASSIGN v_path = "D:\FY12_RK_UnitsUnrented\EXPORTS" SET SESSION strt_exportSET SESSION strt_export
COMMENTCOMMENT ************************************************ COUNT TO TEST FOR THE Audit OBJECTIVECOUNT TO TEST FOR THE Audit OBJECTIVE ************************************************************************************************
OPEN UnitsUnrented_%v_end_date%OPEN UnitsUnrented_%v_end_date% SET FILTER TO c_DaysDiffOdy > 5SET FILTER TO c_DaysDiffOdy > 5 COUNTCOUNT CLOSECLOSE
Step1: Export report & Indicator FileStep1: Export report & Indicator File COMMCOMM ****************************************** export the report – done in del format for ease of integration with SSISexport the report – done in del format for ease of integration with SSIS **********************************************************
EXPORT FIELDS FIELD1 AS ‘FIELD1' FIELD2 AS ‘FIELD2' FIELD3 AS ‘FIELD3' DELIMITED TO EXPORT FIELDS FIELD1 AS ‘FIELD1' FIELD2 AS ‘FIELD2' FIELD3 AS ‘FIELD3' DELIMITED TO "%v_path%\UnitsUnrented_%v_end_date%" KEEPTITLE SEPARATOR "," QUALIFIER '"'"%v_path%\UnitsUnrented_%v_end_date%" KEEPTITLE SEPARATOR "," QUALIFIER '"'
COMMCOMM ************************************ export indicator file, using the COUNTN variable which is converted to CHAR, then read by export indicator file, using the COUNTN variable which is converted to CHAR, then read by
SSIS package and decision is taken based solely on the threshold –which is the COUNTN SSIS package and decision is taken based solely on the threshold –which is the COUNTN variable. NOTE: “FIRST N ROWS , optional APPEND is not usedvariable. NOTE: “FIRST N ROWS , optional APPEND is not used
******************************************************
OPEN UnitsUnrented_%v_end_date%OPEN UnitsUnrented_%v_end_date% EXPORT TIME() + " " + DATE() + STRING(COUNT1, 10) TO "%v_path%\Indicator.txt" EXPORT TIME() + " " + DATE() + STRING(COUNT1, 10) TO "%v_path%\Indicator.txt"
FIRST 1 FIRST 1 CLOSECLOSE SET SESSION end_exprtSET SESSION end_exprt SET SAFETY ONSET SAFETY ON QUITQUIT
Step2 – Create the .bat fileStep2 – Create the .bat file Save the script (master or other) as a Save the script (master or other) as a
.bat file.bat file
Step2: Screen ShotStep2: Screen Shot
Step3: Use Execute Process Task to locate Step3: Use Execute Process Task to locate the .bat filethe .bat file
Step 4: Create a Stored Procedure in SMSStep 4: Create a Stored Procedure in SMS USE USE rene_dbrene_db GOGO
-- =============================================-- ============================================= -- Author:-- Author: Rene KennedyRene Kennedy -- Create date: 2011-11-05-- Create date: 2011-11-05 -- Description:-- Description: Return status for upload file from acl to trigger ssis to do workReturn status for upload file from acl to trigger ssis to do work --EXEC Ia_sp_Ind_chck--EXEC Ia_sp_Ind_chck -- =============================================-- =============================================
ALTER PROCEDURE IA_sp_Ind_ChckALTER PROCEDURE IA_sp_Ind_Chck ASAS DECLARE @UnitsIndChck intDECLARE @UnitsIndChck int BEGINBEGIN SET @UnitsIndChck =( select CASE WHEN ACL_Count> 3300 THEN 1 ELSE 0 SET @UnitsIndChck =( select CASE WHEN ACL_Count> 3300 THEN 1 ELSE 0
END ExceptStatusEND ExceptStatus from ACL_Ind_File)from ACL_Ind_File)
--selects the status dynamically-----selects the status dynamically--- SELECT @UnitsIndChck AS UnitsIndChckSELECT @UnitsIndChck AS UnitsIndChck RETURNRETURN ENDEND
-- done in other cft task----- done in other cft task--- execute IA_sp_Ind_Chck execute IA_sp_Ind_Chck
Step 5 : Put it all together in SSISStep 5 : Put it all together in SSIS
Lessons LearnedLessons Learned Negatives:Negatives:
– ““Buy vs Build” – is trueBuy vs Build” – is true– We “paid for it” in terms of :We “paid for it” in terms of :
Resource allocationResource allocation
Time to market (time to have reports running on the Time to market (time to have reports running on the server)server)
Changing roles (no longer auditors)Changing roles (no longer auditors)
Conflict of Interests (audit vs developers)Conflict of Interests (audit vs developers)
Still need to determine exception management Still need to determine exception management solution framework (SharePoint or ASP.NET)solution framework (SharePoint or ASP.NET)
Lessons LearnedLessons Learned Positives:Positives:
– Exception Management can be a scalable Exception Management can be a scalable solution.solution.
– Able to “test drive” the platformAble to “test drive” the platform
– IT is more willing to help out now that we have IT is more willing to help out now that we have “SQL code to look at”“SQL code to look at”
– Answered our business needAnswered our business need
ACL Take-AwaysACL Take-Aways Don’t “hard code” the paths within the ACL projects, use Don’t “hard code” the paths within the ACL projects, use
variable substitution and code migration will be easier!variable substitution and code migration will be easier!
Leverage ACL’s “built-in” variables (i.e.. COUNTN, LOW1, Leverage ACL’s “built-in” variables (i.e.. COUNTN, LOW1, HIGH1, ABS1) to perform automated steps for you.HIGH1, ABS1) to perform automated steps for you.
Use EXPORT to send an “N row” table- which allows other Use EXPORT to send an “N row” table- which allows other applications (ie. SSIS) to read and act on it.applications (ie. SSIS) to read and act on it.
When using SSIS and ACL, it will be MUCH easier if both When using SSIS and ACL, it will be MUCH easier if both apps are on a single “production” server.apps are on a single “production” server.
Q & AQ & A
Thank You!Thank You!
top related