OLAP guide

Overview

This guide will give instructions for how to navigate and filter data in OLAP cube reports. An OLAP is a cube of data that can be looked at from different views.  Across the top is an area called Available Dimensions.  This is where various data elements can be dragged onto the cube either as Columns or Rows depending on how you want to view the data (down or across).

 

How to navigate and filter data using OLAP 

  1. In an OLAP, at least one data element needs to be ‘measured’.  This will create a column that is either COUNTING or SUMMING that data element.  In this example, it is counting the ID’s, one for each ID on the report.

  2. At the bottom of the cube, the data element Measures are indicated as well as some icons to help filter/edit the measures. 

  3. By clicking on this icon, a window pops up called the Measure manager.  You can filter what data you see here.



  4. By clicking on this red X it will disable all measures (however, you must always have at least one measure) or you can click on the green check in front of the measure to make just one measure red.  This will remove this measure from the view you are currently looking at.

  5. Another way to filter is to put some parameters on the data itself.  Let’s say I want to only view employees who are 50+ years of age.  You can click on the Filter tab, click on the Current_Age measure, then click the Enable checkbox and set the Min.value to 50.  Click the green check at the bottom and the report will only show the current age at 50+.



  6. To filter by a specific data element in the Rows or Columns, click on the down arrow in the data element box andthe Dimension editor window pops up. 





  7. In the original report, I can view all departments.  I might just want to view and drill down to the data for the Admissions department.  Once the Dimension editor opens, I canclickthe red X icon on the bottom and all departments will be excluded.  Then just click the box in front of the Admissions line and it will becomeagreen check.  Once you click on the green check icon at the bottom, the report will now just show the Admissions department information.

  8. When the dimension editor first comes up, all elements are checked green.  If you just want to exclude one, click in the box and it will turn to a blue X.  That makes it invisible to see but still counted in the total.  To exclude it completely, click it again and the X will become red.


  9. You can take any data elements from the Available Dimensions and drag-n-drop them into the Rows (gray area on theleft)orColumns (blue area).  If I drag the GENDER element down to the Columns area, I immediately now have two gender columns showing with the totals for each Department.



  10. As you click the plus sign in front of eachRow orColumn, it will expand the cube to see the other data elements you have selected.  By clicking the plus sign in front of Academic Technology, I can then see the ID.  If I click the plus sign in front of the ID, I can then view the Name.  You can also click the plus sign at the Row level so it expands all Rows at once instead of by individual row.



  11. Any OLAP data can be exported to Excel simply by right-clicking anywhere on the OLAP cube and selecting ‘Export to Excel’.  This will export the data just as you see it on the OLAP.  Values only are exported, not formulas.

Saving Settings in an OLAP 

There are two options to save settings on an OLAP screen.  This is helpful if you have specific parameters you frequently use or a layout and filtering of the OLAP report you wish to save to use in the future.


Parameter settings 

Parameters are the items you select to filter the data returned in the report.

  1. Select each of the parameters you wish to save, then choose <Save Current Settings> using the Dashboard Options dropdown box in the upperleft corner.
  2. A pop-up box will appear where you can name these settingsandgive it a description to give you a reference later.
  3. The <Manage Settings> option allows you to edit a name or delete a saved setting at a later time.


OLAP Settings 

At times, you may drag-n-drop or filter on OLAP settings to get the rows/columns in a particular view that you use frequently.  You may also save those settings for quick reference to use again.

The sub-menu (found by right clicking on the results) contains multiple selections:

  • Manage OLAP Settings
  • Save Current OLAP Settings
  • A list of all your saved OLAP settings for this cube
  • A list of all shared OLAP settings that other users created for this cube

Selecting Save Current OLAP Settings opens a dialog prompting you to save the current state of the OLAP cube for future use. The saved settings include items such as the current dimensions, measures, filtering, any custom formatting, column widths, etc., so that you can quickly return to the same cube view you had previously.

Enter a name and description for this saved OLAP setting. You can also choose to share it with other users who run this OLAP cube.

You can manage any existing saved options, save your current settings, go to your default, or use other saved options.

 

Select Manage OLAP Settings to edit the name, description, or share status of previously saved OLAP settings. To delete a saved OLAP setting, select it and then click the red X.  If you click on the pushpin, you will make that saved setting the default that the OLAP will open for you. 


Export to Excel 

Selecting this option exports the OLAP cube results directly to a spreadsheet application and displays the results on the screen.

Video: Exporting OLAP Cubes to Excel

Print 

The print options are similar to other Windows print dialogs in terms of selecting a printer, number of copies, and other settings. The sliding percentage at the lower right zooms the image on the screen but is not considered in the printed output. The Fit Width drop down provides choices to either fit the image or scale it on the printed output.


 

Other Information

Print

To print this article, click on Tools on the right, and choose Export to PDF.  Open the PDF to print.


Questions or Feedback

If you have any additional questions, or if you have feedback about this article, please contact Information Technology.