Adapt Mondrian schemas to work with Analyzer
Use the workflow outlined in the steps below to adapt your Mondrian schemas to work properly with Analyzer:
- Apply related date filters.
- Adjust for calculated members and named sets.
- Edit the schema to allow comparison operators on numeric dimension levels.
- Add geo map support.
- Disable drill-through links.
- Configure custom Analyzer actions.
Set the Available field list, folder description, and cell format
You can define fields to hide or show users, add folder descriptions, and render cell formats in an Analyzer report using the annotations described in the table below.
Annotation | Description | Mondrian Schema Element | Value |
AnalyzerBusinessGroup | Overrides the folder name for this field in the Available fields list. The default folder for a field is the hierarchy or dimension name. | Folder name | |
AnalyzerBusinessGroupDescription | Set the text for a Tool Tip when a user hovers the mouse pointer over the folder. | Description | |
AnalyzerHideInUI | Hides a field in the Available fields list. | true or false | |
AnalyzerFieldListView | Set on cube to set the default view of the Available fields list. | CMDVIEWCATEGORY, CMDVIEWNAME, CMDVIEWTYPE, and CMDVIEWSCHEMA | |
AnalyzerShowFormattedValue | For an Excel export, this renders the cell as a formatted string literal, instead of the raw value and the Excel format string. | true or false |
Step 1: Apply relative date filters
Analyzer supports many types of relative date filters, but in order to apply them for a given level, you need to define the format string used to construct MDX members for that level. This is because each data warehouse implementation may have a different date format and set of hierarchy levels.
Common relative date filters
In the Steel Wheels sample data cube provided by Pentaho for evaluation and testing, the Month level uses abbreviated three-letter month names. Furthermore, the Month level sits under the Quarter level. In Steel Wheels, the format string for an MDX member from the Month level would look like this:
[yyyy].['QTR'q].[MMM]
Some other common date formats:
[yyyy]
(Year)[yyyy].[q]
(Quarter)[yyyy].[q].[M]
(Month)[yyyy].[q].[M].[w]
(Week)[yyyy].[q].[M].[w].[yyyy-MM-dd]
(Day)
The Day line, above, also specifies a format to represent the entire date. Without this
format, a simple [d]
parameter would be difficult to put into context. For
more information on date format strings, refer to the SimpleDateFormat page on the ICU Project site.
To set up relative date filtering, for each level, you need to do the following:
- In your Mondrian schema file, set the levelType XML attribute to TimeYears, TimeMonths, TimeQuarters, TimeWeeks or TimeDate.
- Define the MDX date member format as an annotation with the name AnalyzerDateFormat.
Here is an example from the Pentaho sample data (Steel Wheels) Time dimension:
<Level name="Years" levelType="TimeYears" ... > <Annotations><Annotation name="AnalyzerDateFormat">[yyyy]</Annotation></Annotations> </Level> <Level name="Quarters" levelType="TimeQuarters" ... > <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].['QTR'q]</Annotation></Annotations> </Level> <Level name="Months" levelType="TimeMonths" ... > <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].['QTR'q].[MMM]</Annotation></Annotations> </Level>
Relative date filters for weeks
Use relative date filters for weeks to control the starting day of the week, the
number of days comprising the first calendar week, and the starting calendar week of each
year. If your week number and week year are required to be ISO8601 week-date compliant, or
if you need to account for cultural time-keeping differences, you can customize how week
numbers and week years are generated for the w
and YYYY
format strings.
Configure the following filter.relative.dates.week properties in the analyzer.properties file:
Property | Description | Default value |
filter.relative.dates.week.firstDayOfWeek | Specifies the first day of the week. Values are Sunday=1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, and Saturday=7. | 2 |
filter.relative.dates.week.minimalDaysInFirstWeek | Specifies the number of days considered to define the first week of a year. The ISO8601 standard requires 4 days to be present before counting the first week. | 4 |
filter.relative.dates.week.firstWeekOnJanuary1st | Specifies the week number so that the first week of the calendar always starts on January 1st. When this property is set to true, the week year and the calendar year are the same. | false |
filter.relative.dates.week.firstWeekOnJanuary1st.fromOne | If filter.relative.dates.week.firstWeekOnJanuary1st=true
and the first week does not have the minimal number of days, this property specifies
if week numbering should start at 0 or 1 . | false |
For example, an alternative method for week numbering commonly used in the United States starts week 1 of every year on January 1st with the first day of all subsequent weeks starting on a Sunday. For this method, configure the following properties with the associated values:
- filter.relative.dates.week.firstDayOfWeek=1
- filter.relative.dates.week.minimalDaysInFirstWeek=4
- filter.relative.dates.week.firstWeekOnJanuary1st=true
- filter.relative.dates.week.firstWeekOnJanuary1st.fromOne=true
YYYY
)
uses a different format string than the calendar year (yyyy
). For
example, in the ISO8601 week date, the date 2018-12-31 belongs to week 2019-W1, so the
calendar year is 2018 and the week year is 2019. In the previous United States week
example, the calendar year and the week year are always the same, so the format string may
use yyyy
or YYYY
.Other relative date filters
Other types of relative date filters are often used, especially for the fiscal year in the business sector. A fiscal year varies with each business and is based on how that business calculates its annual financial statements. You can define a Fiscal Calendar dimension in your Mondrian schema to accommodate this calculation, so Analyzer uses the current date to look up fiscal time periods in the fiscal time dimension.
For example, a business may define their fiscal year to always start on the first of May. Their fiscal time dimension table would look like the following table:
Date | Fiscal Week | Fiscal Month | Fiscal Quarter | Fiscal Year |
2014-04-30 | 2014-W53 | 2014-M12 | 2014-Q4 | 2014 |
2014-05-01 | 2015-W1 | 2015-M1 | 2015-Q1 | 2015 |
2014-05-02 | 2015-W1 | 2015-M1 | 2015-Q1 | 2015 |
Looking at the table and using a date such as 2014-05-01, we can find which Fiscal Week, Fiscal Month, Fiscal Quarter, or Fiscal Year that it belongs to. Just look for the date in the table, then look further up the hierarchy to find 2015-M1. If you need to get the Current Month and Previous Month, you can first find 2015-M1 and then look back on the hierarchy to find 2014-M12, which is a sibling of 2015-M1 in the hierarchy.
There are a few key points to keep in mind about this dimension, before you get started:
- The bottommost level must be a Date, which will be used to look up a parent-level member based on the current date.
- The Date level must specify a new AnalyzerFiscalDateFormat annotation. This annotation value should specify a Java format string, which when evaluated with the current date, yields the MDX name of the Date level member. This format string should not include the format string for any parents above the Date level. This is different from the AnalyzerDateFormat annotation in which parents are also included in the format string.
- The Date level members must be unique within the level, so uniquemembers is set to true. This does not need to be the same for parent levels, but it is a good practice to do so since this is a time dimension.
- All levels in this hierarchy need to specify the levelType attribute.
- Levels above the Date level should not specify the AnalyzerDateFormat annotations.
Here is an example of a Fiscal Calendar dimension defined within a Mondrian schema:
<Dimension name="Fiscal Calendar" type="TimeDimension"> <Hierarchy hasAll="true" primaryKey="DATE_KEY"> <Table schema="FOODMART" name="CALENDAR"/> <Level name="Fiscal Year" levelType="TimeYears" column="FSC_YEAR_STR" uniqueMembers="true" type="String" ordinalColumn="FSC_YEAR" /> <Level name="Fiscal Quarter" levelType="TimeQuarters" column="FSC_QUARTER_YEAR_STR" uniqueMembers="true" type="String" ordinalColumn="FSC_DIM_QUARTER_NUM" /> <Level name="Fiscal Month" levelType="TimeMonths" column="FSC_MONTH_YEAR_STR" uniqueMembers="true" type="String" ordinalColumn="FSC_DIM_MONTH_NUM" /> <Level name="Fiscal Week" levelType="TimeWeeks" column="FSC_WEEK_YEAR_STR" uniqueMembers="false" type="String" ordinalColumn="FSC_DIM_WEEK_NUM" /> <Level name="Date" levelType="TimeDays" column="CAL_DATE" uniqueMembers="true" type="Date" ordinalColumn="DATE_KEY" > <Annotations><Annotation name="AnalyzerFiscalDateFormat">[yyyy-MM-dd]</Annotation></Annotations> </Level> </Hierarchy> </Dimension>
With this set up, Analyzer will be able to generate the MDX to turn a filter like Current Month into the correct Fiscal Month member:
Ancestor([Fiscal Calendar].[Date].[1997-06-28],[Fiscal Calendar].[Fiscal Month])
This MDX references a specific date member in the Date level, and then uses the Ancestor function to locate the parent month. Finding the Previous Month would be as simple as using the Lag MDX function:
Ancestor([Fiscal Calendar].[Date].[1997-06-28],[Fiscal Calendar].[Fiscal Month]).Lag(1)
Once you have these set up, your users will be able to apply this filter by selecting Choose a commonly used time period in the Filter on Fiscal Month dialog box.
Verify the relative date filter values
Procedure
In Analyzer, click the More actions and options icon.
Go to
.Click Check AnalyzerDateFormat.
Results
Configure default values in filter dialog
When filtering on a level, by default, the filter dialog box will show the first N
values as configured by the filter.members.max.count
property. However, if
you want to show a relative range of time periods, you can set the AnalyzerDateFilterStart and AnalyzerDateFilterEnd annotations to specify a range of time periods relative
to the current date. For example, if for the Date level, you want to show the Last 30 Days,
Today and Next 7 Days, you can set AnalyzerDateFilterStart
to -30 and AnalyzerDateFilterEnd to
7. AnalyzerDateFilterStart can
contain a negative or zero integer indicating a previous time period or the current time
period as a starting range. AnalyzerDateFilterEnd can
contain a zero or positive integer indicating a current or future time period as an ending
range.
When using the range filter, you can also set the default value for the Before, After and Between operators using the AnalyzerDateFilterDefaultStart and AnalyzerDateFilterDefaultEnd annotations. For example, if you want to set the Before, After and the Between Start and End values to the Current Date, you can set AnalyzerDateFilterDefaultStart and AnalyzerDateFilterDefaultEnd to 0. The values specified for AnalyzerDateFilterDefaultStart and AnalyzerDateFilterDefaultEnd must be within the range specified by AnalyzerDateFilterStart and AnalyzerDateFilterEnd.
Step 2: Adjust for calculated members and named sets
If your Mondrian schema defines calculated
members or named sets that reference MDX members without the dimension prefix, then you must
set mondrian.olap.elements.NeedDimensionPrefix
to
false in your mondrian.properties file. Under
all other conditions you would set this property to true because it
increases Mondrian performance, as well as the readability of the schema XML file.
Step 3: Edit the schema to allow comparison operators on numeric levels
Set the level type in the Mondrian schema to either Numeric or Integer to enable the comparison operators, as shown below:
<Dimension foreignKey="CUSTOMERNUMBER"
name="Credit Limit"> <Hierarchy hasAll="true" primaryKey="CUSTOMERNUMBER">
<Table name="CUSTOMER_W_TER"> </Table> <Level name="Credit
Limit" levelType="Regular" column="CREDITLIMIT" type="Numeric" uniqueMembers="true"
hideMemberIf="Never"> <Annotations> <Annotation
name="AnalyzerBusinessGroup">Customers</Annotation></Annotations>
</Level> </Hierarchy> </Dimension>
MDX performance
The performance of these comparison operators is highly dependent on the following Mondrian property:
# Max number of constraints in a single 'IN' SQL clause. mondrian.rolap.maxConstraints=1000
Under ideal conditions, the join between the numeric level and other report levels is done in the database so that only tuples with data are processed in Mondrian. This can only occur if, after applying the comparison operator, the number of members is less than the maxConstraints setting.
Step 4: Add Geo Map support to a Mondrian schema
The Geo Map visualization in Analyzer requires both a Geo Service that provides coordinate data (delivered through a Pentaho Server Pentaho-geo plugin), and special Mondrian schema annotations and member properties.
Only the levels marked with Geographical roles (via annotations) can be added to a Geo Map visualization in Analyzer. During the rendering process, the visualization will call the Pentaho-geo plugin in the Pentaho Server to look up coordinates that correspond with the level.
Annotations
First, find all levels that describe location data, then add the appropriate annotations as shown and explained below:
<Level name="CITY" column="CITY" type="String" uniqueMembers="false"> <Annotations> <Annotation name="Data.Role">Geography</Annotation> <Annotation name="Geo.Role">city</Annotation> </Annotations> </Level>
Data.Role: Indicates the type of level. Presently, the only valid data role type is Geography.
Geo.Role: Specifies the geographical classification (city, state, zip, etc.). While there are built-in types used in the Data Source Wizard and PDI modelers, the values are arbitrary. You could specify a "city" type and, as long as the service provides data for this classification, it will work.
Member properties
In addition to retrieving coordinates from the Geo Service, the location Geo.Role value defines a level with member properties supplying latitude and longitude values. Levels that are tagged with location must also provide two member properties with the exact names of Latitude and Longitude that point to the column in the database which contains these values for the level.
<Level name="LatTest" column="CUSTOMERNUMBER" type="Numeric" uniqueMembers="false"> <Annotations> <Annotation name="Data.Role">Geography</Annotation> <Annotation name="Geo.Role">location</Annotation> </Annotations> <Property name="Latitude" column="CUSTLAT" type="Numeric" /> <Property name="Longitude" column="CUSTLON" type="Numeric"/> </Level>
Step 5: Disable drill-through links
Procedure
Global Disable: Perform these steps to disable drill-through links for all cubes and analysis data sources.
Open your analyzer.properties file with a text editor and find the property called report.drill.links.disabled.
Set the value from false to true as shown here.
From:
report.drill.links.disabled=false
To:
report.drill.links.disabled=true
Individual Cubes: Perform these steps to disable drill-through links on a cube-by-cube basis.
Open the schema for the particular cube on which you want to disable drill-through links.
Find the Cube element in the schema file and add this annotation to disable the links:
<Annotations><Annotation name="AnalyzerDisableDrillLinks">true</Annotation></Annotations>
Save and close the schema.
Step 6: Configure custom Analyzer actions
Analyzer can be configured with custom action links that call out to JavaScript functions. These action links are available in a context menu by right-clicking on level members or measure cells.
Define custom actions in Mondrian
Action links can be defined in your Mondrian schema as annotations. These can be defined under a Level or a Measure. There is no limit to the number of custom action links that you can define, but they need to be named in ascending order, such as AnalyzerCustomAction, AnalyzerCustomAction2, AnalyzerCustomAction3.
The annotation value is just a link-label and JavaScript function, separated by a comma. Analyzer will automatically try to add custom action links on a Type level or a Sales measure whenever they are used in a report.
Annotation defined on a Type level:
<Dimension foreignKey="STATUS" name="Order Status"> <Hierarchy hasAll="true" allMemberName="All Status Types" primaryKey="STATUS"> <Level name="Type" column="STATUS" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"> <Annotations> <Annotation name="AnalyzerCustomAction">Custom action 3,customHandlerThree</Annotation> <Annotation name="AnalyzerCustomAction2">Custom action 4,customHandlerFour</Annotation> </Annotations> </Level> </Hierarchy> </Dimension>
Annotation defined on a Sales measure:
<Measure name="Sales" column="TOTALPRICE" formatString="#,###" aggregator="sum" description="Foo"> <Annotations> <Annotation name="AnalyzerBusinessGroup">Measures</Annotation> <Annotation name="AnalyzerCustomAction">Custom action 1,customHandlerOne</Annotation> <Annotation name="AnalyzerCustomAction2">Custom action 2,customHandlerTwo</Annotation> </Annotations> <CalculatedMemberProperty name="CHART_SERIES_COLOR" value="#0d8ecf" /> </Measure>
Implement a custom action JavaScript function
In order to implement the JavaScript function,
you need to create a new Pentaho
plugin that injects your JavaScript functions into Analyzer. Here is an example of an
analyzer_extension_plugin.xml
:
<?xml version="1.0" encoding="UTF-8"?> <plugin title="analyzer-extension"> <static-paths> <static-path url="/analyzer-extension/resources" localFolder="resources"/> </static-paths> <external-resources> <file context="analyzer">content/analyzer-extension/resources/analyzer_extension_plugin.js</file> </external-resources> </plugin>
This basically tells the Pentaho Server to inject the analyzer_extension_plugin.js file into Analyzer so that those functions are now available to Analyzer to call when a user clicks on a custom action link.
Here is an example analyzer_extension_plugin.js.
cv.extension = cv.extension || {}; /** * report - Analyzer report definition. * formula - The level or measure that was clicked on. * ctx - All levels that intersect on the clicked on level or cell. * filter - Filters applied on the report. Only includes filters which includes members. */ cv.extension.customHandlerOne = function (report, formula, ctx, filter) { var year = ctx['[Time].[Years]']; // Returns the member unique name if (year) year = cv.util.parseMDXExpression(year); // Extract the name of the member var url = window.CONTEXT_PATH + "api/repos/:public:Steel%20Wheels:Country%20Performance%20(heat%20grid).xanalyzer/viewer?yearParameter=" + year; if (window.parent && window.parent.parent && window.parent.parent.mantle_openTab) { window.parent.parent.mantle_openTab("Custom One", "Custom One", url); } window.open(url); } cv.extension.customHandlerOne_validate = function (report, formula, ctx, filter) { var territory = ctx['[Markets].[Territory]']; if (territory == "[Markets].[Japan]") return false; return true; }
You must define your custom action JavaScript function under the cv.extension namespace. The name of the JavaScript function must exactly match the name you used in the AnalyzerCustomAction annotation. The function requires four parameters:
- report: This is the Analyzer report object.
You normally will not use this, but if you want to access the report XML definition to
inspect the state of the current report definition, you can access
report.reportDoc
. - formula: This is either the level MDX unique name or the measure unique name, depending on what the user clicked on.
- ctx: This is a map of all the levels on the row or column zone and their corresponding MDX members. When clicking on a cell, this map will contain all row and column levels on the report. When clicking on a level member, this map will only contain outer levels which are usually to the left or above the clicked-on level.
- filter: This is a level map-to-filter operator-to-member array of all report filters with the exception of numeric filters like Top10 or Greater than.
The filter object is a map of levels to predicate objects. A predicate object is a map of predicate operators to operator arguments. A single level such as [Customer].[Name] may have more than one predicate operator, such as contains "John" but does not contain "Doe".
The possible operators are: EQUALS, NOT_EQUAL, BEFORE, AFTER, BETWEEN, CONTAIN, and NOT_CONTAIN. For all operators with the exception of CONTAIN and NOT_CONTAIN, the operator arguments are MDX members such as [Time].[2014]. CONTAIN and NOT_CONTAIN have string literals as operator arguments. Numeric filters such as 'Top 10 Account by Sales' are not exposed in the filter object.
As an example, assuming the user clicks in the cell, then the member,
ctx
, and filter arguments will look like:
ctx: Object [Markets].[Territory]: "[Markets].[APAC]" [Measures].[MeasuresLevels]: "[Measures].[Sales]" [Order Status].[Type]: "[Order Status].[Shipped]" [Time].[Years]: "[Time].[2003]" __proto__: Object filter: Object { '[Product].[Line]': {EQUALS:['[Product].[Trucks and Buses]','[Product].[Trains]','[Product].[Planes]']} , '[Time].[Years]': {EQUALS: ['[Time].[2013]','[Time].[2014]']} } formula: "[Measures].[Sales]"
Here are a couple of helpful tips for implementing the JavaScript functions:
- You can use
cv.util.parseMDXExpression
to extract the name of the member. For example,[Year].[2013]
would return: 2013. - You can construct your own URL and then open the URL in a new PUC tab, assuming Analyzer is running within PUC with the function:
window.parent.parent.mantle_openTab
.
Determine when to show a custom action
There is also another feature to validate
whether a custom action link should be included in the context menu or not. You can
implement a validation function which returns false to hide the link in the UI. If this
validation function is not implemented, then the link will always be shown. This validation
function must be named by suffixing the custom action JavaScript function name with
_validate
.
In this example, the Custom action 1 menu item will not be included
if the user right-clicks on a Measure cell where the current context includes
Territory: Japan:
cv.extension.customHandlerOne_validate = function (report, formula, ctx, filter) { var territory = ctx['[Markets].[Territory]']; if (territory == "[Markets].[Japan]") return false; return true; }
Notice how Custom action 1 was not included in the above menu.