Substitution variables, attribute dimensions, and user-defined attributes (UDAs) are three very important aspects to any Essbase cube. Like most OLAP sources, Essbase provides value by allowing the user to query data across multiple dimensions and hierarchies, from the top of a hierarchy down to the base-level data. Cubes with many dimensions and/or many members in each dimension could potentially have trillions(or more!) data intersections populated. Substitution variables, attribute dimensions, and UDAs enable the user to navigate all this data seamlessly and intuitively.

Unfortunately, working with substitution variables, attribute dimensions, and UDAs outside of the Essbase environment is often hard, if not impossible. ExoInsight removes these difficulties by providing a straightforward way to utilize all three of these methods to query Essbase data.

(Note: All the examples below were performed on the default Sample Basic Essbase cube, but the same principles apply to any BSO or ASO Essbase cube)

Substitution Variables

The Sample.Basic Essbase cube does not come with any substitution variables pre-defined, so one was set up called “MyMarket” with a value of “Central”:

Using substitution variables in ExoInsight is as easy as using any other member: simply add it to the POV String or MDX statement. For example, here is a query in SSMS on the Sample.Basic database that pulls all descendants of Market:

Changing the POV String to utilize the substitution variable MyMarket instead of pulling back all descendants of Market is trivial:

Now when the MyMarket substitution variable is updated in Essbase, the query above will automatically pull back the relevant data.

This is what it would look like in Tableau:

And now the result set returned in the report can be controlled by the substitution variable in Essbase…no need to update the report:

Attribute Dimensions

Attribute dimensions provide a flexible way to define alternate views of data in an Essbase cube. The main benefit is that their use in a query is optional: since they are not “true” base dimensions, they can be included or excluded from a query based on the needs of the report. Additionally, attribute dimensions do not actually store data, keeping the size of the cube small.

The Sample.Basic cube has five attribute dimensions defined:

ExoInsight allows you to utilize these attribute dimensions exactly as you would any other dimension defined in an Essbase cube: simply add it to the POV String or MDX statement. To illustrate, we will create a POV String that only pulls one row of aggregated data:

Now if we only want to see the products tagged as “Bottle”, we simply add it to the POV String, exactly as we would a member from any dimension:

Notice that the Amt column has been reduced; this is because only the products that are tagged as Bottle underneath the Product rollup 100 are included in the total. Also notice that an additional column called Pkg Type has been added to the result set.

We can confirm this behavior is identical to what Sample.Basic would return in SmartView by doing a simple retrieve:

Without Bottle

With Bottle

Set functions can be used against attribute dimensions exactly like regular dimensions. In the below query, all descendants of the Ounces attribute dimension are returned. This allows for any type of filtering and reporting in downstream applications:

UDAs

Essbase result sets can be filtered by any user-defined attribute (UDA) by using the UDA() set operator. This provides an easy way to query an Essbase database for members tagged with a specific UDA, eliminating the maintenance that would need to be performed on every report when a new member was added with a specific UDA.

Building on our example, if we wanted to pull back all markets that were tagged with the Major Market UDA, we’d simply add this syntax to the POV String (the double-quotes must be escaped with a backslash):

All the functionality explained above is available via ExoInsight to any reporting tool, data warehouse, transactional system, or other downstream application that can connect to SQL Server or ODBC connection. ExoInsight removes all the complexity of interacting with your Essbase data, providing a user-friendly interface that can save you from creating exports, formatting, creating processes, process monitoring, and all the other headaches that are inherent when attempting to interface Essbase with other applications. Additionally, this is all in real-time, presenting data and metadata at the moment of the request.

If you’d like to learn more, please reach out to us via our Contact Us Page. We’d be happy to schedule a demo and show how ExoInsight can eliminate the headaches of working with Essbase!