DSA Automatic Distribution

From DataSelf Knowledge Base
Jump to navigation Jump to search

Contents

Newer Automatic Distribution (AD) Pages

DSA AD   =   DataSelf Analytics, Automatic Distribution

Automatic Distribution (AD) for Tableau

The report & distribution commands drive the automatic distribution operations.

 

OLDER Version of DSA AD

Versions of DSA AD

This page is for DSA AD up to version 1.5.     For DSA AD versions 2.x and later see DSA Automatic Distribution v2.

Concept

Dataself Analytics Automatic Distribution (DSA AD) Module automates the distribution of views (reports), dashboards or entire workbooks created by DataSelf Analytics Web Server. A typical use case is emailing a sales report to each member of a sales force containing just that individual's sales information.
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.
DSA AD:

  • Automatically run DSA views with given parameters.
  • The output may be in .pdf or .png (image) 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.
  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

Finding tabcmd

Default location is C:\Program Files\Tableau Server\Tableau\8.1\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. Command / 'Batch' file - file type .bat

When DSA AD is run by a scheduler this is the file that is given to it to run.

Template file: Run_template.bat
This .bat file executes DSA AD and specifies the static parameter file and DSA Commands file to use.

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 .bat file

The .bat is usually configured to be executed by the SQL scheduler. The .bat executes DSA AD and specifies which static parameter file and DSA Command file to use for each run.

  • Run_template.bat is the template for you to copy, rename and edit for each use.
  • See the comments in the file for more information.

The .bat file has variables which must be set to tell DSA AD:

  1. Where to find PowerShell.
  2. Path to the DSA AD folders.
  3. Name of the static parameter file to use for this run.
  4. Name of the Command Parameters (.csv) file to use for this run.

DOS path & file references can be tricky in .bat files. See Path Names in Batch Files.

Example: .bat file

set psExeFileRef=%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

cd %DSA_AD_Path%\PowerShell_Scripts

%psExeFileRef% ^ -ExecutionPolicy Unrestricted ^ -Command .\Main.ps1 -DSAABasePath "%DSA_AD_path%" ^

-StaticParmsFile "SParms0.ps1" ^
-DSACmdParmsFile "DSACmdParmsAWS.csv" ^
-ParamOnlyTest:$TRUE ^
-SkipEmail:$false ^
-SkipTABCMD:$false -SkipTabCmdLogin:$false -SkipTabCmdLogout:$false

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

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.
SMTPUseSSL =

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.

NOTES:

  • 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.

Preparation

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

ID

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

URL

The URL that opens a worksheet in workbook available from the DataSelf Analytics Web Server.
For Versions of DataSelf Analytics prior to version 10: The string in the URL column must end in either ".png" or ".pdf" to match the value set in the Output-Format column.

Example
WHEN The URL used for testing in the web browser is:
http://localhost:8000/views/TestA/Sheet3 or
http://localhost:8000/views/TestA/Sheet3?AcctManagerID=BB
AND the Output-Format column = pdf
Set the URL column to
http://localhost:8000/views/TestA/Sheet3
- or -
http://localhost:8000/views/TestA/Sheet3.pdf

NOTES: The .pdf ending is not recommended for later versions of Tableau. In some situations the .pdf extension may prevent the pdf output being saved.

Short URLs Required by Some Servers

A URL such as

http://localhost:8000/views/TestA/Sheet3.pdf

may have to be shortened to:

/views/TestA/Sheet3.pdf or /views/TestA/Sheet3
URL-Filters

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

  • 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):

?AcctManagerID=AA,BB&Salesperson%20Name=John%20Smith&:refresh=yes

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.

Important Note about URL Encoding.

This encoding is required by the HTML protocol.

  • Ampersand must be replaced with %26 in order to differentiate it from the ampersand which signals the start of another filter field.
  • Other characters that must be encoded include the ampersand ("&") and apostrophe ("'").

For more on URL encoding see: DSA Server - Filter Views using URL 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
http://localhost:8000/views/TestA/Sheet3?AcctManagerID=BB
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
?AcctManagerID=AA&:refresh=yes
  • NOTE: This feature needs to be tested in order to determine the proper syntax for combining it with the query strings shown above.
Recipient-Email-Address

- 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

Recipient-Name

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

Output-Path

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.
Output-File-Name

the name of the file to write the .png or .pdf 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 / \ ? % * ; : “ < > .
Output-Format

Required. Must be either pdf or png.

  • This value must match the ending of the URL column.

NOTES:

  • The csv option, also variously known as crosstab or crosstab to Excel on some DSA/Tableau screens and documentation, is not supported in earlier versions of DSA AD (crosstab is supported in later versions).
    • 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.
    • Because of this, earlier versions of DSA AD did not allow this option.
  • All views in a workbook. This documentation is for an earlier version of DSA AD. Later versions include a fullpdf option which outputs all views in a workbook to a pdf file.
Subject-Line

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

Body-Line

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

Process

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.

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.

Examples:
  • 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.
(=CONCATENATE("Sales1_",A4))
  • 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:

http://dataself/#/views/Company-ARAging/Customer.pdf

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

/views/Company-ARAging/Customer.pdf

Problems with Batch (.bat) Files

.bat files must be ASCII.
Fix:

  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]
ECHO "%CD%"
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)
Retrieved from "https://wiki.dataself.com/index.php?title=DSA_Automatic_Distribution&oldid=5746"