Оглавление учебного курса по CalcScript:

Subj:

Introduction

Learning Essbase
Syntax
Concepts
Usage
Techniques
Optimization
Creativity
Class Overview
Reviewing calculation architecture
Building calc scripts
Using calc scripts
Exercise 1-1 – Hook, Line and Sinker Company
Building an OLAP system
Unit Summary

2 Calculation Architecture

Types of Calculation
Outline calculations
Calc scripts
Outline Calculations
Default Calculation
Default Calculation Characteristics
Minimizes Physical Passes Through the Database
Allows Users with Access to Specific Calc Scripts to Run the Default Calc
Calc Script Calculations
Contents of calc scripts
Reasons for calc scripts
Functions and commands that work only in scripts
Calculation Order
Data storage
Dimension types
Dense and Sparse Dimensions
Dense dimensions
Sparse dimensions
Dimension tags
Block and Index Structure
Generations and Levels
Block Types
Input blocks
Non-input blocks
Level zero blocks
Upper-level blocks
Calculation Order
Dense Calculations
Sparse Calculations
Calculation Order: Dense Dimensions
Calculation Order: Sparse Dimensions
Calculation Order: Dynamic Calc Versus Batch
Calculation order exceptions
Two-Pass Calculation
Calculation passes
Exercise 2-1: Review Class Files
Unit Summary

3 Calculation Script Environment

Introducing Calc Scripts
Components of calc scripts
Calc Script Editor and online Help
Commands and functions
Using Calc Script Editor
Calc Script Editor
Associating an Essbase Outline
Inserting Functions (Using the Function Button)
Checking the Syntax and Saving and Running the Calc Script
Using Essbase Documentation
Inserting Functions (From on-line Help)
Exercise 3-1: Explore Calc Script Editor and Online Help
Reviewing Essbase Data Using Spreadsheet Add-in
Exercise Procedures
Exercise 3-2: Running Calc Scripts
Calc Script Basics
Member Calculations
Arithmetic Operators
Adding In-line Documentation
Introduction to Calc Script Commands and Functions
Calc Script Command Categories
Essbase Functions Categories
Exercise 3-3: Member Calculations and Mathematical Operators
Unit Summary

4 Calculation Script Commands

Calc Member Blocks
Focusing Calculations
Focusing Calculations Using Fix…Endfix
Focusing Calculations Using Conditional Statements
Focusing Calculations Using the Cross-Dimensional Operator
Calc Script Commands
Summary of Calc Script Commands
Detailed Description of Calc Script Commands
&
AGG
ARRAY
CALC
CALC ALL
CALC DIM
CALC (others)
CCONV
CLEARBLOCK
CLEARDATA
DATACOPY
FIX / ENDFIX
IF / ELSE / ELSEIF / ENDIF
LOOP/ENDLOOP
SET
SET AGGMISSG
SET CACHE
SET CALCHASHTBL
SET CALCPARALLEL
SET CALCTASKDIMS
SET CLEARUPDATESTATUS
SET FRMLBOTTOMUP
SET LOCKBLOCK
Math Functions
Transforming Values with Math Functions
Math Function Parameters
Summary of Math Functions
Detailed Description of Math Functions
@ABS
@AVG
@EXP
@FACTORIAL
@INT
@LN, @LOG, @LOG10
@MAX, @MAXS, @MIN and @MINS
@MOD
@POWER
@REMAINDER
@ROUND
@SUM
@TRUNCATE
@VAR
@VARPER
Exercise 5-1: Using Math Functions
Unit Summary

6 Boolean Functions

Testing for Values Using Boolean Functions
Boolean Function Parameters
Summary of Boolean Functions
@ISACCTYPE
@ISANCEST, @ISANCEST
@ISCHILD, @ISICHILD
@ISDESC, @ISIDESC
@ISGEN
@ISLEV
@ISMBR
@ISPARENT, @ISIPARENT
@ISSAMEGEN
@ISSAMELEV
@ISSIBLING, @ISISIBLING
@ISUDA
Using Logical Connectors with Boolean Functions
Exercise 6-1: Using Boolean Functions
Unit Summary

7 Member Set Functions

Generating Lists Using Member set Functions
Member Set Function Parameters
Summary of Member Set Functions
@ALLANCESTORS
@ANCEST
@ANCESTORS
@ATTRIBUTE
@CHILDREN
@CURRMBR
@DESCENDANTS
@GENMBRS
@IALLANCESTORS
@IANCESTORS
@ICHILDREN
@IDESCENDANTS
@ILSIBLINGS
@IRDESCENDANTS
@IRSIBLINGS
@ISIBLINGS
@LEVMBRS
@LIST
@LSIBLINGS
@MATCH
@MEMBER
@MERGE
@PARENT
@RANGE
@RDESCENDANTS
@RELATIVE
@REMOVE
@RSIBLINGS
@SIBLINGS
@UDA
@WITHATTR
@XRANGE
Exercise 7-1: Applying Member Set Functions
Unit Summary

8 Relationship Functions

Using Relationship Functions
Relationship Function Parameters
Summary of Relationship Functions
@ANCESTVAL
@ATTRIBUTEBVAL
@ATTRIBUTESVAL
@ATTRIBUTEVAL
@CURGEN
@CURLEV
@GEN
@LEV
@MDANCESTVAL
@MDPARENTVAL
@PARENTVAL
@SANCESTVAL
@SPARENTVAL
@XREF
How @XREF works
Defining a Location Alias
Exercise 8-1: Applying Relationship Function (@ANCESTVAL)
Exercise 8-2: Applying Relationship Function (@CHILDREN)
Exercise 8-3: Applying Relationship Function (@XREF)
Unit Summary

9 Range and Financial Functions

Using Range and Financial Functions
Range and Financial Function Parameters
Summary of Range and Financial Functions
@ACCUM
@AVGRANGE
@COMPOUND
@COMPOUNDGROWTH
@CURRMBRRANGE
@DECLINE
@DISCOUNT
@GROWTH
@INTEREST
@IRR
@MAXRANGE
@MAXSRANGE
@MDSHIFT
@MINRANGE
@MINSRANGE
@NEXT
@NEXTS
@NPV
@PRIOR
@PRIORS
@PTD
@SHIFT
@SLN
@SUMRANGE
@SYD
Exercise 9-1: Applying Range Functions (@PRIOR)
Exercise 9-2: Applying Financial Functions (@IRR)
Unit Summary

10 Allocation Functions

Using Allocation Functions
Common Parameters
Summary of Allocation Functions
@ALLOCATE
@MDALLOCATE
Exercise 10-1: Applying Allocation Functions
Unit Summary

11 Forecasting Functions

Using Forecasting Functions
Forecasting Function Parameters
Summary of Forecasting Functions
@MOVAVG
@MOVMAX
@MOVMED
@MOVMIN
@MOVSUM
@MOVSUMX
@SPLINE
@TREND
Exercise 11-1: Applying Forecasting Functions
Unit Summary

12 Statistical Functions

Generating Lists Using Statistical Functions
Statistical Function Parameters
Summary of Statistical Functions
@CORRELATION
@COUNT
@MEDIAN
@MODE
@RANK
@STDEV
@STDEVP
@STDEVRANGE
@VARIANCE
@VARIANCEP
Exercise 12-1: Applying Statistical Functions
Unit Summary

13 Date and Time Functions

Using Date and Time Functions
@TODATE
Exercise 13-1: Applying the Date & Time Function

14 Miscellaneous Functions

Using Miscellaneous Functions
Summary of Miscellaneous Functions
@CALCMODE
@CONCATENATE
@NAME
@SUBSTRING

15 Tips, Tricks and Techniques

Calc Script Design Considerations
IF or FIX
Dense calculations before sparse calculations
Two-pass calculations
Intelligent Calcs
Blocks on equations
Approaching Calc Scripts Solutions
Business problem
Affected data
Rollup considerations
Individual calculations
Communication
IF vs FIX
Exercise 15-1: Isolating Data Using IF or FIX
Exercise 15-1b: Isolating Data Using IF or FIX
Simpler is Usually Better
Exercise 15-2: Allocations
Exercise 15-3: Product Share Analysis
Unit Summary

A Suggested Answers

Possible Solutions

B Essbase Outlines

Sample – Basic
Calc – HLandS1
Calc – HLandS2
Calc – COGS