Integrating ACL and SQL Server

1. Integrating ACL and SQL Server

2. AGENDA • Intros/Bios • Definition of Terms Used • Architecture (Before & After) • Business Reason for upgrade • Factors Considered in solution • Case Study: • Problem/objective • Solution • Lessons Learned • Q & A

3. Background of Presenter • Rene • 2 years EHI experience • 9 years audit experience (6 yrs. CAATS) • Toolset: ACL, T-SQL, PLSQL, SAS, SSAS, SSRS, SSIS, SQL Management Studio • Nick • 13 years EHI Experience • 8 years audit experience

4. Definition of Terms Used • SSIS – SQL Server Integration Services, provides ETL of data and automation of starting ACL. It is part of the Business Intelligence Developer Studio (BIDS) software. • SMS- SQL Server Management Studio, the more “dba” centric tool used for adding, deleting, updating tables on the server.

5. Definition of Terms Used (cont.) • SSRS – SQL Server Reporting Services, the reporting engine that hosts the reports on a web server, which the users access via internet.

6. Before Our Client/Server Solution

7. Infrastructure Investment • Efficient exception management solution • Repository for external data we accumulate • Flexibility of being able to access reports without having to be “ACL” proficient • Needed to be able to query/store large amounts of data

8. Factors Considered • In house Expertise • Scalability Across the Enterprise • Cost of Ownership • Familiarity with solutions • Technical Support Resources Availability • Out of box connectors for ETL

9. Internal Audit Solution

10. After Our Client/Server Solution

11. Case Study: ACL and SQL Server • Problem: Wanted to leverage our existing ACL CM projects and allow our end users to consume the reports in a variety of ways (i.e. ACL, excel or via the web). • Solution: Use the built in functionality of ACL to trigger SSIS into performing certain functions.

12. Solution- Using an Audit Objective

13. Solution –Step by Step • Step 1: Add a final script to existing ACL script stack (Indicator_script) • Step 2: Create the Indicator script to capture “threshold needed” (i.e. COUNTN variable)

14. Step1: Indicator Script • this syntax is done in the master- just to illustrate the SSIS process its done here • ****************************************** • SET SAFETY OFF • SET SESSION initialze_SYSDATE • SET DATE 'YYYYMMDD' • v_sys_date = ALLTRIM(DATE()) • SET LOG "LOG_%v_sys_date%" • ASSIGN v_end_date = ALLTRIM(DATE(CTOD(%v_sys_date%) - 34)) • ASSIGN v_ana_date = CTOD(%v_end_date%) • SET SESSION calcuate_days • COMM • ******************* • set script vars, note variable path • *********************** • ASSIGN v_path = "D:\FY12_RK_UnitsUnrented\EXPORTS" • SET SESSION strt_export • COMMENT • ************************ • COUNT TO TEST FOR THE Audit OBJECTIVE • ************************************************ • OPEN UnitsUnrented_%v_end_date% • SET FILTER TO c_DaysDiffOdy > 5 • COUNT • CLOSE

15. Step1: Export report & Indicator File • COMM • ********************* • export 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 "%v_path%\UnitsUnrented_%v_end_date%" KEEPTITLE SEPARATOR "," QUALIFIER '"' • COMM • ****************** • 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 variable. NOTE: “FIRST N ROWS , optional APPEND is not used • *************************** • OPEN UnitsUnrented_%v_end_date% • EXPORT TIME() + " " + DATE() + STRING(COUNT1, 10) TO "%v_path%\Indicator.txt" FIRST 1 • CLOSE • SET SESSION end_exprt • SET SAFETY ON • QUIT

16. Step2 – Create the .bat file • Save the script (master or other) as a .bat file

17. Step2: Screen Shot

18. Step3: Use Execute Process Task to locate the .bat file

19. Step 4: Create a Stored Procedure in SMS • USE rene_db • GO • -- ============================================= • -- Author: Rene Kennedy • -- Create date: 2011-11-05 • -- Description: Return status for upload file from acl to trigger ssis to do work • --EXEC Ia_sp_Ind_chck • -- ============================================= • ALTER PROCEDURE IA_sp_Ind_Chck • AS • DECLARE @UnitsIndChck int • BEGIN • SET @UnitsIndChck =( select CASE WHEN ACL_Count> 3300 THEN 1 ELSE 0 END ExceptStatus • from ACL_Ind_File) • --selects the status dynamically--- • SELECT @UnitsIndChck AS UnitsIndChck • RETURN • END • -- done in other cft task--- • execute IA_sp_Ind_Chck

20. Step 5 : Put it all together in SSIS

21. Lessons Learned • Negatives: • “Buy vs Build” – is true • We “paid for it” in terms of : • Resource allocation • Time to market (time to have reports running on the server) • Changing roles (no longer auditors) • Conflict of Interests (audit vs developers) • Still need to determine exception management solution framework (SharePoint or ASP.NET)

22. Lessons Learned • Positives: • Exception Management can be a scalable solution. • Able to “test drive” the platform • IT is more willing to help out now that we have “SQL code to look at” • Answered our business need

23. ACL Take-Aways • Don’t “hard code” the paths within the ACL projects, use variable substitution and code migration will be easier! • Leverage ACL’s “built-in” variables (i.e.. COUNTN, LOW1, HIGH1, ABS1) to perform automated steps for you. • Use EXPORT to send an “N row” table- which allows other applications (ie. SSIS) to read and act on it. • When using SSIS and ACL, it will be MUCH easier if both apps are on a single “production” server.

24. Q & A • Thank You!