Dynamic Datasource Path

Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Dynamic Datasource Path

Post by Jonsulli » Tue Jun 07, 2011 9:19 am

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 ?

lotsaram
MVP
Posts: 3136
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

Post by lotsaram » Tue Jun 07, 2011 10:40 am

All you need to do is dynamically asign a value to the variable DataSourceNameForServer on the prolog of your process.

Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Dynamic Datasource Path

Post by Jonsulli » Tue Jun 07, 2011 11:20 am

lotsaram wrote:All you need to do is dynamically asign a value to the variable DataSourceNameForServer on the prolog of your process.
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 Gielis
MVP
Posts: 1824
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Datasource Path

Post by Wim Gielis » Tue Jun 07, 2011 11:44 am

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) ?
Yes; without a path, TI will look in the TM1 data directory for that server.

Wim
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

Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Dynamic Datasource Path

Post by Jonsulli » Tue Jun 07, 2011 11:49 am

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 ?

User avatar
paulsimon
MVP
Posts: 647
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Dynamic Datasource Path

Post by paulsimon » Tue Jun 07, 2011 9:09 pm

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

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 2:44 pm

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
tomok
MVP
Posts: 2490
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

Post by tomok » Tue Feb 21, 2012 3:06 pm

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.
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 3:22 pm

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.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 3:29 pm

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.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
tomok
MVP
Posts: 2490
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

Post by tomok » Tue Feb 21, 2012 3:50 pm

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.
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 4:06 pm

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. :evil:
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
Steve Vincent
Site Admin
Posts: 1049
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

Post by Steve Vincent » Tue Feb 21, 2012 4:59 pm

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.
path_example.png
path_example.png (32.77 KiB) Viewed 7391 times
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: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

lotsaram
MVP
Posts: 3136
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

Post by lotsaram » Tue Feb 21, 2012 6:12 pm

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. :evil:
Jim, I think you're barking up the wrong tree.
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).

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 6:31 pm

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)
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

lotsaram
MVP
Posts: 3136
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

Post by lotsaram » Tue Feb 21, 2012 6:51 pm

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?

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 7:06 pm

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.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 7:12 pm

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
Michel Zijlema
Site Admin
Posts: 702
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

Post by Michel Zijlema » Tue Feb 21, 2012 7:22 pm

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

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dynamic Datasource Path

Post by jim wood » Tue Feb 21, 2012 7:40 pm

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Post Reply