Monday 16 September 2019

How to automate exporting Tableau work book and sending to unlicensed users daily

There are several ways to send tableau workbooks daily to the users who do not have access to the tableau server.
This is one of the ways
1. First, connect to the Tableau server using admin access and export the needed workbook to the pdf or CSV or Image and save it under the network folder.
2. Create a SQL Job to execute the batch file as a first step and attach that pdf or CSV or image, send it as an attachment to the unlicensed users as a 2nd Step.

1. You can create a batch file with below CONTENT in it to export the workbook to the pdf or Image. please replace the respective values wherever needed. This can only be used if Tableau version is before 2018.1.2 since TABCMD is deprecated in the later versions.  Please make sure to install Tableau command utility (Tabcmd For Server) in the server which has SQL Server instance installed in case if SQL Server and Tableau Server installed in different servers.

::change variables below before running Batch file 
SET ServerIP="http://TABLEAU_SERVER:IP_ADDRESS/"
SET adminLogin="Administrator"
SET adminPwd="<PWD>"
SET TabcommandPath="D:/Tableau/2018.1/bin/TABCMD.EXE"
::TabcommandPath="C:/Program Files/Tableau/Tableau Server/2018.1/extras/Command Line Utility/tabcmd.exe"
SET SQLDBLogin="sa"
SET SQLDBPassword="PWD"

::please remove the suffix (?:iid=1) of workbook name when setting the View Name
::add "?:refresh=yes" as a suffix to refresh view before generating a Pdf file 
SET ViewName="/views/WORKBOOK-NAME/WORKBOOK-NAME?:refresh=yes"
SET CSVViewName="views/WORKBOOK-NAME/WORKBOOK-NAME.csv"
SET ExportViewName="views/WORKBOOK-NAME/WORKBOOK-NAME"
SET PdfPath="D:\Tableau\Automated Dashboards\WORKBOOK-NAME.pdf"
SET CrossTabPath="D:\Tableau\Automated Dashboards\WORKBOOK-NAME.csv"

SET SMTPServer="smtp-relay.gmail.com"
SET UserName = "noreplyfromNara@Narasimha.com"
SET ToEmail = "Narasimha@live.com"

%TabcommandPath% login -s %ServerIP% -u %adminLogin% -p %adminPwd% 
%TabcommandPath% get %ViewName% -f %PdfPath% 
%TabcommandPath% get %CSVViewName% -f %CrossTabPath% 
%TabcommandPath% logout

2.   Create a SQL Job to execute the batch file and send the workbook as an attachment to the users. Below is the 2nd Step CONTENT in the SQL job, please replace the respective values wherever needed.
USE msdb
GO
DECLARE @CurrentDate DATE = GETDATE()-1
DECLARE @Subject VARCHAR(500),@body VARCHAR(500)
SELECT @Subject = 'ORGANIZATION - TABLEAU WORK BOOK' + cast(format(@CurrentDate ,'dd-MMM-yy') as varchar)
,@body =  'Attached is ' + @Subject + ' Note: This is an auto generated mail. Please do not reply to this mail. '

--SELECT cast(format(@CurrentDate ,'dd-MMM-yy') as varchar)
EXEC sp_send_dbmail
@Profile_name= 'NARAProfile',
@Subject= @Subject,
@recipients = 'Narasimha@live.com',
@body = @body,
@file_attachments = 'D:\Tableau\Automated Dashboards\WORKBOOK-NAME.pdf'
GO

This would help organizations who have limited tableau licenses but all the users wanted to analyze the tableau dashboards.

No comments:

Post a Comment