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
Get all SQL from TI processes
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Get all SQL from TI processes
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.
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.
-
- Posts: 21
- 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
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!
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!
-
- MVP
- Posts: 3117
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Get all SQL from TI processes
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.
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
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 132
- 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
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)