BI 4 ALL Demonstration 03 Stored Procedures www

BI 4 ALL Demonstration 03 Stored Procedures www. Instant. BI. com 1/7/2012

Introduction ®What is the idea behind using SPs ®SP Standards ®Example SPs ² Demonstration

Why Use Stored Procedures ® Data models are becoming more complex ® The “universe” and “catalogue” tools must reflect that complexity ² Business Objects universes are proliferating ® The likelihood of errors in reports is increasing ® The ability to understand what is on a report and to have confidence it is correct is decreasing ® The increase in coverage and complexity of data shows no signs of slowing up, indeed it is speeding up ® IBI predicts that we will see more errors creep into reports based on an abstraction layer between the database and the end user tool. Public Information Copyright 2007 – Instant Business Intelligence. 3

Why Use Stored Procedures ® Using Stored Procedures reduces likely error rates ® SPs are written by people who know the database very well and know the data very well ® SPs can be exposed via many tools ® The same SPs are executing under the covers means consistency in reports no matter the reporting tool ® Makes it faster and easier to deliver the same report into the reporting tool of choice for the end user ² How many BO Reports are replicated in Excel? Lots! ® Easier to have a variety of reporting tools rather than one standard tool. ² Mergers and acquisitions have caused issues with different reporting tools being widely used in the combined company Public Information Copyright 2007 – Instant Business Intelligence. 4

Standards ® SPs can be defined so that the text contains source for many different database languages. ² Eg. One text doc contains source for Oracle, DB 2, SQL Server etc ® Se. ETL can process the text to produce 1 file for each database the SP is to be delivered to. ® Formatting standards of the SPs ® Parameter standards ® Where clause standards ® Joining standards Public Information Copyright 2007 – Instant Business Intelligence. 5

Example Stored Procedures ® We will view some simple SPs ² ² SP 0001_One_Row SP 0001_Select_Currency_Code SP 0004_Select_Year_Name SP 0004_Select_Month_Name ® We will view some complex SPs ² ² SP 1013_Select_Product_Plnv. Act_By_Year_Month_Company_Geog_ Product_Sum 01_tpl SP 1015_Select_Sale_Plnv. Act_By_Year_Month_Company_Geog_Pro duct_tpl SP_SAA 0101 A_001 SP_SAA 0100 B_001 Public Information Copyright 2007 – Instant Business Intelligence. 6