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!