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!