BI Architect expressions
I work a lot with IRIS BI, but some features are still a mystery to me. There is a great feature here that allows you to create expressions in Architect.
I'm already very familiar with it, but I still don't quite understand what environment it uses.
It can execute system classes like $SYSTEM.SQL, $PIECE(), %cube, %source(), it can do string concatenation, call methods from another classes.
I have two questions actually.
What environment it uses to execute this? Is it ObjectScript, SQL, MDX or maybe even its own? I need to know this to understand the mechanics behind it, how the Architect work
Second, what are the FULL list of what it can do and what it can not.
I looked through the "wonderful" documents on this and found several pieces of information scattered over 3-5 pages.
Comments
Hi @Dmitrij Vladimirov ! This is pure ObjectScript AFAIK in an expression mode (think of it if you create a classmethod in ObjectScript in Expression mode), with options of %source to reference base class properties and %cube to reference Cube class methods.
Thanks for the information, but I would like to see more comprehensive documentation. Such feature rich application and no solid docs anywhere. I think i would create an idea on Ideas Portal about it.
I agree, makes total sense. Documentation also have a feedback button on every page IIRC
When building data for a cube, either a full build or a synchronization, think about it this way. The code generated to support this essentially does a SELECT AllOfYourDimensionsMeasuresRelationships FROM SourceTable.
when you have non primary field references these expressions are evauluated as ObjectScript expressions.
These are documented but not on a single page
%sourceID although this is typically thought of being used in a detail listing specifically if you chose the option for whereas the other's are when defining the cube dimension, measures etc
If it is pure ObjectScript how to write IF ELSE statement in %expression for example? Or can i use RETURN stetement?
There are situations when you need to return a specific value. The Range expression doesn't work, for example, when you need to determine the display value for Null.
So there's only one way
IF 'value {return "Value is False"} else {return "Value is True"}
But this is wont work
I can still define a class method inside the cube and reference it from within, for example, %cube.CustomLogicClass(%source.DBValue), but that seems redundant. It leaves my cube looking cluttered with simple functions containing only if-else logic.
Suggestion: get some lessons with ObjectScript
then you will find that instead of
IF 'value {return"Value is False"} else {return"Value is True"}you get an expression by
$select(value:"Value is True",1:"Value is False")https://docs.intersystems.com/iris20191/csp/docbook/Doc.View.cls?KEY=RCOS_fselect
Thanks for sharing, @Stephen Canzano!
%expression - is a really impactful feature that can greatly help to tune performance while cubes building.