Get a Quote
Carr Harriman
ExoInsight

ExoInsight – Working with Essbase Substitution Variables, Attribute Dimensions, and UDAs

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!

ExoInsight

Tableau and Essbase – A Match Made Perfect by ExoInsight

One of the main trends to come out of the Business Intelligence world in the past five years is the rise of self-service analytics. Reporting and visualization tools have matured both in terms of capability and ease-of-use, putting control back into the hands of the analyst. Instead of submitting report requirements to an IT group and waiting weeks for the first iteration of a report or dashboard, end-users can now immediately produce amazing visualizations, iterate endlessly, and unlock insights to their data that never would have been possible before.

Tableau has been a pioneer in this space since the beginning. A leader in the Gartner Magic Quadrant, Tableau brings powerful and intuitive capabilities to the masses by providing countless data connections, gorgeous visualizations, and a usability factor that’s through the roof. Tableau provides a complete platform that enables data discovery, reporting, dashboarding, and communication throughout the entire organization.

However, there is one area that Tableau and almost all other visualization tools have struggled to seamlessly adopt: OLAP data. Data stored in OLAP cubes such as Oracle Essbase is often difficult to work with in a reporting tool built primarily to handle relational data. OLAP data sources are almost always proprietary and built for speedy access from a native interface. Enabling OLAP data in a reporting tool such as Tableau is like putting a square peg in a round hole.

ExoInsight

ExoInsight eliminates this challenge by presenting your Essbase data and metadata in a relational format, instantly and on-the-fly, unlocking it for downstream reporting tools such as Tableau. The challenges with Tableau’s built-in Oracle Essbase connector are well documented, not because the connector doesn’t do the job advertised, but because of the inherent discrepancies between OLAP and relational data. Tableau’s internal engine prefers data in row-and-column format, and this concept simply doesn’t apply to OLAP sources such as Oracle Essbase.

Enter ExoInsight. Instead of struggling to get your Oracle Essbase data in a format that Tableau prefers, ExoInsight does all this heavy lifting for you, formatting your Essbase data in such a way that makes it easy to work with.

Connecting to Essbase from Tableau

This is evident from the moment you connect to ExoInsight. Instead of using Tableau’s native Oracle Essbase connector, you’ll simply connect via a Microsoft SQL Server connection:

This brings you to the familiar SQL Server data source tab:

There are multiple ways to pull your data from Essbase using ExoInsight, and each way allows you to utilize both the Live and Extract connection options that Tableau provides. Depending on your use-case, you may have the need to refresh the data from Essbase every time someone refreshes the report. Other times you may be looking at data that doesn’t change often and it might make more sense to use the Extract option. Or you may have the need for a report to utilize both…the choice, and control, is yours.

Your Essbase data is presented in a familiar relational format, perfect for absolutely anything you want to do in Tableau. You can pull data in from any level of the cube and pull any calculated members as well. This is important because Essbase can often contain complex calculations that you would not want to replicate in Tableau so you can make sure a single version of the truth is maintained. ExoInsight allows you to write reports against Essbase data that will match the data other users pull through SmartView or other Hyperion-specific reporting tools.

Metadata is just as easy to pull into Tableau. All properties of your Essbase dimensions are available, including the Expense flag, Shared Member flag, level number, ordering, etc. Additionally, metadata is presented in both parent/child as well as generational format. This makes utilizing your Essbase dimensions a breeze in Tableau.

Security

One of the most important aspects of working with Essbase is data security. Data filters in Essbase can be complex and numerous. Replicating this security in a reporting tool is risky, cumbersome, and an incredible maintenance nightmare. ExoInsight allows you to utilize native Essbase security, the same security maintained in Hyperion Shared Services. If your company uses Windows usernames for your Essbase users, simply pass the value returned by the Tableau USERNAME() function to ExoInsight to filter the data for that username. Alternatively, if native Essbase security is used, you could simply have a username and password parameter within the Tableau report to filter the data accordingly.

Working with Essbase Data in Tableau

Once you’ve connected Tableau to ExoInsight, your Essbase data is available and working with the data is as easy as working with any other relational data source. Pull in your Essbase data (again, at any level of any dimension), link it up with a dimension table pulled from ExoInsight, utilize the Essbase hierarchies, blend data with other sources, use your Essbase data as a secondary data source…the options are literally unlimited. Anything you can do with a relational data set you can do with your Essbase data via ExoInsight. Create calculated fields, add additional data filters, publish to Tableau Online, even Essbase substitution variables are available with ExoInsight!

Recent Comments
    Archives
    Categories
    About Exponent

    Exponent is a modern business theme, that lets you build stunning high performance websites using a fully visual interface. Start with any of the demos below or build one on your own.

    Get Started
    Privacy Settings
    We use cookies to enhance your experience while using our website. If you are using our Services via a browser you can restrict, block or remove cookies through your web browser settings. We also use content and scripts from third parties that may use tracking technologies. You can selectively provide your consent below to allow such third party embeds. For complete information about the cookies we use, data we collect and how we process them, please check our Privacy Policy
    Youtube
    Consent to display content from Youtube
    Vimeo
    Consent to display content from Vimeo
    Google Maps
    Consent to display content from Google
    Spotify
    Consent to display content from Spotify
    Sound Cloud
    Consent to display content from Sound
    Get a Quote