Windows cmd shell script for dynamically generating JDBC.Properties file (A.C. 1.4)
Batch import automation scripts using a SQL query in Anaplan Connect (AC) 1.4 require a companion .properties file that contains a lot of information about the query (connect string, login credentials, SQL statement, parameters). As a best practice, you should avoid hardcoding usernames and passwords in plaintext or having redundant information that may change in multiple .properties files. A Windows shell script named createJDBC.properties is provided that dynamically creates a temporary jdbc.properties file on the fly from secure, centralized information.
Any computer code in this post is provided "as-is", there is no guarantee of fitness for purpose. As the implementer, you are responsible for understanding it, modifying it to your situation, testing it, and taking precautions to avoid any damages that may result from use.
Anaplan Connect 1.4 uses a .properties file when connecting to most relational databases such as Oracle, MySQL, and many more. (AC connections to MS SQL Server are the exception, those do not use a .properties file as of the time this was written.) The contents of the .properties file need to look something like this:
jdbc.query= Select Descr || ', ' || State || ': ' StoreNum FROM Stores WHERE Open_Date>(Select sysdate from Dual)
As you can see, there are Oracle login credentials and a SQL statement that may require periodic maintenance. A batch process for updating your data hub might involve a dozen queries, each stored in a separate .properties file. Do you really want to hardcode this information in a dozen files? It would be better to generate the .properties file on-the-fly as your process requires it and to delete it immediately after use.
Another nuisance issue: For SQL queries to be maintainable they must be readable by people and readability requires line breaks. Which code would you rather maintain:
SELECTA.Descr || ', ' || A.State || ': ' A.StoreNum as Name, B.Region as Parent, A.StoreNum as Code FROM Stores A inner join StoreStatus B on A.StoreNum=B.StoreNum WHERE B.Open_Date<(Select sysdate from Dual)
SELECT A.Descr || ', ' || A.State || ': ' A.StoreNum as Name, B.Region as Parent, A.StoreNum as Code FROM Stores A inner join Store_Info B on A.StoreNum=B.StoreNum WHERE B.Open_Date<(Select sysdate from Dual)
Both are the same query, but the second one is much easier to understand and update.
You may be asking "Why not use Views in the relational database?" I agree, and I will generally advise anyone to store complicated SQL statements in their relational database as a view. This allows you to have VERY simple query logic in your AC script: SELECT * FROM <name of view>. However, on a recent project the client felt it would be too cumbersome to work with their Oracle DBA to change the view definition whenever they needed minor changes to the query logic. Anaplan models can be quite flexible, they felt they needed the same flexibility to modify the data source for their import processes. For them, it would be better to keep control of their complex SQL queries in the business.
A Dynamic Alternative:
Here's a dynamic approach that solves several problems:
Maintain connection and login information in a centralized encrypted master file (Globals.bat). This .bat sets temporary variables that are then used in later steps of the process. The contents of Globals.bat look like this: SET jdbcConnection=<connect string to database> SET jdbcUsername=<database username> SET jdbcPassword=<database password> SET anaplanUser=<Anaplan username:password> For security, it is recommended that this file be encrypted. Make sure the userid that will run your scheduled batch tasks has the ability / privileges needed to read the encrypted file.
Maintain SQL scripts in another centralized master file (SQLQueries.txt). The contents of SQLQueries.txt should look like this: ::<name of query 1> <SQL statement 1> ::end ::<name of query 2> <SQL statement 2> ::end
So for our example SQL Statement above, SQLQueries.txt would include a section like the following. ::StoreHierarchy SELECT A.Descr || ', ' || A.State || ': ' A.StoreNum as Name, B.Region as Parent, A.StoreNum as Code FROM Stores A inner join Store_Info B on A.StoreNum=B.StoreNum WHERE B.Open_Date<(Select sysdate from Dual) ::end
CreateJDBCProperties.bat: copy-paste this content to create this utility script. The only modifications you'll need are the three lines that set the variables jdbcPropertiesFilePath, sqlQueriesFilePath, and LogFilePath.
cls rem SETLOCAL EnableDelayedExpansion @echo off
REM Set location of jdbc.properties file, sqlQueries.txt, and log file set jdbcPropertiesFilePath=c:\Anaplan\anaplan-connect-1.4\jdbc.properties set sqlQueriesFilePath=c:\Anaplan\anaplan-connect-1.4\scripts\sqlQueries.txt set LogFilePath=c:\Anaplan\anaplan-connect-1.4\ErrorLogs\jdbcproperties.log
REM Test for missing required arguments, (1-8 are required.) REM If any are missing, show help message if "%~1"=="" set msg=Missing required argument(s^) if "%~2"=="" set msg=Missing required argument(s^) if "%~3"=="" set msg=Missing required argument(s^) if "%~4"=="" set msg=Missing required argument(s^) if "%msg%"=="" goto resume echo ************* %msg% ************** echo USAGE: CreateJDBCProperties [dbuser] [dbpwd] [jdbcconnection] [sqlqueryname] [fetchsizemb] [sqlparams] echo. echo dbuser: Userid for relational database [required] echo dbpwd: Password for relational database [required] echo jdbcconnection: Connect string to database [required] echo sqlqueryname: Name identifying sql statement in sqlqueries.txt file. [required] echo dbfetchsize: databasefetchsize number of rows integer [optional] echo sqlargs: If sql statement uses tokens (?), values to be inserted [optional] echo. echo EXAMPLE: echo CreateJDBCProperties sysaccount syspwd123$ jdbc:oracle:thin:server.domain.com:1521:edw ^"Sales by Division and Period^" 8 ^"2019,Jan,'Div 12'^" echo. echo SQL Query must be found in sqlqueries.txt file echo Query length limited to ~8100 characters echo Query must be preceeded by ::<name of query> and must followed by ::end. Case sensitive. echo. echo Parameters for use in ? tokens in your SQL statement are optional. On the command line, enclose echo the complete parameter list in double quotes, put each parameter value with spaces in single quotes ''. echo. echo Example SQL query: echo ::Sales by Division and Period echo Select Product_ID, Customer_ID, Div_ID, Year, Period, Units, Amount echo From Tbl_Transactions echo where Year=? and echo Period in (?) and echo Div_ID in (?) echo ::end goto end :resume
call :tee START REM Assign command line arguments 1-8 to variables. set dbuser=%~1 set dbpwd=%~2 set jdbcConnection=%~3 set sqlqueryname=%~4 if "%~5"=="" ( set fetchsizemb=10 ) else ( set fetchsizemb=%~5 ) REM Get query parameters from arg6. (Extra code needed to handle comma separated list of params) ECHO %1 %2 %3 %4 %5 %6 >x&FOR %%? IN (x) DO SET /A strlength=%%~z? - 2&del x set queryparams=%~6
REM if jdbc.properties exists, delete it if exist %jdbcPropertiesFilePath% (del %jdbcPropertiesFilePath%) REM Create new empty jdbc.properties file echo .> %jdbcPropertiesFilePath%
REM Set User, password, fetch size set jdbcUser=jdbc.username=%dbuser% set jdbcPwd=jdbc.password=%dbpwd% set jdbcFetch=jdbc.fetch.size=%fetchsizemb% set jdbcIsStoProc=jdbc.isStoredProcedure=false
REM **** Find named multiline query in sququeries.txt ************ set queryfound=false REM Loop through file line by line, find named query, write to sqlstring setlocal EnableDelayedExpansion for /f "usebackq tokens=*" %%a in ("%sqlQueriesFilePath%") do ( set myline=%%a if !myline!==::end set queryfound=false if !queryfound!==true set sqlstring=!sqlstring! %%a if !myline!==::%sqlqueryname% set queryfound=true )
REM Add escape characters before < > | & and \ characters for %%B in (^< ^> ^| ^& ^\) do ( set sqlstring=!sqlstring:%%B=^^^%%B! ) rem set sqlstring=!sqlstring:"=""!
set jdbcSQL=jdbc.query="%sqlstring%"" set jdbcQryParams=jdbc.params="%queryparams%"
:: a function to write to a log file and write to stdout :tee @echo off For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b) For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b) echo %mydate%_%mytime% : %* >> %LogFilePath% ECHO %* EXIT /B 0
Put it all together: each Anaplan Connect script will a) Set some local variables, b) Call Globals.bat c) Call CreateJDBCProperties.bat, d) Set the OPERATION string, e) Pass all the necessary parts to AnaplanClient.bat. f) Delete the jdbc.properties file
The contents of a typical Anaplan Connect script would look like this: REM set workspace ID, model ID, and SQL query name SET workspaceId=<Anaplan workspace ID> SET modelId=<Anaplan model ID> SET anaplanFileName=<name of file to replace in Anaplan> SET anaplanActionType=<process|import|export> SET anaplanImportAction=<name of action (process, import, export) to run in Anaplan> SET rejectLogFolder=<path to folder for rejected records logging> SET processLogFile=<full path to file where AC 'chatter' will be logged> SET sqlQueryName=<name of query in SQLQueries.txt> REM *** End of settings - Do not edit below this line *** CALL ./Globals.bat CALL ./CreateJDBCProperties.bat %jdbcUsername% %jdbcPassword% %jdbcConnection% %sqlQueryName%