SharePoint integrations use a token-based authentication system called OAuth 2.0. This document will provide instructions for the creation of the SharePoint application and the configuration of OAuth 2.0.

Files

Best Practice: Use CSV files rather than Excel files. CSV files tend to be more stable in Denodo in general and we have also noticed that Excel files accessed over HTTP paths have limitations. Excel limitations can be avoided by converting them to CSV before integrating them with Denodo.

Best Practice: Use the same basic format for all your CSV files. Not only is consistency a good idea, but it will make the SharePoint integration more stable as well. In this document we will standardize on using the comma ( , ) character for the column delimiter, \n for the end of line delimiter, and the first row will have column headers.

The format for interpolation variable sp_path is as follows: /sites/[SiteName]/[DocumentLibrary]/[Folder]/[FileName]

• [SiteName] is the name of the SharePoint site.
• [DocumentLibrary] is the name of the document library.
• [Folder] is the name of the folder.
• [FileName] is the name of the file.

Notes: Be sure to include the file extension of the file. Your document may not be in a folder. If not, leave that portion out. Your document may also be in a nested folder; in that case repeat that section to give a full path. By default, SharePoint document libraries display as “Documents” yet the name of the actual document library is “Shared Documents."

Creating SharePoint Applications

• Log in to the Azure Portal: https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps
• Click New registration.

• Enter a name for the app according to your naming standards in the Name field.
• For Supported account types, select the Accounts in this organizational directory only option.
• For the Redirect URI section, select Web as the platform and add the Redirect URI of the environment you'll be using with this app. Due to a SharePoint limitation, the value you set for Redirect URI will be the only one you can use as a redirect URI for this app. Azure supports adding more redirect URIs to an app, but SharePoint only supports one. Be sure you are adding the URI of the Denodo environment you wish to use. The URIs for the different environments in Denodo are listed below:

Production: https://datahub.uillinois.edu/oauth/2.0/redirectURL.jsp

QA: https://qa.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp

Dev: https://dev.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp

• Click Register.

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

• Click Integration assistant.

• For What application types are you building?, select Daemon (background process or automation).
• Click Evaluate my app registration.

API Permissions

• Under Manage in the menu on the left, click API permissions.

• Click Add a permission.

• Select SharePoint from the list of Commonly used Microsoft APIs when it opens.

• Select Delegated permissions.
• Under Permission, expand the AllSites menu.
• Select AllSites.Read.
• Click Add permissions.

Certificates & Secrets

• Under Manage in the left-hand menu, click Certificates & secrets.

• Click New client secret.

• Enter a Description.
• For Expires, select an expiration time frame.

• Click Add. You will see that the new client secret has been added to the Client secrets list.
• Copy and paste the Value (not the Secret ID) of the client secret to a convenient, easily accessible location, such as Keepass.
  
  NOTE:This is the only time you can view the value of the client secret!
  You will need this value when you create Denodo data source connections using this SharePoint app.

• In the left-hand menu, click Overview.
• Copy and paste the Application (client) ID to a convenient, easily accessible location, such as Keepass.
  
  • NOTE: You will need this value when you create Denodo data source connections using this SharePoint app.
• Copy and paste the Directory (tenant) ID to a convenient, easily accessible location, such as Keepass.
  
  • NOTE: You will need this value when you create Denodo data source connections using this SharePoint app.

• Navigate back to the App Registrations page.
• Click New registration and create 2 more SharePoint applications - one for the QA environment and one for the Production environment,
• After creating these three applications, your list of app registrations should include three for Denodo.

Create a Date Source in Denodo

For this document we will use CSV files. In this example we have 3 CSV files of course data, instructor salary data, and awarded grant data.

Best Practice: Only use the SharePoint app credentials in a single Denodo connection. Creating more than one Denodo connection that re-uses the same SharePoint app Client ID will cause the access tokens associated with that Client ID to interrupt one another, and your integration will become unstable.

Since we do not want to use the same SharePoint app credentials for more than one connection, it is a good idea to make the data source reusable. The below instructions show how to make a reusable CSV connection to SharePoint.

• In Denodo Design Studio, click the context menu for 01 - Data Sources and select New > Data source > Delimited file.

• Give the data source a name.
• For Data route, select HTTP Client.
• Leave the HTTP method set to GET, and add the following URL to the Base URL field.

https://uillinoisedu.sharepoint.com/sites/[SiteName]/_api/Web/GetFileByServerRelativePath(decodedurl='@sp_path')/$value

[SiteName] will be the name of the SharePoint site you have access to. For example, if my SharePoint site was named FAA Enrollment Data, the URL would be https://uillinoisedu.sharepoint.com/sites/FAAEnrollmentData/_api/Web/GetFileByServerRelativePath(decodedurl='@sp_path')/$value

The link above uses an interpolation variable for sp_path (SharePoint path), which is what will make this connection reusable.

• Expand the Authentication section, and select OAuth 2.0 from the Authorization options.
• Leave the Authorization Grant value set to Authorization code grant, and paste the Client identifier and Client secret from your SharePoint app in their respective fields.

• Click the link titled Launch the OAuth 2.0 credentials wizard to help you obtain these credentials, which should now be enabled. If the link is not enabled, go to the Tools tab at the top right and select OAuth credentials wizards > OAuth 2.0 wizard.

• Populate the Token endpoint URL and the Authorization server URL fields with the following values.

Token endpoint URL: https://login.microsoftonline.com/common/oauth2/token

Authorization server URL: https://login.microsoftonline.com/common/oauth2/authorize?resource=https%3A%2F%2Fuillinoisedu.sharepoint.com

• For Redirect URI, select the Other radio button and populate that field with one of the following redirect URIs, based on which environment you're working with.

QA: https://qa.datahub.uillinois.edu:9443/oauth/2.0/redirectURL.jsp

• Expand 2. Generate the Authorization URL, click the Generate link to populate the Authorization URL field, and then click the Open URL link.

• If a web page opens asking for Permission requested, click Accept.
• Copy the URL on line 3 of the web page that opens.

• Expand 3. Paste the authorization response URL and paste the link you just copied.
• Expand 4. Obtain the OAuth 2.0 credentials and click the OAuth 2.0 credentials link.

• You should receive the following confirmation that the OAuth 2.0 credentials were obtained.

• Click the Copy credentials to the clipboard link, and then click OK.
• Scroll down to the Column delimiter field and add a comma ( , ), and check the box next to Header.

• Click Save.
• Once the data source is saved, we can click Test Connection, which will bring up an Interpolation variable screen where we'll need to enter the sp_path for the first CSV file in SharePoint. See the Files section above to learn how to obtain these sp_path values. Click OK.

Best practice: Once your connection is initially tested, avoid using the “Test Connection” feature. We have found that doing so could introduce an issue with the authentication.

Create Base Views

In this document we will create 3 base view to illustrate how to reuse the box data source for multiple CSV files.

• Open the Data Source created above and click the Create Base View link.

• You will be prompted for a value for sp_path. See the Files section above for the correct format of the sp_path variable. Enter the sp_path of the first CSV file and click Ok.

• In the context menu for the base view we just created, select Rename and give the base view a new name.
• Drag the base view from the 01 - Connectivity / 01 - Data Sources folder to the 01 - Connectivity / 02 - Base Views folder.
• Repeat the steps above to create base views for the other two CSV files. When complete, you should have one data source and three base views.

Create Derived Views that Encode the File IDs in the View

There is one aspect of this solution to make the data source reusable that is a little annoying. If we were to execute these base views, we would be prompted for the File ID each time. If we use the wrong File ID, our query may not return any data. We can take care of that by creating integrated views of these base views.

There are a few ways to accomplish creating these integrated views.

• Using the GUI
  
  • In the context menu for the base view, select New > Selection.
  • On the Where Conditions tab, enter [Base View Name].sp_path = [sp_path value]

• Using a VQL script
• • Run the following script in the VQL Shell to create the integrated view. Note: your integrated view name, folder names, base view name and sp_path value will be unique to your data source.

CREATE OR REPLACE VIEW iv_sharepoint_csvs_course_schedule_data FOLDER = ' / ahall1 / 02 - Integration'

AS

select * from bv_sharepoint_csvs_course_schedule_data where sp_path = '/sites/TestSiteforDenodoImplementation/Shared%20Documents/New/Course_Schedule_data.csv';

Now, we can query these integrated views directly, without having to enter a File ID each time.

Note: You may need to click the File tab at the top and select Refresh to see the new integrated views you just created.