TM1 Forum

Hi

We're preparing a migration of our Datawarehouse system which is the source of a lot of the data in TM1 (via SQL). To get an idea of how hard the migration will be we want to analyze all SQL to look for system-specific SQL-dialect and stuff like that.

Now instead of looking through all processes in TM1 and manually copy the SQL from there it would be practical if that could be done using a script that creates a text-file with the SQL for each process containing SQL. Has somebody written something like this that you would like to share?


Thanks in advance
Joris Pieters

It shouldn't be too difficult to do via offline script as the .pro files are plain text.

The properties you need to inspect are
562: DatasourceType
566: DatasourceQuery

It is important to include a filter to only include processes where datasource type = "ODBC". (Due to the way TM1 handles memory and saving of .pro files you can end up with artifacts if a process was saved as and the data source changed. So you can have processes with no data source or of another type that still have property 566 filled.)

This would also be really easy to do with TM1py. Just iterate the processes list, read the DatasourceQuery property and write it out somewhere.

Thanks

I'll try that. As I'm looking at the pro-files it indeed seems to be in the 566 field. Thanks for mentioning to look at 562 first!

From the 'Useful code, tips and tricks' section:

http://www.tm1forum.com/viewtopic.php?f=21&t=13373

Note that the Prolog tab can still overwrite certain settings like the one in 566 and 562.
So you need to take that into account as well.

That's why externalizing the SQL Code is a good practice. For all our processes, we have a Control Cube where we keep the Data Source Type and also the Data SOurce Query (which will be SQL Query for processes with ODBC Data Type, MDX Statement or a saved subset name for Processes using a Dimension as Data SOurce, CubeView Name or CubeView Definition, for processes using a CubeView as Data Source)