Oh, something interesting is happening here. In the PAW MDX Editor, the interface is changing the colour of a word, AXIS in this case, to indicate it is a reserved word. But now, I'm wondering, how does it know that this word is an MDX reserved word?
A quick look in the chromium dev tools (Control-Shift-i) Network tab shows there is no traffic exchange to the server while I type in the MDX Editor window. Apparently, the MDX Editor already knows what to highlight, therefore the keywords to be highlighted must be in the cache somewhere.
I will pick the TM1SORT keyword to search through the browser cache because that word is specific to MDX in TM1, so it is unlikely to return many hits from the cache, unlike other keywords such as children or dimension.
Again in the chromium dev tools, press Control-Shift-F, TM1SORT in the input field and bullseye, just 1 hit in mdv.js.
You can see in that mdv.js file the creation of 2 new mime-types "text/x-tm1setmdx" and "text/x-tm1viewmdx". So we have the following keywords lists for tm1setmdx and tm1viewmdx.
- That list is not exhaustive, only keywords highlighted by the MDX Editor are shown, e.g. InStr or Tm1ToggleExpandMode will not be highlighted, but these functions actually work.
- Some keywords are grouped in subcategories according to their context
- Most keywords below are linked to their corresponding definition on the Microsoft MDX Reference website.
- Functions and Entity Properties listed below in italic are not yet referenced in the excellent TM1 MDX Reference written by George Tonkin (gtonkin) at https://www.tm1forum.com/viewtopic.php?t=16217
TM1 MDX for Sets and Views
Reserved Words for Statements
Data Manipulation Statements: CLEAR CALCULATIONS WITH MEMBER AS SELECT CELL NON EMPTY VISUAL ON COLUMNS ROWS PAGES SECTIONS CHAPTERS MEASURE FROM WHERE UPDATE PROPERTIES
Scripting Statements: CALCULATE FREEZE SCOPE
Data Definition Statements: CREATE CALCULATION SET SESSION GLOBAL CUBE USE_EQUAL_ALLOCATION USE_WEIGHTED_ALLOCATION USE_WEIGHTED_INCREMENT DROP GROUP GROUPING HIDDEN NOT_RELATED_TO_FACTS PROPERTY CURRENTCUBE TYPE
ACTIONPARAMETERSET AVERAGE BY CACHE CELLFORMULASETLIST COLUMN CREATEPROPERTYSET CREATEVIRTUALDIMENSION END ERROR FALSE FOR IGNORE INDEX NEST NO_ALLOCATION NULL PASS ROOT SORT STORAGE STRTOVAL THIS TOTALS TREE TRUE UNIQUE USE
Functions
Array Functions: SetToArray
Hierarchy Functions: Dimensions
Level Functions: Levels
Logical Functions: IsAncestor IsEmpty IsGeneration IsLeaf IsSibling
Member Functions: Ancestor ClosingPeriod Cousin LinkMember Members OpeningPeriod ParallelPeriod StrToMember ValidMeasure
Numeric Functions: Aggregate Avg CalculationCurrentPass CalculationPassValue CoalesceEmpty Correlation Count Covariance CovarianceN DistinctCount IIf LinRegIntercept LinRegPoint LinRegr2 LinRegSlope LinRegVariance LookupCube Max Median Min Predict RollupChildren Stddev StddevP Stdev StdevP StrToValue Sum Var Variance VarianceP VarP
Set Functions: AddCalculatedMembers Ancestors Ascendants Axis BottomCount BottomPercent BottomSum CrossJoin Descendants Distinct DrilldownLevel DrilldownLevelBottom DrilldownLevelTop DrilldownMember DrilldownMemberBottom DrilldownMemberTop DrillupLevel DrillupMember Except Extract Filter Generate Head Hierarchize Intersect LastPeriods Mtd NameToSet NonEmptyCrossjoin Order PeriodsToDate Qtd StripCalculatedMembers StrToSet Subset Tail ToggleDrillState TopCount TopPercent TopSum Union VisualTotals Wtd Ytd
String Functions: CoalesceEmpty Generate IIf Left LookupCube MemberToStr Right SetToStr TupleToStr UserName
Tuple Functions: StrToTuple
other functions: Call Ignore Rank
TM1 Functions
TM1BANGPREFIX TM1DRILLDOWNMEMBER TM1IGNORE_BADTUPLES TM1FILTERBYLEVEL TM1FILTERBYPATTERN TM1FORMAT_STRING TM1MEMBER TM1ROLLUP TM1RULEDERIVED TM1SORT TM1SORTBYINDEX TM1SUBSETBASIS TM1SUBSETALL TM1SUBSETTOSET TM1SUBSET_IDENT TM1TOGGLEDRILLSTATE TM1TUPLESIZE TM1UPDATE TM1UPDATEABLE
Entity Properties
AllMembers Children Count Current CurrentMember DataMember DefaultMember Dimension FirstChild FirstSibling Hierarchy Item Lag LastChild LastSibling Lead Level Levels Members Name NextMember Ordinal Parent PrevMember Properties Siblings UniqueName Value
TM1 MDX for Views only
Cell Properties
BACK_COLOR CELL_EVALUATION_LIST CELL_ORDINAL FORE_COLOR FONT_NAME FONT_SIZE FONT_FLAGS FORMAT_STRING FORMATTED_VALUE NON_EMPTY_BEHAVIOR SOLVE_ORDER VALUE
Member Properties
CALCULATION_PASS_DEPTH CALCULATION_PASS_NUMBER CONDITION DISABLED
CATALOG_NAME CHILDREN_CARDINALITY DESCRIPTION DIMENSION_UNIQUE_NAME HIERARCHY_UNIQUE_NAME LEVEL_NUMBER LEVEL_UNIQUE_NAME MEMBER_CAPTION MEMBER_NAME MEMBER_TYPE MEMBER_UNIQUE_NAME PARENT_COUNT PARENT_LEVEL PARENT_UNIQUE_NAME SCHEMA_NAME MEMBER_GUID MEMBER_ORDINAL CUBE_NAME
Flags
SELF AFTER BEFORE BEFORE BEFORE_AND_AFTER SELF_AND_AFTER SELF_AND_BEFORE SELF_BEFORE_AFTER LEAVES
Essential reference guides for MDX in TM1
gtonkin's guide https://www.tm1forum.com/viewtopic.php?t=16217
Wim Gielis' MDX examples https://www.wimgielis.com/tm1_mdxstatements_EN.htm
MDX IBM TM1 Reference https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=support-microsoft-defined-mdx-expressions-functions
Microsoft MDX Language Reference https://learn.microsoft.com/en-us/sql/mdx/mdx-language-reference-mdx?view=sql-server-ver16
As per usual with undocumented features, use at your own risk and all the yada yada yada that goes with it. There is no guarantee that any of these functions actually work as expected according to the Microsoft's original MDX reference documentation.
For now, I just dump these keywords here, and that material will probably lead to a new series of posts about MDX in TM1.
Python script I used to grab the keywords from the tm1forum MDX reference tables and compare which are missing:
import requests
from bs4 import BeautifulSoup
mdv = "AllMembers Children Count Current CurrentMember DataMember DefaultMember Dimension FirstChild FirstSibling Hierarchy Item Lag LastChild LastSibling Lead Level Levels Members Name NextMember Ordinal Parent PrevMember Properties Siblings UniqueName Value"
mdv = mdv.upper()
# fetch on tm1forum gtonkin's MDX reference page
url = "https://www.tm1forum.com/viewtopic.php?t=16217"
response = requests.get(url)
# Parse HTML
soup = BeautifulSoup(response.content, "html.parser")
# Find all tables
tables = soup.find_all("table")
# Loop through the tables in that html document and extract the first column that should contain MDX keywords
words = []
for table in tables:
rows = table.find_all("tr")
for row in rows:
cells = row.find_all("td")
if len(cells) > 0:
words.append(cells[0].get_text().upper())
print(f"All MDX words documented on tm1forum:\n{words}" )
print(f"Still undocumented:\n{sorted(set(mdv.split()).difference(set(words)))}" )
Add new comment