Integrating ACL and SQL Server

Document Sample
Integrating ACL and SQL Server Powered By Docstoc
					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 PROD
                   ACL 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

             ACL                AUTOMATED EMAILS VIA NOTIFY
DATASOURCE




                                     MANUAL POSTING TO SITE




                       SSIS
                                      POSTING TO WEB SERVER




                                    ACCESS TOREPORT VIA ACL




                                   ACCESS TOREPORT VIA EXCEL




                   SQL Server
                   SQL 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 SUB-
                                                                                 SCRIPTS


                                     ACL
DATA SOURCE    START ACL                          RUN ACL
                                  App Server                   ACL SCRIPTS




                                                                               Script to create a 1
                                SSIS and ACL                                   row table to be
                                are on same                                    read by SQL Server
                                                                                                        Entire
                                   server                                                              Process
                                                                EXPORT &
                                                                INDICATE                              Controlled
                                                                                                       via SSIS
                                                                               More than X
                                                                               number of cars not
                                                                               on rent in > 5 days?


                                                               Is the Audit
                                                     YES        Threshold
                                                                exeeded?
              REPORT DELIVERY




                                                                   NO
                                 Post to Server
                                 Post to Server
                Access via
                Access via
       Security application and/or
       Security application and/or                          EMAIL COMPLETION
             Audit Approval
             Audit Approval                                    TO MAILER




                                                                  END
        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           "%v_path%\Indicator.txt"
    FIRST 1
   CLOSE
   SET SESSION end_exprt
   SET SAFETY ON
   QUIT
   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---

         SELECT @UnitsIndChck AS UnitsIndChck
         RETURN
   END

   -- 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!

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:94
posted:5/20/2012
language:English
pages:24