Tech and T-Accounts

Tableau Server refresh all extracts using REST API

Just worked through the 10.3 REST API for refreshing data extracts on the server.  The documentation language was all there, just not tremedously clear as the explanation paragraphs state that some parameters need to appear, yet the example doesn't reflect those parameters being present.  The missing piece -- adding the -ContentType parameter to the invoke statement is alluded to in the instructions, but as stated before, not present in the example.

I work with PowerShell to invoke the REST API as PowerShell comes with every Windows PC, which is what use.  The syntax shown below would need to be changed if some other shell language were used.

 

Some caveats to get PowerShell to run a script on your PC:


1. If you want to run this as a script from a saved file (extension to save PowerShell script files is .ps1), then you find that the PowerShell won't run the script, it is likely due to the PC having Restricted the ability to Execute scripts.  This must be changed in order to run a script from a file.  Some people suggest resetting the script Execution policy to unrestricted, but that opens the PC up to unknowns when also browsing the internet.  Instead, setting the policy to 'remotesigned' permits only local scripts and signed internet scripts to run.  After running your local scripts on a PC, for safety, you can also just set the Execution policy back to 'restricted'.

 

To reset the Execution policy, the logged in person must have PC admin rights AND the Windows PowerShell ISE must be launched with the 'Run as Administrator' option.

Then run these two command -- the first is to obtain the policy as it currently exists (should you want to revert) and the second changes the policy.  The policy cannot be changed from within a script, so these commands must be run manually.

$original_policy = Get-ExecutionPolicy

Set-ExecutionPolicy remotesigned

 

2. The default TLS protocols on Windows 10 PC's doesn't typically enable all the protocols needed for the WebMethods to work properly.  Enable all three TLS protocols (TLS, TLS1.1, TLS1.2) by also running this PowerShell command while you are in 'Run as Administrator' mode.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls12

 

Powershell to Kickoff Tableau Server Extract Refresh - all extracts:

 

#Tableau Server - where data resides and extracts need refreshing

# Setup to connect

$server = "http://tableauserver"

$s = Invoke-RestMethod -Uri $server/api/2.4/serverinfo -Method get #works on server version 10.1 and later

$api = $s.tsResponse.serverInfo.restApiVersion  #10.3 server

 

$username = “some_tableau_admin_username”

$password = “password for above user”

$sitelogin = "mysite"  #site name where extracts exist

 

# generate body for sign in

$signin_body = (’<tsRequest>

  <credentials name=“’ + $username + ’” password=“’+ $password + ’” >

    <site contentUrl="'+$sitelogin +'" />

  </credentials>

</tsRequest>’)

$response = Invoke-RestMethod -Uri $server/api/$api/auth/signin -Body $signin_body -Method post

# save the auth token, site id and my user id

$authToken = $response.tsResponse.credentials.token

$siteID = $response.tsResponse.credentials.site.id

$myUserID = $response.tsResponse.credentials.user.id

$siteURL = $response.tsResponse.credentials.site.contentUrl

 

# set up header fields with auth token

$headers = New-Object “System.Collections.Generic.Dictionary[[String],[String]]”

 

# add X-Tableau-Auth header with our auth token

$headers.Add(“X-Tableau-Auth”, $authToken)

 

#tests whether logged in user is an Administrator  (site or server)

$loginUserid = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/users/$myUserID -Headers $headers -Method Get

$admin = $loginUserid.tsResponse.user.siteRole -like "*Administrator"

 

#create body element required for refresh extract POST

$refresh_body = (

’<tsRequest>

</tsRequest>’

)

 

# only admin can perform extractRefresh, so this next set of commands only execute when logged in user is an admin

if ($admin)

{

#get list of extract tasks from server -- Tableau 10.3 REST API can only kick off an extract task, not an individual Extract refresh

#this gets list of all extracts -- no filter applied to select a schedule, owner, priority, workbook, etc.

$extractlist = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/tasks/extractRefreshes -Headers $headers -Method Get

 

Foreach ($taskid in $extractlist.tsResponse.tasks.task.extractrefresh.id)

{

$runnow = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/tasks/extractRefreshes/$taskid/runNow -Headers $headers -Body $refresh_body -ContentType "application/xml" -Method POST

}

}

Gravatar

Tableau - Total one dimension in separate column

Well, many of us come to Tableau after being frustrated with Excel.  Then, to our surprise, Excel does some things easily that Tableau doesn't seem to handle well.  There are lots of posts concerning how to create subtotals and grandtotals that are customized to a particular need.  However, the problem that I had was that I wanted the Totals for a particular dimension to show as a single column -- that is, if using something like Superstore Sales, I want a crosstab that has Region as the first column discrete dimension, then year(Ship Date) as the sub-column.  Relatively simple, until I wanted to see the order of the Columns to be like this:
 

Total Central East
2011 2012 2013 2014 2015 2011 2012 2013 2014 2015 2011 2012 2013 2014 2015
                             

I only show two of the Regions, but the idea remains -- how can I get the Years' Total to show up in this fashion within Tableau?

Well, the answer is in custom SQL -- I union the source table with itself, substituting the word 'Total' for the Dimension that is listed at the top -- in the above example, that would be Region.  So, using Superstore Sales, which is an Excel file, that means using the legacy connection so that Custom SQL can be created.

Basic overview of SQL:

Select dimension1, dimension2, dimension3, measure1 from Source
Union
Select 'Total' as dimension1, dimension2, dimension3, measure1 from Source

What this does is adds a 'Total' value to all the rows for that one selected dimension, so that when the dimension is selected for a crosstab, not only are all the values present, but there is a new value also available - called 'Total'.  Using Sum on the measure will result in correct values for all the pieces and the Total, which then can be broken down by any of the other dimensions as a second, third, etc. column.

With the above in mind, we can get a bit fancier.  Tableau permits sending a parameter value to the connection -- great, now instead of creating a connection for each dimension that I might want to have a Total presented in the above manner, I can instead use a comparison statement to the parameter to determine whether that field value will be 'Total' or what is on the row.  For most SQL, this comparison would be done using a Case statement -- nothing too difficult about that.  But, Superstore Sales is an Excel sheet -- and Excel doesn't permit Case statements in Custom SQL.  Instead of a Case statement, a Switch statement is used in the SQL.  First steps first, however, create the Parameter and fill the list with the names of the Dimensions that you may want to show as a total.  (BTW, if you pick a field that normally would be shown as a date, it will instead be treated as a text field -- you'll have to create a calculated field to convert the text field to a date field should you want to use Tableau's built in date capabilities).

So, after creating the Parameter (in my case it was named 'Total Field Chooser'), the above SQL will look like this <>:

Select dimension1, dimension2, dimension3, measure1 from Source
Union
Select
Switch(<Parameters.Total Field Chooser> = 'dimension1', 'Total', true, [Source].[dimension1]) AS [dimension1],
Switch(<Parameters.Total Field Chooser> = 'dimension2', 'Total', true, [Source].[dimension2]) AS [dimension2],
Switch(<Parameters.Total Field Chooser> = 'dimension3', 'Total', true, [Source].[dimension3]) AS [dimension3],
measure1 from Source

So, with the above in place as Custom SQL, I can modify the parameter and get the selected dimension filled with the word 'Total'.  One more task - create a calculated field that interprets the Parameter to select the Dimension -- for this, a simple Case statement in Tableau works fine. I named the calculated field 'Selected Total Field'

CASE [Total Field Chooser]
WHEN 'dimension1' then [dimension1]
WHEN 'dimension2' then [dimension2]
WHEN 'dimension3' then [dimension3]
END

Download the workbook (version 9.3) to see how this is built - it has three tabs, one connecting to Superstore Sales to show what Tableau's layout options are out of the box.  There is another with a 'fixed' column (Segment) that then has Total added.  Finally, there is a Custom SQL with Parameter driven version that permits a person to choose which Field is to have the Totals.  Enjoy.

Tableau Version 9.3 download

Gravatar

Create Oracle Linked server in SQL Server (64 bit)

One of the most misguided series of posts are those discussing how to create a linked server connection to Oracle on a 64-bit installation of SQL Server.  The one that gets you there if no other problems exist is found here by David Browne: https://blogs.msdn.microsoft.com/dbrowne/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server/  (however, the guidance on that page is generic and uses locations specific to their example – below modifies those directions to a more generic version to any installation).

At large company installations it is more usual that there is a common LDAP or TNSNAMES.ora file managed by a corporate infrastructure team that resides somewhere on the network.  In those cases, using the alias for the connection is all that is needed (as the service name, port and server name are all mapped within the alias entry).  I find that using these corporate managed sources for the aliases is the safer approach for individual installs by allowing he infrastructure team to abstract the actual connection parameters which decouples the local install and relieves the local person from having to keep up with the physical location of their server on the network (especially useful when there are clustered servers or backup servers that can be modified by the infrastructure team at a moment's notice to accommodate other priorities).

Other setup should already be in place before working through this approach to fix a failed installation, as this approach relies upon advanced and potentially dangerous modification of the PC's registry to eliminate prior failed attempts.  Do not work with the registry without having a full backup of the system to restore to the condition it is in prior to working with the registry!  Prior to that action, be sure that other setup conditions have been examined:

  1. Create an empty file and name it Test.UDL.  Once created, click to open it, then navigate to the first tab 'Provider'.  If "Oracle Provider for OLE DB" appears, then the provider is already installed.  If not, turn off any antivirus / root protection services before attempting to install the driver as noted in the blog by David Browne. 
  2. Make sure that the installed location of the Oracle Client and its bin directory are found in the Environment Path variable - preferably towards the start of the Path variable.  On servers where multiple people may have remote login access, it is important to have only one installatoin of Oracle Client that everyone uses.  I've found that if another user installs Oracle Client after the server has been setup for linked servers, that later installation notoriously confuses the system and lots of different types of error messages occur.  I prefer using a common location rather than in a person's user directory (which is the Oracle install default appraoch).  In this example, I'll use C:\App\OracleClient\product\11.2.0\Client_1 and C:\App\OracleClient\product\11.2.0\Client_1\bin.
  3. Remove the Oracle_Home from the environment variable if using LDAP as the location for the alias listing.  If you are using a corporate TNSNAMES.ora file, be sure to map it using UNC naming convention, not as a mapped drive (e.g.  \\[servername]\[some directory path statements].  Another tip, be sure to check that the UNC path that you put actually opens the folder where the TNSNAMES.ora exists (too often folks type this in and when checking with windows explorer, what they typed in actually doesn't get to the right directory -- be safe, just paste what you type into windows explorer, if it works, no harm, no foul.  If it doesn't, back off the later parts of the UNC until something does appear in windows explorer and then 'walk' down the directory tree to find the right directory.  Copy that from the windows explorer address bar and use that in the Oracle_Home environment variable.
  4. Don't forget to Reboot -- the Test.UDL won't show the new provider until the reboot is performed.
  5. If the new provider doesn't show up in Test.UDL -- check with Regedit whether the dll was registered (search for OraOLEDB*.dll and it should pick up the .dll -- ignore any other items).  If the dll isn't found in the registry, then you must manually register the dll.  So, for instance, if you installed the v11 ODAC to C:\App\Oracle\product\11.2.0\client_1\BIN, then the dll would be OraOLEDB11.dll.  With windows explorer, right mouse on the file, select properties -- see if in the General tab there is a message: "This file came from another computer and might be blocked to help protect this computer."  If so, click the 'Unblock' button.  Then, open a command prompt with administrative privileges and run this command (change the path and file name as appropriate for your install location and version)  %systemroot%\System32\regsvr32.exe C:\App\Oracle\product\11.2.0\client_1\BIN\OraOLEDB11.dll .  Once that is complete, once again check the Test.UDL and registry.  The provider should show up as an option and the registry search on the specific filename should find a hit.
  6. Once the Provider is found with Test.UDL, open SSMS and change the properties of the provider to "Allow InProcess" and "Dynamic Parameter".
  7. Add the Linked Server using connection information as you would for any other Oracle product.
Gravatar
Page 2 of 7 << < 1 2 3 4 5 6 7 > >>
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2021 KB Earle Associates LLC | |