Power BI Connector for Essbase: Now Available Without MDX

Our Power BI connector for Essbase lets companies pull data directly. It’s hard to overstate how important this simple fact is. Being able to access your Essbase data without having to extract it to another format first, whether a file or database, is a huge productivity boost and timesaver. The only catch was that you had to know MultiDimensional eXpressions (a.k.a. MDX).

Following is a typical MDX query:

CrossJoin ({[100-10],[200].Children}, {[East],Descendants([West])}) ON COLUMNS,
  CrossJoin (
    {[Sales],[Total Expenses]}, {[Qtr1]}
  )  ON ROWS
FROM Sample.Basic WHERE ([Actual])

Upon seeing the above, anyone familiar with SQL for querying relational databases will experience a strange, mixed feeling of familiarity and that’s-just-not-quite-right. And given that there are orders of magnitude more users/experts of SQL than of MDX, it can be difficult to find someone to write the mysterious query needed to get to your Essbase data. In fact, it would probably surprise PBI practitioners to find that most Essbase administrators don’t even know MDX. It’s a relative newcomer compared to report scripts and calc scripts, so many have just never bothered to learn it.

Well, everyone can now take a page out of the Essbase administrator’s procrastination book, because you too no longer need MDX to report from your Essbase cube. That’s right, our latest version of the PBI Essbase connector doesn’t require curly braces or crossjoins. Instead you can pull data based on a simple POV string, such as the following equivalent to the MDX above: 100-10|Children(200);Qtr1;Actual;Sales|Total Expenses;East|Descendants(West)

member POV string example

The POV mode syntax follows two basic rules:

  1. Dimensions are separated by semicolons: Product;Market
  2. Members within dimensions are separated by pipes: Jan|Feb;South|Children(East)

We’ve also added the ability to use DIMENSION PROPERTIES, both in MDX and in POV mode. This allows you to specify alternate alias tables, which is particularly important in international companies. Using the Sample_U application that ships with Essbase, you can pull the RussianNames alias table with the following POV syntax: [RussianNames];Jan;Descendants(100)

POV mode - pulling RussianNames alias table


In related news, Microsoft just announced that support has been added for refreshing custom connectors through the PBI personal gateway. So, if you’re using the personal gateway, your reports based on our Essbase connector can now refresh automatically. Microsoft goes on to say that support for refreshing through the Enterprise Gateway will be coming “in a month or two”, so around June 2018. Exciting times!

The improvements we’ve made above, along with those coming out of Microsoft, will soon make reporting from Essbase a seamless experience for everyone, irrespective of Essbase knowledge.