Find a Data Source Database

This example demonstrates how to set hierarchy, measure adn member level security in a data model programmatically.

The example uses API authentication driven from JavaScript. See Authentication APIs for alternatives.

// URL of the Pyramid installation and the path to the API 2.0 REST methods
var pyramidURL = "http://mysite.com/api2/";

// step 1: authenticate admin account and get token
// NOTE: callApi method is a generic REST method shown below.
let token = callApi("auth/authenticateUser",{
	"data":{
		"userName":"adminUser",
		"password":"abc123!"
	}
},false);
log("got token "+token);

//step 2: find server by name
let server= callApi("dataSources/findServerByName ",{
	"searchCriteria": {
		"searchValue":"Pyramid IMDB"
	},
	"auth": token
});
 // get server's system ID
let serverId = server.data[0].itemId

//step 3: find database by name
let database= callApi("dataSources/findDatabaseByName ",{
	"searchCriteria":{
	    "dataContainerId":serverId,
	    searchCriteria:{
		    "searchValue":"PyramidDemo"
	    }
    },
	"auth": token
});

//get database's system ID
let databaseId = database.data[0].itemId

//step 4: find model by name for the chosen database
let model= callApi("dataSources/findModelByName ",{
	"searchCriteria":{
	    "dataContainerId":databaseId,
	    searchCriteria:{
		    "searchValue":"PyramidDemo"
	    }
    },
	"auth": token
});

//get model's system ID
let modelId = model.data[0].itemId


//step 4: find role by name
let role= callApi("access/getRolesByName ",{
	searchCriteria:{
		    "searchValue":"role 1"
    },
	"auth": token
});

//get role's system ID
let roleId = role.data[0].roleId



//step 5A: adding member security to the model by specific selection
// set this role to see 2009 and 2010 (and by definition hide any other year)
let addMembersSecurityToModel= callApi("dataSources/addMembersSecurityToModel ",{
	"hierarchyMeasureSecurityApiObject":{
	    "modelId":modelId,
	    "roleId":roleId,
	    "enableMode": true,
	    "hierarchyUniqueName": "[Date].[year]", //this is the unique name of the parent hierarchy
	    "members":["2009", "2010"] //list of members (using their captions)
    },
	"auth": token
});

//step 5B: adding member security to the model by script
// set this role to see any country where the non empty cross join 
// to the 'user' column in the 'security' table matches the logged in user's username (using the username function)
let addMembersSecurityToModel= callApi("dataSources/addMembersSecurityToModel ",{
	"hierarchyMeasureSecurityApiObject":{
	    "modelId":modelId,
	    "roleId":roleId,
	    "enableMode": true,
	    "hierarchyUniqueName": "[Geography].[Country]", //this is the unique name of the parent hierarchy
	    "script":"NonEmpty([Geography].[Country].AllMembers, {StrToMember([Security].[User],UserName())})" //logical PQL script for defining a list of members
    },
	"auth": token
});

//step 5C: adding member security to the model by script
// set this role to see any records in the model where it is inherently filtered (inner joined) 
// against the value of the currently logged in user's name in the 'userIds' column in the 'Org' table (from the username function)
let addMembersSecurityToModel= callApi("dataSources/addMembersSecurityToModel ",{
	"hierarchyMeasureSecurityApiObject":{
	    "modelId":modelId,
	    "roleId":roleId,
	    "enableMode": true,
	    "hierarchyUniqueName": "[Security].[User]", //this is the unique name of the parent hierarchy
	    "script":"StrToMember([Org].[UserIds],UserName())" //logical PQL script for defining a list of members
    },
	"auth": token
});



//step 6: adding hierarchy security to the model
// set this role to NOT see the state hierarchy in the customer dimension/table
let addHierarchySecurityToModel= callApi("dataSources/addHierarchySecurityToModel ",{
	"hierarchyMeasureSecurityApiObject":{
	    "modelId":modelId,
	    "roleId":roleId,
	    "enableMode": false,
	    "uniqueNames": ["[Customer].[State]"] //this is the unique name of the hierarchy, in a list
    },
	"auth": token
});


//step 7: adding measure security to the model
// set this role to see the the cost measure in the model (and by definition to hide all other measures)
let addMeasureSecurityToModel= callApi("dataSources/addMeasureSecurityToModel ",{
	"hierarchyMeasureSecurityApiObject":{
	    "modelId":modelId,
	    "roleId":roleId,
	    "enableMode": true,
	    "uniqueNames": ["[measures].[Cost]"] //this is the unique name of the measure, in a list
    },
	"auth": token
});
			
				

// ##### optional generic login method for debugging ##############
function log(msg){
	document.write(msg);
	console.log(msg);
}

// ##### generic REST API calling method ##############
function callApi(path,data,parseResult=true){
	var xhttp = new XMLHttpRequest();
	xhttp.open("POST", pyramidURL+path, false);
	xhttp.send(JSON.stringify(data));
	if(parseResult){
		return JSON.parse(xhttp.responseText);
	}else{
		return xhttp.responseText;
	}
}