Skip to Content
Technical Articles
Author's profile photo Manuel Mora Rodrigues

Excel File / Flat File Copy & Paste Into SAP Analytics Cloud Planning Model With Analytic Application

In this blog I will show how to load master data and transactional data into a SAP Analytics Cloud Planning model from a copy paste from an excel table.

In certain business scenarios it is advised not to give business users access to the data management features of the planning modes, due to confidential data and also because it is risky to create data loading jobs using flat files / excel files, a wrong click might delete too much.

There are already other blogs explaining how to deal with this requirement, in this blog I will present a different approach and explain the differences between the approaches.

Existing Solutions

 

Upload Data From Excel file using custom widget

This solution allows the direct import of an excel file from an Analytic Application using a custom widget, in terms of UX is amazing,however, it could be complex to implement as it requires a dedicated web-server to store the custom widget JS file and it requires some of JavaScript coding.

Flat File Upload using dataset

This approach is quite simple to implement, however, it requires the users to access a separate screen when importing the data (dataset import screen), also, it poses some challenges in terms of confidentiality, because all users who access the dataset will be able to see that data.

 

New Approach: Copy paste data from Excel Table / Flat File into Analytic Application

 

Step 1: User select the and copies the required data to be uploaded.

 

Step 2: User pastes the copied data into a text input field in the analytic application and clicks on a button to import/process data.

Step 3: Data is processed, If master data does not exist then it will be created, if it exists it will be updated. Transactional data will be added also.

Video of functionality

 

Used code / Script

The script converts the excel table which was copied in a tab delimited like string into an planning model members array, that can be used to call the createmembers & updatemembers functions for master data management and setUserInput function for transactional data.

//Input text field that receives the copy paste from excel
var text = InputField_1.getValue();

//Creation of an array type planning model members of dimension Project, this array will be used in the create and update of master data functions
var result = RB_FACTORY.getMembers("Project", {
    limit: 1
});
console.log(result);

//Creation of an array type planning model members of dimension Project,containing all projects to be used in the create master data functions, to validate if the member already exsists
var existing_projects = RB_FACTORY.getMembers("Project", {
    limit: 5000
});

//Creation of array to be used in the set user input function
var values = ArrayUtils.create(Type.string);

//Creation of one string variable per column to be used in the excel file.

var ID = "";
var DESCRIPTION = "";
var INC_TO_BOTTOMUP = "";
var INPLAN_OOPLAN = "";
var INCL_TP = "";
var BRAND = "";
var BRAND_SEGMENT = "";
var COST_BUCKET = "";
var START_DATE = "";
var END_DATE = "";
var STATUS = "";
var VALUE = "";
var DATE = "";

// Split by tab, the text received in the input text field 
var file = text.split("\t");


console.log(file);

//Initialization of var count and row to be used in the processing of the file array
var count = 0;
var row = 0;


for (var i = 0; i < file.length; i++) {


    if (count < 14) {
        switch (count) {
            //Map the position of the columns in the array to the respective variables.				
            case 0:
                ID = file[i].split(" ")[1];
                if (ID === undefined) {
                    ID = file[i];
                } else if (ID !== undefined) {
                    ID = file[i].split(" ")[1];
                }

            case 1:
                DESCRIPTION = file[i];
            case 2:
                INC_TO_BOTTOMUP = file[i];
            case 3:
                INPLAN_OOPLAN = file[i];
            case 4:
                INCL_TP = file[i];
            case 5:
                BRAND = file[i];
            case 6:
                BRAND_SEGMENT = file[i];
            case 7:
                COST_BUCKET = file[i];
            case 8:
                START_DATE = file[i];
            case 9:
                END_DATE = file[i];
            case 10:
                STATUS = file[i];
            case 12:
                DATE = file[i].split(" ")[0];
        }


    }
    count = count + 1;
    if (count > 13) {
        // Push dimension and property values into the result planning model members type array (to be used in the create and update members functions)
        result.push({
            id: ID,
            description: DESCRIPTION,
            hierarchies: {
                H1: {
                    parentId: "ALL_PROJECT"
                }
            },
            properties: {
                INC_TO_BOTTOMUP: INC_TO_BOTTOMUP,
                INPLAN_OOPLAN: INPLAN_OOPLAN,
                INCL_TP: INCL_TP,
                BRAND: BRAND,
                BRAND_SEGMENT: BRAND_SEGMENT,
                COST_BUCKET: COST_BUCKET,
                START_DATE: START_DATE,
                END_DATE,
                STATUS: STATUS
            }
        });

        count = 0;
        row = row + 1;
        //Map the value to a variable and create an array of values to be used in the ser user input function for the transactional data 
        VALUE = file[i + 1].split(" ")[0];
        values[row] = VALUE;


        //Validate if dimemsion member already exists if yes, then update the member with the data from the file.
        //Any additional business logic can be included here to validate the master data.
        if (existing_projects.indexOf(result[row])) {
            RB_FACTORY.updateMembers("Project", result[row]);
            Application.showMessage(ApplicationMessageType.Success, "Updated Project:" + result[row].id);

            var quarter = "[Date].[YQM].[Date.CALQUARTER].[" + DATE.substr(0, 4) + DATE.substr(DATE.length - 1, 1) + "]";
            console.log("VALUE IS" + values[row]);

        }
        //If the dimemsion member does not exsis create the new member with all the attributes in the file.
        //Any additional  business logic can be included here to validate the master data.
        else {
            RB_FACTORY.createMembers("Project", result[row]);
			Table_Projects.getDataSource().refreshData();
			
			
			
            Application.showMessage(ApplicationMessageType.Success, "Created Project:" + result[row].id);
			
        }
        //Create transactional data in the model in case amount/value exists, if not only master data will be created	
		
		if(values[row] ){
	
	
		Table_Projects.getDataSource().refreshData();
		
        var input = Table_Projects.getPlanning().setUserInput({
            "Version": "public." + "FC1",
            "@MeasureDimension": "Productivity",
            "Date": quarter,
            "Project": "[Project].[H1].&[" + ID + "]"
        }, values[row]);
		
       Table_Projects.getPlanning().submitData();
	   	
		console.log (input);
		}
    }

}

 

Conclusion

This approach shows a different way to upload external master data and transactional data into SAP Analytics Cloud Planning models, the logic / script can be adjusted to accommodate more complex data validations / business rules.

Looking forward to read your comments / feedbacks!

 

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Simon Lewis Head
      Simon Lewis Head

      This is a very useful addition to the user experience. Thank you

      Author's profile photo Arpit Shah
      Arpit Shah

      This is very useful. Can you tell me how many line items we can upload at a time ?

      Author's profile photo Manuel Mora Rodrigues
      Manuel Mora Rodrigues
      Blog Post Author

      I don't know the maximum limit, but I tested with 10k rows and it worked. Please take into consideration that if you load big amounts of data it might take a while to run.

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Not necessarily nr. of items, but you will quickly hit limits if you add more dimensions to the table. This is because the used setUserInput() API can only work on displayed cells (in unbooked mode). For example: 6 dimensions in a sparse data set of just 10 rows will cause generation of 1M cells, which is a showstopper for performance.

      What is good in above use case is that a lot of attributes were used in the Project dimension, avoiding the use of additional dimensions in the planning model.

      Author's profile photo Manuel Mora Rodrigues
      Manuel Mora Rodrigues
      Blog Post Author

      In a situation with multiple dimensions you could potentially replace the setUseinput () with a triggering of a data action to create transactional data, with that approach you would not be required to have unbooked data.

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Hi Manuel, I really don't see how that can work. Please elaborate. 🙂

      Author's profile photo Manuel Mora Rodrigues
      Manuel Mora Rodrigues
      Blog Post Author

      Instead of using the setUserinput() API to create transactional data, you can use the data action.execute() and pass the same parameters to write transactional data.

      You would need to create a simple data action to support this process.

      https://blogs.sap.com/2021/03/29/the-data-action-api-for-analytics-designer-beautiful-game-changer/

      Author's profile photo Jef Baeyens
      Jef Baeyens

      If you believe that works, I want to see that in your next blog!

      Author's profile photo Andreia Palma
      Andreia Palma

      This is an excellent insight, very helpful. Thank you for sharing your experience.

      Author's profile photo Holger Lämmel
      Holger Lämmel

      Good information Manuel! Is there also an option to not create master data but rather gives some feedback to the user if it is not existing or wrong IDs etc?

      Author's profile photo Manuel Mora Rodrigues
      Manuel Mora Rodrigues
      Blog Post Author

      Yes, after copying the table into the text input field and "digest" the string into an array you can enrich it with other validations depending on the business requirement.

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak

      Great post!

      Thanks for sharing! Very useful functionality. Importing Excel data to models wasn't friendly at all. I will test it.

       

      kind regards

      Mayumi

      Author's profile photo Parameshwaran S
      Parameshwaran S

      Hi Manuel,

       

      Great Solution.

      Just like @Sangamesh Reshmi even I am getting error for Transaction data but able to update Master data. I am trying to replicate your solution but the date format for us is in "YYYYMM". Please find the code below.

      //Input text field that receives the copy paste from excel
      var text = InputField_1.getValue();
      
      //Creation of an array type planning model members of dimension Project, this array will be used in the create and update of master data functions
      var result = Model_Planning.getMembers("DIMENSION", {
          limit: 1
      });
      console.log(result);
      
      //Creation of an array type planning model members of dimension Project,containing all projects to be used in the create master data functions, to validate if the member already exsists
      var existing_projects = Model_Planning.getMembers("DIMENSION", {
          limit: 5000
      });
      
      //Creation of array to be used in the set user input function
      var values = ArrayUtils.create(Type.string);
      
      //Creation of one string variable per column to be used in the excel file.
      
      var ID = "";
      var DESCRIPTION = "";
      var ENTITY = "";
      var ENTITY_DESC = "";
      var SAP_ENTITY = "";
      var SAP_ENTITY_DESC = "";
      var VALUE = "";
      var DATE = "";
      
      // Split by tab, the text received in the input text field 
      var file = text.split("\t");
      
      
      console.log(file);
      
      //Initialization of var count and row to be used in the processing of the file array
      var count = 0;
      var row = 0;
      
      
      for (var i = 0; i < file.length; i++) {
      
      
          if (count < 9) {
              switch (count) {
                  //Map the position of the columns in the array to the respective variables.				
                  case 0:
                      ID = file[i].split(" ")[1];
                      if (ID === undefined) {
                          ID = file[i];
                      } else if (ID !== undefined) {
                          ID = file[i].split(" ")[1];
                      }
      
                  case 1:
                      DESCRIPTION = file[i];
                  case 2:
      	ENTITY = file[i];
                  case 3:
                      ENTITY_DESC = file[i];
                  case 4:
                      SAP_ENTITY = file[i];
                  case 5:
                      SAP_ENTITY_DESC = file[i];
                  case 6:
                      DATE = file[i].split(" ")[0];
              }
      
      
          }
          count = count + 1;
          if (count > 8) {
              // Push dimension and property values into the result planning model members type array (to be used in the create and update members functions)
              result.push({
                  id: ID,
                  description: DESCRIPTION,
                  hierarchies: {
                      ALL_LOCATION: {
                          parentId: "ALL LOCATION"
                      }
                  },
                  properties: {
                      ENTITY: ENTITY,
      	 ENTITY_DESC: ENTITY_DESC,
      				SAP_ENTITY: SAP_ENTITY,
                      SAP_ENTITY_DESC: SAP_ENTITY_DESC,
                      
                  }
              });
      
              count = 0;
              row = row + 1;
              //Map the value to a variable and create an array of values to be used in the ser user input function for the transactional data 
              VALUE = file[i + 1].split(" ")[0];
              values[row] = VALUE;
      
      
             //Validate if dimemsion member already exists if yes, then update the member with the data from the file.
              //Any additional business logic can be included here to validate the master data.
              if (existing_projects.indexOf(result[row])) {
                  Model_Planning.updateMembers("DIMENSION", result[row]);
                  Application.showMessage(ApplicationMessageType.Success, "Updated Location:" + result[row].id);
      			var quarter = "[Date].[YM].[Date.CALMONTH].[" + DATE.substr(0, 6) + "]";
      			console.log("VALUE IS" + values[row]);
              }
              //If the dimemsion member does not exsis create the new member with all the attributes in the file.
              //Any additional  business logic can be included here to validate the master data.
              else {
                  Model_Planning.createMembers("DIMENSION", result[row]);
      			Table_Projects.getDataSource().refreshData();
                  Application.showMessage(ApplicationMessageType.Success, "Created Location:" + result[row].id);
      			
              }
              //Create transactional data in the model in case amount/value exists, if not only master data will be created	
      		
      		if(values[row] ){	
      		Table_Projects.getDataSource().refreshData();
      	
              var input = Table_Projects.getPlanning().setUserInput({
                  "Version": "public." + "Actual",
                  "@MeasureDimension": "AMOUNT_GC",
                  "Date": quarter,
                  "DIMENSION": "[DIMENSION].[ALL_LOCATION].&[" + ID + "]"
              }, values[row]);
      		
             Table_Projects.getPlanning().submitData();
      	   	
      		console.log (input);
      		}
      
          }
      
      	}
      

       

      Data to be uploaded

       

      Error

       

      Thanks in Advance

      Paramesh

      Author's profile photo Paul McKinsey
      Paul McKinsey

      Hi,

      I was struggling really hard with this feature.

      The code above is good as a starting point but there were several places where I had to adjust or amend it.

      I've finally got the "Copy & Paste" feature for some simplified models BUT..

      There was NO WAY to set properly the setUserInput command for the selection of standard dimension Date, because of its own standard hierarchies and properties.

      Even THERE IS NO SAP Documentation (explanatory enough) around this, especially when the dimension has hierarchies.

      For sure if you do not get the proper setUserInput when coding, you will get the red message saying the proper conditions were not met when running application

      I would really appreciate SAP could release the proper information around this basic configuration: setting setUserInput for standard dimension Date.

      Regards,

      Author's profile photo Paul McKinsey
      Paul McKinsey

      Hi,

      I final tip...

      I really hope I am wrong but I think the trick of this this code resides on the Dimensions included into the SAC table (DataSource) MUST be configured with the "Unbooked Data" option, checked on.

      So, in case your table has many Dimensions with a lot of members each, and this representing a huge number of unbooked combination records (Number of Dimensions x Number of Members), the code will not work because SAC would not support that number of records.

      More specifically, the own API Reference Guide, when referring to setUserInput, it mentions:

      "setUserInput(selectedDataSelectionvaluestring)boolean
      Sets a value to data cells. The data cells are specified by the selection. The selection references a visible data cell in the rows or columns of the table"
      . (Clear enough)

      It is really a pity this still has these restrictions and the expectation could not be covered. We will have to see if Q2-2023 SAC release will include a working feature for Copy & Paste from Excel as scheduled or it would be postponed.

      Best regards,

      Author's profile photo Zackary Suarez
      Zackary Suarez

      Great blog Manuel Mora Rodrigues An excellent starting point for this alternative method!

      I agree with the above comments on having unbooked data and "visible" intersections for this to work.  Some use cases involve multiple dimensions, resulting in a very big table when unbooked.  The other concern is submitting 1 record at a time.

      Tips below for testing:

      Tip #1 - Check the Granularity of the Model - is it Yearly, Quarterly, or Monthly

      Tip #2 - Check the syntax of the ID's used in the dimension members AND hierarchy

      Tip #3 - Check what the Date dimension hierarchy is set in the application table - is it YQM or YM.

      Tip #4 - Check application ID's are in line with your logic - Table_1 or Table_Project

      Tip #5 - Dimensions can be flat (except for Date) or show leaves.  The intersections have to be visible.  This will not work if the hierarchy is collapsed.

      Tip #6 - Use consol.log() on every variable for a better understanding of the logic.  You can use the below code to identify variables more easily with Chrome Debugger (F12) using different "BREAKS".

      console.log("BREAK1");
      console.log(period);
      console.log("BREAK2");

       

      Below is a condensed version for transactional data upload only.  Master data maintenance can be added from the original code.  Holger Lämmel

      var text = InputField_1.getValue();
      var values = ArrayUtils.create(Type.string);
      var file = text.split("\t");
      var count = 0;
      var row = 0;
      var ID = "";
      var VALUE = "";
      var DATE = "";
      var EMPLOYEE = "";
      for (var i = 0; i < file.length; i++) {
      	if (count < 5) {
              switch (count) {
      	case 0:
      	 	ID = file[i].split(" ")[1];
                      if (ID === undefined) {
                          ID = file[i];
                      } else if (ID !== undefined) {
                          ID = file[i].split(" ")[1];
                      }
      	case 2:
      		DATE = file[i].split(" ")[0];
      	case 3:
      		EMPLOYEE = file[i].split(" ")[0];
      		}
      	}
      	count = count + 1;
      	if (count > 4) {
      		count = 0;
      		row = row + 1;
      		VALUE = file[i + 1].split(" ")[0];
         	 	values[row] = VALUE;
      	//var quarter = "[Date].[YQM].&[" + DATE.substr(0, 4) + DATE.substr(DATE.length - 1, 1) + "]"; **Original
      	var period = "[Date].[YQM].&[" + DATE + "]";	
      		if(values[row] ){
      		Table_Projects.getDataSource().refreshData();
              var input = Table_Projects.getPlanning().setUserInput({
                  "Version": "public." + "FC1",
                  "@MeasureDimension": "Productivity",
                  "Date": period,
                  "Project": "[Project].[H1].&[" + ID + "]",
      			"Employee":"[Employee].[H1].&[" + EMPLOYEE + "]"
              }, values[row]);
             Table_Projects.getPlanning().submitData();
      		console.log (input);
      		}
      	}
      }

      Here is my dataset with YYYYMM.  Parameshwaran S

       

      0 1 2 3 4 5
      ID VERSION DATE EMPLOYEE MEASURE VALUE
      PJT_1 FC1 202301 A Productivity 50000
      PJT_2 FC1 202301 B Productivity 70000

      Happy Learning!

       

      Questions I have for anyone to answer.

      • Can we submit all entries at once instead of 1 record at a time? Please provide the code if you are able to solve it.
      • What is the pretty printer hotkey combination? 🙂