DSA Automatic Distribution v2

From DataSelf Knowledge Base
Jump to navigation Jump to search


Versions of DSA AD

This page is for DSA AD version 2.x. For DSA AD version 1.x see DSA Automatic Distribution.

Note: For the most part the documentation for version 2 applies to version 3.


Dataself Analytics Automatic Distribution (DSA AD) Module automates the distribution of views (reports), dashboards or entire workbooks created by DataSelf Analytics Web Server. A key use case: A sales manager weekly emails a sales report to each member of a sales force. The sales manager maintains a single file of commands which automates the entire process. The report would containing just that individual's sales information. Regional managers would also receive a report for all their sales representatives.
This module runs Dataself Analytics Web Server views or entire workbooks (reports and dashboards) with specified filters. The output can be in PDF, .png (image file) or .csv format. Output is saved in a file and optionally emailed to one or more recipients. This operation can be repeated with different data filters with an unlimited number of users via shared folders and/or emails.

  • Automatically run DSA views with given parameters.
  • The output may be in .pdf, .png (image) or .csv format files.
  • The output is written to a specified folder and optionally emailed to specified recipients.

Major elements - 2 configuration files, Tableau server with workbooks, report output files, email server

Major Operations (Summary of What DSA AD Does)

DSA AD performs the following major operations:

  1. Read static parameter file - contains parameters and settings that rarely change.
  2. Read .csv file (from Excel) containing parameters that specify a DSA view, view/report filters, run parameters, and a email destination. New option for version 2.2 accepts PDF format options.
  3. Parse parameters into commands to DataSelf Analytics Web Server.
  4. Execute DataSelf Analytics Web Server.
  5. Emails out the results.
  6. Creates logs records of the process.

Key Elements of DSA AD

The main DSA AD folder contains the following sub-folders:

  • Output - report files created by DSAAuto. These file may also be attached to emails.
  • Settings - Contains the 'master'/parameter files which specify what DSA reports are run and how they are distributed.
  • Software - the DSA AD program.
  • Status_Logs - Run logs. Saves information about every run of DSA AD.
  • Detail_Logs - technical logs. Contains more information than the Status_Logs.

DSA AD Installation Checklist

List of information needed to install DSA AD on a server hosting DataSelf Analytics Web Server.

See also:

Software Requirements:

Microsoft PowerShell version 3.0 (or higher)

  1. PowerShell version 3 or higher. PowerShell is a free download from Microsoft. PowerShell version is the default install for newer versions of Windows Server 2008. See How to Check the Version of Powershell and How to DOWNLOAD & Install Microsoft PowerShell 3.
  2. Powershell 3 requires .NET framework 4.0 or above is required for Powershell 3. The PowerShell 3 installer usually includes the required version of .NET framework
  3. DataSelf Analytics Web Server
  4. Tableau Server Command Line Utility (tabcmd.exe or TabcmdInstaller.exe). (Installer should be included in the DataSelf Analytics Web Server installation files)
  5. DSA AD - DataSelf Analytic Automatic Distribution package
  6. A SMTP server for outgoing email. This is usually on external servicer or a commerical service.
How to Check the Version of Powershell
  1. Run Windows Powershell (Start > type "Powershell" into search box)
  2. Type $PSVersionTable at prompt. (not case sensitive)

If the $PSVersionTable variable doesn't exist, then you are running V1. If it does exist, then the version info will be available from $PSVersionTable.

How to Check for 32 or 64 bit Versions of Powershell
  1. Run Windows Powershell (Start > type "Powershell" into search box)
  2. Type if ([System.IntPtr]::Size -eq 4) { "32-bit" } else { "64-bit" } at prompt. (not case sensitive)

PowerShell locations On a 64 bit machine
C:\Windows\SysWOW64\WindowsPowerShell - this is the 32 bit version
C:\Windows\System32\WindowsPowerShell - this is the 64 bit version
  • NOTE: It's the reverse of what you would expect. The 64 bit version is on the System32 path and the 32 bit version is on the SysWoW64 path!
How to determine which versions of the .NET Framework are installed


Tabcmd.exe is an API to the Tableau web server provided by Tableau Software. DSA AD adds features and a user interface to tabcmd.exe's "export" command.

Tabcmd.exe "export" Command
Finding tabcmd

Default location is C:\Program Files\Tableau Server\Tableau\<version #>\bin

Recommended Software

  • Excel or other spreadsheet software. Valuable for editing the commands in the .csv file.
  • PDF file reader.

Installation Checklist

For more see Installation Checklist.

Hardware Requirements

See DataSelf Web Server / Tableau Server Requirements.

For more see Installation Checklist

Steps to Install DSA AD

  1. Gather the configuration and security information listed in the DSA_AD_Installation_Checklist.
  2. Determine if PowerShell version 3 (or higher) is installed on the server platform.
  3. Download and install Microsoft PowerShell 3 as required.
  4. Set the Powershell execution policy or Unblock Script Files.
  5. Install Tableau tabcmd.exe if required.
  6. Copy the DSA AD folder and all contexts to the server platform.
  7. Use the Preliminary Installation tests in the SetupTests folder to validate parameters.
  8. Follow the instructions at How to Configure DSA AD.
  9. Ensure that the client has access to the DSA AD folder on the server on which DSA AD is installed.
DOWNLOAD & Install Microsoft PowerShell 3 as Required

http://technet.microsoft.com/en-us/library/hh847837.aspx Microsoft Windows Management Framework 3.0 Recommended version to download: Windows6.1-KB2506143-x64.msu

NOTE: Installing PowerShell version 3 requires restarting the server.

NOTE: With the Server Core option "There is no Windows shell and very limited GUI functionality (the Server Core interface is a command prompt)."

INSTALL Tableau tabcmd.exe As Needed

http://kb.tableausoftware.com/articles/knowledgebase/tabcmd-install http://kb.tableausoftware.com/articles/knowledgebase/installing-tabcm

Available as a free download.

How to Configure DSA AD

Configuration Checklist

  1. Gather the configuration and security parameters listed in the DSA_AD_Installation_Checklist. We recommd using the Preliminary Installation tests in the SetupTests folder to validate the parameters.
  2. Prepare a DataSelf Analytics Web Server workbook containing the worksheet you want to automate. DSA Desktop or Tableau Desktop has the capability to fully configure a view.   See Prepare View for DSA AD, The DSA/Tableau Worksheet to Automate.
  3. Configure the static parameter file.
  4. Configure a DSA AD Commands Parameter File / .csv file.     See the DSACmdParms.csv file for an example.
  5. Test the URL column and URL-Filter column settings in a web browser logged into the DSA or Tableau Web Server.   See Test the URL in DSA/Tableau Web Server
  6. Configure a *.bat file.
  7. Run the .bat file and test.
  8. (Optional) Configure a scheduler to periodically execute the .bat file.

  • Configure other DSACmdParms*.csv and .bat files for other processes as appropriate.
  • The Prepare View for DSA AD page includes the procedure we use for preparing views in DataSelf Analytics/Tableau workbooks and for testing the values required for the URL and URL-Filter columns.

The DSA/Tableau Worksheet to Automate

Also called a report. Prepare a DataSelf Analytics Web Server workbook containing the worksheet you want to automate. DSA Desktop or Tableau Desktop has the capability to fully configure a view. See Prepare View for DSA AD.

The Settings Files

The three settings files must be configured for each use.

  • The files must be saved to the Settings folder of the main DSA AD folder.
  • The files can be edited by any text file editor such as Microsoft Notepad.

In order of execution these files are:

1. Batch Execution file - file type .ps1

When DSA AD is run by a scheduler this is the file that is given to it to run. See below for more information.

2. Static Parameter file - file type .ps1

  • Template: .../Settings/StaticParms.ps1 or .../Settings/SParms0.ps1
  • File type (file name ends with) .ps1. Can be edited with any text editor such as Notepad.
  • Referenced by the .bat file.
  • More information at Static Parameter File below.
This file contains rarely changed parameters such as the username & password of your DataSelf Analytics Web Server and email server.
The static parameter file specifies:
  • Location of folder containing TABCMD.exe. ( DSA/Tableau upgrades often changes this location.)
  • Login info for DataSelf Analytics Web Server
  • Email server settings
Static Parameter Field Settings
For more information see Static Parameter File below.

3. DSA Commands parameter file - file type .csv

Template: DSACmdParms.csv
Names of DSA views to run, output files, email addresses, etc.
Recommended editor. This file is most easily edited by Microsoft Excel or other spreadsheet program. It can also be edited by a text file editor such as Microsoft Notepad.
The detailed parameter file specifies:
  • DSA views
  • DSA view filters for each report & recipient
  • output file name
  • output file type
  • Optional Email address(es)
  • Other email specifications such as subject line.

Each of these three files should be copied and modified for each different run*.bat file for running DSA AD. Each run*.bat file represents the instructions for one job. Many users may only need 1 job/run.bat. But you can have can more than one process. For instance you might have one DSA AD job that runs every weekday and another that runs once a week. If so, then the daily process/job will need one set of run.bat, static parameter file, and detail parameter files and the weekly job will need another set.

Configuring the Batch Execution file

The batch execution Contains the commands to run the reports specified in the Report Commands (.csv) file. This file can be configured to be executed by the SQL scheduler.

  • ...\Settings\SampleDSA_AD_ReportBatch.ps1 is the template for you to copy, rename and edit for each use.
  • See the ...\Settings\ReadMe.txt file for more information.

The file must be configured to contain:

  • The full file name and path to the master script which runs DSA AD. By default this file is ..."\Settings\DSA_AD.ps1".
  • Name of the Command Parameters (.csv) file in the ... \Settings\ folder to use for this run.
$MyCSV_File = "CommandsForThisBatch.csv"
& "D:\Program Files (x86)\DataSelf\DSA_AD2.2\Settings\DSA_AD.ps1" `

Configuring the Static Parameter file

Static Parameter file

File type .ps1. Contains the 'static', mostly unchanging parameters for a run.
This file can edited by a text editor such as Notepad or by a PowerShell editor such as PowerShell ISE.

  • Template: .../Settings/StaticParms.ps1 or .../Settings/SParms0.ps1
  • File type (file name ends with) .ps1. Can be edited with any text editor such as Notepad.
  • Referenced by the .bat file.

Static Parameter Field Settings

This list of parameters is not complete.

SMTPUser =
  • *$Global:SMTPUser = "*NONE*" -- Enter "*NONE*" when there is not a user name for the SMTP server login. (So called anonymous login).
static_TabCmdPath =

Location of the tabcmd.exe API file.

Example: [string] $global:static_TabCmdPath = "C:\Program Files\Tableau Server\Tableau\10.4\bin"
This folder usually changes with each release of DSA/Tableau. Therefor, update this line whenever Tableau is updated.

When SMTPUseSSL = $true turns on SSL and TLS security negotiation.

  • For Microsoft Office 365 set to $True in order to negotiate TLS security.
  • Internally Microsoft Powershell's language's send-MailMessage command. This command is designed to be flexible and "smart" in negotiating settings with the email server DSA AD is trying to connect to.
  • In cases where the other email server rejects the connection attempt the detail log file will show the connection error message issued by the other email server
Fields by Type
  • Login information (security credentials) for the DataSelf Analytics Web Server.
  • "Email parms" -- Connection and logon information for the SMTP Email server. Required in order to send outgoing email.
  • For output files distributed by email the "From" email address that will appear on the email.
  • Optional email address or addresses that will receive an status email whenever DSA AD is run.


  • The .ps1 extension indicates that this file is a Windows PowerShell script file.
  • The Static Parameter file can edited by a text editor such as Notepad or by a PowerShell editor such as PowerShell ISE.
  • The template file is SParm0.ps1. Copy, rename, and edit.

Example: Static Parameter file

-------- Path to tabcmd.exe --------
File Path to DSA's tabcmd.exe file.
[string] $global:static_TabCmdPath = "C:\Program Files\Tableau Server\Tableau\8.2\bin"

------- DSA Server Login Info ----------
[string] $global:static_DSAServerDomain = "http://localhost" [string] $global:static_DSAServerUserName = "admin" [string] $global:static_DSAServerPassword = "a password"

--------- Email function parms --------
[string] $global:SMTPServer = "Smtp.gmail.com"
[int] $global:SMTPPort = 587
[bool] $global:SMTPUseSSL = $true
[string] $global:SMTPUser = "dsbi@dataself.com"
[string] $global:SMTPPassword = '^^^^^^' (value suppressed)
[string] $global:defaultSubject = "Report for "
[string] $global:EmailFrom = "dsbiFROM@dataself.com"
[bool] $global:EmailBodyAsHTML = $true

------- Run Status Report Recipient ---------
Receives an email with a brief status report whenever DSA AD is run.

Value of parameter may be overridden with the optional parameter passed directly to the program at run time.
-- Separate two or more email addresses with semi-colons (example: ... = " firstaddress@acme.com; secondaddress@acme.com" )

[string] $global:DefaultRunStatusRecipientEmail = "cwilson@dataself.com"

Configuring the DSA Commands Parameter File

Contains the names of DSA views (reports) on the DataSelf Analytics Web Server, DataSelf Analytics Web Server filter parameters, output file names, and optional email delivery information including email address, email subject line text, etc.

  • (Was named Detailed Parameter file in ver 1.0)
  • DSACmdParms.csv is a template containing some examples.

Copy, rename, and edit this file for each use. We recommend editing the .csv file using Microsoft Excel or other .csv format aware editor.

Important: The names of the column headers (the first row/line in the .csv file) must not be changed.


The Prepare View for DSA AD page includes the procedure we use for preparing views in Tableau workbooks and for testing the values required for the URL and URL-Filter columns.

Columns in the DSA Command Parms file


Any name or id value you make up. Numbers are recommended. The ID is helpful for tracking each line if there is a problem.


The URL that opens a worksheet in workbook available from the DataSelf Analytics Web Server.
Use the DSA/Tableau Web Server to navigate to the workbook or view (tab) you want to distribute.

Copy the URL from the web browser's URL window, delete everything in the string except the part between "/view/" and to the the question mark ("?"), and copy the remaining string to the URL column.

WHEN The URL used for testing in the web browser is:
http://localhost:8000/views/TestA/Sheet3 or
http://localhost:8000/views/TestA/Sheet3?id=1 or
Set the URL column to


  • See also the "fullpdf" option in the Output-Format column.
  • Dashboards call other worksheets. A URL to a worksheet (tab) that is configured as a dashboard will run all the worksheets specified in the dashboard.


The filter parameters (or query string) portion of the URL that is passed to DataSelf Analytics Web Server.

  • When output is a dashboard the filters will apply to all filters on the views included in the dashboard.
  • When output is of all views in a workbook the filters will apply to all views. (see the fullpdf option of the Output-Format column)
  • Tableau documentation refers to string in the URL-Filters column as the "query string".

Example of a query string (all these elements are covered below):


For more information see: DSA Server - Filter Views using URL Parameters

For Testing: Combine the URL string (minus .pdf or .png) and the URL-Filter and copy it to URL box of a web browser. The entire URL should open the right view and set the desired filter parameters.

Example 1:

Filter for AcctManagerID identified by code "BB".

URL-Filters = ?AcctManagerID=BB
For Testing: The complete URL copied to the browser might be
After Testing: Copy the part before the "?" to the URL column and the question mark and the query string after it to the URL-Filters column.
Example 2: Spaces in Field Names or Values
Replace spaces with %20
URL-Filters = ?Salesperson%20Name=John%20Smith

For more see: DSA Server - Filter Views using URL Parameters

Example 3: More than One Field Name

Combine filter parameter segments with "&".

Filter for Salesperson Name John Smith in Region 20.
URL-Filters = ?Salesperson%20Name=John%20Smith&Region=20
Example 4: More than One Value for Filter

Separate multiple values for a filter with a comma.

Filter for Regions 20 and 22.
URL-Filters = ?Region=20,22
Filter for Salespersons John Smith and James Wesson.
URL-Filters = ?Salesperson%20Name=John%20Smith,James%20Wesson
Example 5: Force a Fresh Data Query

Add the URL parameter ?:refresh=yes to force a fresh data query instead of pulling the results from the cache.

Refresh=yes option when used next to a filter parameter
  • NOTE: This feature needs to be tested in order to determine the proper syntax for combining it with the query strings shown above.
Important Note about URL Encoding.

This version of DSA AD now automatically adds somes of the most common URL encoding for you if encoding is required.
Either format below can be used in the URL-Filters column:

?Salesperson Name=John Smith

Note for advanced users: To test URL string with DSA/Tableau Web Server the Web Server will require the URL encoding on URL strings sent to it.

How to Add URL Encoding

Spaces in URL-Filters must be replaced with "%20". This encoding is required by the HTML protocol.
Other characters that must be encoded include the apostrophe ("'").

- email address or list of addresses (optional)
The email address or addresses to distribute the report output to.

  • Multiple Email Addresses: Separate addresses with semi-colons.
  • Use a blank or the word "NONE" in this column to skip distributing by email.

Example:   joe@domain.com; sue@domain.com


- any name you specify. Usually the name of the email recipient. Information only.


The Window's file system path to the file specified in the Output-File-Name column.

  • The folders specified in the Output-Path must already exist.

the name of the file to write the .png, .pdf or .csv file to. Default is blank or NONE.

  • The Recipient-Email-Address and the Output-File-Name should not both be set to blank or "NONE".
  • If delivered by email then this will also be the name of the report file attached to the email.
  • We recommend using file names that do not contain spaces or special characters.
  • File names containing spaces may have to be delimited by quotes.
  • File names cannot contain special characters such as / \ ? % * ; : “ < > .
  • For file names with non-ASCII characters see Non-ASCII characters and PDF exports

Required. Must be either pdf, png, fullpdf or csv.

  • This value must match the .png or .pdf extension, if any, ending of the URL column.
  • Use pdf or png to output a view or a dashboard in a workbook.
    • Note: The optional dashboard features called web page objects won't be included in the PDF
  • fullpdf outputs all views in a workbook to a pdf file, one view per page in the .pdf file.
    • To export a workbook, it must have been published with Show Sheets as Tabs selected in the Tableau Desktop Publish dialog box.
  • Use csv only for one view at time.
CSV ("crosstab") output

See also: Tableau - Export measure fields to csv

  • The csv option is also variously known as crosstab or crosstab to Excel on some DSA/Tableau screens and documentation.
  • The output to csv must be a simple table with column labels from a single view. Do not put row labels, totals or other so-called "crosstab" features in the view.
  • WARNING: It's easy to create views that Tableau cannot accurately render in .csv format output. In our experience, most views have complex structures that cannot be rendered in .csv format. The software gives no warning when this condition occurs and the .csv output that results is unpredictable.
    • Earlier versions of DSA AD did not allow this option because of the unpredictable nature of the output.
    • We recommend mentally translating "crosstab" to "raw data in a widely used format".

- text that will appear in the subject line of the email.
Ignored if Recipient-Email-Address is blank or "NONE".


- text that will appear in the body text of the email.


Recognizes two codes - "NO" and "TEST".
Any value other than "NO" or "TEST" is ignorned and the line is processed normally.

When Process = NO this line is skipped.
When Process = TEST no email is sent but the log file will display the email settings that would have been used.

Optional PDF format options for pagelayout, pagesize, width and height.

This column was added in version 2.


-pagelayout portrait --width 800
PDF Format Options


Sets the page orientation (landscape or portrait) of the exported PDF.
  • If not specified, its Tableau Desktop setting will be used.


Sets the page size of the exported PDF as one of the following:
unspecified, letter, legal, note folio, tabloid, ledger, statement, executive, a3, a4, a5, b4, b5, or quarto.
  • Default is letter.
  • unspecified asks the software to dynamically adjust the size to fit the report.
Example: --pagesize legal
Example: Landscape mode, adjust page size to fit the data.
--pagelayout landscape --pagesize unspecified
Warning about unspecified option: use of --pagesize unspecified may cause problems with the PDF output and the PDF file is read by Abobe's PDF Reader.


Sets the width in pixels.
  • Default is 600 pixels (px). ?
Example: --width 600


Sets the height in pixels.
  • Default is 800 pixels.

Mailing to Two or More Email Recipients

Use a email distribution list, distribution group (Microsoft Exchange), contact group (Outlook), or group mailing service. Most email engines have this capability.

Editing CSV Files in Excel

Excel functions that may be helpful when preparing the DSA Command Parms file.

You can add new columns as long as you don't delete the required column headers included in the template file.

  • Subject-Line - derived from recipients name. =CONCATENATE("Report for ",C3)
  • URL-Filters - derived from recipients name. Spaces are encoded as "%20".
=CONCATENATE("?Cust%20Acct%20Manager%20Name=",SUBSTITUTE(B3," ","%20"))
  • Output-File-Name - derived from "Sales1" + value from ID column.
  • Replace space with URL space code =SUBSTITUTE(B3," ","%20")
  • URL-Filters column =CONCATENATE("?Cust%20Acct%20Manager%20Name=",SUBSTITUTE(B3," ","%20"))
Miscellaneous Excel Functions
  • =CONCATENATE("Report for ",C3)
  • =SUBSTITUTE(B3," ","-")

Trouble Shooting

PDF File is Corrupted or Unreadable

Symptom: The process appears to run without errors (no error messages on the log file) but when the .pdf file is opened the PDF reader says the file is corrupted or unreadable.
Fix: Correct the URL in the URL column. See Fixing Problems with URLs.
Discussion: We would like Tableau to always issue an error message when it is presented with a URL that it doesn't understand. But it doesn't.

In this situation Tableau may appear to be working as expected but the output file create is unreadable.

"404 Not Found" message

Symptom: On the log file (Detail_Logs folder) the "404 Not Found" message appears.
Symptom: The output file is not saved or cannot be emailed.
Fix: The problem is probably a problem with the URL - fix the URL or URL-Filters columns in the .csv file.

Problems with URLs

Remove First Part of URL

The URL string taken from the web browswer produces .pdf or .png files than cannot be read. To fix this problem the URL below:


was edited to remove left-most segments of the URL as shown below.


Problems with Batch (.bat) Files

.bat files must be ASCII.

  1. Open the file with Windows Notepad editor.
  2. File > Save As
  3. Change the Encoding to "ANSI".
  4. Save.

A batch file is a kind of script file in Windows. It consists of a series of commands to be executed by the command line interpreter, stored in a plain textfile.

Reports don't load latest data - Problems with cache

Cache might be responsible for reports with outdated data (considering when extracts have updated data). This might happen in Tableau/DSA v8.x and earlier versions.

vizqlserver.exe is responsible for managing DSA Web Server cache. Killing it is a way to force a cache reset.

Add the following two lines to the batch file running AD:

   taskkill /im vizqlserver.exe /f
   timeout 60

The first line should kill all vizqlserver.exe applications, and the second line will wait 60 seconds that should be enough time for DSA to reload vizqlserver.exe.

Path Names in Batch Files

Folders Names with Spaces (e.g. "Program Files (x86)")

Folder or File names that contain embedded spaces must be put in double quotes. For example:

set DSA_AD_path="C:\"Program Files (x86)"\DataSelf\DSA_AD"

"Short Names"

dir <path> /X -- Use the /X parameter of the DOS dir command to display the short names generated for non-8dot3 file names.

set DSA_AD_path="C:\"Program Files (x86)"\DataSelf\DSA_AD"
dir %DSA_AD_Path% /X

Then set the variable to the short name shown by the the dir /X command.

For example: set DSA_AD_path="C:\PROGRA~2\DataSelf\DSA_AD"

dir C:\ /X -- This format may be required to display the short name of "C:\Program Files".

CD (Change Directory) Command

Recommended Usage:

CD [/D] [drive:][path]
Where: /D -- change the current DRIVE in addition to changing folder.

Problems with Email

Smtp.gmail.com Accounts "5.5.1 Authentication Required" error

Error from DSA AD: The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required.

  1. Use the SMTP server user and password to connect the gmail.com account page ( For more on SMTP user & password See Static Parameter File ).
  1. Click Sign-in & security
  2. Toggle Allow less secure apps to ON. (Allow less secure apps: ON)