Thursday Oct 17, 2013

Essbase - FormatString

A look at the documentation for "Typed Measures" shows:
"Using format strings, you can format the values (cell contents) of Essbase database members in numeric type measures so that they appear, for query purposes, as text, dates, or other types of predefined values. The resultant display value is the cell’s formatted value (FORMATTED_VALUE property in MDX). The underlying real value is numeric, and this value is unaffected by the associated formatted value."

To actually switch ON the use of typed measures in general, you need to navigate to the outline properties:
  1. open outline
  2. select properties
  3. change "Typed Measures enable" to TRUE

(click to enlarge)

As an example, I created two additional members in the ASOSamp outline.
- A member "delta Price" in the Measures (Accounts) Dimension with the Formula: ([Original Price],[Curr_year])-([Original Price],[Prev_year])
This is equivalent to the Variance Formula used in the "Years" Dimension.

- A member "Var_Quickview" in the "Years" Dimension with the same formula as the "Variance" Member.
This will be used to simply display a second cell with the same underlying value as Variance - but formatted using Format String hence enabling BOTH in the same report.

(click to enlarge)

In the outline you now select the member you want the Format String associated with and change the "associated Format String" in the Member Properties.
As you can see in this example an IIF statement reading:
MdxFormat(IIF(CellValue()< 0,"Negative","Positive" ) )
has been chosen for both new members.

After applying the Format String changes and running a report via SmartView, the result is:

(click to enlarge)

Essbase Database Admin Guide ,Chapter 12 "Working with Typed Measures "

Wednesday Apr 10, 2013

How to track MDX queries against Essbase

(in via Nancy)

Nancy pointed me at a newly created KM Doc that talks about MDX tracking unsurprisingly called:  Tracking MDX Queries Against an Essbase Database [ID 1543793.1].

This CFG setting is not yet fully documented, but may make it into the next documentation update.

MDX queries run against an Essbase application can be tracked by adding a line in the Essbase configuration file,

TRACE_MDX appname dbname 2

The appname and dbname refer to the specific application and database that you want to track.
Both are optional. Omit them if you want all applications tracked.

The MDX queries are dumped to a file named mdxtrace.log in ARBORPATH/appname/dbname folder,
i.e. /Oracle/Middleware/user_projects/epmsystem1/EssbaseServer/essbaseserver1/app/Sample/Basic

The log contains:

  • The time stamp at the time of completion of the query.
  • The elapsed time for the query
  • it does NOT contain the user who issued this statement - for that you would need to cross check in the Application log file

So for example, executing an MDX query against the Sample/Basic database, the mdxtrace.log contains:

Following MDX query executed at Mon Apr 08 08:56:13 2013
{[100-10], [100-20]} ON COLUMNS,
{[Qtr1], [Qtr2], [Qtr3], [Qtr4]} ON ROWS
FROM Sample.Basic

=== MDX Query Elapsed Time : [0.009] seconds ===================

Please Note:

Only queries that were executed successfully are printed to file. Queries that end in error are not printed.

We hope that this can assist you in tuning your Database/MDX queries.


This blog talks about the latest news and upcoming events initiated by the EPM/BI Proactive Support Team.



« March 2015