Tech and T-Accounts

Obfuscate username and passwords in Powershell 

Wednesday, December 20, 2017 3:20:40 PM

There are a whole host of blogs on how to encrypt passwords using PowerShell.  For the best security, those approaches should be followed, not this approach.  I wanted to not include my password and username information in scripts, yet also not go through the setup required for encryption to work (from what I gathered, this encryption is machine and user specific -- not the setup tasks that I wanted to pursue.)

The steps are similar -- create a file with the text coded, then read that file and decode the text to use in the PowerShell script.

PowerShell to code text to a file:

#this is simple obfuscation method for password not appearing as clear text in powershell script
# change the three items below to satisfy your needs
$acc = "myaccount"
$pass = "mypassword"
$filelocation = "E:\folder_where_files_are_saved"
[System.Text.Encoding]::Unicode.GetBytes($acc) | Set-Content $filelocation'\am.txt' 
[System.Text.Encoding]::Unicode.GetBytes($pass) | Set-Content $filelocation'\amp.txt' 
Then, in a PowerShell script to use the saved files, add these rows to retreive the username and password:
$filelocation = "E:\folder_where_files_are_saved"
# Get username and password from files created by obfuscation method
$gu = Get-Content $filelocation'\am.txt'  -ReadCount 0
$username = [system.text.encoding]::Unicode.GetString($gu)
$gp = Get-Content $filelocation'\amp.txt'  -ReadCount 0
$password = [system.text.encoding]::Unicode.GetString($gp)

Tableau Server refresh all extracts using REST API 

Tuesday, December 19, 2017 3:11:00 AM

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.


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 +'" />



$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 = $

$myUserID = $

$siteURL = $


# 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 = (





# 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 $


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



Tableau - Total one dimension in separate column 

Thursday, October 13, 2016 3:24:53 PM

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
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
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]

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

Page 1 of 6 1 2 3 4 5 6 > >> 
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2018 KB Earle Associates LLC | |