I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here: https://github.com/StuartStephenKing/TM1v12BOX/tree/main.  This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Thanks Stuart

Can you please refresh your link? I am getting a "Page not Found" error

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here: https://github.com/StuartStephenKing/TM1v12BOX/tree/main.  This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

There is an errant . on the end of the link,

https://github.com/StuartStephenKing/TM1v12BOX/tree/main

Thanks Stuart

Can you please refresh your link? I am getting a "Page not Found" error

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here: https://github.com/StuartStephenKing/TM1v12BOX/tree/main.  This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here:

https://github.com/StuartStephenKing/TM1v12BOX/tree/main   

This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

@STUART KING Hello Stuart..Good morning..I was going through this thread and I have a similar setup as you have, as in I am using box source files for my PA project..Currently I am downloading and uploading these source files from Box to PA..Just curious to know, If we could directly connect to box from PA, through some API calls..Any guidance would be of great help..thanks in advance

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here:

https://github.com/StuartStephenKing/TM1v12BOX/tree/main   

This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Hi Venkata,

The above post has a link to a GIT repo with some sample TI code that highlights how to use the BOX API to read and write files from BOX to TM1.  There are examples of how to iterate over the content of a BOX folder and delete a folder.

Please keep in mind that this TI code sample is based on TM1 v12, which is only found in the newer Planning Analytics SaaS offering.  

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Tue November 12, 2024 01:34 AM
From: Venkata Nori
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

@STUART KING Hello Stuart..Good morning..I was going through this thread and I have a similar setup as you have, as in I am using box source files for my PA project..Currently I am downloading and uploading these source files from Box to PA..Just curious to know, If we could directly connect to box from PA, through some API calls..Any guidance would be of great help..thanks in advance

------------------------------
Venkata Nori

Original Message:
Sent: Mon November 11, 2024 08:19 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here:

https://github.com/StuartStephenKing/TM1v12BOX/tree/main   

This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Edward Stuart and Stuart King

I need to migrate files now between PAaaS instances / database File Managers. For instance beween a Dev and Prod instance

Have either of you done this as yet? Can you point me to references?

***Update***
I have been able to 'pull' between servers:
sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/[Database name]/api/v1/Contents(''Files'')/Contents("ServerExport/ServerLoad1.txt")/Content';

sHeader = '-h Authorization:Basic ' | sEncode;
sHeader1 = '-h Content-Type:application/json';

sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  'ServerImport/ServerLoad1.txt' );

Is there a way to 'push' to another server as well?

i.e. can  '-o ' |  'ServerImport/ServerLoad1.txt'  be a reference to another server?

------------------------------
John Young

Original Message:
Sent: Tue November 12, 2024 09:22 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Venkata,

The above post has a link to a GIT repo with some sample TI code that highlights how to use the BOX API to read and write files from BOX to TM1.  There are examples of how to iterate over the content of a BOX folder and delete a folder.

Please keep in mind that this TI code sample is based on TM1 v12, which is only found in the newer Planning Analytics SaaS offering.  

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Tue November 12, 2024 01:34 AM
From: Venkata Nori
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

@STUART KING Hello Stuart..Good morning..I was going through this thread and I have a similar setup as you have, as in I am using box source files for my PA project..Currently I am downloading and uploading these source files from Box to PA..Just curious to know, If we could directly connect to box from PA, through some API calls..Any guidance would be of great help..thanks in advance

------------------------------
Venkata Nori

Original Message:
Sent: Mon November 11, 2024 08:19 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here:

https://github.com/StuartStephenKing/TM1v12BOX/tree/main   

This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

Hi John,

Where you GET the existing File you can also POST the same file as part of the body.

There are a few points to note, particularly if your filesize is over 1Mb then it will not be picked up

Documentation of various endpoints to Files and Folders is here:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=data-folders-contents

Details for POSTing these doesn't appear to be available as yet but the principle for POSTing Files/ Folders between environments is the same

Most of my work on this has been to migrate Cubes/ Dimensions/ Processes/ Cubes/ Data etc.. and I've been using JSON as a datasource to create/ update/ synchronise the objects between environments but I've yet to move files in production

A point to note, to POST the file you will need to ensure you are generating valid JSON and this likely means you will need to read the contents of the file and build/ rebuild the json as required (is this a direct copy, will the folder structure remain the same, will any details within the file(s) need updating, do you want to check if the last updated time matches between environments i.e. do you need to update the file etc..)

Edward Stuart and Stuart King

I need to migrate files now between PAaaS instances / database File Managers. For instance beween a Dev and Prod instance

Have either of you done this as yet? Can you point me to references?

***Update***
I have been able to 'pull' between servers:
sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/[Database name]/api/v1/Contents(''Files'')/Contents("ServerExport/ServerLoad1.txt")/Content';

sHeader = '-h Authorization:Basic ' | sEncode;
sHeader1 = '-h Content-Type:application/json';

sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  'ServerImport/ServerLoad1.txt' );

Is there a way to 'push' to another server as well?

i.e. can  '-o ' |  'ServerImport/ServerLoad1.txt'  be a reference to another server?

------------------------------
John Young

Original Message:
Sent: Tue November 12, 2024 09:22 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Venkata,

The above post has a link to a GIT repo with some sample TI code that highlights how to use the BOX API to read and write files from BOX to TM1.  There are examples of how to iterate over the content of a BOX folder and delete a folder.

Please keep in mind that this TI code sample is based on TM1 v12, which is only found in the newer Planning Analytics SaaS offering.  

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Tue November 12, 2024 01:34 AM
From: Venkata Nori
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

@STUART KING Hello Stuart..Good morning..I was going through this thread and I have a similar setup as you have, as in I am using box source files for my PA project..Currently I am downloading and uploading these source files from Box to PA..Just curious to know, If we could directly connect to box from PA, through some API calls..Any guidance would be of great help..thanks in advance

------------------------------
Venkata Nori

Original Message:
Sent: Mon November 11, 2024 08:19 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here:

https://github.com/StuartStephenKing/TM1v12BOX/tree/main   

This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------

I've attached a Postman collection for PA SaaS.  Example the 'Database File Management' folder for examples of API calls to upload files.

Uploading files requires two API calls.  First API call is to create the empty file and the next API call is to load the file content.  TM1 v12 also introduced multipart file upload API endpoints but this would be difficult to use from a TI process as you need the client to divide up the file. 

You can also remotely execute a transient TI process on a remote TM1 database using ExecuteHTTPRequest TI function on the local TM1 database.  This might be an easier option (tell the remote database to pull the file instead of push the file).

Hi John,

Where you GET the existing File you can also POST the same file as part of the body.

There are a few points to note, particularly if your filesize is over 1Mb then it will not be picked up

Documentation of various endpoints to Files and Folders is here:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=data-folders-contents

Details for POSTing these doesn't appear to be available as yet but the principle for POSTing Files/ Folders between environments is the same

Most of my work on this has been to migrate Cubes/ Dimensions/ Processes/ Cubes/ Data etc.. and I've been using JSON as a datasource to create/ update/ synchronise the objects between environments but I've yet to move files in production

A point to note, to POST the file you will need to ensure you are generating valid JSON and this likely means you will need to read the contents of the file and build/ rebuild the json as required (is this a direct copy, will the folder structure remain the same, will any details within the file(s) need updating, do you want to check if the last updated time matches between environments i.e. do you need to update the file etc..)

Edward Stuart and Stuart King

I need to migrate files now between PAaaS instances / database File Managers. For instance beween a Dev and Prod instance

Have either of you done this as yet? Can you point me to references?

***Update***
I have been able to 'pull' between servers:
sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/[Database name]/api/v1/Contents(''Files'')/Contents("ServerExport/ServerLoad1.txt")/Content';

sHeader = '-h Authorization:Basic ' | sEncode;
sHeader1 = '-h Content-Type:application/json';

sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  'ServerImport/ServerLoad1.txt' );

Is there a way to 'push' to another server as well?

i.e. can  '-o ' |  'ServerImport/ServerLoad1.txt'  be a reference to another server?

------------------------------
John Young

Original Message:
Sent: Tue November 12, 2024 09:22 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Venkata,

The above post has a link to a GIT repo with some sample TI code that highlights how to use the BOX API to read and write files from BOX to TM1.  There are examples of how to iterate over the content of a BOX folder and delete a folder.

Please keep in mind that this TI code sample is based on TM1 v12, which is only found in the newer Planning Analytics SaaS offering.  

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Tue November 12, 2024 01:34 AM
From: Venkata Nori
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

@STUART KING Hello Stuart..Good morning..I was going through this thread and I have a similar setup as you have, as in I am using box source files for my PA project..Currently I am downloading and uploading these source files from Box to PA..Just curious to know, If we could directly connect to box from PA, through some API calls..Any guidance would be of great help..thanks in advance

------------------------------
Venkata Nori

Original Message:
Sent: Mon November 11, 2024 08:19 AM
From: STUART KING
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I have a similar (but more complex) use case with the BOX API being used to create folder, upload and download files, and delete folders in BOX.  A repo with my TI processes can be found here:

https://github.com/StuartStephenKing/TM1v12BOX/tree/main   

This includes examples of using the new JSON functions.

Note that I keep my BOX OAuth client id and secret as attributes in the }Clients dimension (which are not included in the GIT repo).  For security reasons it would probably make more sense to pass these details to the processes as parameter values.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Original Message:
Sent: Sun November 10, 2024 07:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Yes this works, thank you very much

In addition and for others that come across the post, I was able to get download working as well:

ExecuteProcess( 'API_DropBox_Get_Refresh_Token');

 sTargetFolder = 'Import';

sFile = sTargetFolder | '/' | pFile;

# https://api.dropbox.com/oauth2/token 
sURL = 'https://content.dropboxapi.com/2/files/download';
sToken =  CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );

sBody = '
{"path": "/Test/DownloadTest.xlsx"}
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: '|sBody;
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, '-o ' |  sTargetFolder | '/' | pFile);

sTokentext = HttpResponseGetBody();

------------------------------
John Young

Original Message:
Sent: Fri November 08, 2024 10:34 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

The Dropbox API Documentation is better than most and the broad strokes of the process to upload documents to dropbox are:

• Have a dropbox account
• Create a App - https://www.dropbox.com/developers/apps/create
• Set Permissions on the app (file.contents.write)
• Generate a token or setup OAuth Flow https://developers.dropbox.com/oauth-guide
• Check the /Upload endpoint documentation https://www.dropbox.com/developers/documentation/http/documentation#files-upload

This is the code I used to send a file from PAE to Dropbox:

sToken = '<MyToken>';

sFile = '<My File to Upload>';

sURL = 'https://content.dropboxapi.com/2/files/upload';

sBody = '
{
  "autorename":false,
  "mode":"add",
  "mute":false,
  "path":"/<Location inside App folder on Dropbox>",
  "strict_conflict":false
  }
  ';

sHeader1 = '-h Authorization:Bearer ' | sToken;
sHeader2 = '-h Dropbox-API-Arg: ' | sBody;
sHeader3 = '-h Content-Type:application/octet-stream';
sHeader4 = '-d @' | sFile;

if(FileExists( sFile ) > 0);

  ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3, sHeader4);

nStatus = HttpResponseGetStatusCode();
sBody = HttpResponseGetBody();

ASCIIOutput( 'DropboxText.txt', NumberToString( nStatus ) );
ASCIIOutput( 'DropboxText.txt', sBody );

endif;

The contends of "DropboxText.txt" are:

"200"
"{"name": "<My File Name>", "path_lower": "<My Dropbox Path>", "path_display": "<My Dropbox Path>", "id": "id:iWWauMhxqI8AAAAAAAAACA", "client_modified": "2024-11-08T15:21:46Z", "server_modified": "2024-11-08T15:21:46Z", "rev": "01626684f2a2bc300000002c9d310b1", "size": 446, "is_downloadable": true, "content_hash": "23752a95461f9b1249720018164d351e00f68d441fc7a65ef9588d90b69ccdc4"}"

This can easily be extended to generate a file direct from PAE/ v12 and dynamically update the Dropbox Filepath as required,

Let me know how you get on

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Thu November 07, 2024 05:14 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi Edward

Thanks for the reply. I dont know how to reference it. The above code was trial and error and transalation from successful loads in Postman, however Postman uses its own file interface to load the file, and I couldnt find the code anywhere to replace this step. Even ChatGPT was no help :)

------------------------------
John Young

Original Message:
Sent: Thu November 07, 2024 09:33 AM
From: Edward Stuart
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

Hi John,

I've not got around to testing this as yet but have you tried running the script to output the file to sTargetFolder | '/' | pFile and using the same reference when running the upload file from PAE/ v12?

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "' | sTargetFolder | '/' | pFile | '", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

------------------------------
Edward Stuart
Solutions Director
Spitfire Analytics Limited
Manchester

Original Message:
Sent: Tue November 05, 2024 10:45 PM
From: John Young
Subject: Planning Analytics Engine - ExecuteHttpRequest FTP replacement to and from Cloud file storage (i.e. dropbox)

I am investigating how to use ExecuteHttpRequest to replace FTP to and from file hosting services like Box or Dropbox.

I have the below code translated to TI from some test code in Postman loading a local file successfully to Dropbox, this is incomplete and I have not been able to reference a PAaaS File Manager file to send:

sURL = 'https://content.dropboxapi.com/2/files/upload';

sHeader1 = '-h Authorization:Bearer ' | CellGetS('sys.API_Assumptions', 'Token', 'DropBox' );
sHeader2 = '-h Dropbox-API-Arg:{"path": "/Test/DropBoxText.txt", "mode": "add", "autorename": true, "mute": false}';
sHeader3 = '-h Content-Type:application/octet-stream';

ExecuteHttpRequest( 'POST',  sURL, sHeader1, sHeader2, sHeader3 );

I am also able to successfully move files within PAaaS ising this code:

sURL = 'https://ap-southeast-2.planninganalytics.saas.ibm.com/api/'| sTenant | '/v0/tm1/API_Test_Area/api/v1/Contents(''Files'')/Contents(''' | pFile | ''')/Content';

        sHeader = '-h Authorization:Basic ' | sPwd ;
        sHeader1 = '-h Content-Type:application/json';

        sRes = ExecuteHttpRequest( 'GET',  sURL, '-k', sHeader, sHeader1,  '-o ' |  sTargetFolder | '/' | pFile  );

Has anyone successfully achieved this and can share?

Are there other recommended methods for FTP replacement in PAaaS?

------------------------------
John Young
------------------------------