SharePoint Online is a cloud-based service that helps organizations share and manage content, knowledge and applications to:
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 are divided in two different script files:
The first one contains the data sources and base views for the following SharePoint OData entities:
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.
In order to get the SharePoint credentials to configure the SharePoint templates you must follow these steps:
In order to register an app in Azure, follow these steps:
Once the app is registered we will need to grant permissions for clients to access SharePoint Online using the app:
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.
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.
After the API permissions are set we have to generate a new client secret key. In order to generate the key:
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:
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:
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.
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>
Construct the below POST request:
Where:
NOTE: all values need to be URL encoded (including the client_secret)
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:
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 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.
Before importing the VQL in Denodo, you have to set the following parameters defined in the sharepoint_api_rest_templates_denodo.properties file:
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:
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.
Please note: All paths are relative to /sites/${sharepoint.site.name}/.
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:
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:
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:
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.
Now, you can execute queries on the SharePoint Online views that have been created.
Example, executing the bv_${view.prefix}_sitepages view: