integrating acl and sql server n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Integrating ACL and SQL Server PowerPoint Presentation
Download Presentation
Integrating ACL and SQL Server

Loading in 2 Seconds...

play fullscreen
1 / 24

Integrating ACL and SQL Server - PowerPoint PPT Presentation


  • 138 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Integrating ACL and SQL Server' - lori


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
agenda
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
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
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
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.
infrastructure investment
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
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
case study acl and 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 step by step
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
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
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
Step2 – Create the .bat file
  • Save the script (master or other) as a .bat file
step 4 create a stored procedure in sms
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
lessons learned
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 learned1
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
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.
slide24
Q & A
  • Thank You!