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
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: }
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
Facing this exact problem...
ReplyDeleteI 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
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!
DeleteI 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.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHello, I'm getting this error. It can't find 'access_token'. Thanks for your help
ReplyDeleteInvoke-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
ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
ReplyDeleteAt 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
How to play the best casino slots with real dealers?
ReplyDeleteThe best casino slots 상주 출장안마 have an interesting 구미 출장안마 dynamic game layout that you can win big 공주 출장안마 at 경산 출장샵 once. 성남 출장안마 The most important thing you need to
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.
ReplyDeletePlayers betting the utmost wager can take part within the progressive jackpot. A half of} their wager contributes to the progressive 카지노사이트.online jackpot. The jackpot consists of all of the players at a selected time. The availability of free slot video games online lets them turn out to be conversant in slots earlier than playing their cash. With the popularization of the Internet and the smartphone, casino video games have moved online.
ReplyDelete