Skip to main content

Celonis Product Documentation

Microsoft SharePoint Connection

The SharePoint Extractor is based on the Microsoft Graph API, which requires an Azure cloud infrastructure.

It is specifically designed to download files (xlsx and CSV) from Microsoft SharePoint and to push them to the Celonis EMS.

Note

The SharePoint Extractor is a python extractor. This means it is implemented in a ML App.

You can find the python script as a Jupyter Notebook for download below. The file can be uploaded into the ML Workbench.

Azure Portal Account Type

For every step in the Azure Portal, you have to be logged in with an admin account.

Steps to set up a SharePoint connection

Step 1: Application Setup in Microsoft Azure

The first step is to set up a new application within the Azure Portal.

1. Sign in to your Azure Portal and go toApp Registrations. On the top left, you click onNew registration.

35554337.png

2. On the registration page, specify the following:

  • Name (e.g. msgraph-app)

  • Supported account types = <choose the fitting value> (e.g. Accounts in any organizational directory (Any Azure AD directory - Multitenant))

  • Redirect URI

3. Press theRegisterbutton. You are directed to the applicationOverviewpage. Save yourApplication (client) IDandDirectory (tenant) IDsince you need them for the authorization setup of the python extractor. You can always come back to this page.

4. Go toCertificates & secretsand click onNew client secret.

35554340.png

5. Choose a suitable description (e.g. Secret1), a suitable timeframe (e.g.in 1 year), and click onAdd. You can then see your newly created client secret. Make sure to copy the value and store it somewhere safe. Since this value is equivalent to a password and you only see the value this one time.

Step 2: Choose Authentication Scenario

The next step is to specify the permissions. The authentication scenario depends on the permissions you want to give your application.

There are two scenarios to choose from:

  • Delegated Permissions: the application runs on behalf of the user

  • Application Permissions: the application performs without any user context

In general, the application permissions need holistic access to the directory, while the delegated permissions work on a user level.

The MS Graph extractor searches for a file (Documentation) and then downloads it (Documentation). Therefore, it needs specific permissions which will be discussed in the next step.

Choose either permission scenario and proceed with the specific step (either 3.1 or 3.2).

Step 3.1: Application Permissions

1. Click on API Permissions > Add a Permission > Microsoft Graph > Application Permissions

35554339.png

2. Search and select the following permissions:

  • Files.Read.All,

  • Sites.Read.All,

  • Sites.ReadWrite.All

3. PressAdd permissionsandGrand admin consent for <YOUR_ENVIRONMENT> (example from the image: Add permissions and Grand admin consent for democelonis)

35554338.png

As a result, it should look like in the image below with the green check marks next to the required permissions.

35555674.png

4. Go toHomeand selectGroups

35554344.png

5. Here you can create a new group with all users that should have access to your files or you can choose an already existing group. You only need to copy theObject Id(also calledGroup Id) for the extractor.

6. Now you can go to the Celonis EMS and select the extractor script in your Machine Learning App.

35554343.png

7. We are only interested in this field. Here you need to specify the following:

  • file_name: enter the file name with the correct spelling including the data type (.xlsx or .csv; e.g.test_file.xlsx)

  • file_type: enterEXCELfor an excel spreadsheet orCSVfor a CSV file

  • app_id: enter your application (client) id

  • app_secret: enter your application secret

  • tenant_id: enter your tenant id

  • group_id: enter your group id

  • celonis_team_url: your team URL can be found in URL of your Celonis environment (e.g.name.eu-1.celonis.cloud)

  • celonis_api_token: when you logged into your Celonis account on the EMS, click on your profile on the top right corner and then onEdit Profile. (See image below)

35554342.png

Scroll down to the API-Keys section, enter a name for your API key (e.g. MS Graph API Key), and press Create API Key.

35554341.png

A pop-up appears which displays the API key you need to enter into the celonis_api_token field in the extractor script.

  • celonis_pool_id: you can find the Celonis pool id when you click on the Data Integration tab on the top. Now, select (or create) the data pool to which you want to push the spreadsheet data. Your URL should look like <ENVIRONMENT>.eu-1.celonis.cloud/integration/ui/pools/11111111-aaa1-111a-aa1a-11111a1a1111/overview. The relevant part for this field is right behind ...pools/ and before .../overview.

8. Now you can set up the scheduling for the ML App and the Data Model.

Step 3.2: Delegated Permissions

1. Click on API Permissions > Add a Permission > Microsoft Graph > Delegated Permissions

35554349.png

2. Search and select the following permissions:

  • Files.Read.All

3. PressAdd permissionsandGrand admin consent for <YOUR_ENVIRONMENT>

35554348.png

As a result, it should look like in the image below with the green check marks next to the required permissions.

35555674.png

4. Now you can go to the Celonis EMS and select the extractor script in your Machine Learning App.

35554347.png

5. We are only interested in this field (see image above). Here you need to specify the following:

  • file_name: enter the file name with the correct spelling including the data type (.xlsx or .csv; e.g.test_file.xlsx)

  • file_type: enterEXCELfor an excel spreadsheet orCSVfor a CSV file

  • app_id: enter your application (client) id

  • tenant_id: enter your tenant id

  • username: enter your Mircosoft username/email address

  • password: enter your Microsoft password

  • celonis_team_url: your team URL can be found in URL of your Celonis environment (e.g.name.eu-1.celonis.cloud)

  • celonis_api_token: when you logged into your Celonis account on the EMS, click on your profile on the top right corner and then onEdit Profile.

35554346.png

Scroll down to the API-Keys section, enter a name for your API key (e.g. MS Graph API Key), and press Create API Key.

35554345.png

A pop-up appears which displays the API key you need to enter into the celonis_api_token field in the extractor script.

  • celonis_pool_id: you can find the Celonis pool id when you click on the Data Integration tab on the top. Now, select (or create) the data pool to which you want to push the spreadsheet data. Your URL should look like <ENVIRONMENT>.eu-1.celonis.cloud/integration/ui/pools/11111111-aaa1-111a-aa1a-11111a1a1111/overview. The relevant part for this field is right behind ...pools/ and before .../overview.

6. Now you can set up the scheduling for the ML App and the Data Model.

Dummy User

As one enters the username and password in the extractor script, it is highly recommended to create a dummy user for this.

User Setup

With this setup, the extractor is only able to find files that have been shared with the dummy user's OneDrive. In order to be able to extract the files, you should be able to see them under Shared > Shared with you in the dummy users OneDrive. You can share files from SharePoint with the dummy user and subsequently find them in the respective OneDrive.

35555675.png

Share files with a user's OneDrive

Select the respective file in SharePoint and click on Share. Then send the link to the dummy account.

35555676.png

Note: it might take up to one hour until the file appears in the dummy user's OneDrive Shared with me section.

Deleted File

When the shared file was deleted in SharePoint, you can in most cases still see the file with the dummy user afterward. Please note that the dummy user cannot access it anymore, and therefore, I cannot be extracted.

A new file with the same name as the deleted one can be accessed by the dummy user even though the new file has never been shared.