Get all SQL from TI processes

Post Reply
Joris
Posts: 18
Joined: Wed Sep 30, 2015 2:19 pm
OLAP Product: TM1
Version: 10_2_2
Excel Version: 2010

Get all SQL from TI processes

Post by Joris » Tue Apr 03, 2018 7:19 am

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

lotsaram
MVP
Posts: 3094
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Get all SQL from TI processes

Post by lotsaram » Tue Apr 03, 2018 8:47 am

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Joris
Posts: 18
Joined: Wed Sep 30, 2015 2:19 pm
OLAP Product: TM1
Version: 10_2_2
Excel Version: 2010

Re: Get all SQL from TI processes

Post by Joris » Tue Apr 03, 2018 8:58 am

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!

Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Get all SQL from TI processes

Post by Wim Gielis » Tue Apr 03, 2018 1:16 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

tm123
Posts: 113
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Get all SQL from TI processes

Post by tm123 » Tue Apr 03, 2018 2:36 pm

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)

Post Reply