Recent Posts

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

Comments

  1. Facing this exact problem...

    I tried to paste your code in powershell ise but getting error below.
    Seems something's wrong around row 65

    White space is not allowed before the string terminator.
    + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : WhitespaceBeforeHereStringFooter

    ReplyDelete
    Replies
    1. Maybe an issue with copy/pasting. The "@ needs to be at the very beginning of the line, and how it's formatted in the blog post may have an extra space at the beginning. Hope that helps and you were able to get it working!

      Delete
  2. I have a query, can we have other sources along with sharepoint & will that work? And how do i connect to SQL server from flows. Any help is highly appreciated.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hello, I'm getting this error. It can't find 'access_token'. Thanks for your help

    Invoke-RestMethod : {"error":"invalid_grant","error_description":"AADSTS9002313: Invalid request. Request is malformed or invalid.\r\nTrace ID:
    cb46c481-5c5a-440b-8fb4-3efb0e6c2800\r\nCorrelation ID: cfd516ea-cb2c-46e7-bc07-2f87026b1204\r\nTimestamp: 2020-07-11 14:27:20Z","error_codes":[9002313],"timestamp":"2020-07-11
    14:27:20Z","trace_id":"cb46c481-5c5a-440b-8fb4-3efb0e6c2800","correlation_id":"cfd516ea-cb2c-46e7-bc07-2f87026b1204","error_uri":"https://login.windows.net/error?code=9002313"}
    En C:\Users\ahenaoa\Desktop\refreshSharepoint.ps1: 16 Carácter: 8
    + return Invoke-RestMethod -Uri $uri -Method Post -Body $body
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
    No se encuentra la propiedad 'access_token' en este objeto. Compruebe que existe.
    En C:\Users\ahenaoa\Desktop\refreshSharepoint.ps1: 96 Carácter: 5
    + if ($refreshToken.access_token)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

    ReplyDelete
  5. ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
    At D:\03 - Unity\refreshtoken.ps1:30 char:37
    I am getting the below error, any help why the $dataSource.Option might become null

    + $options = $dataSource.Options | ConvertFrom-Json
    + ~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand

    ReplyDelete
  6. How to play the best casino slots with real dealers?
    The best casino slots 상주 출장안마 have an interesting 구미 출장안마 dynamic game layout that you can win big 공주 출장안마 at 경산 출장샵 once. 성남 출장안마 The most important thing you need to

    ReplyDelete
  7. The Best Spinner & Table Games For Playing
    Spins · Table titanium earring posts of Contents · Table of titanium mens ring Contents · Table of titanium tv alternative Contents · titanium element Table of Contents · Table of Contents · Table of Contents. black titanium rings

    ReplyDelete
  8. With over 45 tables waiting for you, odds are you're in for the time of your life. The video 메리트카지노 games listed are subject to change or removal from the slot flooring. Well-dressed women and men wander about, pushing buttons and pulling levers, watching cherries and sevens spin on liquid crystal displays. We've got it all – every little thing from the high-stakes fashion of blackjack, the fast-paced pleasure of craps, to the thrill and technique of poker. With 14 tables waiting for you, odds are you're in for the time of your life.

    ReplyDelete
  9. If you are be} nonetheless having a tough time deciding which one to use, take one more take a look at|have a glance at} our high picks, choose your favourite, and at all times remember to gamble responsibly. Come up with the username, and password, enter your e-mail, and supply some personal information like full name, tackle, etc. 메리트카지노 There are many different promotions out there right here, including the GM Reload Bonus, Blockbuster Bonus, and so on. Cryptocurrencies are essentially the most commonly used banking strategies at Rich Palms, and for a great reason. The first is a 250% bonus + 100% cashback, the second is a 275% bonus + 100% cashback, and the final one is a 300% bonus + 50% cashback.

    ReplyDelete

Post a Comment