Barcelona – March 2005 International Sales & Services Meeting

1. Barcelona – March 2005 International Sales & Services Meeting Application Best Practices

2. AppSet Configuration Application Configuration Calculations Reports & Schedules Security Managing Production Hardware Configuration Agenda

3. MS Excel The Engine • What SQL and OLAP are ? • What Write-Back is ? • What FACT tables are ? • ............... ? DB Server Real Time Partition FAC2 Partition FACT Partition Client Analisys Server DTS SQL Server FACT (MOLAP) SQL Server FAC2 (MOLAP) Application Server SQL Server WB (ROLAP) IIS/COM+

4. Multiple appsets per customer is good Ex: Gannett appsets Global Consolidated Newspapers Broadcast USA Today Data content and Security access is defined by respective owners Users needing access across Appsets still has single interface to information, just multiple logons Benefits Business Units get full control over their data and administration HQ gets good data when it is ready HQ analysts can still have access to Business Unit detail Minimize impact of admin changes to production systems AppSet Configuration

5. Multiple applications is normal and good Why Different dimensionality Ease of use Performance Ex: Gannett All Newspapers Newspapers with additional sub-department detail Ex: Legal Consolidation shell LegalApp Intercompany Application Configuration

6. Microsoft’s MDX calculation performance is limited Base Restatements or large volume of calcs Avoid using Dimension logic Use Hierarchies, even alternates Never use for base level calculations If you need to use it with extreme care Use Modeling logic for Base Calcs SQL vs MDX Do everything in SQL if possible (i.e. Default FX in v4 is pure SQL) If calcs not required in Real Time move to a logic script that is run from DM Default logic can really reduce server scalability during peak submission times Use BOTH worksheet calcs for schedules and Modeling logic for integrity and restatements Calculation

7. Design for ease of use and simplicity 2-3 clicks to get to what they need CV and control panels are only for power users and analysis Assume no user training Build in guided navigation and error-proofing New evDREfunction New Park N GOfunction Design for performance 1-3 minute rule Minimize # and volume of refreshes Minimal use of expansions Design for low bandwidth Use calc’s in sheet for schedules Put reports and schedules for the masses in wizards Reports & schedules

8. Objective Increased performance of large reports and schedules To optimize the performance of a report or schedule query A new function to build either a report or schedule Supports “Static” reports/schedules Dynamic reports/schedules Row Expansion Col Expansion N-Level Nested Expansions Cell based exceptions New evDRE function

9. MS Excel SQL Server Analysis Server evDRE Schema • Designed for Scalability DB Server Application Server Client evDRE Server: QueryOptimizer evDRE IIS/COM+

10. Goal Decrease OLAP interaction when sending Target Enhance # of Concurrent users from 250 to 1,000 with typical appset Enhance performance 30% Design Temporary staging area to hold data updates – eliminates deadlocks when AS is stressed Stored procedure to batch updates to the WB table – manages AS updates to increase capacity with high concurrency Still need to consider the size of the WB table for performance of query Send Governor

11. Security processing can be time consuming (>30 mins) per appset Factors affecting processing time Number of users Number of applications Mixing of Appset and App Security Data is not a factor Adding 1 user takes same time as 500 users Use multiple appsets to manage the number of users per appset if security too large. Security

12. What is Dynamic Security (DS)? Dynamical OLAP security by each logon user What is the Benefit of DS? Have more light OLAP DB Faster Security Processing time Support Incremental Processing Do not need to remove Cube roles when we do full process for secured dimension Advantage: 100-150 % increase in number of users (long term goal is 10,000) Incremental processing of users Dynamic Security

13. Optimize frequently Records in Real Time Storage (Write Back table) get heavy around 100K Optimize every night (or day) during close cycles Watch overlap of heavy processes Pay attention to business process deadlines (i.e. Actual, Budget, Forecast deadlines) Most people always try to send their data at the last minute. Avoid overlap of heavy data loads or restatements during heavy user usage. Define OLAP Slices Ex: per Time, Category Use the Server admin extension for maintenance Managing Production

14. Put calculations in schedules Calculations necessary for data input process are Excel formulas in the xls Move calculations to consolidation logics Calculations not necessary for data input are moved to the consolidation step Optimized base logic Move some dimension calculation to the default logic “Single schedule” optimization Define one schedule for each expansion type Workbook optimization Expansion filter optimized Eliminate the HQ duplication No data duplication Summary: Solution improvements • Reduce server load • Speed-up calculation feed-back • Speed-up send process • Reduce server load • Speed-up refresh process • Speed-up refresh process • Improved usability • Speed-up opening process • Speed-up expansion process • Speed-up opening process • Speed-up expansion process • Speed-up closing process • Eliminate data duplication

15. New schedule functionality EVDRE New input approach New schedule workbook data-caching New send process New refresh process Updated query algorithm New logon process New Security Summary: Solution improvements • Reduce server load • Speed-up refresh process • Reduce server load • Speed-up send process • Speed-up refresh process • Reduce server load • Speed-up logon process

16. Everest v4.2 requires: Microsoft SQL Server SP3 with hotfix 921 or higher Microsoft Analysis Services SP3 with hotfix 986 or higher Microsoft Analysis Services client (PTS) hotfix 986 or higher Server install will provide hotfix and installation for: Microsoft SQL Server SP3 with hotfix 921 Microsoft Analysis Services SP3 with hotfix 986 Microsoft Analysis Services client (PTS) hotfix 986 Reporting Service SP1 is required for Audit and Journals Pre-requisite Change

17. Minimum of Production and Development servers per customer High-end for production, low-end for development High end – quad, lots of ram, raid Low end – 1 cpu , lots of ram, no raid Single vs Multiple servers for production Single preferred If multiple needed, highest end server is database 2 quads are better than 4 duals. Keep Olap and SQL together Max scalability is a shared web server farm with multiple database servers Test Appsets on production server Copy of Production appset to track down problems in production environment ApShell to identify product vs environmental problems Hardware Configuration