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

      13 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