Making MDX Refreshable
By default, Smart View does not offer any options for making MDX queries refreshable. It would be a great addition if Oracle did something like storing the query in the metadata for the worksheet, and then when you go to the Essbase ribbon > Query > Execute Query, the query would be prefilled. There could also be an option that skips the dialog entirely, and just refreshes the sheet based on the MDX query.
But, that is not present in the current release of Smart View (11.1.2.5.600), and it may never be added. What are our options in the meantime?
Refreshable MDX requires:
- A way to store the query
- A way to execute the query
That is a pretty short and achievable list. Let’s tackle each thing in turn.
Storing the Query
Storing a query is useful whether we are looking to make MDX refreshable or not. The fifth post in this series recommended storing a query in the A1 cell as a comment. This makes it easy to access, and executing and MDX query does not clear the comment, so it gets preserved on the sheet for as long as it is needed.
There are other ways that queries could be stored, but this one is simple and easy, so we will use it here.
Excel and Macros
To execute the query, we need to turn to something in Excel that is a little more powerful: macros. You may already be familiar with macros, but if you are not, here’s a brief run-down. Excel lets you record actions and play them back. This can be a great way to perform complicated tasks multiple times.
When you record a macro, Excel records your actions and translates them into its programming language, called Visual Basic for Applications (VBA). For simple macros, recording actions may be enough, but it is also possible to write VBA code directly, which allows for more precise control over what happens, and allows actions that cannot be performed in Excel without VBA.
To add a macro to your workbook, we need to use the Developer ribbon. By default, the Developer tab of the ribbon is not shown. To add it, right-click on any tab of the ribbon and choose Customize the Ribbon.
Make sure the Developer box is checked, and click OK to save the changes
From the Developer tab, click the Visual Basic button. https://ecapitaladvisors.com/wp-content/uploads/4-5-2.png
The VBA editor appears. Our next step is to add a Module to our workbook. Find your workbook in the navigation pane on the left, right-click on the workbook, and select Insert > Module.
A Module folder gets added. This is where we will write the code for our macro.
Importing SmartView.bas
Hyperon Smart View for Office has a number of functions that are available to make VBA able to interact with Essbase (and other Hyperion applications). In order to make Excel aware of these functions, we must declare them. The online documentation includes details on how to do this for individual functions, but Oracle has also provided a file that includes every declaration for every function that is available.
This is easier than making the function declarations ourselves, so we will simply import this file. Right-click again on your workbook and select Import File.
The file that needs to be imported is included with every Hyperon Smart View for Office installation, so if you have Smart View installed, then you already have the file on your computer. The default Smart View installation path is C:\Oracle\SmartView, so you can navigate there, unless your installation directory has been changed from the default. (If this is the case, you may need to search your hard drive for the file, or contact your administrator.)
From the Smart View installation directory, open the bin folder, and then select the smartview.bas file.
Optionally, you can double-click the module that gets imported to see the constants and functions that it declares. Or you can simply proceed to the next section.
Adding Code to the Module
Double-click on Module1 to make sure it is open in the editor window.
This is where we will add our macro code. Macros are called Subs in VBA (for subroutine). (A copy of this Excel file is included at the end of this post, so you do not have to type this out if you don’t want to.)
This macro does not do anything particularly complicated, but we’ll walkthrough the code briefly.
Executing the Query
Close the VBA editor using the File menu.
From Excel, make sure that the worksheet is connected to Sample.Basic and that the A1 cell has an MDX query in it.
Using the Developer ribbon, click Macros.
Depending on whether you have other macros set up, you may see only the macro that we just made, or you may have a larger list. Either way, find “RefreshMDXFromA1Comment” in the list, and click Run.
The sheet will refresh, and a Message Box dialog will appear. If the code 0 is displayed, then the macro executed successfully.
Error Handling and More
That macro already represents a basic approach to refreshable MDX, but there’s a lot it does not do. What are some additional things we would like it to do?
- Detect whether the sheet is connected, and display a message if it is not.
- Not duplicate comments when a WHERE statement is used.
- Provide more than just a numeric code for an error.
- Retrieve details from the server if there are syntax problems in the MDX query.
This post is not going to explain how we can solve all of these problems, but the sample worksheet includes a more advanced macro that addresses them. Look for the macro “RefreshMDXFromA1CommentAdvanced” in the sample file.
eCapital_Refreshable_MDX_VBA_Example
Taking it to the Next Level
Overall, this was a very simple approach. There are more exciting possibilities. This section is included as a brief thought exercise along one particular line of potential development.
VBA is one way to extend Excel, but there is an even more powerful option, called Visual Studio Tools for Office. This can allow for custom ribbon development, and–perhaps more importantly–it offers a set of controls that are much more modern than VBA allows. (The core of VBA is VB 6.0, which was released 1998. That is…a very long time ago.) For example, VBA has very limited support for a TreeView control, but Visual Studio has an excellent, modern TreeView.
Even better, there are third-party controls, like the ScintillaNet control that can build a code editor with MDX-specific highlighting. This makes is possible to do something like this in Excel:
The above image is a proof-of-concept. There would be many problems to solve before anything like it could be released, and we may never get to that point. However, even at the prototype stage, it includes features for providing a basic query from the connected worksheet, syntax highlighting that is correct for MDX, storing the history of queries that have been executed, and more.
A View of Things to Come
We hope you have found some value in this series. This has been an experiment for us in some ways, both in having a series of related posts, intentionally released close together, and in having a series of post that provides an introduction to a topic.
Depending on feedback, we are open to doing more series like this on other topics. Additionally, there is a lot more that could be covered about MDX in particular, and so if this has been valuable for you, or you have any specific areas that you would find helpful for us to cover, let us know.
