Accessing SharePoint files from Datasphere using BTP Open Connectors

[Uzair30]

Our customer had a requirement  to combine data from SharePoint files with other data in an SAP Analytics Cloud (SAC) dashboard.  Since there is no native SharePoint connector for SAP Datasphere, we created a connection using SAP BTP’s open connector, which we then utilize from Datasphere. 

Here are the steps:

1. Register an app for SharePoint API Access in Azure Active Directory
2. Configure the registered application’s SharePoint API permissions
3. Generate certificates and secrets for your registered app
4. Create a SharePoint Open Connector instance in SAP BTP Integration Suite
5. Establish a connection to your SharePoint Open Connector in Datasphere
6. In Datasphere, create a dataflow to read your SharePoint directory
7. In SAC create your story using your SharePoint-based model in Datasphere

1.    Register an app for SharePoint API Access in Azure Active Directory

• Logon to your Azure Portal using your SharePoint online credentials
• Navigate to Azure Active Directory and select App Registrations
• Click New Registration to create an OAuth application

• In the application registration prompt, enter an application name e.g. SharePointOAuthApp
• Select the supported account types

Enter the redirect URL for SAP Cloud Platform Open Connectors:  https://auth.cloudelements.io/oauth 

 

2.    Configure the registered application’s SharePoint API permissions

 The registered application by default has only User.Read permission from Microsoft Graph APIs, so you need to add in permission to access SharePoint REST APIs.

• Select API permissions tab and then click on Add a permission to add permissions for SharePoint REST APIs. 

• Select SharePoint to add in the API permissions for SharePoint

 
 In SAP Cloud Platform Open Connectors, access to the API is via the signed-in user.

• Select Delegated Permissions for accessing APIs as signed-in user 

•  Select permissions shown below, then click Add permissions 

 
 Some of the selected permissions require administrator consent 

• After the permission is selected, click on Grant admin access

 The permission may take some time to updated as shown in the warning, so wait for few minutes before selecting the Grant admin consent option. 

• Select Yes if you are prompted to confirm the administrator consent 

  When successful, the status will change to Granted for your user. 

3.    Generate certificates and secrets for your registered app

For connecting to your SharePoint Online account from SAP Cloud Platform Open Connectors, an OAuth secret and client ID are required.

• Select Certificates & secrets tab, click on New client secret. 

• Enter a description for your OAuth secret and add

• Note! Copy and save the generated client secret.  You need to provide the secret in order to create the SharePoint connector instance from SAP Open Connectors, and it cannot be retrieved later.

• To get your OAuth Client ID , select Overview tab, copy the Application (client) ID value. 

4.    Create a SharePoint Open Connector instance in SAP BTP Integration Suite

• In the SAP BTP navigate to Integration Suite
• Select Extend Non-SAP Connectivity. If this option is not visible, click  Manage Capabilities and enable Open Connectors capability.
   

•   Select the Connectors tab
• Hover over the SharePoint connector and select  Authenticate to connect to your own SharePoint account. 

• In the connection wizard, enter a name for your connector instance
• Enter your SharePoint Site Address in the format {your_sharepoint_domain}.sharepoint.com
• In API Key enter your copied OAuth Client ID
• In the API Secret dialog enter your copied OAuth secret
• Select Show Optional Fields. 

• Enable graph authentication to prompt user authentication (this corresponds with the delegated scoped defined on Azure)
• Select Create Instance

You may be prompted to enter your SharePoint user credentials if  you are not already logged into your SharePoint account  
 

• Trust your app
  

After successfully creating your authenticated connection to your SharePoint account, you can test it

• Choose Test in the API docs

• Select GET /files to read files from your SharePoint sites

• Click on Try it Out 

• Insert the file path for a valid file in the folder (no sub folders) and choose execute

  (Note:- If your site contains spaces, then in the Subsite field enter the site name without spaces.) 

Once the test has run successfully, the updated file should be available for download.

5.    Establish a connection to your SharePoint Open Connector in Datasphere 

• Enter your SAP BTP Subaccount Region  

The BTP region can be found within the Account Explorer page in the BTP Cockpit  

• Enter your Organization Secret 
• Enter your User Secret 

For the Organizational and User secrets, after creating the instance to the app you need, make any type of API request, for example "GET" 

The authorization String will contain the necessary detail as well. 

6.    In Datasphere, create a dataflow to read your SharePoint directory

• Create a dataflow to read your SharePoint directory and post the data into a local table
• Create a fact view to transform your data as required
• Create an analytical model for external consumption

7.    In SAC create your story using your SharePoint-based Datasphere model