Query Designer – The Prius of EPM Reporting

Finit has delivered solutions for the office of finance 20 years, beginning in 2002 with a focus on Oracle EPM. In 2011, we were the first OneStream implementation partner and delivered the first OneStream solution to go live. We no longer offer consulting services for Oracle EPM. However, the functional experience and technical expertise built from this experience form the foundation of our industry-leading OneStream practice.

What is the Query Designer? It just looks like another ad-hoc grid to me…

The Query Designer (QD) is a hybrid between an ad-hoc grid and the structured reports that are built using FR Studio. It allows the drill-down capability of an ad-hoc grid, while allowing you to return to the original data set.

What are the benefits of QD reports?

• Completeness – When setting up QD grids for users who are unfamiliar with suppression options, QD provides a more complete set of data, especially with dynamic member lists. This can be used to build structured reports using lookups because the data will be stable, but still usable to research values.

   

  • QD grids get around some of the risks of using suppression, e.g. missing/invalid suppression in a regular ad-hoc grid when you change your POV and refresh.
  • With QD, if you “Run Report” again, the grid goes back to the data that was originally referenced in the designer
• Ease of use – A super-user can create a complex query and send the file out to users. With minimal knowledge of QD, the user can select “Run query” to get the complete data set as defined in the query.
• Dynamic – If the QD is set up dynamically (ex. {Account.[Descendants]}), new metadata set up within a selection would automatically be included.

How would I set one of these up?

1. Log into smart view and activate your connection (Connections > Active Connections – select app).
2. On the Ad-hoc ribbon (i.e. HFM Ad Hoc, Essbase, Planning Ad Hoc), select Query > Query Designer

   

3. The ad-hoc grid shows up with the Query Design POV pane. Note that the POV pane is slightly different than what you normally see for ad-hoc grids. A “query” sheet is also generated. The panel is different for HFM v. Essbase v. Planning.

    

   1. HFM

      

   2. Essbase – attributes are available to pull into grid (if the original dimension is also in the column or row). There are also options to run MDX queries and a Data Filter (to pull in top 10 or bottom 10 of a data set).

      

       

      

4. Drag the dimensions that you want into the rows and columns.

   

    

   

5. To change the selected items for each dimension, single click on the dimension or the drop down button and “…”.

    

   1. You can select multiple values for dimensions in rows or columns, including:

       

      1. Individual nodes
      2. Dynamic lists (e.g. [Base], [Descendants], etc.) for the whole dimension or for specific items (ex. {Assets.[Base]}
      3. Member Lists (System and user-defined)
      4. Essbase only – generation or level of full hierarchy or sub-hierarchies
   2. See examples below:

      

       

      

6. Select the Query option from the HFM Ad-hoc ribbon and “Run Report”

   

7. The Sheet1 – Query tab gets hidden and the query runs to Sheet1 – Report. At this point, it functions like a regular ad-hoc grid. You can zoom in/out, change POV, remove values, change dimensions, formatting, etc.

   

8. However, when you select Query ? Run Report, the grid goes back to its original form as defined in the designer. In 11.1.2.4, the HFM grid reverts to the definition in the design, but the POV dimension (i.e. [ICP Top]) changes remain.

   

   NOTE: The behavior for EPM applications build in versions prior to 11.1.2.4 v. 11.1.2.4 may be different:

    

   • HFM & Planning – Before v11.1.2.4 applications, all dimensions, including the grid dimensions and the POV dimensions, would revert back to the original QD options. In applications built in 11.1.2.4, changes made to the POV dimensions will remain, but grid dimensions will revert back to the designer.

      

     • For a permanent change to the grid, it had to be made in the design view.
     • In the above example, the [ICP Top] would have reverted back to [ICP None].
   • Essbase – For all versions up to 11.1.2.4, all the dimensions will revert back to the QD definition. No changes made during ad-hoc operation of the QD would remain if user selects “Run Report”.
9. The Smart View Member (i.e. zoom level, member level display, etc.) and Data options (i.e. suppression and replacement) work the same for QD queries as they do for ad-hoc grids.

This seems like the best of both worlds…. So, why would I ever want to use the regular ad-hoc grids again?

• Refresh times – Since the QD is designed to be more structured, this often resulted in pulling more information than you need and therefore longer refresh times. For example, if you wanted to pull December of prior year and all the months of the current year, your QD set up would effectively force you to pull all twelve months for both years.
• QD does not handle “Keep Excel Formatting” well.

   

  • Neither regular nor conditional formatting stays in a QD report, even with “Keep Excel Formatting” option checked.
  • No blank rows or column can be inserted like you can with a normal ad-hoc.
  • With the above two features, I have seen some highly formatted reports being generated using normal ad-hoc that would not be a possibility with QD.
• With Essbase, multiple QD grids cannot be placed on one worksheet, whereas multiple ad-hoc grids can

Additional food for thought on QD…

• Cascade function – works OK, but the dynamic lists for the grid dimensions turn into hard-coded values
• Using VBA with QD – there are two VBA functions related to this – HypMenuVRunReport, HypMenuVQueryDesigner

   

  • Once the report is run, the ad-hoc VBA functions would work

As Toyota says… “Rethink the Hybrid”