Creating an OLAP overlay is straightforward. The Hierarchy tab of the logical model provides an interface to create the overlay. Entities and attributes are listed in the left pane in a tree structure.
To add a dimension to the overlay, simply drag an individual attribute from the ‘Entities’ panel to the ‘Dimensions’ panel. You can also drag an entire entity to the ‘Dimensions’ panel, in which case all attributes of that entity are added as a dimensional hierarchy. The order of dimensions inside each dimensional hierarchy is significant, as this is the order of the drilling hierarchy.
When creating dimensions, the Date dimension should be given special attention, since breaking a single date field into individual components can drastically increase usability for the end user. Additionally, mapping date components into business attributes can be a powerful usability enhancement, for example, mapping the month of a date field so that June becomes “calendar month 1,” and so on.
 |
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software. |
To create new date components of this kind, you can define expression attributes in the Model tab. These expression attributes will then be available for use in the hierarchical overlay. For example, to create a new ‘Month’ attribute in which June is “calendar month 1,” you could define a new expression attribute with the formula
where ‘orders.order_date’ is the existing date column. The particular formula would need to use SQL syntax and functions that are appropriate to the underlying database.
Editing Dimension and Measure Properties
You can change the names of dimensions and measures from the ‘Properties’ panel of the Hierarchy tab. Additionally, the aggregation method for each measure can be specified using the ‘Aggregate’ menu.
The aggregation method determines how a particular measure is summarized at the different levels of dimensional granularity. For example, consider the case where you have the hierarchically-related dimensions ‘Month’ and ‘Day’, and a measure ‘Cost’. If you aggregate the measure by ‘AVG’, then the value of the measure when viewed along the ‘Month’ dimension will be “average cost per month,” while the value of the measure when viewed along the ‘Day’ dimension will be “average cost per day.” Likewise, if you aggregate the measure by ‘MIN’, then the value of the measure when viewed along the ‘Month’ dimension will be “minimum cost per month,” while the value of the measure when viewed along the ‘Day’ dimension will be “minimum cost per day.”
Continued: Materialized OLAP Overlays