Transcript Integrating ACL and SQL Server

Integrating ACL and
SQL Server
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
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
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.
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.
Before Our Client/Server Solution
EMAIL DISTRIBUTION -NOTIFY
ACL
DATASOURCE
MANUAL POSTING OF REPORTS TO SITE
ACL
ACL PROD
PROD
Server
Server
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
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
Internal Audit Solution
ACL CODE PROMOTION
ReportBuilder
ACL -DEVELOPMENT
CORP USER
SSRS web server
ACL -PROD
VIA ODBC /EXCEL
OUTBOUND REPORT/DATA POSTS
.FILs NEEDED FOR PROJECTS
IA USER
SQL db Server
VIA ODBC/ACL
INBOUND DATA LOADS
FRAUD USER
SQL -db Server
FTP SERVER
VENDOR DATA
ORACLE
TERADATA
SQL SERVER
After Our Client/Server Solution
AUTOMATED EMAILS VIA NOTIFY
ACL
DATASOURCE
SSIS
MANUAL POSTING TO SITE
POSTING TO WEB SERVER
ACCESS TOREPORT VIA ACL
ACCESS TOREPORT VIA EXCEL
SQL
SQL Server
Server
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.
Solution- Using an Audit Objective
DIFFERENT SUBSCRIPTS
DATA SOURCE
START ACL
ACL
App Server
RUN ACL
ACL SCRIPTS
Script to create a 1
row table to be
read by SQL Server
SSIS and ACL
are on same
server
EXPORT &
INDICATE
More than X
number of cars not
on rent in > 5 days?
REPORT DELIVERY
Post
Post to
to Server
Server
Access
Access via
via
Security
application
Security application and/or
and/or
Audit
Audit Approval
Approval
Is the Audit
Threshold
exeeded?
NO
YES
EMAIL COMPLETION
TO MAILER
END
Entire
Process
Controlled
via SSIS
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)
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
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
FIRST 1
CLOSE
SET SESSION end_exprt
SET SAFETY ON
QUIT
"%v_path%\Indicator.txt"
Step2 – Create the .bat file
 Save
the script (master or other) as
a .bat file
Step2: Screen Shot
Step3: Use Execute Process Task to locate the
.bat file

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---






END
SELECT @UnitsIndChck AS UnitsIndChck
RETURN
-- done in other cft task--execute IA_sp_Ind_Chck
Step 5 : Put it all together in SSIS
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)
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
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.
Q&A

Thank You!