Applied Dimensionality


Posted at — Jul 8, 2012

It’s time to open-source / make available a utility I wrote more than a year ago. I originally intended to develop it  more whilst doing more TM1 projects, but my focus is mostly BI / DM these days, so I might as well just let it out and make changes by requests, if any. Enter TM1MN (hoped to make it TM1 Metadata Navigator).

Current version allows you to get such an Excel table from your TM1 model:

SourceType Source Target TargetType LinkType Link Details
pro plan_load_budget to_report_cube cub plan_Report pro CellPut
pro plan_load_budget to_report_cube dim plan_source pro SubsetCreate for subset Vplan_source_SSN SubsetElementInsert for subset Vplan_source_SSNSubsetDestroy for subset Vplan_source_SSN
cub plan ExchangeRate cub plan BudgetPlan DB DB line 11,15,19,23,27,31
cub plan ExchangeRate cub plan_Report DB DB line 5,9,13,17,21, 40,44,48,52,56
cub plan ExchangeRate cub plan BudgetPlan LineItem DB DB line 6,11,16,21,26
pro plan set_language pro plan set_language pro Calculated ATTR for Attribute vFromAttr Calculated AttrPut attribute vAttr
dim plan business_unit cub plan BudgetPlan Attr Attribute currency from stmt line 11, 15, 19, 23, 27, 31

And  (with additional hassle) a diagram like this:

How to use it:

  1. Download and unpack executables (source code is there as well)
  2. Run tm1mn.exe -i path\_to\_your\_TM1\_server -l log_file (or it leave blank to dump everything in console)
  3. Open graph_contents.csv file in Excel
  4. It also generates a dot graph representation of the model, so you can download graphviz and run dot graph_to_draw > graph.svg to get a scalable A3 picture of your model

Behind the scenes:

TM1, in contrast with Enterprise Planning, is very-very open, rules and TI are text files you can just read with any script. I really missed dependancy analysis (what rules / TI affect this cube and how) or even visualisation from EP Manager. So I started writing a script that will work through rule and process file and find “connections” between cubes, dimensions and data sources.

Most reasonable folks (as the guys at bihints) would’ve gone with simple “search for DB’s and ATTRS”, but it’s quite limited approach, since TM1 is pretty rich in syntax (you can have nested IF statements in DB target, for example). I thought that there should be a better way. Being damaged by CS education, I thought about writing a decent TM1 syntax parser (LR, for anybody who cares).

Easier said than done, but I’ve got a working pyparsing-based TM1 rules and processes parser. Originally I thought about doing quite a bit of automated model analysis (search for calculation cycles, search for missing/unnecessary feeder statements), so parsing results are recorded as a NetworkX oriented multi-graph, providing built-in graph operations (find shortest path, neighbours, connections).

Moreover, any NetworkX graph can be exported into DOT format, so it’s possible to use graphviz to generate nice model pictures (as here).

All that should’ve been tied to some sort of GUI to allow filtering cubes, interactive diagrams creation and yadi-yadi, but that’s the point where I got to my limits.  Can’t really do GUIs, as it turns out.

So I’m just dumping all graph contents into a csv file, so you can use Excel’s Autofilter magic for dependancy analysis. I’m routinely using this for any TM1 model analysis (like finding an overfed cube with Perfomance Monitoring and looking and what rules feed it).


comments powered by Disqus