Dynamic Datasource Path
-
- Posts: 39
- Joined: Tue Jan 04, 2011 3:42 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2003
Dynamic Datasource Path
Hello,
I would like to create a Dynamic path for a flat file datasource in Turbo Integrator, not only a UNC path but as well the Server Folder (Sdata, Pdata, PlanningSample).
For example I would like to copy the content of Sdata into Pdata (Cube, Process,...) without editing the Turbo Integrator Process
So I want to get
C:\Program Files\Cognos\TM1\Custom\TM1Data\SData\NewEngland.cma
or
C:\Program Files\Cognos\TM1\Custom\TM1Data\PData\NewEngland.cma.
According to the server I am using, for example Sdata is my test environment and Pdata is my live envirnment.
Is is possible ?
I would like to create a Dynamic path for a flat file datasource in Turbo Integrator, not only a UNC path but as well the Server Folder (Sdata, Pdata, PlanningSample).
For example I would like to copy the content of Sdata into Pdata (Cube, Process,...) without editing the Turbo Integrator Process
So I want to get
C:\Program Files\Cognos\TM1\Custom\TM1Data\SData\NewEngland.cma
or
C:\Program Files\Cognos\TM1\Custom\TM1Data\PData\NewEngland.cma.
According to the server I am using, for example Sdata is my test environment and Pdata is my live envirnment.
Is is possible ?
-
- 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: Dynamic Datasource Path
All you need to do is dynamically asign a value to the variable DataSourceNameForServer on the prolog of your process.
-
- Posts: 39
- Joined: Tue Jan 04, 2011 3:42 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2003
Re: Dynamic Datasource Path
Can I have an example please, I don't understand if I have to specify a path in the Datasource Tab when I use the DataSourceNameForServer in the Prolog Tab ? And I have to type in the Prolog tab : DataSourceNameForServer='New England.cma' (for example) ?lotsaram wrote:All you need to do is dynamically asign a value to the variable DataSourceNameForServer on the prolog of your process.
-
- MVP
- Posts: 3118
- 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: Dynamic Datasource Path
Yes; without a path, TI will look in the TM1 data directory for that server.Jonsulli wrote:Can I have an example please, I don't understand if I have to specify a path in the Datasource Tab when I use the DataSourceNameForServer in the Prolog Tab ? And I have to type in the Prolog tab : DataSourceNameForServer='New England.cma' (for example) ?
Wim
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: 39
- Joined: Tue Jan 04, 2011 3:42 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2003
Re: Dynamic Datasource Path
Thanks Wim.
But If I delete the Datasourcetab I will lose the Variable specified into the Variable Tab and manpping tab as well ?
Another question regarding Dynamic path, is it possible to get a Dynamic Path when exporting with TextOuput and ASCIIOutput ?
But If I delete the Datasourcetab I will lose the Variable specified into the Variable Tab and manpping tab as well ?
Another question regarding Dynamic path, is it possible to get a Dynamic Path when exporting with TextOuput and ASCIIOutput ?
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: Dynamic Datasource Path
Hi
You will need a path that will always be there so that you can reference this in the DataSourceTab, otherwise you will lose your variables. You can create a Template files folder alongside the Data directory of the server, where that is eg always on the D: drive or give a fully qualified path \\server\share so long as it is accessible from both Dev and Prod.
Then in the Prolog you use
DataSourceNameForServer = 'C:\Program Files\Cognos\TM1\Custom\TM1Data\SData\NewEngland.cma';
or something a bit more sophisticated eg
IF( vEnv @= 'Dev' ) ;
DataSourceNameForServer = 'C:\Program Files\Cognos\TM1\Custom\TM1Data\SData\NewEngland.cma';
ELSEIF( vEnv @= 'Prd' ) ;
DataSourceNameForServer = 'C:\Program Files\Cognos\TM1\Custom\TM1Data\PData\NewEngland.cma';
ELSE ;
ItemReject('Unknown Environment') ;
ENDIF ;
And you set vEnv further up in the Prolog via a CellGetS from a Reference Data Cube.
If you want to get really independent of any files, that make the source a View that will always exist and completely change the DataSource type as well as the Name. The commands for this are listed in the TM1 Reference guide.
Regards
Paul Simon
You will need a path that will always be there so that you can reference this in the DataSourceTab, otherwise you will lose your variables. You can create a Template files folder alongside the Data directory of the server, where that is eg always on the D: drive or give a fully qualified path \\server\share so long as it is accessible from both Dev and Prod.
Then in the Prolog you use
DataSourceNameForServer = 'C:\Program Files\Cognos\TM1\Custom\TM1Data\SData\NewEngland.cma';
or something a bit more sophisticated eg
IF( vEnv @= 'Dev' ) ;
DataSourceNameForServer = 'C:\Program Files\Cognos\TM1\Custom\TM1Data\SData\NewEngland.cma';
ELSEIF( vEnv @= 'Prd' ) ;
DataSourceNameForServer = 'C:\Program Files\Cognos\TM1\Custom\TM1Data\PData\NewEngland.cma';
ELSE ;
ItemReject('Unknown Environment') ;
ENDIF ;
And you set vEnv further up in the Prolog via a CellGetS from a Reference Data Cube.
If you want to get really independent of any files, that make the source a View that will always exist and completely change the DataSource type as well as the Name. The commands for this are listed in the TM1 Reference guide.
Regards
Paul Simon
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
I've not really had to use the define server data source before and while I get the data source connection I'm then struggling with the method for mapping the data variables within the ascii data source that I've defined.
Any pointers would be greatly appreciated.
Any pointers would be greatly appreciated.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Dynamic Datasource Path
The only realistic way I know to map variables in a TI process is through the variables tab and the only way to get that populated is to have the query preview produce it. So, what I always do is start the creation of the TI process with a query that produces the EXACT same column structure, with at least one record, so that I can map the variables and finish off the coding of the TI process. After that is done, you can use the DataSource variables to change the exact source your process will use at run time. Of course, the structure returned by your new data source wll have to be the same as the example query you used to create the process.jim wood wrote:I've not really had to use the define server data source before and while I get the data source connection I'm then struggling with the method for mapping the data variables within the ascii data source that I've defined.
Any pointers would be greatly appreciated.
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
While I can understand how works it won't fit with what I have to achieve which is the promotion of processes through dev to UAT to production. Each server has it's own san storage area, they don't want to have one loaction for a file and if we do use this route they will have to make sure they keep an open shared area where the files are never deleted.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
My bad I just noticed that Paul covered this in his post. I guess what this means is that this really acts as a data source override rather then setting a data source.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Dynamic Datasource Path
What Paul talks about is only necessary if you want to edit the TI code at a later date. If all you are going to do is run the code, then the file you used to define the variables structure doesn't have to exist anymore. The variables definition does not reset when you run a TI process, only when you edit the code and save it.jim wood wrote:My bad I just noticed that Paul covered this in his post. I guess what this means is that this really acts as a data source override rather then setting a data source.
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
That helps but the company I'm working for have become red hot on their production environments and no production server can reference file soutside the production environment. For the editing of processes going forward the file will need to exist, therefore the production process will reference a file outside teh production environment.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: Dynamic Datasource Path
Jim,
create a cube that holds the filepath. depends on how complex your folder structures are but it could even just hold a server name / share (ie. ServerBob/ShareCrisps). The TI can grab that value as a variable & add anything extra it may need (filename, extra folders etc). You can then use the prolog of the TI to set the datasource as it gets processed, the TIs are indentical between all servers and all you need to maintain is the cube holding the filepaths in each server instance.
In the prolog;
For editing of TI purposes, why not just have a dummy file in the same location, same shape as a real file?
create a cube that holds the filepath. depends on how complex your folder structures are but it could even just hold a server name / share (ie. ServerBob/ShareCrisps). The TI can grab that value as a variable & add anything extra it may need (filename, extra folders etc). You can then use the prolog of the TI to set the datasource as it gets processed, the TIs are indentical between all servers and all you need to maintain is the cube holding the filepaths in each server instance.
In the prolog;
Code: Select all
sFile = cellgets ( 'cubename' , 'source file path' , 'detail' );
DatasourceNameForServer = sFile;
DatasourceNameForClient = sFile;
For editing of TI purposes, why not just have a dummy file in the same location, same shape as a real file?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
-
- 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: Dynamic Datasource Path
Jim, I think you're barking up the wrong tree.jim wood wrote:That helps but the company I'm working for have become red hot on their production environments and no production server can reference file soutside the production environment. For the editing of processes going forward the file will need to exist, therefore the production process will reference a file outside teh production environment.
1/ the data source defined in the data tab can be any text file, it is just there to map the variables nothing more. As long as the real data source assigned at runtime has the same or less variables than the dummy file used for the variable mapping then you won't have a problem. Steve has already pointed out one way to manage this.
2/ DQP shouldn't be a problem as you can have the same LOCAL directory structure in each environment (in fact you probably should if it is a "real" DQP setup) e.g. D:\ImportFiles\WeeklySales.csv. The file that the process was physically pointed to in D doesn't have to be the same file as present in P. As Tomok said it just needs to have the same column structure (or actually not any more columns).
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
Guys,
I think you're completly missing the point (The last one I made anyways!!) here. I have 3 servers:
Production, UAT and Development.
I have 3 san storage areas:
Production, UAT and development.
The san storage areas does contain the data directory but all data files are held in a different directory. (They have to be, they can't be in the data directory as the location of the data files on any of the servers has to be shared so that files can be deployed to teh directory.)
So that rules out having files in the data directory (dummy files or otherwise).
I could (I agree) just use a file in the development san storage area because (as you rightly say) once built the dummy file will never be referenced again. But (and it's a big one) whether the file is actually used or not the production process will still contain a reference to a file outside the production environment. (Which against their IT policy)
So I can't do this either. This is the point I was making.
Steve as to your point, I get all that. I haven't used the cube referrence as their are so many sub folders which can change. I thought it just as easy to change sub folder within the process. I am however referring to the main UNC path in a cube, combining the 2. (This was never my problem and Paul had already covered this point anyway.)
Edited due to my really bad typing (as usual)
I think you're completly missing the point (The last one I made anyways!!) here. I have 3 servers:
Production, UAT and Development.
I have 3 san storage areas:
Production, UAT and development.
The san storage areas does contain the data directory but all data files are held in a different directory. (They have to be, they can't be in the data directory as the location of the data files on any of the servers has to be shared so that files can be deployed to teh directory.)
So that rules out having files in the data directory (dummy files or otherwise).
I could (I agree) just use a file in the development san storage area because (as you rightly say) once built the dummy file will never be referenced again. But (and it's a big one) whether the file is actually used or not the production process will still contain a reference to a file outside the production environment. (Which against their IT policy)
So I can't do this either. This is the point I was making.
Steve as to your point, I get all that. I haven't used the cube referrence as their are so many sub folders which can change. I thought it just as easy to change sub folder within the process. I am however referring to the main UNC path in a cube, combining the 2. (This was never my problem and Paul had already covered this point anyway.)
Edited due to my really bad typing (as usual)
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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: Dynamic Datasource Path
Hi Jim,
Why on earth would the file be in the data directory, I don't think anyone mentioned anything about that? DatasourceNameForServer holds the fully qualified file name including the the folder path e.g. DatasourceNameForServer = 'D:\SomeDirectory\SomeFile.txt';
Also as tomok said on prod (especially if it is a proper tied down don't ever edit anything prod) you are never going to open the TI in edit mode only ever execute it in which case the file the data tab is pointing to is utterly irrelevant as it is only used for the data preview window and to define the variables on the variable tab. When the process is running it is never referenced as DatasourceNameForServer will be set on the prolog.
Either we're not getting something or you're not getting something. Maybe you need to have another shot at explaining but why is a dummy file ruled out and why can't you just have the same relative local path in each environment?
Why on earth would the file be in the data directory, I don't think anyone mentioned anything about that? DatasourceNameForServer holds the fully qualified file name including the the folder path e.g. DatasourceNameForServer = 'D:\SomeDirectory\SomeFile.txt';
Also as tomok said on prod (especially if it is a proper tied down don't ever edit anything prod) you are never going to open the TI in edit mode only ever execute it in which case the file the data tab is pointing to is utterly irrelevant as it is only used for the data preview window and to define the variables on the variable tab. When the process is running it is never referenced as DatasourceNameForServer will be set on the prolog.
Either we're not getting something or you're not getting something. Maybe you need to have another shot at explaining but why is a dummy file ruled out and why can't you just have the same relative local path in each environment?
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
I'm getting you. The bit you are missing is that the dummy file in the data source will have to (by default) point to somwhere outside their production environment, so even though (I agree with you) that the file and it's address in the data source is completely meaningless it can't be on their production server as it is against their production server policy. It's small and (just a little) petty but it's what I'm working with. (The problem is the policies are virtually all new so most of the guys don't really know them so they are airing on the side of caution)
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
To clarify further. When creating (and editing) the process on the dev server the address of the file maybe something like \\dev_server\datashare\file.txt. To edit it on develoment going forward the file needs to be in place. Saying that while it's not being developed it could be removed, but still in the address bar in the process will say the same address regardless of the server, so when we copy it to production the production process will have the data source \\dev_server\datashare\file.txt. We could change this but we are trying to do is setup an environment were IT can promote processes (through servers) with zero interaction. They don't want to change the address bar.
The alternative we suggested was using a dns for production with local overrides in the server host file for both dev and UAT. This isn't possible as on all servers the host file is locked down.
The next one we are looking at is local server variables....
Wish me luck.
The alternative we suggested was using a dns for production with local overrides in the server host file for both dev and UAT. This isn't possible as on all servers the host file is locked down.
The next one we are looking at is local server variables....
Wish me luck.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- Michel Zijlema
- Site Admin
- Posts: 712
- Joined: Wed May 14, 2008 5:22 am
- OLAP Product: TM1, PALO
- Version: both 2.5 and higher
- Excel Version: 2003-2007-2010
- Location: Netherlands
- Contact:
Re: Dynamic Datasource Path
Hi Jim,
It appears you're not having an issue, but someone is looking for an issue.
What is the definition of the production environment? Is this only relating to the server and not to the client side?
The process definition has two datasource names, one from a client perspective and one from a server perspective. The 2nd one will actually be used by the process and can be set in ways described above. The client perspective path can point to a dummy file not containing real data (so you're not placing any actual data outside of the production environment). If the IT department (or whoever else) thinks this is a problem, they need to be challenged.
If this cannot be setup in the way described above you should advice them to pull all network-cables from the server and move the server to a dark secured place.
Michel
It appears you're not having an issue, but someone is looking for an issue.
What is the definition of the production environment? Is this only relating to the server and not to the client side?
The process definition has two datasource names, one from a client perspective and one from a server perspective. The 2nd one will actually be used by the process and can be set in ways described above. The client perspective path can point to a dummy file not containing real data (so you're not placing any actual data outside of the production environment). If the IT department (or whoever else) thinks this is a problem, they need to be challenged.
If this cannot be setup in the way described above you should advice them to pull all network-cables from the server and move the server to a dark secured place.
Michel
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Dynamic Datasource Path
Michel,
You have no idea how strongly I agree with you. The IT department in question have gone in to policy overdrive.
I am aware of the client address being seperate, again not my problem. The problem is that (regardless of the addess) the process as a file will exist on the production server with an address (client or otherwise) will point outside the production environment. This has nothing to with the way a process operates. (Which is were you missing my original point) it's do with the physical file being ran by the production service. That physical file will contain an address pointing outside the production environment. This is teh reason they seperate san storage environments for each server.
You have to keep in mind (This will give the location away if nothing else) that after 09/11 they are very up on DR. Only their production environment is in full DR.
You have no idea how strongly I agree with you. The IT department in question have gone in to policy overdrive.
I am aware of the client address being seperate, again not my problem. The problem is that (regardless of the addess) the process as a file will exist on the production server with an address (client or otherwise) will point outside the production environment. This has nothing to with the way a process operates. (Which is were you missing my original point) it's do with the physical file being ran by the production service. That physical file will contain an address pointing outside the production environment. This is teh reason they seperate san storage environments for each server.
You have to keep in mind (This will give the location away if nothing else) that after 09/11 they are very up on DR. Only their production environment is in full DR.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7