TM1MN
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:
- Download and unpack executables (source code is there as well)
- Run
tm1mn.exe -i path\_to\_your\_TM1\_server -l log_file(or it leave blank to dump everything in console) - Open graph_contents.csv file in Excel
- 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).
Updates
- Update 1 (version 0.11): uploaded a corrected ) version of the script, with proper error handling.
- Update 2 (version 0.12): newer version, with better ASCIIOutput handling and TI variables names handling.
- Update 3 (version 0.13): Little more bugfixing and clean-up.
- Update 4 (version 0.14, 26/11/2012): Even more bugfixing and proper quoting for dot file edge labels.