Essbase XTD Platform Services 6.5.1 Release Update

1. Essbase XTD Platform Services6.5.1 Release Update Karen Zubetz Essbase Domain Leader September 19, 2002 1

2. Agenda • What is Essbase XTD? • 6.5.1 Platform Services Release Update • Analytic Services • Optimizations / Performance • Stability / Recoverability • Calculator/Interoperability/Miscellaneous • MaxL / API • Other Platform Services • Integration Services • Deployment Services • Administration Services • What’s Next for Essbase XTD?

3. Analysis Studio Open Application Foundation Essbase XTD: BI Tools, Technologies, and Services Essbase XTD Business Performance Management Application Framework Web Broadcast Performance Management Frameworks Developer Workbench 3rd Party Integration Essbase XTD Platform Services Interoperability Services Query & Reporting Services Analytic Services Deployment Services Integration Services Administrative Services

4. Performance Management Accelerators Data Mappings Application Models Business Metrics Reports/Views Analysis Studio Web Browsers Spreadsheets HTML PDFs Dashboards Developer Workbench IDE/SDK Graphical Tools Reusable Templates Object Libraries Web Broadcast Broad internal and external information distribution over the web 3rd Party Integration Tailored Applications ETL Visualization Data Mining Open Application Foundation J2EE, .NET, XML for Analysis, SOAP, WAP Essbase XTD Application Framework

5. Interoperability Services Metadata Management, Shared Security, Process/Workflow Management * Analytic Services OLAP, Business Rules, Stored Procedures, Database Triggers, Data Mining Integration Services ERP/CRM systems, Data Warehouses, SQL, Flat Files, XML/XMI Deployment Services Connection Pooling, Database Clustering Load Balancing, Fail-Over • Administrative Services • Schema Creation, Calculation Definitions, • Data Loads and Updates, User Management • Maintenance Automation, Monitoring Consoles Essbase XTD Platform Services Query & Reporting Services Production and Operational Reports User Managed Reports Exception Reporting Scheduled Reporting *Not currently available

6. Analytic Services 6.5.1: Positioning • Major 6.5 Themes: Parallel Calculation/Kernel, Hybrid Analysis • Essbase 6.5.1 is primarily a maintenance release. • Most enhancements are to optimize existing functionality. • Any feature that is considered new is off by default and needs to be explicitly enabled.

7. Analytic Services 6.5.1: Optimization / Performance • Intelligent Calc Optimization • Optimize Calc Range Functions • DataCopy Set Command to prevent #Missing Blocks • New Compression Algorithm (ZLIB) • Proactive handling of Fragmentation • Windows 4GT RAM Option Support

8. Intelligent Calc Optimizations • Current Implementation requires blocks to be returned to calc layer. • Kernel has been enhanced to determine cleanliness of block • Only returns to calculator blocks that need to be calculated

9. Intelligent Calc OptimizationExample:

10. Optimize Calc Range Functions • Typically would receive an “increase Calc Lock Block setting” message in the log. • Blocks no longer need to all reside in cache for: • @AVGRANGE • @SUMRANGE • @MAXRANGE • @MINRANGE • Essbase will let you know it is rewriting the formula when all blocks cannot be addressed:

11. DataCopy Set Command • Current DataCopy on a Dense Dimension may create #Missing Blocks • No change to existing behavior • Using SET COPYMISSINGBLOCK OFF before statement will prevent the #Missing block from being created. • Additional Messaging provided in log:

12. New Compression Algorithm • New Compression Algorithm Provided – ZLIB • Bitmap still the default • Performs best under Direct I/O • Direct I/O moved to a database setting in 6.5 • Calc performance on par vs. Bitmap • Data Loading about 10% slower vs. Bitmap • Set via MaxL Alter database appname.dbname set compression zlib; • Set via API EssSetDatabaseState()

13. New Compression Algorithm • To estimate the storage savings you may obtain with ZLIB, create a small database with a small sampling of real data, change the compression setting, restart Essbase and note the difference in storage. • It is difficult to estimate savings in any other way, because ZLIB compression is extremely dependent on the actual pattern of bytes in the data block. • Unlike other compression methods, the storage space saved has little or no relationship to the number of missing cells or the number of contiguous cells of equal value.

14. Proactive Fragmentation Management

15. Proactive Fragmentation Management

16. Details – Windows 4GT Ram Option • Essbase Release 6.5.1 supports the Microsoft 4 GB RAM Tuning (4GT) feature. • Enables users with large dbs to take advantage of additional memory space • Increases amount Windows gives to applications from 2 Gig to 3 Gig • Available only on NT 4.0 Enterprise Edition and 2000 Advanced Server • The option must be placed in boot.ini file • Instructions documented, and customers are referred to microsoft.com for more information • Enabling 4GT on Essbase installations configured for buffered I/O is not recommended. Use Direct I/O only.

17. Analytic Services 6.5.1: Stability/ Recoverability • Security File Backup Enhancements • Better Exception Handling on Unix • Invalid Block Header Identification • Invalid Block Header Correction • Memory Manager

18. Security File Backup Enhancements • Keeps Essbase.bak up to date with Essbase.sec at configurable intervals • Compares .sec to .bak – if different update .bak file • Does not only occur when Essbase server is restarted • Prevention of copying corrupted .sec file over .bak file in the event of a server crash • Ensure you have copy of .bak file before restarting Essbase server. • Commands available to immediately force backup • Via EssCommand UpdateBakFile • Via MaxL Alter system synch security_backup; • Via API EssUpdateBakFile()

19. Better Exception Handling (UNIX) • On Unix platforms more information is provided for troubleshooting • In addition to XCP file, a CORE file is also created. • These should be forwarded to tech support for development to troubleshoot. • Location of the CORE file is specified in the log file. • Server (Agent) exceptions: $ARBORPATH/Essbase.timestamp/core. • Application (Server Process) exceptions: $ARBORPATH/App/appname/dbname/ESSSVR.timestamp/core.

20. Invalid Block Header Identification (IBH) • Essbase now helps you assess the severity of invalid block header errors (IBH), and provides some tools to repair the corruption indicated by the presence of IBHs. • There are two different sources of IBH errors: • Type mismatch, where the Essbase version number in a data block doesn't match the version in the index entry. • Block key mismatch, where the block key does not match in locations where it should. • The IBHFIXTHRESHOLD (in essbase.cfg) setting controls how many IBH messages are returned to the client or server log, relative to the number of level 0 blocks written to disk. IBHFIXTHRESHOLD [appName | xxxx] [dbName | xxxx] percentage

21. Invalid Block Header Identification (IBH) • Message in Log or client will signify IBH • Ability to spool which combinations have invalid block headers to a file • Available via MaxL alter database DBS-NAME validate data to local logfile 'FILE-NAME'; • Available via API EssLocateIBH()

22. Invalid Block Header Correction • Allows for correction of corrupted blocks • Only option is REMOVE • However, if corrupted blocks are parent blocks, this allows a simple recalc of the DB or portion thereof. • Gives recoverability options beyond “rebuild your DB” • Via MaxL alter database DBS-NAME repair invalid_block_headers; • API • EssGetIBH() • EssFixIBH()

23. Memory Manager • Provide a centralized memory manager for Essbase. • Ability to proactively notify of out-of-memory issues. • Allows the setting of the “maximum” (threshold) amount of memory for the ESSSVR process. • When memory grows to 90% of the threshold, messages are spooled to the log. • OFF by Default • Set via config.mem file in $ARBORPATH/bin directory.

24. Memory Manager (cont) • Options for config.mem file: Format: MemoryLimit n M|G where M = Meg, G = Gig For example: MemoryLimit 100M sets the memory limit at 100 Megabytes. When it approaches 90% (90 Meg), you will see the following in the log:

25. Analytic Services 6.5.1: Calculator/Interoperability/Misc • @MOVSUM Calc Function • @XRANGE Calc Function • Siteminder Support (External Authentication) • MS Active Directory Support (External Authentication) • Port Usage Statistics • Drill Through Filter Persistence (EIS / SS Addin) • Query Logging Statistics • Re-support of Excel ’97 for Essbase Spreadsheet Add-In

26. @MOVSUM Function • Format: @MOVSUM((mbrName [, n [, rangeList]]) • Consistent with other “moving” functions. • The @MOVSUM function calculates a trailing, rather than a centered, sum. • Default for n is 3 • While calculating the moving sum, @MOVSUM skips #MISSING values. For example, if one value out of three is #MISSING, Essbase adds the remaining two values.

27. @MOVSUM – Usage Example @MOVSUM(Sales, 3);

28. @XRANGE Function • Allows Cross-Dimensional reference to time in specific functions • Supported in: @AVGRANGE, @SUMRANGE, @MINRANGE, @MINSRANGE, @MAXRANGE, @MAXSRANGE, @STDDEVRANGE, @MOVAVG, @MOVMIN, @MOVMAX, @MOVMED, @SPLINE, @MOVSUM

29. @XRANGE - Usage Example #1 @SUMRANGE (Sales, @XRANGE(Actual->Feb, Budget->Mar));

30. @XRANGE - Usage Example #2 @SUMRANGE (Sales, @XRANGE(Feb->Actual, Mar->Budget)); Order of dimensions in @XRANGE function affects results!

31. Siteminder / Active Directory Support for External Authentication • External Authentication introduced in Essbase 6.2 • Current support is LDAP only (i.e. NTLM) • Separate library module – NOT PART OF BASE CODE • .DLL files that get referenced in the essbase.cfg file • No impact to users not using external authentication. • Authenticate user passwords in Siteminder or MS Active Directory Services vs. security file. • Allows for single passwords for users in multi system environments. • Please refer to Addendum docs for essbase.cfg settings for MSAD or Netegrity SiteMinder.

32. Port Usage Statistics • Spools messages to the Agent Log • Allows admins to monitor their concurrent port usage • Time Interval set in the essbase.cfg file via PORTUSAGELOGINTERVAL X • X indicates the number of minutes, default is 5 • Off by default

33. SS Add-in Feature Customized Drill Thru Filters saved in EIS Catalog for Collaboration Drill Through Filter Persistence

34. Query Logging • Allows admin to track what members, levels or generations are being queried in Essbase • Extremely flexible • Useful in determining what dimension levels are candidate for Hybrid Analysis • Options for logging specified in a separate database-specific config file • (e.g. basic.cfg located in …Sample\Basic folder) • Off by default. Changes to config require restart of database. • Expect performance degradation when Query Logging is on

35. Query Logging – Example for basic.cfg QueryLog [Product] QueryLog Level 1 QueryLog [Market] QueryLog [Scenario] QueryLog [Measures] QueryLog [Year] QueryLog LogFormat Tuple QueryLog ON Log will confirm Query Logging is active. [Mon Jul 15 17:56:55 2002]Local/Sample///Info(1024030) Query logging enabled.

36. Query Logging OuputFile View First Query Second Query (Drill on Year)

37. Query Logging Output Browser View

38. Features - MaxL • Security File Backup alter system sync security_backup; • ZLIB Database Compression alter database DBS-NAME set compression zlib; • Reset DB Info (modified MaxL command) alter database dbs-name reset data | all; • Invalid Block Header Identification alter database DBS-NAME validate data to local logfile 'FILE-NAME'; • Invalid Block Header Correction (Remove) alter database DBS-NAME repair invalid_block_headers;

39. New C APIs EssResetDatabase() EssLocateIBH() EssGetIBH() EssFixIBH() EssGetServerLocaleString() EssUpdateBakFile() New VB APIs EsbListRequests() EsbKillRequest() EsbSetSpanRelationalSource() EsbClrSpanRelationalSource() Features - APIs

40. Remaining XTD Platform Services:6.5.1 Update • Essbase Integration Services / Hybrid Analysis • Essbase Deployment Services • Essbase Administration Services

41. Major EIS 6.5 Themes • Multiple Data Sources • Hybrid Analysis Framework

42. MultiSource Connectivity

43. Hybrid Analysis

44. Hybrid Analysis vs SQL Drill Thru • SQL drill thru acts as a link from a cube to relational to bring back a list or report. Hybrid Analysis actually partitions the the physical storage of the cube between relational and multidimensional databases, providing seamless direct ad-hoc access to relational through the Essbase Analytic Services Essbase Analytic Services Multiple Dynamic SQL Queries MDDB RDBMS RDBMS Single Predefined SQL Query

45. Essbase Integration Services 6.5.1 • Virtual tables • Drill-through to secondary data source • Drill-through on all levels of a recursive hierarchy • Recursive support for attribute dimensions • Teradata FastExport • Persisting filters • Replace zeros with #missing • Save As for model • Reconnect

46. Virtual Tables • User defined tables • Virtual tables behave like an RDBMS view • Used anywhere a regular RDBMS table or view would be • No need to modify database schema • Can be used to override member load SQL • SQL generated in the form • SELECT col1, col2, … coln • FROM (virtual table sql statement) • E.g. • SELECT aa.stateid • FROM (select distinct a.stateid from tbc.market a, tbc.sales b • where a.stateid = b.stateid) aa

47. Essbase Deployment Services 6.5.1 • Server-based architecture ideal for Internet Deployment [HTTP(S)] • Scales to support large numbers of users through middle-tier • High-availability required for mission-critical analytics: load balancing, fail-over • Built-in support for connection pooling, Java API for Essbase • Take databases off-line for maintenance without impacting service • Supports all major J2EE deployment platforms (JOLAP) • Supports major XML standards (XML for Analysis) • Platform for Web Services: Essbase Spreadsheet Services

48. Essbase Server Essbase Server Essbase Server Essbase Deployment Services: Manageability and Reliability 1 Cluster of Servers • Connection Pooling • Cube clustering allows load balancing of user requests • Administrator defines properties for cluster at runtime • EDS will detect that an Essbase operation was unsuccessful and attempt to correct the situation. • A cube can be taken off-line from the cluster for maintenance and it will be transparent to the user. 2 Deployment Services 3

49. Essbase XTD Spreadsheet Services 6.5.1 • New Web-based Spreadsheet Services component • Encrypted Communication on the Internet: HTTP/HTTPS protocol • Firewall-friendly deployment • Light footprint on client • Eliminates need for IT install of add-in .dlls • Support for Spreadsheet Toolkit Macros

50. Essbase XTD Spreadsheet Services 6.5.1