OLAP Pivot Table Template

 

OLAP stands for Online Analytical Processing. This document will explain how users will interact with the OLAP Pivot Table template at runtime, as well as additional features that developers have access to while creating OLAP Pivot Reports.

Template Overview

The primary use of this template will be for the user who needs to be able to quickly total both column and row numerical data, while grouping via one or multiple fields. There are many advantages to using m-Power OLAP Pivot Tables over other alternatives, including:

  • Security — Developers can easily assign Row Level Security to this template to control which users see which data.
  • Graphing — End Users can quickly and easily switch between numerous graphing options, including: Graph Type, Selection Value, and Field to Graph.
  • Smart Record Selections — As end-users enter Record Selection values, the Pivot report will filter out non-matching records to alert the user what record selection values are still applicable.

Dimensions

When end-users run this application, they will be brought to an initial screen that shows a “Dimensions” screen. With respect to the OLAP Pivot table, fields that have been defined as sequence keys qualify as Dimensions. A dimensions screen could look something like this:

Notice that the end user has the ability to choose which numeric values they would like to see totaled at runtime. By adding dimensions, users are essentially adding additional GROUP BY statements to their SQL query. Additionally, users can select dimensions at runtime. Simply click and drag values into either the “Row” dimension area or the “Column” dimension area. By selecting more than one row/column dimension, users have the ability to drill-down to explore their dataset. Clicking Accept will take all changes made on-screen, and reload the OLAP Pivot Report to use the updated settings.

Graphing

Your end user is going to see a graph with every application. By default, their graph may look something like this:

Due to the advanced complexity of the OLAP template, drilling down from within the image has been disabled. However, users still retain numerous levels of functionality. For instance, users can, on the fly, switch between Bar, Stacked Bar, Line, and Pie graphs. Additionally, users have the ability to limit the data seen within their graph by utilizing the “Selection” dropdown. All valid values from the first row dimension are available to be chosen from the “Selection” dropdown. Lastly, users can decide which value they want graphed. Users can select from any of the fields they selected from the Dimensions portion above.

Record Selections

The Record Selection pane on the left hand side of the screen has the same functionality as the Interactive Report Template. This means that as users select values, the OLAP Pivot table will highlight available matching records, simultaneously graying out non-matching records. This alerts the user that, while they can select this value, no matching record exists. More information about this functionality can be found here.

The Record Selections pane has two buttons available to the user: Toggle Selections and Toggle Dimensions. By clicking “Toggle Selections”, the entire left side record selection box will slide to a hidden view. By clicking “Toggle Dimensions”, users can either show/hide the portion of the screen that allows them to select values and/or drag and drop row/column dimensions.

Developer Notes

One of the most common requests from our end users is they would like to see more functionality, with less time in m-Painter. You have requested, and we have answered! One of the most promising features of our OLAP Pivot Table template is that there is minimal work to do in m-Painter. For instance, here is a standard OLAP Pivot Report in m-Painter:

Notice that as a developer, the data portion of the screen has been removed from m-Painter. This is necessary due to the advanced complexity of the OLAP Pivot Template. In fact, mrc recommends using the WYSIWYG portion of m-Painter exclusively for adding dropdown lists to your record selections. You can learn more about this process by clicking here.

Application Properties

However, just because we have limited the need for excessive work in m-Painter does not mean that you are limited in developer options. In fact, through the use of Application Properties you have a myriad of choices.

  • max_web_records — This serves as the governor of your report. If the number of records you are trying to return is greater than this value, no records will be returned. The default value is 10,000.
  • drilldown_records — When drilling into a level to see more detail, the report will only show this number of detail items per click. Users have the option of seeing the next set of detail records if they choose. The default value is 50.
  • max_col_records_pivot — This governor controls how many unique columns can be returned to your report. If you have too many unique column values, the report is deemed too wide and will not run. The governor is in place primarily for performance considerations as too many columns will cause your report to be slow. Increase this value to better meet your needs when necessary. The default setting is 100.
  • max_row_records_pivot — This governor controls how many unique rows values can be returned to your report. If you have too many unique row values, the report is deemed too long and will not run. The governor is in place primarily for performance considerations as too many rows will slow your report. Increase this value to better meet your needs when necessary. The default setting is 300.
  • dimensions_button — By default the “Toggle Dimension button” is sent to show. This allows your end users to change their OLAP Pivot to better meet their needs for an Ad-hoc Report. By changing this feature to “Hide” forces the end user to only see the default dimensions and data set that you have set up for them.
  • show_dimensions_onload — You have the option as the developer to have the end user see the dimensions when the page loads the first time. If you set this option to hidden, the end user can still access the dimensions window, however they would have to click the “Toggle Dimensions” button on screen to change dimension options.

Other Notes

  • The OLAP Pivot table template requires at least one numeric field that is not a sequence key to use in the dataset/graph.
  • Fields that have been selected as sequence keys (dimensions) are not available to be totaled within the dataset/graph.
  • While the OLAP Pivot table will function correctly with just one sequence key, the true power of the OLAP Pivot table is numerous sequence keys. The more sequence keys you select as a developer, the more row/column dimensions your end users will have to select from at runtime.
  • If an end-user selects multiple row/column dimensions, they will be able to drill from the 1st dimension value, to a list of all values from the 2nd dimension that apply to the first, and so on.
  • By default, m-Power will automatically add one numeric field to the dataset/graph. This will always be the first numeric field listed in Field Settings that is not a sequence key.
  • m-Power will also add the first sequence key you have listed under “Sequencing” to the Row dimension, by default. If there is a second sequence key, it will be added to the Column dimension by default. All other sequence keys will be available for use in the “Available Dimensions” section found directly above the “Apply Changes” button.
  • If you would like to add more than one numeric field by default, you can follow a few easy steps to enable this.
    • Open m-Painter and search for “Begin Pivot Value Fields for Totaling” in the code.
    • Within this section, you will find each numeric field has its own line. Simply add checked=”checked” within each <input> tag.
    • Click Save. At runtime, each checked value will be totaled within your dataset/graphed.
  • If you would like to change the order of your row/column dimensions, you can also do this easily within m-Painter.
    • Open m-Painter and search for “Begin Available Dimension Fields” in the code.
    • Within this section, you will find a line for each dimension field that is not currently in use.
    • Cut the entire line and paste it directly after the “Begin Selected Column Dimensions” section to add it as a column dimension.
    • Similarly, you can paste it directly after the “Begin Selected Row Dimensions” section to add it as a row dimension.
    • Keep in mind that the order in which you place your dimensions matter. Adding Year then Month would give you a different output than would Month, then Year.
    • Click Save.