Business Analytics

Using Files in Planning Analytics as a Service

There are 2 main ways of using flat files within Planning Analytics as a Service.  

File Manager: Manage files including uploading a file to the Planning Analytics database storage . This can be helpful if you want to, for example, import data from a specific file into a dimension or cube you are using.

TI Process Upload: A TurboIntegrator process contains a script of TurboIntegrator functions and commands to programmatically import data as well as create and modify TM1 objects, such as cubes and dimensions. As an modeler you also upload files to your database storage for using in TI Processes.

There is also another approach "Using Text Files with ODBC Data Connector". Details can be found after the 2 sections of this blog.

Finally, you can access a video helping with TI processing of files "File Management in PAaaS" from Product Management.

Before starting, I should point out that files can also be transferred/managed using the Planning Analytics Workspace RestAPI. For an excellent walkthrough on how to do this with the API please see the following blog: Managing Planning Analytics as a Service using API requests.

1. Steps to upload a file into your database
   
   

   In Planning Analytics home screen, select Data and Models
   Select Create (step 1) and choose New workbench (step 2)
   In the database tree (step 3), select your database and click the three dots for the context menu (step 4). Then, access the file manager option (step 5)
   Click on file upload icon (step 6). Drag and drop files or click browse to select your file to upload (step 7). Your upload file is now showing (step 8) and you can either continue to select other files to upload or select upload (step 9). Once uploaded, a blue checkmark will validate your upload. You can now close out the upload (step 10).
   You can see your uploaded file in the file manager.
   You now have other capabilities within file manager to complete other actions with your files: Download, Preview or delete.
   You can also create a folder to organise your uploaded files: Click on the Create Folder icon (step 1). A new window will open where you can enter the name of folder (step 2) and validate creation (step 3). Your new folder has been created (step 4).

   
   
   
2. Steps to upload a TI process into your database
   
   

   There are 2 ways to upload files trough a TI process (TurboIntegrator process) depending on the role you may have: Modeler/Administrator or Analyst. If you are Modeler/Administrator, you can create or edit a TurboIntegrator process in the process editor on a modeling workbench and upload a file. For an Analyst, the modeler/administrator needs to create an “Action Button” to allow the analyst to upload files. The action button has a new property option "Allow file upload" that lets you upload a file to a Planning Analytics database and run a process to use that file. Below are 2 sections describing each upload process.
   A. Administrator/Modeler: Create a TI Process
   In Planning Analytics home screen, select Data and Models
   Select Create (step 1) and choose New workbench (step 2)
   In the database tree (step 3), expand and select your database (step 4) on which you want to create the new process. Right-click the Processes option (step 5) and select Create process (step 6). Enter a name for the process (step 7) and click Create (step 8).
   A new tab opens with the name of your process. Define a Data source (step 9) by selecting, in the drop down menu, "File". Then click on Select file (step 10) to choose your file. Note: There are other options you can set (see in step 9) like cubes, database connections, ... The script is where the actual logic is created for what you want to do with the file. For example, it could be to load data into a dimension or into a cube. For more details on what you can do with files and TI Processes, check the following link: https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=r-turbointegrator-functions
   Find the file name within the file manager. Select the appropriate file name by selecting the box (step 11) and click on Select (step 12).
   Validate if the correct file name is selected in the File field (step13) and click on Save (step14). Note: The star near the name of the process indicates that the object has unsaved elements. It needs to be saved to reflect all creations or changes made within this object.
   In your TI Process, you can also automate the upload process by adding a variable (step 2) in the parameters tab (step 1) as well as a script (step 5) in the script tab (tab 4). This will recognise the uploaded file during runtime.
   Once this is complete, you can include this variable in an action button in your books. SEE STEPS BELOW in Section B how to create an upload button to activate this new variable. ONCE BUTTON is created, go to the  Properties of your button a click Set parameters (step 1). Click on the drop down of Control Type (step 2) and select File. Make sure the Save the setting (step 3)
   B. Analyst: Use a Upload Data Button in a book
   In the Planning Analytics home screen (step 1), select Reports and Analysis (step 2).
   Select Create (step 3) and choose the option "Book".
   A new book has been created (step 4) in edit mode.  Click on the Widget option (step 5) and drag the "Action Button" widget to your book (step 6). Use the handles on the perimeter of the Action Button to resize the button if needed and rename text to indicate function of the button -here "Load Data" (step 7).
   Select the button and open the Properties pane (step 8) for your button. Select the Run Process tab (step 9) and enable Run Process (step 10). Select the database in which the process resides (step 11). Select the process that you want to run (step 12). Check mark "Allow file upload" (step 13). Make sure to save the setting by clicking on the "Save" option (step 14).
   Your new 'Load Data' button is now ready to be used by analyst in any book you would like. To test the button, uncheck the Edit mode (step 15). Click the Action Button 'Load Data' to run the process (step 16). A File upload prompt appears to allow analyst to select files to upload (step 17). Validation is done by clicking Upload (step 18).
   Note: The button will then trigger the TI Process which will run the upload for the analyst/user and any process logic you created within the process, ie. load the data into a dimension.

Another option to work with files:

It is also possible to use files stored outside of Planning Analytics as a Service using the ODBC Data Connector and an ODBC text file driver.  For more information see the final section "Using Text Files with ODBC Data Connector" in Configure database access