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.

Russian installer

Почти половина посетителей этой страницы приходится на Россию. Получается конечно, что они уже умеют читать по-английски. Но кому не удобнее на своем родном языке?

Я давно начал переводить cubeSavvy на русский, но далеко не весь текст был переведён. Однако в этой последней версии (6.0.1) дела куда лучше обстоят с переводом. Практически всё на русском, так в самом cubeSavvy, как в инсталляционной программе.

Последную версию, как всегда, можно загрузить здесь.

И так, быстро пройдёмся по всем окнам инсталляционной программы. Начнём с самого начала - выбор языка:

Если выбрать Russian:

Настоятельно рекомендую выбрать каталог, в котором нет пробелов, а то программа может не запуститься:

More about new features in production release

There are several new features that I'd like to call attention to in the production release. One of the most important, even though it's not visible to end users, is the installer's ability to create a Windows service. Previously, the only way to run cubeSavvy on Windows was to use the start.bat file. While very easy to use and understand, it had a major shortcoming - the DOS command window remained open as long as cubeSavvy was running. The window could easily be closed accidentally, ending the cubeSavvy session in the process. Also, it's just not a good practice from a system administration point of view to have open DOS windows for each process running on a server. The Windows service allows for stopping and starting cubeSavvy just as you would any other service.

The next new feature of note is the ability to share ad-hoc grids with other cubeSavvy users. Administrators have always had this capability, using the "Users" - "Edit User Access" button of the "Home" tab. Now regular users can also share the grids they create. This is key: users can only share those grids that they themselves create. For such grids they have the option of giving fellow users either "Read/View" access or "Full Access". As the name implies, read access means that the grid appears in the user's list of grids on the left. The user can then open the grid, refreshing the retrieval contained therein. They can even modify the retrieval on the grid while they have it open, however, they do not have the ability to save their changes and make them visible to other users. Users with "Read" access to a grid are also not authorized to share it with other users. Only the creator of the grid, and admins, have this level of authorization.

An "Insert Attributes" button has also been introduced. Clicking this button will add those attribute dimensions to the grid that are not already present. For example, using an ad-hoc grid based on Sample.Basic, if the retrieval already contains "Caffeinated_True"; "Ounces", "Pkg Type", "Population", and "Intro Date" will be added to the header row. If no attribute dimensions are currently present, they will all be added.

The last enhancement is the "Environment" button, also for ad-hoc grids (are you seeing a trend here? ;-)). Clicking on this button presents a slide-out panel on the right of the grid. This panel displays most of the information available when creating or editing a grid: Environment, Application, Database, Calc script, Shared Services Groups, Comments, Description, and Decimal Places. A user with read access will see these options all grayed out, but can at least see the main settings for the grid. An admin user, the grid's creator, or a user with "Full Access" will be able to edit these fields and save the changes.

Give cubeSavvy a try. It has a 30-day free trial, so you have nothing to lose!

If you have any questions, please let me know in the comments.

Regards,
Harry