Audit tool for workbooks
Posted: Wed Sep 17, 2014 12:56 pm
Hi,
As our models grow and more TM1 servers are added to the environment we find ourselves needing to be able to (relatively quickly) audit all workbooks in the system to see where particular subsets be getting used. Example:
When we change a subset definition we are not sure what workbooks are affected by this change and so need to be able to search through all workbooks prior to making the subset change to know if the proposed change is suitable for all reports making use of the current subset definition. I researched this in the past and someone suggested File Locator Pro which is a brilliant tool but takes a lot of excel manipulation of the output to be able to sort through the text to find what subsets are used in what dimensions and in what tabs of the workbook concerned.
So it would look through all tabs of the workbook for a string like
=TM1RPTROW($C$5,TM1Server&":ABC","SubsetName","","Alias")
and return "SubsetName" in dimension "ABC".
Or =SUBNM(TM1Server&":XYZ","Subset2","")
and return "Subset2" in dimension "XYZ"
Hopefully it would point out what tab in the workbook it came from as well.
If anyone out there has any idea of a useful tool that is able to interrogate existing workbooks for subsets used I would be interested to know what you've come up with. Many thanks.
As our models grow and more TM1 servers are added to the environment we find ourselves needing to be able to (relatively quickly) audit all workbooks in the system to see where particular subsets be getting used. Example:
When we change a subset definition we are not sure what workbooks are affected by this change and so need to be able to search through all workbooks prior to making the subset change to know if the proposed change is suitable for all reports making use of the current subset definition. I researched this in the past and someone suggested File Locator Pro which is a brilliant tool but takes a lot of excel manipulation of the output to be able to sort through the text to find what subsets are used in what dimensions and in what tabs of the workbook concerned.
So it would look through all tabs of the workbook for a string like
=TM1RPTROW($C$5,TM1Server&":ABC","SubsetName","","Alias")
and return "SubsetName" in dimension "ABC".
Or =SUBNM(TM1Server&":XYZ","Subset2","")
and return "Subset2" in dimension "XYZ"
Hopefully it would point out what tab in the workbook it came from as well.
If anyone out there has any idea of a useful tool that is able to interrogate existing workbooks for subsets used I would be interested to know what you've come up with. Many thanks.