Using Sharepoint Lists in SSAS

I recently had a client who wanted to manage some data and have it available to them within their Tabular model. Previously we've managed this using Excel spreadsheets, dropping them into share folders, and picking them up for processing on a schedule. While that would have worked here, we decided to take a different approach using Sharepoint Lists. This provided a lot of value out of the box and it could easily be pulled into an Analysis Services tabular model to join up with the rest of the data. Sounds great... well almost. Let's look at an example of setting this up and some of the challenges with running this in a production environment, or as I like to say "for realzies".

Setting up a SharePoint List

I'll quickly walk through setting a Sharepoint List for us to play with, in case you haven't done it yet.

• Go to the SharePoint site where you want to have the list, click New -> List

• Give it a name and click create. Bada-Bing! You got a SharePoint List

• I'll leave it to you to create some columns and add data, here's a screenshot of the list I'll be using. I have a two year old so Dr. Seuss and Sesame Street are life.

Now, let's bring this into Analysis Services! 

Import a Sharepoint List into SSAS

Let's fire up Visual Studio and create a model to import our data into. Before we can bring in the SharePoint data we need to make sure our model is at the 1400 compatibility level, which can be set in the properties of the Model.bim file. Importing a new data source will bring up the following window where we can select "SharePoint List" from the Online Services menu

Next you'll be prompted for the Site URL, this is where the SharePoint List lives

This will look like this http://<TenantName>.sharepoint.com/sites/<SiteName>, where <TenantName> is your organization's name, and <SiteName> is the name of the SharePoint site. After entering the site URL, you'll be prompted for credentials. Since this is SharePoint Online we'll use our Microsoft account. After authenticating you'll see a bunch of different items to choose from, just pick the list you want to import. The preview will so a bunch of additional column, so we'll click "Edit" to clean this up a bit.

That will let me use the Power Query Editor to remove these metadata columns and keep only the stuff I'm interested in. Here's my final result. 

Clicking Import, will load the data into my model. Since this was a new model this is all the data I got. I can process the data and now view it in what ever tool that connects to a tabular model. This works just like any other table in a tabular model, so I can add measures, create relationships and hierarchies, and whatever else I like. 

Viewing the data in Excel, I confirm that the data is available. (Obviously, this isn't how you would typically view the data, but you get the idea)

Getting into Production

So now that we've set this up, we'd like to start using it in production. Go ahead and deploy like you normally would and you'll see that there are no problems, and that the data processes successfully. But wait one hour and try to process again.... HA! GOTCHA! You'll get an error that looks like this one below.

So what's going on? This thing worked a minute ago. Well it turns out when we setup the credentials an access token was acquired from Microsoft and that token expires after 1 hour. Also SSAS doesn't make the effort to go refresh that token either. So what do we do? Manually refresh the data source? We could, but it's just going to expire after an hour. A better option is to automate refreshing the token, but how? Turns out, the data source stores a refresh token that can be used to fetch a new access token from Microsoft's API. So let's look a PowerShell script to do just that.

1:  $query = 'select * from $System.TMSCHEMA_DATA_SOURCES'  
2:  $server = ""  
3:  $database = ""  
4:  $tenant = ""  
5:    
6:    
7:  function RefreshSharepointToken  
8:  {  
9:    Param([string]$refreshToken)  
10:    
11:    $uri = "https://login.windows.net/$tenant/oauth2/token"  
12:      
13:    $body = @{refresh_token=$refreshToken;  
14:        grant_type="refresh_token"}  
15:    
16:    return Invoke-RestMethod -Uri $uri -Method Post -Body $body  
17:  }  
18:    
19:  function UpdateSSASDataSource  
20:  {  
21:    Param($dataSource, $tokenResponse)  
22:    
23:    # get the expired datetime   
24:    $startDate = Get-Date -Year 1970 -Month 1 -Day 1 -Hour 0 -Minute 0 -Second 0  
25:    $expireDateTime = $startDate.AddSeconds($tokenResponse.expires_on).tostring("R")  
26:    
27:    # convert json fields to objects  
28:    $connectionDetails = $dataSource.ConnectionDetails | ConvertFrom-Json  
29:    $credential = $dataSource.Credential | ConvertFrom-Json  
30:    $options = $dataSource.Options | ConvertFrom-Json  
31:    
32:    ## TODO add parameter for type and apiVersion  
33:    $tmsl = @"  
34:    {  
35:      "createOrReplace": {  
36:       "object": {  
37:        "database": "$database",  
38:        "dataSource": "$($dataSource.Name)"  
39:       },  
40:       "dataSource": {  
41:        "type": "structured",  
42:        "name": "$($dataSource.Name)",  
43:        "connectionDetails": {  
44:         "protocol": "$($connectionDetails.protocol)",  
45:         "address": {  
46:          "url": "$($connectionDetails.address.url)"  
47:         },  
48:         "authentication": null,  
49:         "query": null  
50:        },  
51:        "options": {  
52:         "apiVersion": $($options.apiVersion)  
53:        },  
54:        "credential": {  
55:         "AuthenticationKind": "$($credential.AuthenticationKind)",  
56:         "Expires": "$expireDateTime",  
57:         "RefreshToken": "$($tokenResponse.refresh_token)",  
58:         "ProviderType": "$($credential.ProviderType)",  
59:         "PrivacySetting": "$($credential.PrivacySetting)",  
60:         "AccessToken": "$($tokenResponse.access_token)"  
61:        }  
62:       }  
63:      }  
64:     }  
65:  "@  
66:     Write-Output $tmsl  
67:     ## execute the TMSL and return the result  
68:     [xml]$results = Invoke-ASCmd -Server $server -Database $database -Query $tmsl  
69:     return $results  
70:  }    
71:    
72:  ## query the model for data sharepoint data sources  
73:  [xml]$xmlResults = Invoke-ASCmd -Server:$server -Database:$database -Query:$query  
74:    
75:  ## look for OAuth2 creds and refresh them  
76:  foreach ($dataSource in $xmlResults.return.root.row)  
77:  {  
78:    if ($dataSource.Credential)  
79:    {  
80:      $credJson = ConvertFrom-Json $dataSource.Credential  
81:         
82:      ## update any OAuth2 data sources   
83:      if ($credJson.AuthenticationKind -eq "OAuth2")  
84:      {  
85:        ## get the Expired time for the token  
86:        $expireTime = [datetime]::parseexact($credJson.Expires, "R", $null)  
87:        $currentTime = [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId((Get-Date), "Greenwich Standard Time")  
88:    
89:        ## only update expired tokens  
90:        if ($currentTime -gt $expireTime)  
91:        {  
92:          ## Get the refresh token and call the API to refresh  
93:          Write-Output "Refreshing Token for $($dataSource.Name)"  
94:          $token = $credJson.RefreshToken  
95:          $refreshToken = RefreshSharepointToken $token  
96:    
97:          ## check that we have an access token  
98:          if ($refreshToken.access_token)  
99:          {  
100:            ## update the TMSL with the new access token  
101:            Write-Output "Updating ${$dataSource.Name}"  
102:            $updateResults = UpdateSSASDataSource $dataSource $refreshToken  
103:                
104:          }  
105:          else  
106:          {  
107:            Write-Error "Could not retrive access_token"  
108:            Write-Error $refreshToken  
109:          }  
110:        }  
111:      }  
112:    }  
113:  }  

This script will connect to your SSAS instance, and query the DMV table storing information about the data sources. Then for each OAuth2 connection type found, we parse out the refresh token, get the new access token, and generate some TMSL to update the data source with the new access token and refresh token. We can run this before we process our table as it only takes a few seconds to complete.

Bottom Line

We were able to setup a SharePoint List as a data source for our Tabular model. This is great because we have a way for end users to easily manage data that can be used slice and dice. However this isn't completely ready for prime-time without a little extra effort on our parts. Hope this helps to anyone who's trying to use this same pattern. Another approach would be to setup a Flow that would watch for changes on the SharePoint List, write that back to a table in SQL Server (or your database of choice), and have SSAS source from that. That should work, and just goes to show that there's always more than one way to solve a problem. 

Thanks,

Spencer