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.