The General Practice to Import Master Data into SAC Dimensions is through CDS views(Live Connection).

Now one of Our Customers wants to create New Dimension members at SAC Level. Since the user doesn’t have access to Upload Master Data at the Dimension Level.

Steps to Upload Master Data Flat file to a SAC Dimension through Analytic Application Designer :

•  JSON and JavaScript files needs to be developed in GitHub Application for a Custom Widget in SAC Analytics Application.( Please refer the link for JSON and JavaScript Files https://github.com/Priyankasac/Excel-Upload)
• Scenario: Consider the Asset as a Dimension to Update Master Data through Flat File.

Below are the prerequisite files to Create Excel File Uploader Dialogue Box and Custom Widget in Analytics Designer

• Asset.JSON
• excel.js
• aps_excel.js
• xlsx.js

File References:

• In the Asset.JSON file there is a reference to excel.js and aps_excel.js
• In the excel.js file there is a reference to xlsx.js

In excel.js we need to Maintain the Field Length and its Sequential Order of the fields that we are loading data through Flat File(The Same Sequence has to be maintained in Code and Flat File).

Below is the screenshot of the Field length and the order of Fields in the excel.js file

Field Length and the Fields may vary between different Scenarios. So based on the Scenarios we have to create New JSON and excel.js files separately

Note: aps_excel.js and xlsx.js files are common for all the custom widgets

Inserting Custom Widget in SAC Analytic Application:

Upload JSON file under custom Widget tab in Analytic Applications Designer

Custom Widget

From the below Screenshot the Asset Custom Widget of type excel is ready to use

Custom Widget in Stories

Note: If we need Multiple Custom Widgets of Type Excel, We need to give different version Numbers in the JSON File

Multiple Custom Widgets

Design through Analytics Application:

Canvas Page:

Create a Canvas Page and Insert the Custom Widget

Script Variable

Create a Script variable of type PlanningModelMember

Script Variable

Planning Model

Insert a Planning Model

Planning Model

Start Event

In the Start event of Custom Widget Include script to update the Master Data Members

On Start

On Start – Sample Code

var Asset = PlanningModel_1.getMembers("D_ASSET");
console.log(Asset.length);
var last_id = 0;
if(Asset.length > 1) {
	last_id = Asset.length;
} 
console.log("last id:");
console.log(last_id);

var str = Asset_1.getUnit();
var str1 = str.split("},{");
var entity = "";
var ID = "";
var DESCRIPTION = "";
var ASSET_TYPE = "";
var COMPANY_CODE = "";
var ASSET_CLASS = "";
var COST_CENTER = "";
var CWIP = "";

var Ar_ID = ArrayUtils.create(Type.string);
var Ar_DESCRIPTION = ArrayUtils.create(Type.string);
var Ar_ASSET_TYPE = ArrayUtils.create(Type.string);
var Ar_COMPANY_CODE = ArrayUtils.create(Type.string);
var Ar_ASSET_CLASS = ArrayUtils.create(Type.string);
var Ar_COST_CENTER = ArrayUtils.create(Type.string);
var Ar_CWIP = ArrayUtils.create(Type.string);

for(var x=0; x<str1.length; x++) {
	var str2 = str1[x].split(",");	

 

	for(var y = 0; y<str2.length; y++) {
		entity = str2[y].replace("[{", "");
		entity = str2[y].replace("}]", "");

		//ID
		if(y === 0) {
			ID = entity.split(":")[1];
			ID = ID.replace('"','');
			ID = ID.replace('"','');
			Ar_ID.push(ID);
		}

		//DESCRIPTION
		if(y === 1) {
			DESCRIPTION = entity.split(":")[1];
			DESCRIPTION = DESCRIPTION.replace('"','');
			DESCRIPTION = DESCRIPTION.replace('"','');			
			Ar_DESCRIPTION.push(DESCRIPTION);
		}

		//ASSET_TYPE
		if(y === 2) {
			ASSET_TYPE = entity.split(":")[1];
			ASSET_TYPE = ASSET_TYPE.replace('"','');
			ASSET_TYPE = ASSET_TYPE.replace('"','');			
			Ar_ASSET_TYPE.push(ASSET_TYPE);
		}

		//COMPANY_CODE
		if(y === 3) {
			COMPANY_CODE = entity.split(":")[1];
			COMPANY_CODE = COMPANY_CODE.replace('"','');
			COMPANY_CODE = COMPANY_CODE.replace('"','');			
			Ar_COMPANY_CODE.push(COMPANY_CODE);
		}

		//ASSET_CLASS
		if(y === 4) {
			ASSET_CLASS = entity.split(":")[1];
			ASSET_CLASS = ASSET_CLASS.replace('"','');
			ASSET_CLASS = ASSET_CLASS.replace('"','');			
			Ar_ASSET_CLASS.push(ASSET_CLASS);
		}

		//COST_CENTER
		if(y === 5) {
			COST_CENTER = entity.split(":")[1];
			COST_CENTER = COST_CENTER.replace('"','');
			COST_CENTER = COST_CENTER.replace('"','');			
			Ar_COST_CENTER.push(COST_CENTER);
		}

		//CWIP
		if(y === 6) {
			CWIP = entity.split(":")[1];
			CWIP = CWIP.replace('"','');
			CWIP = CWIP.replace('"','');			
			Ar_CWIP.push(CWIP);
		}


	}	
}

 console.log(Ar_ID);
console.log(Ar_DESCRIPTION);
console.log(Ar_ASSET_TYPE);
console.log(Ar_COMPANY_CODE);
console.log(Ar_ASSET_CLASS);
console.log(Ar_COST_CENTER);
console.log(Ar_CWIP);

 Application.showBusyIndicator();
var idx = last_id;
for(var i=0; i<Ar_ID.length; i++) {
	idx = idx + 1;
	
	g_properties = ({
    id: Ar_ID[i],
	description: Ar_DESCRIPTION[i],
	hierarchies: {
		H1: {parentId: Ar_ASSET_TYPE[i]},
	},
	    properties: {
      COMPANY_CODE: Ar_COMPANY_CODE[i],
      ASSET_CLASS: Ar_ASSET_CLASS[i],
      COSTCENTER: Ar_COST_CENTER[i],
      CWIP: Ar_CWIP[i],
	  		
    }
});
	
	
	var result = PlanningModel_1.createMembers("D_ASSET",g_properties);
	
	if(result)
		{
			Application.showMessage(ApplicationMessageType.Success,Ar_ID[i]+ "-New Asset Created Sucessfully");
		}
    else{
		Application.showMessage(ApplicationMessageType.Error,Ar_ID[i]+ "-Failed to Create Asset");
	}
	
    console.log(result);
}
Application.hideBusyIndicator();
Table_1.getDataSource().refreshData();

Steps to upload Flat File:

1. Sequence of the fields should be same in the Excel flat file and in the code.

2. Click on Browse -> select the Excel File -> click on Upload.

Upload New Assets

After Upload You will able to see the Sucess Pop Up Messages and the Members are updated in the Dimension