• User Manuals »
  • Denodo Templates for SharePoint - Quick Use Guide

Denodo Templates for SharePoint - Quick Use Guide

Last modified on: 20 Dec 2021

Download original document


Overview

SharePoint Online is a cloud-based service that helps organizations share and manage content, knowledge and applications to:

  • Empower teamwork
  • Quickly find information
  • Seamlessly collaborate across the organization

You can configure Denodo to retrieve OData entities from SharePoint Online by creating a data source using the Denodo OData2 Custom Wrapper and creating a base view for each OData entity. In addition, you can configure Denodo to retrieve data from your SharePoint Online file system by creating JSON data sources and base views in Denodo using the SharePoint API REST.

As SharePoint Online manages a very large amount of data, it may be hard work to add all the required data sources and base views in Denodo. In order to make this process easier and faster, we distribute templates: a set of predefined VQL and properties files. You just have to configure a few parameters in the properties file and import these files into your Denodo server to get access to Adobe Analytics.

The SharePoint Online templates

The SharePoint Online templates are divided in two different script files:

  • sharepoint_odata_entities_templates_denodo.vql
  • sharepoint_api_rest_templates_denodo.vql

The first one contains the data sources and base views for the following SharePoint OData entities:

  • Appdata
  • Appfiles
  • Attachments
  • Books
  • ComposedLooks
  • ConvertedForms
  • Documents
  • Events
  • EventsCategory
  • FormTemplates
  • ListTemplateGallery
  • MaintenanceLogLibrary
  • MasterPageGallery
  • MasterPageGalleryCompatibleSearchDataTypes
  • MasterPageGalleryCompatibleUIVersionS
  • MasterPageGalleryStandalone
  • MasterPageGalleryTargetControlType
  • MasterPageGalleryTargetControlTypeSearch
  • MasterPageGalleryTemplateLevel
  • SearchConfigList
  • SearchConfigListScope
  • SharePointHomeOrgLinks
  • SitePagesSitePageFlags
  • SitePages
  • SolutionGallery
  • StyleLibrary
  • TaxonomyHiddenList
  • ThemeGallery
  • UserInformationList
  • WebPartGallery
  • WebPartGalleryGroup
  • WebPartGalleryRecommendationSettings

The second contains base views created with the JSON datasources that allow you to navigate through your SharePoint Online file system.

Please note: importing these scripts will create a database called sharepoint in your Virtual DataPort installation, and will replace any existing database previously existing with that exact name.

Getting OAuth 2.0 credentials

In order to get the SharePoint credentials to configure the SharePoint templates  you must follow these steps:

  • Register a new App in Azure
  • Configuring access to SharePoint
  • Create OAuth 2.0 credentials

Register an app in Azure

In order to register an app in Azure, follow these steps:

  1. Login to the Azure portal

  1. Navigate to https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps

  1. Press the button New Registration

  1. Give a name for the app

  1. Fill in the Support Account Types field. The type of the application support depends on your requirement. For the purpose of this documentation we are using Accounts in this organizational directory only (Default Directory only - Single tenant).

  1. Enter Redirect URL value. It should be http://localhost:9090/oauth/2.0/redirectURL.jsp

  1. Press the button “Register”

Configuring access to Sharepoint Online

Once the app is registered we will need to grant permissions for clients to access SharePoint Online using the app:

  • In the newly registered application, click on Integration assistant (on the left side menu) and select Daemon for What application types are you building and select Evaluate my registration.
  • Once the evaluation is done, the Recommended configurations are shown.

There are 2 needed configurations that have to be performed to allow the Denodo Platform to access the data in SharePoint: API permissions and client secret generation.

API permissions

Please note: This step is only necessary if you want to use the sharepoint_api_rest_templates_denodo.vql. If you are only interested in retrieving the Sharepoint OData entities, you can avoid this step.

  • Click on the    icon on Configure API permissions and select Go to page.
  • Select Add Permissions. In the Request API permissions pop-up select SharePoint.
  • Select Delegated Permissions and select AllSites.Read as the permissions

 

Client secret key generation

After the API permissions are set we have to generate a new client secret key. In order to generate the key:

  • In the Integration assistance section, select Configure a valid credential.
  • In the Certificates & secrets page select the option New client secret.
  • After generating the Client secret, copy the Value.

 

Create OAuth 2.0 credentials

In the App Registration page in the Azure portal, select the app that has been registered for the SharePoint integration with Denodo and copy the:

  • The Application (client) ID is the Client identifier.
  • The value of the secret created in the previous step is the Client Secret.
  • The Directory tenant ID is the tenant or site_realm.

Create OAuth 2.0 credentials for OData entities templates

Get the Realm of the site

Realm is a constant GUID for a site. In order to retrieve it, a tool such as Postman can be used. To obtain the Realm, it is necessary to carry out the following steps:

  • Make a GET request like this:

  • https://<tenant_name>.sharepoint.com/_vti_bin/client.svc

  • Header:
  • Authorization: Bearer

  • Get the Bearer realm component from the WWW-Authenticate response header and save it.

  • Get the client id component from the WWW-Authenticate response header and save it. This value is what later we will call Audience Principal ID.

Please note: If you’re using cookies you might not get back the WWW-Authenticate header with the Bearer realm. Delete the cookies in Chrome or use the Postman Interceptor to avoid this issue.

Get the authorization code

Construct an authorization url as follows:

https://<tenant_name>.sharepoint.com/_layouts/15/OAuthAuthorize.aspx?client_id=<client_GUID>&scope=Web.Read&response_type=code&redirect_uri=http://localhost:9090/oauth/2.0/redirectURL.jsp

Change the parameters of the URL to fit your connection data.

Navigate to the URL from your browser. Login to the site if you have not logged in already. This opens a consent page prompt to the user to grant (or deny) the app the permissions that the app requests. In this case, the user would be granting the app read access to the current site (Web).

Once you grant the permission (by clicking trust), SharePoint Online asks Access Control Service (ACS) to create a short-lived (approximately 5 minutes) authorization code unique to this combination of user and app. ACS sends the authorization code to the SharePoint site.

SharePoint Online site redirects the browser back to the redirect URI that was specified when the app was registered. It also includes the authorization code as a query string. The redirect URL is structured like the following:

http://localhost:9090/oauth/2.0/redirectURL.jsp/?code=<authcode>

Get the access token and refresh token

Construct the below POST request:

  • URL:
  • https://accounts.accesscontrol.windows.net/<site_realm>/tokens/OAuth/2

  • Header:
  • Content-Type = "application/x-www-form-urlencoded"

  • Post parameters (in the body of the request):

  • grant_type=authorization_code

  • client_id=<client_id>@<site_realm>

  • client_secret=<client_secret>

  • code=<auth_code>

  • redirect_uri=http://localhost:9090/oauth/2.0/redirectURL.jsp

  • resource=<audience_principal_ID>/<site_host>@<site_realm>

Where:

  • <site_realm> is the Bearer realm obtained in the step “Get the Realm of the site”.

  • <client_id> is <client id when registering the app>@<site realm from the step “Get the Realm of the site>.

  • <client_secret> is the client_secret obtained when registering the app.

  • <auth_code> is the auth code obtained in step “Get the authorization code”.

  • <resource> is <audience principal ID>/<sharepoint domain>@<site realm>.

  • <audience_principal_ID> is a permanent security principal ID for SharePoint. <audience_principal_ID> is obtained in step “Get the Realm of the site” (the value “client_id” in the response header WWW-Authenticate).

        

NOTE: all values need to be URL encoded (including the client_secret)

Create OAuth 2.0 credentials for REST API templates

You can use the credentials created in the previous step to get the access token and the refresh token through the OAuth 2.0 wizard of the VDP.

Where:

  • Client identifier: the client id from the newly created application

  • Client secret: the value of the secret generated in the previous step for the newly created application.

  • Redirect URI: the default one.

  • Scopes: add  AllSites.Read.

After providing all the values click on the option Generate Authorization URL  and, when the Authorization URL is generated, click on the open URL link.

Once the URL is opened on your desired  browser, provide the permission, and once the permission is given a URL with code would be generated. The format of the URL will be: https://<tenant_name>.sharepoint.com/?code=<>&state=<>&session_state=<>#

Paste the same on the Paste the authorized response URL and select Obtain the OAuth 2.0 credentials.

Finally, click on Copy the credentials to the clipboard to save the OAuth tokens.

SharePoint REST API templates

SharePoint includes a REST service that is comparable to the existing SharePoint client object models. Now, you can interact directly with SharePoint objects by using any technology that supports standard REST capabilities.

The VQL configures access to the SharePoint file system using the SharePoint REST API.

Importing artifacts

Before importing the VQL in Denodo, you have to set the following parameters defined in the sharepoint_api_rest_templates_denodo.properties file:

  • sharepoint.tenant.name:  Your SharePoint Online service name.

 

  • sharepoint.realm: The Directory tenant ID.

  • sharepoint.client.id: The client id generated in the Adobe Developer Console in the previous step.

  • sharepoint.client.secret: The client secret generated in the Adobe Developer Console in the previous step.

  • access.token: Provided by VDP Wizard for OAuth 2.0.

  • refresh.token: Provided by VDP Wizard for OAuth 2.0.

  • sharepoint.site.name: The site name of the specific SharePoint Online site you want to retrieve lists and folders content.

  • view.prefix: You can configure the name prefix of every view created in the VDP in order to be able to relate VDP views to SharePoint Online sites. The max length of this parameter is 10 characters.

You can import as many sharepoint_api_rest_templates_denodo.vql scripts as you want for retrieving data from all your SharePoint Online sites. If a long time elapses between importing one script and another, you will have to regenerate the tokens again.

Note: We have used “site2” as the value of the view.prefix  and “SampleSite” as the value of the sharepoint.site.name configuration parameters in the examples shown below.

Once you have set the properties file, you only have to import both the properties and the sharepoint_api_rest_templates_denodo.vql file using the Import option of the VDP Administration Tool:

Refresh

After the import operation finishes, refresh the Server Explorer by selecting File > Refresh and you will be able to see the data sources and views created to access the specified SharePoint Online site lists and folders content.

  • ${view.prefix}_all_lists: Retrieving all the lists of the SharePoint site specified in the sharepoint.site.name property.

  • ${view.prefix}_file: Retrieving a file.
  • Required parameters:
  • file_path: The path to the file.

  • ${view.prefix}_file_list_item_all_fields: Retrieving the value of the ListItemAllFields property of the file.
  • Required parameters:
  • file_path: path to the file.

  • ${view.prefix}_file_modified_by: Retrieving the value of the ModifiedBy property of the file.
  • Required parameters:
  • file_path: path to the file.

  • bv_${view.prefix}_file_version_events: Retrieving the value of the VersionEvents property of the file.
  • Required parameters:
  • file_path: path to the file.

  • bv_${view.prefix}_file_versions: Retrieving the value of the Version property of the file.
  • Required parameters:
  • file_path: path to the file.

  • bv_${view.prefix}_files_attached_to_list: Retrieving all the files that are attached to a list item.
  • Required parameters:
  • list_name: the name of the list.
  • item_id:  the id of the item.

  • ${view.prefix}_files_in_folder: Retrieving the files in a folder.
  • Required parameters:
  • folder_path:  the path of the folder..

  • ${view.prefix}_folder: Retrieving info about the folder.
  • Required parameters:
  • folder_path: the path to the folder.

  • ${view.prefix}_folder_parent: Retrieving the value of the ParentFolder property of the folder.
  • Required parameters:
  • folder_path: the path to the folder.
  • property_name: the name of the property.

  • bv_${view.prefix}_items_by_list_name: Retrieving all items in a list.
  • Required parameters:
  • list_name: the name of the list.

  • bv_${view.prefix}_list_as_stream: Retrieving items as a stream.
  • Required parameters:
  • list_name: the name of the list

  • ${view.prefix}_list_by_guid: Retrieving the details of a list by GUID.
  • Required parameters:
  • guid: The list guid.

  • ${view.prefix}_list_by_name: Retrieve a list by name.
  • Required parameters:
  • list_name: the name of the list

  • ${view.prefix}_subfolders_in_folder: Retrieving the folders in a folder.
  • Required parameters:
  • folder_path: the path to the folder.

  • ${view.prefix}_web: Retrieving the details of the site specified  in the sharepoint.site.name property.

  • bv_${view.prefix}_web_title: Retrieving the title of the site specified in the  sharepoint.site.name property

  • bv_${view.prefix}_excel_file: Retrieving an excel file.
  • Required parameters:
  • file_path: the path of the file to retrieve.

  • ${view.prefix}_folder_full_content: Retrieving both the files and the folders of the folder.
  • Required parameters:
  • folder_path: the path of the folder.

Please note: All paths are relative to /sites/${sharepoint.site.name}/.

Query example

Now, you can execute queries on the SharePoint Online views that have been created:

Query 1: Retrieving all the content of the folder ‘MyLibrary’:

Query 2:  Retrieving the files attached to the item 2 attached to ‘MyList’ list:

Query 3: Retrieving an excel file:

SharePoint OData templates

Importing artifacts

Before importing the VQL into Denodo, you have to download the Denodo OData 2 Custom Wrapper and add  the denodo-odata2-wrapper-7.0-{version}-jar-with-dependencies.jar using the Extension management option of the VDP Administration Tool:

Before importing the VQL in Denodo, you have to set the following parameters defined in the sharepoint_odata_entities_templates_denodo.properties file:

  • sharepoint.tenant.name:  Your SharePoint Online service name.

 

  • sharepoint.audience.principal.id: The value of the client id obtained in the Get Realm of the site step.

  • sharepoint.client.id: The client id generated in the Adobe Developer Console in the previous step.

  • sharepoint.client.secret: The client secret generated in the Adobe Developer Console in the previous step.

  • access.token: provided by VDP Wizard for OAuth 2.0.

  • refresh.token: provided by VDP Wizard for OAuth 2.0.

  • sharepoint.site.name: The site name of the specific SharePoint Online site you want to retrieve lists and folders content.

  • view.prefix: You can configure the name prefix of every view created in the VDP in order to be able to relate VDP views to SharePoint Online sites. The max length of this parameter is 10 characters.

You can import as many sharepoint_odata_entities_templates_denodo.vql scripts as you want for retrieving data from all your SharePoint Online sites. If a long time elapses between importing one script and another, you will have to regenerate the tokens again.

Note: We have used “site2” as the value of the view.prefix and “SampleSite” as the value of the sharepoint.site.name configuration parameters  in the examples shown below.

Once you have set the properties file, you only have to import both the properties and the sharepoint_odata_entities_templates_denodo.vql file using the Import option of the VDP Administration Tool:

Refresh

After the import operation finishes, refresh the Server Explorer by selecting File > Refresh and you will be able to see the data sources and views created to access the SharePoint OData entities.

Query example

Now, you can execute queries on the SharePoint Online views that have been created.

Example, executing the bv_${view.prefix}_sitepages view: