analytics/configuration/xqe/olap.properties

# Licensed Materials - Property of IBM
# IBM Cognos Products: OQP
# (C) Copyright IBM Corp. 2005, 2020
# US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM corp.

# This properties file contains default configuration attributes for all
# OLAP data sources.  Any data source that is different must override
# the value in their own properties file.
#
# The following properties come from an example in the patent application
# entitled "MULTIDIMENSIONAL QUERY SIMPLIFICATION USING DATA ACCESS SERVICE 
# HAVING LOCAL CALCULATION ENGINE"

# V5 Version number
#   0: Pures Cognos 8.4 Behaviour (do not use XQE)
#   1: Cognos 8.4 Behaviour (where implemented)
#   2: Default, XQE Behaviour (for all but a few deltas)
v5.version=2

#
# V5 Summary - Header, footer and within detail/aggregate.
#
v5.summary.remote=true
v5.summary.local=true 
# remote  - process remotely
# local	  - process locally
# balance - In balanced mode the summary expression will attempt to use local processing
# for simple case only.
v5.summary.location=remote

#
# V5 Sort 
#
v5.sort.remote=true
v5.sort.local=true 
# remote  - process remotely
# local	  - process locally
# balance - In balanced mode the sort expression will attempt to use local processing
# for simple case only.
v5.sort.location=remote 

#
# Used to define if the provider could return some cell value type.
# The information used to discard (performance optimization) related V5 Edge Suppression when
# the provider cannot return such cell values. 
#
result.return.divbyzero=true
result.return.overflow=true


function.nonempty.remote=true 
function.nonempty.local=true 
# When execution location is set to 'both' NonEmpty is always pushed to the 
# remote source to try to reduce the size of the result returned however it 
# is then also be processed locally. This is because this data source has 
# non empty behavior which is inconsistent with the desired non empty behavior 
function.nonempty.location=both

function.crossjoin.remote=true
function.crossjoin.local=true 
function.crossjoin.location=local 
# Local crossjoins are used normally. However remote crossjoins are enabled 
# when the estimated size of a crossjoin result is greater than 100,000 tuples. 
function.crossjoin.threshold=100000 

function.intersect.remote=true 
function.intersect.local=true 
function.intersect.location=remote
# When an intersect contains a single member as one of its parameters the 
# local execution engine will attempt to resolve the intersect using 
# meta-data otherwise it will still be pushed to the remote MDX engine. 
function.intersect.singleMemberExecutionLocation=local 

function.filter.remote=true 
function.filter.local=true 
# In balanced mode the filter expression will attempt to use cached data or 
# meta-data information to locally evaluate the expressions, if the 
# parameters to the filter are not cached then the filter expression is 
# pushed to the remote engine. 

# FILTER( S1, <Condition>) Whether to execute this locally isn't just 
# dependent on the size of S1 but also the complexity of the <Condition>. 
# In cases where the <Condition> is complex it may be quicker to execute it 
# locally rather than ascertain all the dependencies of the <Condition> 
# expression (such as calculated members etc), especially if S1 is small. 
# So the cost of the filter condition and the set size are used to determine 
# the best execution location.
function.filter.location=balance

function.order.remote=true 
function.order.local=true 
# In balanced mode the order expression will attempt to use local processing
# for simple case only.
function.order.location=balance

# The Filter rewrite process looks at the set expression of a filter to 
# determine if it is of the form FILTER( CROSSJOIN( S1, S2), <Condition>) ) 
# if so it will rewrite the filter as CROSSJOIN( FILTER( S1, <Condition>), 
# FILTER(S2, <Condition>)) the second version will be quicker to process 
# locally or remotely. 
function.filter.enableRerite=true

function.aggregate.remote=true
function.aggregate.local=true 
function.aggregate.location=balance
# Aggregate(S1, <Numeric Expression>) Where local execution is possible, 
# that is when the correct aggregation method to use is known, this aggregate
# can be performed locally but only where S1 is small and the 
# <Numeric Expression> is simple.  Thus the cost of the numeric expression 
# and the approximate size of S1 must be determined in order to balance this 
# query. 
function.aggregate.localExecutionSetSizeThreshold=10000

function.generate.remote=true
function.generate.local=true
function.generate.location=remote

# Generate functions are computationally expensive.  The query execution engine 
# rewrites generate to remove unnecessary generates by converting them to 
# Crossjoins. Generate  rewrite is only safe to do in certain circumstances. 
# i.e. GENERATE( S1, CROSSJOIN( S1.CurrentMember, S2) )(where S2 does not 
# contain the dimension in S1)Can be better expressed as: CROSSJOIN(S1, S2) 
function.generate.enableRewrite=true 

# Each Top.. or Bottom.. set operations is very expensive if performed locally 
# hence these all always pushed to the remote database for processing 
function.topcount.remote=true 
function.topcount.local=true 
function.topcount.location=remote 

function.toppercent.remote=true 
function.toppercent.local=true 
function.toppercent.location=remote 

function.topsum.remote=true
function.topsum.local=true
function.topsum.location=remote

function.bottomcount.remote=true
function.bottomcount.local=true
function.bottomcount.location=remote

function.bottompercent.remote=true
function.bottompercent.local=true
function.bottompercent.location=remote

function.bottomsum.remote=true
function.bottomsum.local=true
function.bottomsum.location=remote

# This config determines whether to skip the removing of null suppression from the methods 
# RemoveEdgeNullSuppressionwhenReportSummary() and RemoveEdgeNullSuppressionWhenOuterNestedGroupSameHierarchy
# These two methods covers extremely unusual test cases in which if null suppression is enabled, there will be 
# an data integrity issue.
suppression.aggressive=false

#
# This configuration flag tell us whether to assign or not unreferenced hierarchies to calculated members.
#
unreferencedHierarchy.calculations=true

#
# Use Local OLAP (LOLAP) for these queries
#
v5.useLocalOLAP=false

#
# Use Local OLAP (LOLAP) for Set Alias
#
v5.useLOLAPForSetAlias=false

#
# Use Local OLAP (LOLAP) when different hierarchies from same dimension are in the query. 
#
v5.useLOLAPForDiffHierarchiesFromSameDimension=false

#
# Use Local OLAP (LOLAP) for functions
#
v5.useLOLAPForFunctions=false

#
# Use Local OLAP (LOLAP) for set expressions containing a value expression operand that is context dependent
# on something in the query other than itself.
#
v5.useLOLAPForContextDependentSet=false

#
# Use Local OLAP (LOLAP) for MDX with SAP unsupported CurrentMember.
#
v5.useLocalOLAPForUnsupportedCurrentmember=false

#
# Turn on sub Query planning for Local OLAP (LOLAP) Query Cache generation
#
v5.planSubQueryForLocalOLAP=false

#
# Use Single Axis Query for LOLAP provider queries
#
lolap.singleAxis=false

#
# For LOLAP providers to decide if producing MDX that selects individual members
# on an axis is worthwhile (vs. using a "dim/hier.MEMBERS" clause in the MDX produced
# to retrieve measure values).
# maxAxisSelectionsIgnoreRatio is a hard upper bound on the number of individual members 
# that can be selected. If it is set to -1, there is no upper bound.
# The heuristic is as follows:
# if (maxAxisSelectionsIgnoreRatio > -1 && selCount > maxAxisSelectionsIgnoreRatio ||
#		(selCount > maxAxisSelections && selCount > dim.getCardinality() * maxAxisSelectionsRatio))
# 		// Use a "dim/hier.MEMBERS" clause in the MDX produced.
lolap.maxAxisSelections=200
lolap.maxAxisSelectionsRatio=0.5
lolap.maxAxisSelectionsIgnoreRatio=-1
# 
# For LOLAP providers to decide if splitting a symmetric query for a requested set of tuples into
# multiple more precise queries is worthwhile.
# The heuristic for determining whether to split a requested set into multiple queries is:
# if (symmetricTuples - requiredTuples > minExcessTuplesForSplitQuery 
#		&& symmetricTuples / requiredTuples > excessTuplesRatioForSplitQuery)
# When either threshold is set to -1, a requested set of tuples is never split into multiple queries.
#
lolap.minExcessTuplesForSplitQuery=-1
lolap.excessTuplesRatioForSplitQuery=-1


#
# V5 master-detail optimization when allRows optimization is specified
#
v5.master-detail.transform=true
# false  - generating parameterized detail filter to detail query ( default )
# true	 - generating extra edge to detail query

#
# V5 master-detail optimization disregarding the state of the allRows optimization flag
# This has been introduced for debugging purposes since the allRows flag is not always 
# set in the V5.  Here is an explanation from Daniel Rousseau:
# The Report Server  tries to use request hint : executionOptimization value="allRows" whenever possible.  
# There are a number of cases where this optimization is disabled.  The main ones are:
# - When recordings are enabled, since the data has to be iterated over twice:  once to write it to file 
#		and once to render the report.
# - When the data is saved in the query cache (for the same reason as above).
# - During an interactive session, when the output format is HTML, HTML fragment, MHT, XHTML, XHTML fragment,
#		 raw XML or layout data XML.
# - In some cases where multiple formats need to be rendered and the formats include CSV or XML.
#
v5.master-detail.transform.allRowsOrNot=false
# false  - generating parameterized detail filter to detail query ( default )
# true	 - generating extra edge to detail query


#
# MDX Optimizations
# This configuration flags says whether the provider supports replacing the aggregate of child members with the parent
# when XQE believes this will be true.  This is not guaranteed to work for all providers, so this setting must default
# to false.
#
mdx.optimize.parentmember=false

#
# Force operators such as Cousin, ClosingPeriod, OpeningPeriod, ParallelPeriod, LastPeriod and PeriodsToDate 
# to apply on Ragged Hierarchies
#
engine.forceOperationsOnRaggedTimeHierarchies=false

#
# Null behavior of comparison operators (=, !=, <, <=, >, >=) in the MDX engine. 
# This should be set to "null" or "zero".
# When set to null, expressions such as "null < number" and "number < null" will evaluate to null.
# When set to zero, null is treated as zero.
#
null.comparison.operator=zero

#
# Null behavior of boolean operators (and, or, xor) in the MDX engine. 
# This should be set to "null" or "false".
#
null.boolean.operator=false

#
# Null behaviour in the MDX engine for the arithmetic operators: +, -. *, /, %
# When null.divide.denominator is set to zero, a number divided by null will return /0.
# When null.divide.denominator is set to null, a number divided by null will return null.
#
null.plus.operator=zero
null.minus.operator=zero
null.multiply.operator=null
null.divide.numerator=null
null.divide.denominator=zero
null.modulo.dividend=null
null.modulo.divisor=zero

#
# Null behaviour in the MDX engine vb functions (Abs, Int, Round).
#
null.vb.math.function=zero

#
# Null behavior of order function (TopCount, BottomCount) in the MDX engine. 
# Nulls treated like zero by default.
#
null.order.function=zero

#
# Null value position with sort function. This flag is associated with null.order.function. 
# null.order.function	null.position.order.function	behavior
# null		        first				null + first in the list
# null			last				null + last in the list
# zero			first				for numeric NULL: it's the less than zero larger than smallest negative number. This is the standard MDX behavior.
# 							For non-numeric null: null + first in the list
# zero			last				for numeric NULL: it's the greater than zero smaller than smallest positve number.
#							For non-numeric null: null + last in the list
null.position.order.function=FIRST

#
# Threshold 1: mdx.cjs.tupleToIndBitMap.threshold
# Threshold for the use of the temporary bit map tuples-to-indexes structure for 
# cross joined sets (cjs).
# This structure is used to save memory but it could slow down the execution.
# Threshold2: mdx.cjs.noTupleIndex.threshold
# Threshold for skipping the use of a tuples-to-indexes structure because the set is too
# big to index in memory
# Threshold 3: mdx.cjs.maxTupleMaterialization.threshold
# Multiplication factor used to determine whether a tuples-to-indexes structure
# that is optimized for many nested crossjoined sets (where iterating over the resulting
# tuples is expensive) should be used
#
mdx.cjs.tupleToIndBitMap.threshold=35000
mdx.cjs.noTupleIndex.threshold=10000000
mdx.cjs.maxTupleMaterialization.threshold=100


#
# Threshold controlling if result set cell storage should be in memory or in stream.
# Set it to 0 to disable using in stream cell storage
#
mdx.cjs.maxCellsInMemory.threshold=5000000

#
# When processing an MDX if a member is not found, by default the mdx engine
# throws an exception. Setting this parameter to "true", however, will trigger the
# engine to create a null member for that member and continue the processing.
#
mdx.useNullForNonExistingMember=false

#
# MDX Function Support
#
mdx.support.setalias=true

# 
# This property controls how ambiguous an ambiguous dimension identifier is resolved
# within the MDX engine, when the dimension has a single hierarchy.
#
# For example, with a Cube with a level unique name of [Sales].[Product].[Product],
# the identifer [Sales].[Product] could be referring to the level or to the hierarchy.
#
# This property can have the following values:
#
# level		- The identifier will be resolved as a level
# hierarchy - The identifier will be resolved as a hierarchy
#
mdx.level.hierarchy.ambiguity.resolution=level


# 
# V5 Member and Measure Detail Filter timing for the postAutoAggregation flag.
#  
#   reportDetails: Apply the filter along with Summary Filters to the details of the report.  This would mean it happens after OLAP functions such as TOPCOUNT, as an example.  
#   virtualDetails: Apply the filter to the virtual details cube at the lowest projected levels.  This would mean it happens before OLAP functions such as TOPCOUNT, as an example.  
#  
#   Not that OR filters between different hierarchies are virtualDetails by default and this is not yet configurable.
#  
v5.postAutoAggregation.timing=reportDetails

#
# Indicates if the catalog name is case insensitive or not.
#
olap.metadata.caseInsensitiveCatalog=false

#
# MDX Support for OLYMPIC rank
#
mdx.support.olympicRank=true

#
# MDX Support for OLYMPIC rank with implicit sort
#
mdx.support.olympicRankSort=true

#
# MDX supports re-aggregation for measure with non additive rollups
#
mdx.support.reaggregationNonAdditive=true

#
# This property controls how to handle the projection of nested levels of the same hierarchy.
# Specifically the use of the technique involving nested level decoration rules.
# In that case the planner queries only the lowest level members, later the post-processing queries the outer levels if required.
# - always: always use this technique
# - never: never use this technique
# - automatic: allow the planner to decide whether to use or not this technique.
#
applyNestedLevelDecorationRules=automatic

#
# Some providers accept incorrect names (aliases) in the query while returning the correct name.
# e.g. MSAS2005 accepts '[Time].[Year].&[1997]' but returns '[Time].[Year].[1997]'.
# This may cause a XQE-DEC-0016 error at execution time.
# This can only happen with:
# a) legacy reports which could be fixed by opening them in the corresponding studio and saving them again.
# b) defective models. 
# In both cases XQE would fix the problem at execution by running a secondary query.
# That fix would not be persistent, the problem will take place each time the report is executed
# and the solution enabled by this property slows down a bit the execution.
useCheckMemberRulesSecondaryQuery=false

#
# Force MDX operators like TopCount to be processed by the Database/Native MDX engine even when Local processing LOLAP is turned on
#
v5.enableTopCountFunctionsOptimization=false

#
# Force MDX operators like FilterSet to be processed by the Database/Native MDX engine even when Local processing LOLAP is turned on
#
v5.enableFilterSetOptimization=false

# If the dataset size exceeds the configurable threshold then allow the MDX operator to be processed by the 
# Database/Native MDX engine even when Local processing LOLAP is turned on
v5.topCountFunctionsOptimization.datasetSize=10000

# If the projected dataset size is less the configurable threshold then allow the MDX operator to be processed by the 
# Database/Native MDX engine even when Local processing LOLAP is turned on
v5.topCountFunctionsOptimization.projectedSetSize=500

#
# Some datasources like BW get the cardinality of the level at high cost, use the dimension cardinality unstead 
#
datasource.levelBasedDatasetCardinality=false

# Defer the evaluation of named set when Topcount/Filterset optimization is on for Local processing LOLAP
#
v5.deferNamedSetEvaluation=false

#
# This property controls the application of compensation for ragged hierarchies.
#
applyRaggedCompensation=true

#
# This property controls the application of compensation for unbalanced hierarchies.
#
applyUnbalancedCompensation=true

#
# This property tells whether the provider may filter the data before applying the MDX.
# It may take one of this values:
# - always: the provider always filter the data before applying the MDX,
# - never: the provider never filter the data before applying the MDX,
# - forNonEmpty: Only in the presence of MDX edge NON EMPTY the provider filters the data before applying the MDX.
#
datasource.appliesFilterBeforeMDX=never

#
# This property tells whether the MDX should be generated in a way this is better handled for non empty on the edge
# or non empty crossjoins.
# It may take one of this values:
# - always: always try to generate NON EMPTY friendly MDX
# - never: never try to generate NON EMPTY friendly MDX
# - forNonEmpty: only in the presence of MDX edge NON EMPTY
#
mdx.generateNonEmptyFriendlyMDX=forNonEmpty

# This property controls whether the MDX NON EMPTY clause should be used to assist the V5 edge suppression of nulls.
#
v5.suppress.nulls.useMDXNonEmpty=true

# For report with same hierarchy on row and column, reporter mode will put row and column into a single edge. This property controls the maximum number of edge groups in the single edge.
#
v5.reportermode.maxEdgegroupsPerEdge=300

# Capability key constant for the property that controls
# whether to restrict multi level set overlap with other sets in the same path.
#
restrictMultiLevelOverlap=true

#
# Allow an index in ancestor or descendants function to be converted to a level, regardless of ragged/unbalanced hierarchy condition.
#
convertIndexToLevelInAncDescFunc=false

#
# Optimize the descendants function to fetch descendants of a member directly.
#
OptimizeDescendantsFunctionInSAL=false

#
# Specify the default null expression behaviour for providers using the MDQuerySpec.
#
mdx.mdqs.NullExpressionBehavior=PPYBehavior

# Indicates if this provider adds an implicit NON EMPTY to each MDX edge.
#
mdx.suppression.DatasourceAppliesImplicitNonEmpty=false

#
# Does the provider support caching of named sets between queries in the local MDX engine.
#
mdx.support.namedSetCaching=false

# If true, arithmetic operations use an optimized algorithm that takes advantage of the null behavior defined
# If false, algorithm used for arithmetic operations is the same regardless of the null behavior 
enableArithmeticOperationsSparseProcessing=true

#
# Specify whether or not the provider requires the DefaultMember to be transformed into a NoMember.
#
mdx.useNoMemberDefaultMember=false