A member formula in Oracle EPM is a calculation expression attached to a specific dimension member that defines how that member’s value is derived — from other members in the same dimension or across dimensions — rather than stored as an input. Member formulas are embedded directly in the dimension member’s metadata, making them an intrinsic part of the application’s structure rather than a separately managed calculation artefact. They are written in Essbase MDX (for reporting-context calculations) or in Essbase Calc Script syntax (for BSO member calculations), and they execute automatically during database calculations or at query time, depending on the cube type and the member’s data storage setting.
How Member Formulas Work
In a BSO cube, a member formula is evaluated during the calculation pass that processes the dimension containing the formula member. If the Account dimension contains a formula member for “Gross Margin” defined as “Net Revenue” – “Cost of Goods Sold”, this formula is executed during the Account dimension’s calculation sequence. The result is stored in the database at every intersection where both “Net Revenue” and “Cost of Goods Sold” have values — making it available as a stored value for subsequent calculations that reference “Gross Margin.” In an ASO cube, member formulas execute at query time rather than being stored — the value is computed dynamically when a report requests it, which means no calculation is required to refresh the value when inputs change, but the computation overhead occurs at every query.
The data storage setting on a formula member controls this behaviour. A member with Dynamic Calc storage type computes its value at query time from its formula; a member with Stored storage type stores its computed value after calculation runs. Dynamic Calc is generally appropriate for complex ratios and percentage measures that should always reflect current input data; Stored is appropriate for calculated values that are referenced by other formulas and must be pre-computed to be available during the calculation pass.
Configuration and Design Considerations
The choice between a member formula and a business rule for the same calculation is a design trade-off. Member formulas are simpler to maintain for straightforward arithmetic on a specific member; they execute automatically and do not require a separate rule run. Business rules offer more control over execution sequence, cross-dimensional logic, and conditional behaviour. A member formula that references another formula member must ensure the referenced member is calculated first — a sequencing constraint that is not explicit in the formula syntax and must be managed through the dimension’s calculation order or through the formula’s own reference structure.
What Goes Wrong in Practice
The specific failure that most frequently produces incorrect formula member values in production BSO databases is a circular reference that is not detected at design time. When Formula Member A references Formula Member B, and Formula Member B references a member that rolls up through Formula Member A in the aggregation hierarchy, the calculation engine either produces an error or resolves the circular dependency by using a prior-state value — depending on the calculation settings. The resulting values are wrong but not obviously wrong; the member displays a value that is slightly different from the expected result, and the discrepancy is typically discovered during board pack review rather than during testing. Systematic formula dependency mapping — documenting which formula members reference which other members and in what calculation sequence — is the only reliable way to detect circular dependencies before they reach production.
How Loop Wise Solutions Handles This
We produce a member formula dependency map as part of every EPM detailed design — listing every formula member, its formula expression, the members it references, and the calculation sequence in which it must execute. This map is reviewed in a structured technical walkthrough before any formulas are configured in the application, and it is updated whenever formulas are added or modified during the build phase. The map also serves as the primary troubleshooting reference when unexpected formula values are reported post-go-live.
