Query Designer – it is functionality that has existed in one form or another since the early days of the Essbase add in, but I have rarely seen it used despite having several compelling features not otherwise available to end users. Today, the Query designer lives on in Smart View and is capable of being used with Oracle Cloud (PBCS and EPBCS) applications, albeit with several limitations. This article will be part of series which will examine some lesser utilized functionality available in Smart View.
Query Designer is a tool that provides a way to design queries which provide views into your Planning data. It provides more structure for queries than standard ad hoc queries. Query Designer provides a structure similar to either Financial Reporting or Form grids, but puts that power in the hands of the users. Query Designer allows you to create repeatable dynamic queries. Simply put that means if your outline changes, the query will reflect those changes the next time it is run. The query grid will “refresh” every time the query is run.
To create a query, connect to an Oracle Cloud EPM connection, and choose ad hoc analysis. From the Planning Ad Hoc menu, choose “Query / Query Designer”.
A new worksheet will be created with the “ – Report” Suffix added to the sheet you were previously working with. Additionally, a Query pane will open on the right-hand side of the worksheet.
The Pane on the right will provide the query definition for the worksheet. Dimensions can be moved between rows, columns, and POV. Additionally, attributes may be added into the query.
As dimensions are moved and defined, the worksheet itself will show defined definitions. Choosing members is much like choosing members when designing a form or member selection for ad-hoc grids.
Once you have completed editing your query, you will see updates represented in the worksheet similar to as follows:
When you are ready to run your query, simply choose “Apply Query” from the pane on the right, or “Run Report” from the Query drop down in the ribbon above. In this case, the result of the run query looks like this:
Now that the query has been run, it can stand alone as a simple static retrieve. If data is updated in the application, a standard “refresh” updates the figures. If a new member and associated data has been added, rerunning the query will dynamically add that member. This would be done by selecting the “Run Report” item from the Query menu.
To step back into the Query Designer, simply Choose the “Query Designer” item from the drop down.
Once the query has been run, you may treat it similarly to an ad hoc report. You can submit data, zoom in or out, remove or add members, etc. However, when you choose “Run Report” again from the Query drop down, the grid will “reset” to the current definitions defined in the Query Designer.
This functionality – running a query then navigating the grid in an ad hoc manner, gives you a way to customize a “starting point” for ad hoc retrieves that are commonly used. Or, it lets you create a quick “report” or “form” that is available only to you or those that have access to the current workbook. The queries are saved with the workbook, not online. They can be shared as needed by sharing the workbook.
So, what are the downsides to query designer? It may take marginally more time to set up a query than a standard ad hoc grid. A limitation specific to the Oracle EPM Cloud connection (used for PBCS and EPCBS) is that “Data Filter” functionality is not available.
When connecting to a native Essbase connection (not available in PBCS or EPBCS) this functionality allows a ranked filtering to be applied within resulting grids.
So, that pretty much sums up the Query Designer. More entries in this series will follow, please check back soon.
Blog post by James Renger of Key Performance Ideas.