previous article
With the new function REPLACEEXPRESSION, arcplan 7 affords the possibility to influence the automatically generated SQL and MDX statement. This has the benefit that a design can continue to be made with “arrows” and that one is not limited to formulas. Currently, only simple adjustments of the query are possible with this tool; however, there is a large impact on the resulting possibilities.
The query from the Adventure Works SSAS Cube shown here identifies the internet turnover of the individual customers (sorted according to country, city, …) using a simple query. The generated MDX does not contain zero row suppression (expression NON EMPTY), so that customers without turnover will be shown in the result, as well. As the recipient of the report would like the zero rows to be suppressed, however, the report is supposed to be adjusted.
There are a number of ways to achieve this with arcplan. Here, however, it will explicitly be implemented with the new function REPLACEEXPRESSION for demonstration purposes.
In order to implement this requirement, the expression NON EMPTY must be integrated into the statement in the MDX in a suitable location. The function REPLACEEXPRESSION operates as follows:
REPLACEEXPRESSION((<Search term>;<Term to be replaced>;<Number of replacements, optional>)
Thus, the function behaves similarly to the search and replace function for texts featured in Word.
Process to Implement REPLACEEXPRESSION
- Identify the expression to be added
Insert “NON EMPTY” after “ON COLUMNS,” - Identify the anchor point for the search and replace function.
Tip: As the MDX is dynamically generated by arcplan, particular emphasis must be placed on unambiguousness so that the modification always leads to a correct result.
“ON COLUMNS,“ has been identified as anchor point. - Amend an individual field with the formula REPLACEEXPRESSION.
REPLACEEXPRESSION (“ON COLUMN,”; “ON COLUMNS, NON EMPTY”) - Use arrows to link to all objects which are to be influenced.
- Check whether all situations such as untying a knot etc. lead to a correct result.
Thus, in limited scope, the REPLACEEXPRESSION function allows the expansion of the arcplan standard when accessing the databank, which e.g. permits a better filtering of data in the databank, in particular in the MDX sector on SAP BW or SSAS, as compared to doing it only in arcplan (better performance, more possibilities to filter, statistic operations from the database…)
