Essbase Ad Hoc Query

Default Ad Hoc Grid

Once connected to Essbase, Smart View displays the:

  • Essbase tab and ribbon
  • Default Point of View (POV) in Row 1 of the sheet
  • Account dimension in the row display area
  • Period dimension in the column display area
  • Intersection of data in cell B3, 853903095.1

Ad Hoc Grid and POV

  • Every dimension in the cube must be in either the POV or the Grid
  • The Grid must contain at least one dimension in rows and at least one dimension in columns
  • All other dimensions are in POV
  • Member selected in POV dimensions applies to all cells in report; you can only select one member in POV dimensions
  • You can move dimensions between the Grid and POV
  • This graphic shows the 11 dimensions in the CalRptg cube

Setting the POV

Each dimension defaults to the top level member such as Entity rather than 1_HAAS3. In order to retrieve meaningful data from CalRptg, you’ll select members for the dimensions in the POV and in the grid.

  • For the POV, you may only select one member for each dimension.
  • For the dimensions in the grid rows and columns, you may select multiple members.

Enter Member in Dimension

The fastest way to select a member is to type it in the cell. The Smart View Ad Hoc for CalRptg Dimensions job aid shows the top level members of the most frequently used dimensions; it is a good guide when you want to quickly type in the member name.

Default query with members selected

Refresh

To get the most current data from CalPlanning into your Excel report, click the Refresh button from the Smart View or Essbase ribbon when:

  • You change members in the grid or the POV
  • Your report hasn’t been opened or refreshed recently

Save Your Query in Excel

To save your query in Excel for future use, go to the Excel menu and select File / Save As. If you are accessing Smart View from Citrix, you will need to navigate through the file tree to save to a folder on your desktop. The Accessing Your Files When Using Citrix with Smart View job aid has step-by-step instructions to guide you to your folders.

Disconnect When Finished

When you are finished working with Essbase, be sure to disconnect from the server. This will free up resources for others who are accessing the database.

  1. Select the Panel button from the Smart View ribbon to display connections
  2. Select the drop-down arrow next to Home
  3. Select Disconnect All

Smart View panel with home menu open and Disconnect All highlighted

Open an Existing Excel File with Ad Hoc Query

Once you have created an ad hoc query and saved it in an Excel file, you can open the file with the File / Open command. To refresh the data from Essbase, you’ll need to connect.

 1. Go to the Smart View ribbon and select the Panel button

2. If you have already connected to Essbase since you opened Excel, you will see Recently Used connection on the panel. You can select Ad hoc Grid, Essbase Cluster -1 | CalRptg | CalRptg and skip to step 7

3. Select Shared Connections

4. Select Oracle® Essbase from the Select Server drop-down list

Shared Connections drop-down list with Oracle Essbase highlighted

5. Drill into the Essbase Cluster-1 to select the CalRptg cube, a child of the CalRptg silo

Essbase tree with CalRptg expanded

6. Double-click the CalRptg cube

7. Enter your User Name and Password (CalNet ID and Passphrase)

8. Select Connect

9. Select Reuse sheet contents and POV

Panel with Reuse sheet contents and POV highlighted

  Practice - Exercise 1

Follow the instructions in the Exercise 1 to practice connecting to Smart View Essbase to create an ad hoc query.