英特尔大学进修课程 - 财务资料库运行管理 Finance_DRV.pdfVIP

Injecting custom stored procedures into Finance KTBR cycle using wrapper procedure Subject Area: Finance_DRV Presenter: Jenglung Lin Why Wrapper Procedure? • Consolidate customer procedures into one box in Autosys. • Host both full and delta refresh. • Simplify dependencies Problem Statement • IS • A L2/L3 Process in Finance. • A stored procedure to populate DRV table(s). • A new Autosys box. • IS NOT • A new cycle. • An EMeta script. Architectural View USER Autosys Application Finance_DRV Finance_DRV Appl_Finance_DRV_01 View: Table: Stored Proc: v_fact_fx_var Fact_fx_var Pr_ff_fx_var.sql Stored Proc: Pr_as_fx_var.sql Autosys Scheduler Your Stored Procedure • Naming Convention • pr_ff_YourProblemDomain.sql (if table prefix is “fact”) – For dimension table: pr_fd (full sp) and pr_dd (delta sp) – For fact table: pr_ff (full sp) and pr_df (delta sp) – For relation table: pr_fr (full sp) and pr_dr (delta sp) – For staging table: pr_fs (full sp) and pr_ds (delta sp) • (Example: pr_ff_fx_var.sql) • Location in VSS • $/Subject Areas Dev/Finance_DRV/StoredProcsAppl • Compiled in subject area • Appl_Finance_DRV_01/Procedures Stored Procedure Wrapper • Naming Convention • pr_as_YourProblemDomain.sql • (Example: pr_ff_fx_var.sql) • Location in VSS • $/Subject Areas Dev/Finance_DRV/WrapperProcs • Compiled in subject area • Finance_DRV_MET/Procedures How to create a wrapper procedures? • Pick an existing one as template • Change the following for your proc