Dynamic Datasource Path

tomok
MVP
Posts: 2831
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 »

jim wood wrote: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.
The solution is ridicuously easy then. Point the TI to a relative drive letter on the TM1 server. Like "C:\ImbeingabuttheadITdept\DataFile.txt". it could be C:, or D: or whatever. Every server always has at least one local physical drive so when you migrate from Dev to QA to Prod it will always be pointing to a file that exists in that environment only. It doesn't make jack squat where since the REAL path is going to be what you assign in the DataSourceNameForServer variable.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
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

Post by jim wood »

That will work for the server address and it will also work for client if they (including myself there is development team of at least 6 guys, most part time.) are able to develop all processes on a remote desktop to the development server. That I'm not sure they have (the permissions) but I'll check. Even then there is the MS remote desktop connection limit so I'm not 100% certain how practical that will be, but something worth considering.

The thing to keep in mind is that the customer is a very big company and this is a corporate implementation. It as such supported as corporate solution and the we have had to work around all the rules and regulations that come with that. This isn't (as much as I would like it to be) a small company where you can get away with little hacks and fixes.

Edit: Added the last bit for a little more background. :mrgreen:
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
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Dynamic Datasource Path

Post by Alan Kirk »

jim wood wrote:That will work for the server address and it will also work for client if they (including myself there is development team of at least 6 guys, most part time.) are able to develop all processes on a remote desktop to the development server. That I'm not sure they have (the permissions) but I'll check. Even then there is the MS remote desktop connection limit so I'm not 100% certain how practical that will be, but something worth considering.
Jim, you don't need to be doing everything on the server side.

Data Source Name is in fact where the TI process will look for the data source while you're editing it. (When it runs it uses Data Source Name On Server unless you override it, which is what you're doing.) Consequently each of your team can just as easily create Tomok's suggested folder of C:\ImbeingabuttheadITdept on your local machine and put DataFile.txt inside it. You'll still be able to edit the process using that as your data source. You may get a warning about the possibility that the data source is not available on the server, but who the h3ll cares; as noted above you're overriding the path in the Prolog anyway so neither the value in Data Source Name nor the one in Data Source Name On Server will ever be used when the TI process is run. In fact this is essentially what Michel was getting at.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
stex2727
Posts: 66
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Dynamic Datasource Path

Post by stex2727 »

jim wood wrote:Guys,
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)
Edited due to my really bad typing (as usual)

Sorry if I'm barking up the wrong tree here with a low tech solution but why not

- use the cube references for the correct file path
- change the datasource in the TI process using "DataSourceNameForServer = cellgets(...)
- just before finishing the TI process change the two data source fields in the TI wizard to "xxx" and save without updating variables and ignore the error.

There are then no references to files outside the production environment in the TI process and it still works. While you cant see the variables on the tab they are still there from the previous definition and saved in the .pro file

Stex
User avatar
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

Post by Steve Vincent »

Jim, you know where i work, do you think this place is any easier than yours? I now know where you are coming from, its not the way the Tis are running its just the fact that a tiny wee bit of them is refering to another server once they are promoted. As the last post suggests, even blanking out the filepath on the dev TI before promotion would work. Surely that is good enough for Department Pedant? :lol:
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
User avatar
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

Post by Michel Zijlema »

User avatar
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

Post by jim wood »

Alan Kirk wrote:
jim wood wrote:That will work for the server address and it will also work for client if they (including myself there is development team of at least 6 guys, most part time.) are able to develop all processes on a remote desktop to the development server. That I'm not sure they have (the permissions) but I'll check. Even then there is the MS remote desktop connection limit so I'm not 100% certain how practical that will be, but something worth considering.
Jim, you don't need to be doing everything on the server side.

Data Source Name is in fact where the TI process will look for the data source while you're editing it. (When it runs it uses Data Source Name On Server unless you override it, which is what you're doing.) Consequently each of your team can just as easily create Tomok's suggested folder of C:\ImbeingabuttheadITdept on your local machine and put DataFile.txt inside it. You'll still be able to edit the process using that as your data source. You may get a warning about the possibility that the data source is not available on the server, but who the h3ll cares; as noted above you're overriding the path in the Prolog anyway so neither the value in Data Source Name nor the one in Data Source Name On Server will ever be used when the TI process is run. In fact this is essentially what Michel was getting at.
I understand all that. The problem is that their local c: drives are locked down. They can't use them.

The only way around it I can see is if they create a standard drive letter for teh team with a shared they use for creating the files. That however comes with the danger of being overwritten by a global drive letter in the future. They could ask for a set drive but knowing the way things go they would be told to add a share to their server, but we've already been told by the server team that they can't do that either! It's all fun and games I tell you.

The best solutino for me is if there were some function (or way) of extracting teh variables from the set server data. Do you think I should add that to the development request list??? :)
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
User avatar
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

Post by jim wood »

stex2727 wrote:
jim wood wrote:Guys,
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)
Edited due to my really bad typing (as usual)

Sorry if I'm barking up the wrong tree here with a low tech solution but why not

- use the cube references for the correct file path
- change the datasource in the TI process using "DataSourceNameForServer = cellgets(...)
- just before finishing the TI process change the two data source fields in the TI wizard to "xxx" and save without updating variables and ignore the error.

There are then no references to files outside the production environment in the TI process and it still works. While you cant see the variables on the tab they are still there from the previous definition and saved in the .pro file

Stex
Hi Stex,

If you read through the thread that had already been discussed and my reasons for not going that way outlined.

Thanks for your input though,

Jim.
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
User avatar
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

Post by jim wood »

Steve Vincent wrote:Jim, you know where i work, do you think this place is any easier than yours? I now know where you are coming from, its not the way the Tis are running its just the fact that a tiny wee bit of them is refering to another server once they are promoted. As the last post suggests, even blanking out the filepath on the dev TI before promotion would work. Surely that is good enough for Department Pedant? :lol:
I hear you. I've always seen these things as challenge. Basically they want their cake and they want not just to eat it, they want every crumb! TBF I understand where they are coming from. IBM should now be looking at requests and building development around them. After all they want big corporate clients??

As for making the field blank before promotion, as stated they want it all, they want promotion automatically without any manual input. I know, I know....
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
User avatar
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

Post by Steve Vincent »

Personally i'd tell them to take a long walk off a short pier, but that's just the type of person i am ;) That Dilbert cartoon summed it up perfectly, security is fine but if it stops you from doing the job in any manner then it's useless to anyone.
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
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Dynamic Datasource Path

Post by Alan Kirk »

jim wood wrote:
Alan Kirk wrote:
jim wood wrote:That will work for the server address and it will also work for client if they (including myself there is development team of at least 6 guys, most part time.) are able to develop all processes on a remote desktop to the development server. That I'm not sure they have (the permissions) but I'll check. Even then there is the MS remote desktop connection limit so I'm not 100% certain how practical that will be, but something worth considering.
Jim, you don't need to be doing everything on the server side.

Data Source Name is in fact where the TI process will look for the data source while you're editing it. (When it runs it uses Data Source Name On Server unless you override it, which is what you're doing.) Consequently each of your team can just as easily create Tomok's suggested folder of C:\ImbeingabuttheadITdept on your local machine and put DataFile.txt inside it. You'll still be able to edit the process using that as your data source. You may get a warning about the possibility that the data source is not available on the server, but who the h3ll cares; as noted above you're overriding the path in the Prolog anyway so neither the value in Data Source Name nor the one in Data Source Name On Server will ever be used when the TI process is run. In fact this is essentially what Michel was getting at.
I understand all that. The problem is that their local c: drives are locked down. They can't use them.
Jim, we have been through this before. Locking down My Documents on the C:\ drive does not, does not= "Locking Down The C:\ drive". If it did, applications would cease to function. They would become immobile. They would not work. No IT department, no matter how anal, can lock down the %APPDATA% path without rendering all functionality of the applications on the system null and void. There is always some path on the C:\drive that can be used, including an All Users one.
jim wood wrote:The only way around it I can see is if they create a standard drive letter for teh team with a shared they use for creating the files. That however comes with the danger of being overwritten by a global drive letter in the future. They could ask for a set drive but knowing the way things go they would be told to add a share to their server, but we've already been told by the server team that they can't do that either! It's all fun and games I tell you.

The best solutino for me is if there were some function (or way) of extracting teh variables from the set server data. Do you think I should add that to the development request list??? :)
Why does this triviality need to be so complex?

There are only two times when the path to the data source matters. One is at design time, one is at run time. In between, whatever the .pro specifies as the path is entirely immaterial.

At design time you can set the local path to wherever a copy of the sample data file is. This does not now and never needs to in the future correspond with where the actual runtime file will be. It doesn't even need to correspond to where the initial development was done, nor does it even need to be the same for each developer. You can set it to that path when you need to edit the thing (and really, how often and how regularly are you needing to edit it anyway?), refresh the data source and bingo, you have all of your variables to work with. You have 6 developers? So what? You can have each of them use a different path if you need to or alternatively point it to a network share as you mentioned. That letter changes in the future? Who cares? Use a UNC reference instead. The UNC reference changes? Doesn't matter; see the earlier points about "It doesn't even need to... etc" above, But the point is that as long as you are pointing it to some path, whether it's the same one that you initially developed on or not, any path which has a sample file in it then you'll be able to do whatever you need to when editing it, though personally I tend to regard development as a one shot deal aside from minor bug fixes or external system changes.

The other is at run time and if you're overriding the path to the data source, as you've indicated that you will be, by reading it from a cube in the relevant environment it matters not one iota what the original path specified in the .pro file is, or whether it even exists in that environment.
Jim Wood wrote:As for making the field blank before promotion, as stated they want it all, they want promotion automatically without any manual input. I know, I know....
But that's the whole point of being able to change the variables. It means that you can copy a process to another environment and it will still work because the default values are no longer connected to the runtime values and vice versa. It's not a bug, it's not a feature, it's just the way the sodding thing was designed to work in the first place and that is what your august clients need to be made to understand.

(And incidentally, given their security-mindedness :roll: surely they would see the virtue in having the .pro file's data path pointing to a development environment only since, after all, that is the only place that it should be edited, n'est-ce pas?)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
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

Post by jim wood »

Alan,

Again your thinking about how the process works rather than thinking about the development cycle and how the want things referrenced. To be fair I think we have laboured the point and I now have options which is good. All of the comments so far have been very helpful. I understand the customers need and their security policies (good and bad) and based on what has been dicussed I know what I'm doing and what I can suggest (which some might argue makes a change!! :twisted: ) some suitable paths.

Cheers guys,

Jim.
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
stex2727
Posts: 66
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Dynamic Datasource Path

Post by stex2727 »

The Dilbert cartoon was good, but given the subject matter you are trying to work around I cant help but feel your profile pic of Nelson Muntz is apt.

Steve
User avatar
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

Post by jim wood »

Steve he is of course laughing at everybody elses posts, it just looks like he is pointing at mine.......
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
AmbPin
Regular Participant
Posts: 173
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: Dynamic Datasource Path

Post by AmbPin »

Hello,
I stumbled upon this page whilst looking for something else, however I really like the idea of initializing a procedure with a dummy csv file. As has been stated above this would be especially beneficial where the procedure configures the data source during the prolog tab and therefore may use a cubeview that only exists for the duration of the procedure.

I have tried to do this by creating a simple CSV file:-
Dim01,Dim02,Dim03, ...,Dim20
a,b,c,...,99

At design time my procedure data source is set to look at this CSV file. To change this to my dynamic cube view in the prolog I have the following:-

Code: Select all

DataSourceType = 'VIEW';
DataSourceNameForServer = psCubeName;
DatasourceNameForClient = psCubeName;
DataSourceCubeView = sViewName;
When I try to run this I get an error stating "Variable "NVALUE" not found."

If I change the data source at design time to be any cube with 20 dimensions or less, it works as expected - did I miss something?
lotsaram
MVP
Posts: 3651
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 »

AmbPin wrote:When I try to run this I get an error stating "Variable "NVALUE" not found."

If I change the data source at design time to be any cube with 20 dimensions or less, it works as expected - did I miss something?
Presumably you must have some code on the Data tab referencing the variable nValue. The implicit variables nValue, sValue and value_is_string only exist for processes with DataSourceType = 'VIEW'. These variables are created (but re not visible in the UI) when the manual DatasourceNamneForClient is set as a view or when DataSourceType is set to VIEW dynamically via code on the Prolog.

If you are using a text file as the manual data source to map variables then you can avoid the error by manually declaring the implicit variables on the Prolog.
value_is_string = 0;
nValue = 0;
sValue = '';
If the data source type remains as text then the values will remain constant. If the data source type is changed to view then the values will be dynamic for each record of the data source.

On the topic of dynamic data sources another good trick is DatasourceType = 'NULL'
Setting this on the Prolog will have the effect of skipping the Metadata and Data tabs and going straight to the Epilog. This can be preferable to the minor error reporting that results in skipping/exiting via ProcessBreak.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
AmbPin
Regular Participant
Posts: 173
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: Dynamic Datasource Path

Post by AmbPin »

Thanks very much for your reply,

I have now set the three variables in the prolog as you suggested.

If leave the data tab compltely blank then, as you may expect, the process completes OK.
However if I put anything in the date tab E.g. AsciiOutput('File.nam', 'Hello'); I get the error "Variable "NVALUE" not found."

Is it that dynamically switching from a text to view input means that the nValue, sValue & Value_Is_String are not created?
lotsaram
MVP
Posts: 3651
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 »

Did you allow for redundant columns in the text file vs he actual runtime data source?

(I normally use an artificial 25 dim cube as a dummy data source for such processes which is there on dev but not prod. You can easily switch from a process defined using a view to text. I would have also thought you could do the reverse provided sufficient variable columns are allowed for for the 3 hidden implicit variables)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
AmbPin
Regular Participant
Posts: 173
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: Dynamic Datasource Path

Post by AmbPin »

lotsaram wrote:Did you allow for redundant columns in the text file vs he actual runtime data source?
My dummy csv file has 20 columns, the cube I am using to test on which my dynamic view is craeted has 11 dimensions. Did you men that implicitly the view would therefore present 14 columns with the 3 hidden variables?
This is however still catered for by my dumy csv with 20 columns.

Having a dummy cube would work but I would like to understand what I have done wrong.
jyoung66
Posts: 24
Joined: Fri Aug 13, 2010 2:14 am
OLAP Product: TM1
Version: PAW 2 PAX
Excel Version: 2016

Re: Dynamic Datasource Path

Post by jyoung66 »

lotsaram wrote:Did you allow for redundant columns in the text file vs he actual runtime data source?

(I normally use an artificial 25 dim cube as a dummy data source for such processes which is there on dev but not prod. You can easily switch from a process defined using a view to text. I would have also thought you could do the reverse provided sufficient variable columns are allowed for for the 3 hidden implicit variables)

This is what worked for me too :D

I converted the Text file source into a 20 dimension cube and it solved the problem, none of the above worked with the dummy text file as a source
Post Reply