Power Query: how to connect to Essbase and pull data

UPDATE5: ExoInsight 7.0.0 now makes working with Tableau much easier than before. You can access ExoInsight from inside Tableau!

UPDATE4: ExoInsight also makes working with OLAP data (now both Essbase and Analysis Services) easier in Tableau. Instead of struggling to get your Oracle Essbase data in a format that Tableau prefers, ExoInsight does all this heavy lifting for you. 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.

UPDATE3: ExoInsight enables Power BI Direct Query capability to Essbase. But its data staging ability makes ExoInsight much more useful than as just a PBI connector. ExoInsight allows you to pull Essbase data at any level, including calculated members, into a SQL Server relational, flat format that's perfect for use in downstream processes. Easily schedule SQL Server jobs to have your Essbase data refreshed in ExoInsight. You can also stage Essbase data that doesn't change (last year's budget, for example) in ExoInsight rather than have to make yet another roundtrip to Essbase from Power BI.

UPDATE2: Now you don't even have to know MDX to use the Power BI connector for Essbase. This makes it much more accessible for people who don't know Essbase, but still need to pull live data directly from it, without ETL/data marts/data warehouses and the accompanying lag times.

UPDATE: We've now created a fully-supported Power BI connector for Essbase. It's significantly improved from the raw Power Query Excel workbook below.

As promised in my last post, I'm going to detail the process of connecting Power Query to Essbase and extracting data. Please keep in mind that I'm a Power Query neophyte. I'm sure there are better ways to accomplish some of the steps outlined below. Please post a comment with any advice/improvements, so we can all benefit.

First, let me start by saying that there is no magic involved. It's just rare that anyone on the Essbase side uses the requisite connection method - XMLA. This is likely because Essbase does a terrible job of implementing the XMLA protocol. It only does so partially, and even deviates from the standard in the parts it ostensibly supports. This is the reason why third-party tools are almost always unable to connect to Essbase - and after working with it for over a decade I think this is by design. Anyone coming from the full-featured, beautifully-implemented XMLA on the Microsoft Analysis Services side would naturally be completely stymied. For those used to Essbase, imagine using XMLA to create/modify cubes, measures, members, etc. This has been possible in MSAS since day one. Ok, enough of my Essbase XMLA ranting - for now anyway. On with the show...

The first piece of information you need is the XMLA url for your Essbase server. On my test server (running in VirtualBox) it looks like so: http://192.168.56.101:9000/aps/XMLA. You'll need to replace the 192.168.56.101 with the server's IP address or DNS name where Hyperion Provider Services is installed (this is usually not the same as the Essbase server). The port is 9000, which is standard for compact (i.e. development) installs, but is usually 13080 or 19000 in production installs. You may need to contact your Essbase administrator if none of the above gives you similar output (your version number could obviously be different) when entered in a web browser:

Power Query supports Web queries, which, of course, is what XMLA is (being SOAP). The key, therefore, is knowing the correct SOAP envelope attributes, as well as the Security node's wsse syntax to pass to the Essbase XMLA service. I've highlighted below the items most likely to cause an issue when trying to connect:

Xml.Tables(Web.Contents("http://192.168.56.101:9000/aps/XMLA",
  [
   Headers = [#"Content-Type"="text/xml; charset=utf-8"],
   Content=Text.ToBinary("
    <SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'
 xmlns:wsse='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
 xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
 <SOAP-ENV:Header>
   <Security xmlns='wsse'>
     <UsernameToken>
       <Username>admin</Username>
       <Password>password123</Password>
     </UsernameToken>
   </Security>
 </SOAP-ENV:Header>
 <SOAP-ENV:Body>
 <Execute>
 <Command>
   <Statement>
    SELECT NON EMPTY { crossjoin([Year].members, {[Actual]}) } ON COLUMNS, 
      NON EMPTY {crossjoin(Descendants([Profit]), crossjoin(Descendants([South]), [Product].members)) } ON ROWS 
    FROM Sample.Basic WHERE ([Can])
   </Statement>
 </Command>
 <Properties>
   <PropertyList>
     <DataSourceInfo>Essbase;Data Source=epm</DataSourceInfo>
     <Content>SchemaData</Content>
     <Catalog>Sample</Catalog>
     <Format>Multidimensional</Format>
     <AxisFormat>TupleFormat</AxisFormat>
     <Content>SchemaData</Content>
     <Timeout>30000</Timeout>
   </PropertyList>
 </Properties>
 </Execute>
 </SOAP-ENV:Body>
 </SOAP-ENV:Envelope>
  ")
 ]
)),

NOTES: The "Data Source" in the above <DataSourceInfo> element is the name of the Essbase server. The "Catalog" is the Essbase application, which must be the same as the one referenced in the MDX query in the <Statement> element.

Power Query provides excellent XML parsing capabilities. I've always had to use other languages (Java, Scala, JavaScript even) to parse XMLA's SOAP responses, but PQ handles it right out of the box. Navigating an XMLA response is as easy as pointing and clicking: 

XMLA returns data and metadata separately. In the above you can see Axes (metadata) and CellData. The hard part is marrying up the two of them. Specifically, CellData is returned like so:

Each data point has a CellOrdinal, which is not a row or column number. With all things XMLA, I always use the Microsoft documentation, which is orders of magnitude better, and more complete, than Oracle's. Here is the CellOrdinal explanation: "The axis reference for a cell can be calculated based on a CellOrdinal attribute value. Conceptually, cells are numbered in a dataset as if the dataset were a p-dimensional array, where p is the number of axes. Cells are addressed in row-major order." Yep, even that is better than Oracle's explanation, because there is no Essbase documentation at all of CellOrdinals.

The M code is pretty self-explanatory, so I'll let it do the talking below. The part dealing with CellOrdinals starts with the SetColumnNumberstep and goes up to, but doesn't include, MergeAxis0.

let
Source = Xml.Tables(Web.Contents(fnGetParameter("Provider Services URL"),
   [
   Headers = [#"Content-Type"="text/xml; charset=utf-8"],
   Content=Text.ToBinary("
    <SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'
 xmlns:wsse='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
 xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
 <SOAP-ENV:Header>
   <Security xmlns='wsse'>
     <UsernameToken>
       <Username>" & fnGetParameter("Username") & "</Username>
       <Password>" & fnGetParameter("Password") & "</Password>
     </UsernameToken>
   </Security>
 </SOAP-ENV:Header>
 <SOAP-ENV:Body>
 <Execute>
 <Command>
   <Statement>
     " & fnGetParameter("MDX Query") & "
   </Statement>
 </Command>
 <Properties>
   <PropertyList>
     <DataSourceInfo>Essbase;Data Source=" & fnGetParameter("Essbase Server") & "</DataSourceInfo>
     <Content>SchemaData</Content>
     <Catalog>" & fnGetParameter("Essbase Application") & "</Catalog>
     <Format>Multidimensional</Format>
     <AxisFormat>TupleFormat</AxisFormat>
     <Content>SchemaData</Content>
     <Timeout>30000</Timeout>
   </PropertyList>
 </Properties>
 </Execute>
 </SOAP-ENV:Body>
 </SOAP-ENV:Envelope>
  ")
 ]
)),
Body = Source{0}[Table],
xmlAnalysis = Body{0}[Table],
ExecuteResponse = xmlAnalysis{0}[Table],
return = ExecuteResponse{0}[Table],
mddataset = return{0}[Table],
root = mddataset{0}[Table],
Axes = root{2}[Table],
Axis = Axes{0}[Table],
AxisTextType = Table.TransformColumnTypes(Axis,{{"Attribute:name", type text}}),
ExpandTuples = Table.ExpandTableColumn(AxisTextType, "Tuples", {"Tuple"}, {"Tuples.Tuple"}),
AxisOrdinalMap = Table.ExpandTableColumn(ExpandTuples, "Tuples.Tuple", {"Member", "Attribute:Ordinal"}, {"Tuples.Tuple.Member", "Tuples.Tuple.Attribute:Ordinal"}),
AddDimensionMembers = Table.AddColumn(AxisOrdinalMap, "DimensionMembers", each Text.Combine([Tuples.Tuple.Member][UName], ",")),
AxesTable = Table.AddColumn(AddDimensionMembers, "DimensionAliases", each Text.Combine([Tuples.Tuple.Member][Caption], ",")),
Axis0 = Table.SelectRows(AxesTable, each ([#"Attribute:name"] = "Axis0")),
Axis1Text = Table.SelectRows(AxesTable, each [#"Attribute:name"] <> "Axis0" and [#"Attribute:name"] <> "SlicerAxis"),
Axis1 = Table.TransformColumnTypes(Axis1Text,{{"Tuples.Tuple.Attribute:Ordinal", type number}}),
SlicerAxisText = Table.SelectRows(AxesTable, each ([#"Attribute:name"] = "SlicerAxis")),
SlicerAxis = Table.TransformColumnTypes(SlicerAxisText,{{"Tuples.Tuple.Attribute:Ordinal", type number}}),
CellData = root{3}[Table],
Cells = Table.ExpandTableColumn(CellData, "Table", {"Value", "FmtValue", "Attribute:CellOrdinal"}, {"Table.Value", "Table.FmtValue", "Table.Attribute:CellOrdinal"}),
#"Changed Type" = Table.TransformColumnTypes(Cells,{{"Table.Attribute:CellOrdinal", type number}}),
SetColumnNumber = Table.AddColumn(#"Changed Type", "Axis0", each Number.Mod([#"Table.Attribute:CellOrdinal"], Table.RowCount(Axis0))),
GroupedRows = Table.Group(SetColumnNumber, {"Axis0"}, {{"ColumnNum", each _, type table}}),
AddedClusteredIndex = Table.TransformColumns(GroupedRows, {"ColumnNum", each Table.AddIndexColumn(_,"ClusteredIndex",0,1)}),
ExpandClusteredColumn = Table.ExpandTableColumn(AddedClusteredIndex, "ColumnNum", {"Table.Value", "Table.Attribute:CellOrdinal", "ClusteredIndex"}, {"ColumnNum.Table.Value", "ColumnNum.Table.Attribute:CellOrdinal", "ColumnNum.ClusteredIndex"}),
#"Sorted Rows" = Table.Sort(ExpandClusteredColumn,{{"ColumnNum.Table.Attribute:CellOrdinal", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"ColumnNum.Table.Attribute:CellOrdinal"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Axis0", type text}}),
MergeAxis0 = Table.NestedJoin(#"Changed Type2",{"Axis0"}, Axis0, {"Tuples.Tuple.Attribute:Ordinal"},"Columns",JoinKind.LeftOuter),
ExpandColumns = Table.ExpandTableColumn(MergeAxis0, "Columns", {"DimensionMembers", "DimensionAliases"}, {"Columns.DimensionMembers", "Columns.DimensionAliases"}),
MergeAxis1 = Table.NestedJoin(ExpandColumns,{"ColumnNum.ClusteredIndex"}, Axis1,{"Tuples.Tuple.Attribute:Ordinal"},"Rows",JoinKind.LeftOuter),
ExpandRows = Table.ExpandTableColumn(MergeAxis1, "Rows", {"DimensionMembers", "DimensionAliases"}, {"Rows.DimensionMembers.1", "Rows.DimensionAliases.1"}),
AddSlicerMemberNames = Table.AddColumn(ExpandRows, "SlicerMemberNames", each SlicerAxis[DimensionMembers]),
ExpandSlicerMemberNames = Table.ExpandListColumn(AddSlicerMemberNames, "SlicerMemberNames"),
AddSlicerMemberAliases = Table.AddColumn(ExpandSlicerMemberNames, "SlicerMemberAliases", each SlicerAxis[DimensionAliases]),
ExpandSlicerMemberAliases = Table.ExpandListColumn(AddSlicerMemberAliases, "SlicerMemberAliases"),
result = Table.RemoveColumns(ExpandSlicerMemberAliases,{"Axis0", "ColumnNum.ClusteredIndex"})
in
    result

The code can handle any combination of dimensions in the ROWS, COLUMNS, or SLICER axes. I haven't tested it with PAGE or CHAPTER, however. Also, it will return the "Default" alias, since XMLA returns this by default - whether you specify it in an axis or not. But if you use dimension properties to pull in other alias tables or dimension information (like LEVEL_NUMBER or GEN_NUMBER), you will need to modify the AxesTable step from [Caption] to the property name. 

The fnGetParameter query referenced above was taken verbatim from Ken Puls awesome Excelguru blog. It allows you to store variables and their values in an Excel sheet, then reference them from Power Query.

The code above generates the following output to Excel:

Finally, I've included a workbook containing all of the above that you can download here. Please let me know if you have suggestions for how to make the code better. I am astounded by how easy Power Query has made working with XMLA. At any step in the process above you can easily see what Power Query is doing. I will definitely be using PQ as my go-to Extract and Transform (of ETL) in the future.

My first foray into Power Query

UPDATE: We've now created a fully-supported Power BI connector for Essbase. It's significantly improved from the raw Power Query Excel workbook below.

I'm not a big fan of FDMEE (that's putting it mildly), so when I recently had a requirement to pull data from one Essbase cube and map the data for loading to another Essbase cube, I started looking elsewhere. Since I happen to be using Excel 2016, it was a logical place to start.

It turns out that Microsoft has incorporated Power Query directly into this version of Excel, whereas before it was an add-in that you had to download and install separately, similar to Smart View. Except, since it's an add-in directly from Microsoft it works a thousand times better than Oracle's. This is understandable, considering Microsoft wrote Excel and can modify the code as needed to make its own add-ins work correctly.

Anyway, Power Query functionality can now be found under the Data tab in Excel 2016. Below you can see some of the data sources from which Power Query can pull. Besides the "other sources" shown below, it can pull from any relational database (and Microsoft's OLAP Analysis Services, too, of course). You will not, however, see Essbase as an explicit data source. In fact, people have been requesting that Microsoft add Essbase for almost 2 years now.

This didn't seem very encouraging at first. But hey, I built a rudimentary Google Sheets Essbase add-on, as well as a stand-alone, web-based Essbase grid system (cubeSavvy). How hard could it be to get Power Query talking to Essbase?

Well, it turned out to be pretty easy. So easy, in fact, that I am now a huge fan of Power Query. If Power Pivot is as awesome at BI as Power Query is at ETL, I will definitely be checking it out soon!

Power Query does have limitations, but it is orders of magnitude faster than FDMEE within them. For example, just mapping the 250,000 rows from one cube (not including extracting them) took FDMEE over 15 minutes. Power Query extracted the same data from Essbase and mapped it in a little over a minute!

I'm not sure how much FDMEE costs, but it's probably in the hundred of thousands of dollars like most Oracle products. Do you know how much Power Query costs? Zero dollars!!! It's built directly into Excel, so you get it for a couple of hundred bucks - along with Power Point, Word, Access, Outlook, and the other Office products.

I'm going to write a separate post with the detailed instructions for how to pull Essbase data into Power Query, since I anticipate a lot of people who aren't interested in FDMEE will want to reference it. The sharp-eyed among you will have noticed, however, that the screenshot above contains data from Sample.Basic returned from an Essbase query.

Pulling Essbase data into Google Sheets

I created a thread on network54 to gauge the interest in a Google Sheets Essbase add-in. I developed a sidebar that can run an MDX query against an Essbase database and return the data to a Google Sheet.

The following video demonstrates this functionality: google_sheets_POC

As I mentioned in the network54 thread, please keep in mind the inherent limitation of Google Sheets is that it can only connect to a publicly-available (i.e. internet) URL. As we all know, most companies keep Essbase walled off from the internet behind their firewall. A couple I've worked with have, however, opened up a secure port (https) to the Provider Server URL (e.g. https://CompanyAPS:13080/aps) for partner companies to access data. So it's not completely impossible that companies would be amenable to this option.

I can understand IT Security's reluctance to expose the APS https URL to an external web server, but risks can be mitigated. It is a secure (https) protocol and access is still controlled by Shared Services usernames and passwords passed from the Google Sheet.

If there is sufficient interest, the next step would be to add the functionality contained in the Essbase Excel add-in (and Smart View) to the Essbase Google Sheet add-on.

Please leave a comment if this is something you are interested in.

Thanks,

Harry Gates

MDX Query Tool - XMLA Edition

Response to the MDX Query Tool has been overwhelmingly positive. My sincere thanks for the emails and comments. They keep me motivated to continue making useful tools. There's nothing quite so discouraging as releasing a tool, only to have no one use it.

Several people, however, experienced the phenomenon of JAR hell, caused by the version of the ess_es_server.jar file in the lib directory. The version here has to exactly match the version of Provider Services running on the server from which you want to retrieve data. I recommended that you find the version on your server and copy it to the MDXQuery/lib folder. This felt like a hack, however, so I started thinking about ways to eliminate this requirement.

Like most Essbase developers, in the back of my mind I knew that Essbase supported XMLA, but had never really thought about using it - until now. The main appeal at first was that no Essbase-specific jars are needed. However, as the Wikipedia article on XMLA states, "XML for Analysis (abbreviated as XMLA) is an industry standard for data access in analytical systems, such as OLAP and data mining." It's used by other vendors (Microsoft, Pentaho, SAS) in other OLAP products besides Essbase (MS Analysis Services, Pentaho/Mondrian - open-source, Jedox - open-source). I don't have any of these products installed, but theoretically at least, you could point this version of the MDX Query Tool to them and it should work. Please let me know in the comments if you do this and whether or not it works.

Just as with the original MDX Query Tool, you can download the MDX Query Tool - XMLA Edition from my Google drive, no Google account needed. After unzipping, there are two files to launch: MDXQueryXMLA.bat (for Windows) and MDXQueryXMLA.sh (for the superior operating systems). As a zip file cannot maintain file permissions, you'll need to 'chmod a+x MDXQueryXMLA.sh' on *NIX and Mac OS X. This tool has been incorporated into cubeSavvy Utilities with improved functionality. You can download it here.

After launching the MDXQueryXMLA.bat|sh file, you'll need to input the appropriate parameters for your environment, as seen below. The sharp-eyed will notice that there are no longer fields to enter Application and Cube information. This is not necessary through XMLA, since the MDX expression already contains this information in the FROM statement. For this reason alone I like it better than the Essbase Java API version that uses IEssOpMdxQuery.

As before, clicking on the "Save to file" button at the bottom will open a dialog box that will allow you to choose a file path/name for the file. You can then open this file, copy the contents, and paste directly to Excel.

Happy MDX querying!