Transcription
Oracle EssbaseDatabase Administrator's Guide for OracleEssbaseF17141-10April 2022
Oracle Essbase Database Administrator's Guide for Oracle Essbase,F17141-10Copyright 2019, 2022, Oracle and/or its affiliates.Primary Author: Essbase Information Development TeamThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government endusers are "commercial computer software" or "commercial computer software documentation" pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle, Java, and MySQL are registered trademarks of Oracle and/or its affiliates. Other names may betrademarks of their respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
Contents1Case Study: Designing a Single-Server, Multidimensional DatabaseProcess for Designing a Database1-1Case Study: The Beverage Company1-2Analyzing and Planning1-2Analyzing Source Data1-3Identifying User Requirements1-3Planning for Security in a Multiple User Environment1-4Creating Database Models1-4Identifying Analysis Objectives1-4Determining Dimensions and Members1-5Analyzing Database Design1-8Drafting Outlines1-13Dimension and Member Properties1-14Dimension Types1-14Member Storage Properties1-15Checklist for Dimension and Member Properties1-16Designing an Outline to Optimize Performance1-16Optimizing Query Performance1-16Optimizing Calculation Performance1-17Meeting the Needs of Both Calculation and Retrieval1-18Loading Test Data1-18Defining Calculations1-18Consolidation of Dimensions and Members1-19Effect of Position and Operator on Consolidation1-20Consolidation of Shared Members1-20Checklist for Consolidation1-21Tags and Operators on Example Measures Dimension1-21Accounts Dimension Calculations1-21Time Balance Properties1-22Variance Reporting1-23Formulas and Functions1-23Dynamic Calculations1-24Two-Pass Calculations1-25iii
Checklist for Calculations21-26Defining Reports1-27Verifying the Design1-27Understanding Multidimensional DatabasesOLAP and Multidimensional Databases2-1Dimensions and Members2-2Outline Hierarchies2-2Dimension and Member Relationships2-3Parents, Children, and Siblings2-4Descendants and Ancestors2-4Roots and Leaves2-5Generations and Levels2-5Generation and Level Names2-6Standard Dimensions and Attribute Dimensions2-6Sparse and Dense Dimensions2-6Selection of Dense and Sparse Dimensions2-7Dense-Sparse Configuration for Sample.Basic2-8Dense and Sparse Selection Scenarios2-9Data Storage2-14Data Values2-15Data Blocks and the Index System2-17Multiple Data Views2-21The Essbase Solution for Creating Optimized Databases32-22Creating Applications and DatabasesUnderstanding Applications and Databases3-1Understanding Database Artifacts3-1Understanding Database Outlines3-2Understanding Source Data3-2Understanding Rule Files for Data Load and Dimension Build3-2Understanding Calculation Scripts3-2Creating an Application and Database3-2Using Substitution Variables3-3Rules for Setting Substitution Variable Names and Values3-4Setting Substitution Variables3-4Deleting Substitution Variables3-5Updating Substitution Variables3-5Copying Substitution Variables3-5iv
Using Location Aliases45Creating and Changing Database OutlinesProcess for Creating Outlines4-1Creating and Editing Outlines4-2Locking and Unlocking Outlines4-2Setting the Dimension Storage Type4-2Positioning Dimensions and Members4-3Moving Dimensions and Members4-3Sorting Dimensions and Members4-3Verifying Outlines4-4Saving Outlines4-5Saving an Outline with Added Standard Dimensions4-5Saving an Outline with One or More Deleted Standard Dimensions4-5Creating Sub-Databases Using Deleted Members4-5Creating and Working With Duplicate Member OutlinesCreating Duplicate Member Names in Outlines5-1Restrictions for Duplicate Member Names and Aliases in Outlines5-2Syntax for Specifying Duplicate Member Names and Aliases5-2Using Fully Qualified Member Names63-55-3Calculating Databases5-3Hybrid Mode in Block Storage Databases5-3Qualifying Members by Differentiating Ancestor5-3Using Shortcut Qualified Member Names5-4Using Qualified Member Names in Unique Member Name Outlines5-4Setting Dimension and Member PropertiesSetting Dimension Types6-1Creating a Time Dimension6-1Creating an Accounts Dimension6-2Setting Time Balance Properties6-2Setting Skip Properties6-3Setting Variance Reporting Properties6-4Creating Attribute DimensionsUnderstanding Member Consolidation Operators6-46-4Setting Member Consolidation6-7Operation Results on #MISSING Values and Zero (0) Values6-8Determining How Members Store Data Values6-9v
Stored Members6-10Dynamic Calculation Members6-10Label Only Members6-10Shared Members6-10Understanding the Rules for Shared Members6-11Understanding Shared Member Retrieval During Drill-Down6-12Understanding Implied Sharing6-14Setting Aliases76-15Creating Aliases6-17Working With Aliases and Alias Tables6-22Setting Two-Pass Calculations6-28Creating Formulas6-29Naming Generations and Levels6-29Creating UDAs6-29Adding Comments to Dimensions and Members6-30Member IDs6-30Working with AttributesProcess for Creating Attributes7-1Understanding Attributes7-2Understanding Attribute Dimensions7-3Understanding Members of Attribute Dimensions7-4Understanding the Rules for Base and Attribute Dimensions and Members7-4Understanding the Rules for Attribute Dimension Association7-5Understanding the Rules for Attribute Member Association7-5Understanding Attribute Types7-5Comparing Attribute and Standard Dimensions7-6Solve Order and Attributes7-8Understanding Two-Pass Calculations on Attribute Dimensions7-9Comparing Attributes and UDAs7-9Designing Attribute Dimensions7-11Using Attribute Dimensions7-11Using Alternative Design Approaches7-12Optimizing Outline Performance7-13Building Attribute Dimensions7-13Setting Member Names in Attribute Dimensions7-14Setting Prefix and Suffix Formats for Member Names of Attribute Dimensions7-14Setting Boolean Attribute Member Names7-15Changing the Member Names in Date Attribute Dimensions7-15Setting Up Member Names Representing Ranges of Values7-15vi
Changing the Member Names of the Attribute Calculations DimensionCalculating Attribute Data87-17Understanding the Attribute Calculations Dimension7-18Understanding the Default Attribute Calculations Members7-19Viewing an Attribute Calculation Example7-20Accessing Attribute Calculations Members in Smart View7-20Optimizing Calculation and Retrieval Performance7-20Using Attributes in Calculation Formulas7-21Understanding Attribute Calculation and Shared Members7-22Differences Between Calculating Attribute Members and Non-Attribute (Stored andDynamic Calc) Members7-23Non-Aggregating Attributes7-23Submitting Data for Valid Attribute Combinations in the Grid7-23Suppressing Invalid Attribute Combinations in the Grid7-24Working with Typed MeasuresAbout Typed Measures8-1Working with Text Measures8-2Text Measures Workflow8-2Text List Objects and Text List Members8-5Working with Date Measures8-6Implementing Date Measures8-6Functions Supporting Date Measures8-7Performing Database Operations on Text and Date MeasuresLoading, Clearing, and Exporting Text and Date Measures8-78-8Consolidating Text and Date Measures8-10Retrieving Data With Text and Date Measures8-10Limitations of Text and Date Measures8-11Working with Format Strings97-178-11Implementing Format Strings8-12MDX Format Directive8-12Functions Supporting Format Strings8-12Limitations of Format Strings8-13Designing and Building Currency Conversion ApplicationsAbout Currency Conversion9-1About the Sample Currency Application9-1Structure of Currency Applications9-2Conversion Methods9-5vii
Building Currency Conversion Applications and Performing Conversions109-6Designing Partitioned ApplicationsUnderstanding Partitioning10-1Partition Types10-1Parts of a Partition10-3Data Sources and Data Targets10-4Attributes in Partitions10-5Version and Encoding Requirements10-6Partition Design Requirements10-6Benefits of Partitioning10-7Partitioning Strategies10-7Guidelines for Partitioning a Database10-7Guidelines for Partitioning Data10-8Security for Partitioned Databases10-9Replicated Partitions10-9Rules for Replicated Partitions10-9Advantages of Replicated Partitions10-10Disadvantages of Replicated Partitions10-11Performance Considerations for Replicated Partitions10-11Replicated Partitions and Port Usage10-12Transparent Partitions1110-12Rules for Transparent Partitions10-13Advantages of Transparent Partitions10-14Disadvantages of Transparent Partitions10-15Performance Considerations for Transparent Partitions10-16Calculating Transparent Partitions10-16Performance Considerations for Transparent Partition Calculations10-17Transparent Partitions and Member Formulas10-17Transparent Partitions and Port Usage10-18Creating and Maintaining PartitionsChoosing a Partition Type11-1Setting up a Partition Data Source11-1Setting the User Name and Password11-2Defining a Partition Area11-2Mapping Members in Partitions11-2Mapping Members with Different Names11-3Mapping Data Cubes with Extra Dimensions11-3viii
12Mapping Shared Members11-5Mapping Attributes Associated with Members11-5Creating Advanced Area-Specific Mappings11-6Validating Partitions11-7Populating or Updating Replicated Partitions11-8Viewing Partition Information11-9Partitioning and SSL11-9Troubleshooting Partitions11-9Understanding Data Loading and Dimension BuildingProcess for Data Loading and Dimension Building12-1Sources of Data12-1Items in a Source of DataValid Dimension Fields12-3Valid Member Fields12-3Valid Data Fields12-4Valid Delimiters12-5Valid Formatting Characters12-5Supported Source Data Types1312-112-6Load Rules12-6Security and Multiple-User Considerations12-8Work with Load RulesDefine Load Rules13-1General Process for Creating Rules13-1Generate Load Rules with Application Workbooks13-2Define Load Rules in Essbase Web Interface13-5Access and Edit Rules13-5Create New Dimension Build Rule13-9Create New Data Load Rule13-16Set File Delimiters13-20Set Dimension Build Field Type Information13-21Select a Build Method13-29Set Dimension Build Operational Instructions13-29Set Dimension Build Global Properties and General Source Properties13-34Use the Source Data to Work with Member Properties13-37Define Data Load Field Properties13-39Perform Operations on Records13-39Perform Operations on Fields13-46ix
Perform Operations on Data13-63Validate Rules13-69Define Rules that Query External Sources13-71Pull and Push Methods of Loading External Data into Essbase13-71Overview of SQL Properties in Rules13-73Connection Strings for SQL-Based Connectivity13-74Access External Data Using a Connection and Datasource13-75Access External Data Using an OCI Connection String13-76Access Data Using ODBC Connectivity13-76Configure the ODBC Source of Data (Linux)13-77Configure the ODBC Source of Data (Windows)13-81Customize the Rule File for an ODBC Source of Data13-88Special SQL Configuration Options13-91Access Data Using JDBC Connectivity13-95Stream from a Remote Database13-95Export Load Rules for Offline Editing1413-97Performing and Debugging Data Loads or Dimension BuildsPrerequisites for Data Loads and Dimension Builds14-1Performing Data Loads or Dimension Builds14-1Build Dimensions and Load Data by Streaming from a Remote Database14-2Stopping Data Loads or Dimension Builds14-4Tips for Loading Data and Building Dimensions14-5Performing Deferred-Restructure Dimension Builds14-5Determining Where to Load Data14-6Dealing with Missing Fields in a Data Source14-6Loading a Subset of Records from a Data Source14-7Debugging Data Loads and Dimension Builds14-7Verifying that Essbase Server Is Available14-7Verifying that the Data Source Is Available14-8Checking Error Logs14-8Resolving Problems with Data Loaded Incorrectly14-9Creating Rejection Criteria for End of File Markers14-9Understanding How Essbase Processes a Rules File14-10Understanding How Essbase Processes Missing or Invalid Fields During a Data Load14-11Missing Dimension or Member Fields14-11Unknown Member Fields14-11Invalid Data Fields14-12x
15Understanding Advanced Dimension Building ConceptsUnderstanding Build Methods15-1Using Generation References15-5Using Level References15-8Using Parent-Child References15-10Adding a List of New Members15-12Adding Members Based On String Matches15-12Adding Members as Siblings of the Lowest Level15-14Building Attribute Dimensions and Associating Attributes15-15Building Attribute Dimensions15-16Associating Attributes in a Dimension Build15-17Updating Attribute Associations15-18Removing Attribute Associations15-19Working with Numeric Ranges15-19Building Attribute Dimensions that Accommodate Ranges15-20Associating Base Dimension Members with Their Range Attributes15-21Ensuring the Validity of Associations15-23Reviewing the Rules for Building Attribute and Base DimensionsBuilding Shared Members by Using a Rules FileSharing Members at the Same Generation15-2415-2515-27Using Generation References to Create Same Generation Shared Members15-27Using Level References to Create Same Generation Shared Members15-28Using Parent-Child References to Create Same Generation Shared Members15-28Sharing Members at Different Generations15-29Sharing Non-Level 0 Members15-31Building Multiple Roll-Ups by Using Level References15-33Creating Shared Roll-Ups from Multiple Data Sources15-34Building Duplicate Member Outlines15-35Uniquely Identifying Members Through the Rules File15-35Building Qualified Member Names Through the Rule File15-3616Modeling Data in Private Scenarios17Calculating Essbase DatabasesAbout Database Calculation17-1Outline Calculation17-2Calculation Script Calculation17-3About Multidimensional Calculation Concepts17-3xi
18Setting the Default Calculation17-7Calculating Databases17-7Canceling Calculations17-7Parallel and Serial Calculation17-8Security Considerations17-8Developing Formulas for Block Storage DatabasesUsing Formulas and Formula Calculations18-1Understanding Formula Syntax18-2Operators18-3Dimension and Member Names18-4Constant Values18-4Nonconstant Values18-4Basic Equations18-5Checking Formula Syntax18-6Using Functions in Formulas18-6Conditional Tests18-9Examples of Conditional Tests18-10Mathematical Operations18-12Member Relationship Functions18-13Range Functions18-14Financial Functions18-14Member-Related Functions18-16Specifying Member Lists and Ranges18-16Generating Member Lists18-17Manipulating Member Names18-20Working with Member Combinations Across Dimensions18-20Forecasting Functions18-21Statistical Functions18-22Date and Time Function18-23Calculation Mode Function18-23Value-Related Functions18-23Using Interdependent Values18-24Calculating Variances or Percentage Variances Between Actual and Budget Values18-25Allocating Values18-26Using Substitution Variables in Formulas18-26Using Formulas on Partitions18-27Displaying Formulas18-27xii
1920Reviewing Examples of Formulas for Block Storage DatabasesCalculating Period-to-Date Values in an Accounts Dimension19-1Calculating Rolling Values19-2Calculating Monthly Asset Movements19-3Testing for #MISSING Values19-4Calculating an Attribute Formula19-4Defining Calculation OrderData Storage in Data Blocks20-1Member Calculation Order20-3Understanding the Effects of Member Relationships20-3Determining Member Consolidation20-4Ordering Dimensions in the Database Outline20-5Placing Formulas on Members in the Database Outline20-5Using the Calculation Operators *, /, and %20-5Avoiding Forward Calculation ReferencesBlock Calculation Order2120-620-8Data Block Renumbering20-10Cell Calculation Order20-10Cell Calculation Order: Example 120-11Cell Calculation Order: Example 220-12Cell Calculation Order: Example 320-13Cell Calculation Order: Example 420-14Cell Calculation Order for Formulas on a Dense Dimension20-16Calculation Passes20-16Calculation of Shared Members20-18Understanding Intelligent CalculationAbout Intelligent Calculation21-1Benefits of Intelligent Calculation21-1Intelligent Calculation and Data Block Status21-2Marking Blocks as Clean21-2Marking Blocks as Dirty21-2Maintaining Clean and Dirty Status21-3Limitations of Intelligent Calculation21-3Considerations for Essbase Intelligent Calculation on Oracle Exalytics In-MemoryMachine21-4Using Intelligent CalculationTurning Intelligent Calculation On and Off21-421-4xiii
Using Intelligent Calculation for a Default, Full Calculation21-4Calculating for the First Time21-4Recalculating21-5Using Intelligent Calculation for a Calculation Script, Partial Calculation21-5Using the SET CLEARUPDATESTATUS Command21-5Understanding SET CLEARUPDATESTATUS21-5Choosing a SET CLEARUPDATESTATUS Setting21-6Reviewing Examples That Use SET CLEARUPDATESTATUS21-6Example 1: CLEARUPDATESTATUS AFTER21-6Example 2: CLEARUPDATESTATUS ONLY21-7Example 3: CLEARUPDATESTATUS OFF21-7Calculating Data Blocks2221-7Calculating Dense Dimensions21-7Calculating Sparse Dimensions21-8Level 0 Effects21-8Upper-Level Effects21-9Unnecessary Calculation21-9Handling Concurrent Calculations21-9Understanding Multiple-Pass Calculations21-10Reviewing Examples and Solutions for Multiple-Pass Calculations21-10Example 1: Intelligent Calculation and Two-Pass21-10Example 2: SET CLEARUPDATESTATUS and FIX21-10Example 3: SET CLEARUPDATESTATUS and Two CALC DIM Commands21-11Example 4: Two Calculation Scripts21-12Understanding the Effects of Intelligent Calculation21-13Changing Formulas and Accounts Properties21-13Using Relationship and Financial Functions21-14Restructuring Databases21-14Copying and Clearing Data21-14Converting Currencies21-14Dynamically Calculating Data ValuesUnderstanding Dynamic Calculation22-1Understanding Dynamic Calc Members22-1Retrieving the Parent Value of Dynamically Calculated Child Values22-2Benefitting from Dynamic Calculation22-2Using Dynamic Calculation22-2Choosing Values to Calculate Dynamically22-3Dense Members and Dynamic Calculation22-3Sparse Members and Dynamic Calculation22-3xiv
Two-Pass Members and Dynamic Calculation22-4Parent-Child Relationships and Dynamic Calculation22-4Calculation Scripts and Dynamic Calculation22-4Formulas and Dynamically Calculated Members22-4Scripts and Dynamically Calculated Members22-5Dynamically Calculated Children22-5Understanding How Dynamic Calculation Changes Calculation OrderCalculation Order for Dynamic Calculation22-6Calculation Order for Dynamically Calculating Two-Pass Members22-7Calculation Order for Asymmetric Data22-7Reducing the Impact on Retrieval TimeDisplaying a Retrieval Factor2322-622-922-9Displaying a Summary of Dynamically Calculated Members22-10Increasing Retrieval Buffer Size22-10Using Dynamic Calculator Caches22-10Reviewing Dynamic Calculator Cache Usage22-11Using Dynamic Calculations with Standard Procedures22-11Creating Dynamic Calc Members22-12Restructuring Databases22-12Dynamically Calculating Data in Partitions22-13Solve Order in Hybrid Mode22-13Calculating Time Series DataCalculating First, Last, and Average Values23-1Specifying Accounts and Time Dimensions23-2Reporting the Last Value for Each Time Period23-2Reporting the First Value for Each Time Period23-3Reporting the Average Value for Each Time Period23-4Skipping #MISSING and Zero Values23-4Considering the Effects of First, Last, and Average Tags23-5Calculating Period-to-Date Values Using Dynamic Time Series MembersUsing Dynamic Time Series Members23-523-5Enabling and Disabling Dynamic Time Series Members23-7Specifying Alias Names for Dynamic Time Series Members23-7Applying Predefined Generation Names to Dynamic Time Series Members23-7Retrieving Period-to-Date Values23-8Using Dynamic Time Series Members in Transparent Partitions23-9xv
24Developing Calculation Scripts for Block Storage DatabasesUsing Calculation Scripts24-1Understanding Calculation Script Syntax24-2Adding Comments to Calculation Scripts24-5Checking Syntax24-5Using Calculation Commands24-6Calculating the Database Outline24-6Controlling the Flow of Calculations24-7Declaring Data Variables24-7Specifying Global Settings for a Database Calculation24-8Using Formulas in Calculation Scripts24-9Basic Equations24-10Conditional Equations24-10Interdependent Formulas24-11Using a Calculation Script to Control Intelligent Calculation24-11Grouping Formulas and Calculations24-12Calculating a Series of Member Formulas24-12Calculating a Series of Dimensions24-12Using Substitution, Runtime Substitution, and Environment Variables in Calculation Scripts24-13Using Substitution Variables in Calculation Scripts24-13Using Runtime Substitution Variables in Calculation Scripts Run in Essbase24-13Specifying Data Type and Input Limit for Runtime Substitution Variables inCalculation Scripts Run in Essbase24-16Logging Runtime Substitution Variables24-16Using Runtime Substitution Variables in Calculation Scripts Run in Smart View24-17Example: Runtime Substitution Variable Set to POV24-18XML Tag Reference—Calculation Scripts with Runtime Substitution Variables forSmart View24-19Clearing and Copying Data24-20Clearing Data24-20Copying Data24-21Calculating a Subset of a Database24-22Calculating Lists of Members24-22Using the FIX Command24-22Using the Exclude Command24-24Enabling Calculations on Potential Blocks24-25Using DATACOPY to Copy Existing Blocks24-25Using SET CREATENONMISSINGBLK to Calculate All Potential Blocks24-26Using Calculation Scripts on Partitions24-27Writing Calculation Scripts for Partitions24-27Controlling Calculation Order for Partitions24-27xvi
Saving, Executing, and Copying Calculation ScriptsSaving Calculation Scripts24-28Executing Calculation Scripts24-29Copying Calculation Scripts24-29Checking Calculation Results252624-29Reviewing Examples of Calculation Scripts for Block Storage DatabasesAbout These Calculation Script Examples25-1Calculating Variance25-1Calculating Database Subsets25-2Loading New Budget Values25-3Calculating Product Share and Market Share Values25-4Allocating Costs Across Products25-5Allocating Values within a Dimension25-6Allocating Values Across Multiple Dimensions25-8Goal-Seeking Using the LOOP Command25-10Forecasting Future Values25-13Using Parallel CalculationAbout Parallel Calculation26-1Using CALCPARALLEL Parallel Calculation26-1Essbase Analysis of Feasibility26-1CALCPARALLEL Parallel Calculation Guidelines26-2Relationship Between CALCPARALLEL Parallel Calculation and Other EssbaseFeatures26-3Retrieval Performance26-3Formula Limitations26-3Calculator Cache26-3Transparent Partition Limitations26-4Checking Current CALCPARALLEL Settings26-4Enabling CALCPARALLEL Parallel Calculation26-4Identifying Additional Tasks for Parallel Calculation26-5Tuning CALCPARALLEL with Log Messages26-6Monitoring CALCPARALLEL Parallel Calculation26-6Using FIXPARALLEL Parallel Calculation2724-2826-8Developing Custom-Defined Calculation FunctionsProcess for Creating Custom-Defined Functions27-1Custom-Defined Function Requirements27-1xvii
2829Creating and Compiling a Java Class27-3Installing Java Classes on Essbase Server27-4Registering Custom-Defined Functions27-4Using Registered Custom-Defined Functions27-5Updating Custom-Defined Functions27-6Viewing Custom-Defined Functions27-7Removing Custom-Defined Functions27-7Copying Custom-Defined Functions27-8Performance Considerations for Custom-Defined Functions27-8Developing Custom-Defined Calculation MacrosUnderstanding Custom-Defined Macros28-1Naming Custom-Defined Macros28-1Creating Custom-Defined Macros28-2Using Custom-Defined Macros28-2Viewing Custom-Defined Macros28-3Updating Custom-Defined Macros28-3Copying Custom-Defined Macros28-3Removing Custom-Defined Macros28-3Refreshing the Catalog of Custom-Defined Macros28-4Writing MDX QueriesAbout Writing MDX Queries29-1Understanding Elements of a Query29-1Introduction to Sets and Tuples29-2Exercise: Running Your First Query29-3Rules for Specifying Sets29-4Introduction to Axis Specifications29-5Exercise: Running A Two-Axis Query29-5Exercise: Querying Multiple Dimensions on a Single Axis29-6Cube Specification29-7Using Functions to Build Sets29-8Exercise: Using the MemberRange Function29-8Exercise: Using the CrossJoin Function29-9Exercise: Using the Children Function29-10Working with Levels and Generations29-11Exercise: Using the Members Function29-12Using a Slicer Axis to Set Query Point-of-View29-13Exercise: Limiting the Results with a Slicer Axis29-13xviii
Common Relationship Functions29-14Performing Set Operations29-14Exercise: Using the Intersect Function29-15Exercise: Using the Union Function29-16Creating and Using Named Sets and Calculated MembersCalculated MembersExercise: Creating a Calculated MemberNamed Sets303129-1729-1729-1829-20Using Iterative Functions29-20Working with Missing Data29-21Using Substitution Variables in MDX Queries29-22Querying for Properties29-23Querying for Member Properties29-23The Value Type of Properties29-24NULL Property Values29-25Exporting DataExporting Data Using MaxL30-1Exporting Text Data Using Calculation Scripts30-1Controlling Access to Database Cells Using Security FiltersAbout Security Filters31-1Defining Permissions Using Security Filters31-1Creating Filters31-2Filtering Members Versus Filtering Member Combinations31-3Filtering Members Separately31-4Filtering Member Combinations31-4Filtering Using Substitution Variables31-5Filtering with Attribute Functions31-5Metadata Filtering31-6Dynamic Filtering31-6Managing Filters31-6Assigning Filters31-6Overlapping Filter Definitions31-8Overlapping Metadata Filter Definitions31-9Overlapping Access Definitions31-9xix
32Using MaxL Data Definition Language33Optimizing Database RestructuringDatabase Restructuring33-1Implicit Restructures33-1Explicit Restructures33-2Conditions Affecting Database Restructuring33-2Restructuring Requires a Temporary Increase in the Index and Data Cache Sizes33-3Optimization of Restructure Operations33-3Actions That Improve Performance33-3Parallel Restructuring33-4Options for Saving a Modified Outline33-4Outline Change Quick Reference343533-4Optimizing Data LoadsUnderstanding Data Loads34-1Grouping Sparse Member Combinations34-2Making the Data Source as Small as Possible34-3Making Source Fields as Small as Possible34-4Positioning Data in the Same Order as the Outline34-5Loading from Essbase Server34-5Using Parallel Data Load34-5Understanding Parallel Data Load34-5Enabling Parallel Data Load With Multiple Files34-6Optimizing CalculationsDesigning for Calculation Performance35-1Block Size and Block Density35-1Order of Sparse Dimensions35-2Incremental Data Loading35-2Database Outlines with Multiple Flat Dimensions35-2Formulas and Calculation Scripts35-3Monitoring and Tracing Calculations35-3SET MSG SUMMARY and SET MSG DETAIL35-3SET NOTICE35-3Using Simulated Calculations to Estimate Calculation
The Essbase Solution for Creating Optimized Databases2-22. Creating Applications and Databases. Understanding Applications and Databases3-1. Understanding Database Artifacts3-1. Understanding Database Outlines3-2. Understanding Source Data3-2. Understanding Rule Files for Data Load and Dimension Build3-2. Understanding Calculation Scripts3-2
