Skip to Content
Technical Articles
Author's profile photo Jascha Kanngiesser

Automatically Add Members to Spaces in SAP Data Warehouse Cloud Using @sap/dwc-cli

The @sap/dwc-cli Node.js module is available since wave 2021.20. In another blog “New Command-Line Interface for SAP Data Warehouse Cloud – code your way to the cloud!” I describe how to install and use the command-line interface (CLI). With this blog I’m introducing you to one of the most asked use cases for the CLI: automated mass-member assignment to a space in SAP Data Warehouse Cloud. Wow! 🙂

Introduction

With the @sap/dwc-cli Node module being available, you can easily interact with the space management area in your SAP Data Warehouse Cloud tenant. In this blog I demonstrate how you can use the @sap/dwc-cli module to assign a bunch of members specified in a `*.json` file to one of your spaces with two commands in only a few seconds instead of many manual clicks in the UI taking minutes or hours.
After you have read the blog you will know how to automatically retrieve passcodes to send commands to your SAP Data Warehouse Cloud tenant, work with the space definition and add members to your space.

Project Setup

As we will create a Node.js project we have to make sure that Node is installed in our environment.
To check whether Node is installed run
$ node -v
v12.21.0
If you receive a similar output you’re good to go! Otherwise check out the Node website and the information for setting up Node according to your environment.
Next, we create a new folder and initialize our Node project by running
$ mkdir cli-add-users
$ cd cli-add-users
$ npm init --yes
The –yes option added to the npm init command creates a default package.json file sufficient for our case and should look like this:
{
  "name": "dwc-cli-add-users",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
}
Now, the only missing step is to create an empty index.js file next to the package.json file we will fill with some content in the next section. To create the file run if you are using a Unix-like operating system:
$ touch index.js
The folder cli-add-users contains the following files now:
cli-add-users
|- package.json
|- index.js

Tipp: If you want to push your code to a git repository, you also want to create a .gitignore file in the root folder with the following content:

// .gitignore

node_modules

Installing Dependencies

For our project we require few Node dependencies like the @sap/dwc-cli module and other modules to read and write files (fs-extra), a headless chromium browser for automated passcode retrieval (puppeteer) and the path module helping us with directories and files paths.
To install the dependencies run
$ npm install @sap/dwc-cli fs-extra puppeteer path
Running this command installs the CLI locally in your project into the node_modules folder. If you instead want to install the CLI globally or might did so already earlier, you can also remove it from the list of dependencies and install it by running
$ npm install -g @sap/dwc-cli
Note that if you want to use the globally installed CLI you have to replace ./node_modules/@sap/dwc-cli/dist/index.js with dwc in the code example explained in this blog post.
Now we are good to continue with the fun part, the implementation!

Creating the Executable Skeleton

When we later run our Node program by calling node index.js we need to write a function which is executed. Add the following content to the index.js file:
// index.js

(async () => {
    console.log('I was called!');
})();
We created an asynchronous function printing a simple statement to the console. When you now run node index.js you will the the following:
$ node index.js 
I was called!

Defining the Environment

What we want to implement is a list of tasks where we interact with the @sap/dwc-cli module to
  1. retrieve the definition of an existing space and store it locally
  2. read the `*.json` file containing the additional members to add to the space
  3. update the locally stored space definition file with the additional members
  4. push the updated space definition back to our SAP Data Warehouse Cloud tenant
To carry out the four steps we need to know
  1. where the `*.json` file containing the new members is located
  2. where the space definition shall be stored locally
  3. the URL of the SAP Data Warehouse Cloud tenant
  4. the passcode URL of the SAP Data Warehouse Cloud tenant
  5. which space to update
  6. a business username and password used for reading and updating the space in the SAP Data Warehouse Cloud tenant
Let’s go ahead and declare some variables with the required information (make sure to replace the information wrapped in <…>).
// index.js

const MEMBERS_FILE = 'members.json';
const SPACE_DEFINITION_FILE = 'space.json';
const DWC_URL = 'https://<prefix>.<region>.hcs.cloud.sap/'; // eg https://mytenant.eu10.hcs.cloud.sap/
const DWC_PASSCODE_URL = 'https://<prefix>.authentication.<region>.hana.ondemand.com/passcode'; // eg https://mytenant.authentication.eu10.hana.ondemand.com/passcode
const SPACE = '<space ID>';
const USERNAME = '<username>'; // eg firstname.lastname@company.com
const PASSWORD = '<password>';

(async () => {
    console.log('I was called!');
})();

Retrieving the Passcode

For each command we execute through @sap/dwc-cli we have to provide a unique, non-reusable passcode we have to retrieve from the URL specified with DWC_PASSCODE_URL first. We use puppeteer for this, which is a headless chromium able to run in the background and can be controlled programmatically.
When we run our program we create a browser instance in headless mode and navigate to the passcode URL, enter the username and password and wait for the Temporary Authentication Code page to show up. Let’s replace the console.log statement with some meaningful code.
Note: I’m omitting code we’re not touching in the individual steps for readability. The full content of index.js is added again at the end of this post. Also the example grows incrementally which results in partially duplicated code, for example to retrieve the passcode multiple times during the process. The final version shown at the end of this blog post contains an optimized version of the example we create during this post.
// index.js
const puppeteer = require("puppeteer");

const MEMBERS_FILE = 'members.json';
[...]
const PASSWORD = '<password>';

(async () => {
    const browser = await puppeteer.launch();
    const page = await browser.newPage();
    await page.goto(DWC_PASSCODE_URL);

    await page.waitForSelector('#logOnForm', {visible: true, timeout: 5000}); 
    if (await page.$('#logOnForm') !== null) {
        await page.type('#j_username', USERNAME);
        await page.type('#j_password', PASSWORD);
        await page.click('#logOnFormSubmit');
    }

    await page.waitForSelector('div.island > h1 + h2', {visible: true, timeout: 5000});   
    const passcode = await page.$eval('h2', el => el.textContent);
    
    console.log('passcode', passcode);

    await browser.close();
})();
The implementation starts a new Browser instance, navigates to the passcode URL, checks whether the user is logged in already and if not, logs the user in, waits for the Temporary Authentication Code page to appear, retrieves the passcode, logs it to the console, and closes the Browser instance again.
When you now call node index.js you will find a new passcode logged to the console each time you execute the program:
$ node index.js 
passcode qjm4Zlrza2
If you want to see what’s happening for testing your implementation, you can change await puppeteer.launch() to await puppeteer.launch({ headless: false }) and watch the browser do its magic. 😉
Finally, let’s move the part where we retrieve the passcode from the page to a separate function because we need to call it few times while reading and updating the space:
// index.js
[...]
const PASSWORD = '<password>';

let page;

const getPasscode = async () => {
    await page.waitForSelector('div.island > h1 + h2', {visible: true, timeout: 5000}); 
    await page.reload();  
    return await page.$eval('h2', el => el.textContent);
}

(async () => {
    const browser = await puppeteer.launch({ headless: false });
    page = await browser.newPage();
    await page.goto(DWC_PASSCODE_URL);

    await page.waitForSelector('#logOnForm', {visible: true, timeout: 5000}); 
    if (await page.$('#logOnForm') !== null) {
        await page.type('#j_username', USERNAME);
        await page.type('#j_password', PASSWORD);
        await page.click('#logOnFormSubmit');
    }

    const passcode = await getPasscode();
    
    console.log('passcode', passcode);

    await browser.close();
})();

Reading the Space Definition

To read or update a space we call the locally installed CLI as follows using a separate function. The path.join() call points to the locally installed module.
// index.js
const puppeteer = require("puppeteer");
const path = require('path');
const exec = require("child_process").exec;

    [...]
    return await page.$eval('h2', el => el.textContent);
}

const execCommand = async (command) => new Promise(async (res, rej) => {
    const passcode = await getPasscode();
    const cmd = `${path.join(process.cwd(), 'node_modules', '@sap', 'dwc-cli', 'dist', 'main.js')} ${command} -H ${DWC_URL} -p ${passcode}`;
    exec(cmd, (error, stdout, stderr) => {
        if (error) {
            rej({ error, stdout, stderr });
        }
        res({ error, stdout, stderr });
    });
});

(async () => {
    const browser = await puppeteer.launch({ headless: false });
    [...]
The function execCommand expects a command, for example spaces read <Space ID> –filePath <path> and always attaches the global mandatory parameters like -H, –host and -p, –passcode.
Every time you call execCommand it retrieves a new passcode, thus we can remove the const passcode = await getPasscode() call from the main function.
In order to read our space, we then call the function like this in our main function:
// index.js
    [...]
        await page.click('#logOnFormSubmit');
    }

    await execCommand(`spaces read ${SPACE} -f ${SPACE_DEFINITION_FILE}`)

    await browser.close();
})();
Set SPACE to the actual space ID to read and run the program. When finished, you will find a space.json file next to the index.js file containing the space definition.
// space.json
{
  "<Space ID>": {
    "spaceDefinition": {
      "version": "1.0.4",
      [...]
      "members": [],
      [...]
    }
  }
}

Adding New Members

To add new members to the space we have to
  1. create a file members.json containing the list of members we want to add to the space,
  2. read and parse the file containing the list of members using fs-extra.readFile() and JSON.parse(),
  3. merge the list with the list of already assigned members as defined in the space definition file we retrieved from the tenant,
  4. store the updated members list in the local file using fs-extra.writeFile() and
  5. push the updated space definition back to the tenant.
We create the members.json file using the same structure for the list of members used in the space definition file, an array of objects with two properties name containing the user ID and type set to the constant value user.
// members.json
[
    {
        "name": "<User ID 1>",
        "type": "user"
    },
    {
        "name": "<User ID 2>",
        "type": "user"
    },
    {
        "name": "<User ID 3>",
        "type": "user"
    },
    [...]
]
Add as many objects to the array as members you want to add to the space and use the correct user IDs for the name property.
Reading and parsing the members.json file and space.json file can be achieved as follows:
// index.js
[...]
const exec = require("child_process").exec;
const fs = require('fs-extra');
[...]

  await execCommand(`spaces read ${SPACE} -f ${SPACE_DEFINITION_FILE}`);

  const spaceDefinition = JSON.parse(await fs.readFile(SPACE_DEFINITION_FILE, 'utf-8'));
  const additionalMembers = JSON.parse(await fs.readFile(MEMBERS_FILE, 'utf-8'));

  await browser.close();
})();
Merging the list of additional members with the list of existing members and writing it back to disk can be done like this:
  [...]
  const spaceDefinition = JSON.parse(await fs.readFile(SPACE_DEFINITION_FILE, 'utf-8'));
  const additionalMembers = JSON.parse(await fs.readFile(MEMBERS_FILE, 'utf-8'));

  spaceDefinition[SPACE].spaceDefinition.members = spaceDefinition[SPACE].spaceDefinition.members.concat(additionalMembers);

  await fs.writeFile(SPACE_DEFINITION_FILE, JSON.stringify(spaceDefinition, null, 2), 'utf-8');

  await browser.close();
})();
Finally, we need to update the space using the modified local file. To do so, we execute the spaces create command and provide the modified space.json file. Note that we retrieve a new passcode automatically when calling execCommand().
    [...]
    await fs.writeFile(SPACE_DEFINITION_FILE, JSON.stringify(spaceDefinition, null, 2), 'utf-8');

    await execCommand(`spaces create -f ${SPACE_DEFINITION_FILE}`);

    await browser.close();
})();

Conclusion

Et voilà, there you go! The space was updated successfully and the new members have been correctly assigned. You see, assigning many members to a space in SAP Data Warehouse Cloud is not so complicated when using the @sap/dwc-cli CLI. Of course, the greatest benefit comes with automating the creation of the list of members to be assigned which we hardcoded in the members.json file. If you replace the respective lines of code with a automated process to, for example, convert an excel sheet into the right JSON format and merge this with the list of existing members assigned to your space, that’s then where the beauty lies! To close, here’s the full code example for your reference:
// index.js

const puppeteer = require("puppeteer");
const path = require('path');
const exec = require("child_process").exec;
const fs = require('fs-extra');

const MEMBERS_FILE = 'members.json';
const SPACE_DEFINITION_FILE = 'space.json';
const DWC_URL = 'https://<prefix>.<region>.hcs.cloud.sap/'; // eg https://mytenant.eu10.hcs.cloud.sap/
const DWC_PASSCODE_URL = 'https://<prefix>.authentication.<region>.hana.ondemand.com/passcode'; // eg https://mytenant.authentication.eu10.hana.ondemand.com/passcode
const SPACE = '<space ID>';
const USERNAME = '<username>'; // eg firstname.lastname@company.com
const PASSWORD = '<password>';

let page;

const getPasscode = async () => {
    await page.waitForSelector('div.island > h1 + h2', {visible: true, timeout: 5000}); 
    await page.reload();  
    return await page.$eval('h2', el => el.textContent);
}

const execCommand = async (command) => new Promise(async (res, rej) => {
    const passcode = await getPasscode();
    const cmd = `${path.join(process.cwd(), 'node_modules', '@sap', 'dwc-cli', 'dist', 'main.js')} ${command} -H ${DWC_URL} -p ${passcode} -V`;
    exec(cmd, (error, stdout, stderr) => {
        if (error) {
            rej({ error, stdout, stderr });
        }
        res({ error, stdout, stderr });
    });
});

(async () => {
    const browser = await puppeteer.launch({ headless: true });
    page = await browser.newPage();
    await page.goto(DWC_PASSCODE_URL);

    await page.waitForSelector('#logOnForm', {visible: true, timeout: 5000}); 
    if (await page.$('#logOnForm') !== null) {
        await page.type('#j_username', USERNAME);
        await page.type('#j_password', PASSWORD);
        await page.click('#logOnFormSubmit');
    }
    
    await execCommand(`spaces read ${SPACE} -f ${SPACE_DEFINITION_FILE}`);

    const spaceDefinition = JSON.parse(await fs.readFile(SPACE_DEFINITION_FILE, 'utf-8'));
    const additionalMembers = JSON.parse(await fs.readFile(MEMBERS_FILE, 'utf-8'));

    spaceDefinition[SPACE].spaceDefinition.members = spaceDefinition[SPACE].spaceDefinition.members.concat(additionalMembers);

    await fs.writeFile(SPACE_DEFINITION_FILE, JSON.stringify(spaceDefinition, null, 2), 'utf-8');

    await execCommand(`spaces create -f ${SPACE_DEFINITION_FILE}`);

    await browser.close();
})();

Further Reading

Blog: New Command-Line Interface for SAP Data Warehouse Cloud – code your way to the cloud!

Command-Line Interface for SAP Data Warehouse Cloud on npmjs.com

Command-Line Interface for SAP Data Warehouse Cloud on SAP Help

Get your SAP Data Warehouse Cloud 30 Days Trial Account

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Afshin Irani
      Afshin Irani

      Hi Jascha Kanngiesser,

       

      Thank you for this npm module and I can see great potential with this . Would it be possible in the near future to able to create a table using json ( csn ) ? Ideal scenario would be combining this with the existing cap library would be really handy

      Thank you

      Afshin

       

       

      Author's profile photo Jascha Kanngiesser
      Jascha Kanngiesser
      Blog Post Author

      Hi Afshin Irani thank you for your feedback and that you see great potential in this tool! Deploying entities within spaces like tables and views is on our bucket list for sure. We didn't yet consider the CAP library aspect but I noted it down. Stay tuned for updates!