State Department Per Diem Rates to Workday Import Expense Rate Table
Pipeline sample that grabs per diem rate data from State Department website and imports it into Workday Rate Tables. It gets a little complex as far as the data mapping and filters, but cuts down on tedious manual maintenance of these tables. We run this once a month. There is definitely room for improvement like adding some validation checks. One big issue is that the import is all or nothing; if a single record doesn’t match between the State Dept. spreadsheet and Workday, it fails. Also, the Workday call itself always returns as if it succeeded, so you have to do a search for ‘import expense rate table’ in Workday to see if and what any errors are. You will likely have to play around with the mapping and filters to get the Excel data to match the Spend_Data_ID fields for locations in Workday. And the State Dept adds a new location every couple months which will cause the integration to fail unless you enter the new location in Workday. So, not perfect, but certainly saves HR a few steps. Erik Pearson Senior Software Engineer Bowdoin College HR_PerDiemRates_HR_Import.zip (7.8 KB)
CLI for BCP (SQL SVR) and dynamic file names
The scenario I needed to accomplish was to pull data from SQL server, group the data sets together based on different fields and then dynamically define the file name based on those groupings. Performance was the #1 consideration and competitive tool sets were able to complete the action in under 2 hours with a 300M record set. For my testing and documentation below, I’m using a local data set of 3.6M records. Initial attempts at using the SQL Read and SQL Execute snaps quickly excluded those based on required throughput (was getting somewhere between 12k and 18k records per second over the network, 26k records per second local). Calculated out, that would have taken 7-10 hours just for the query to complete. The final solution ended up being broken down into 3 main steps: Command line execution to call a BAT file which invokes the BCP command Reading the dataset created from step 1 and performing the GROUP BY and a mapper to prep the data Document2Binary snap and then a file write which utilizes the $[‘content-location’] value to dynamical set the name. Attached to this post is the ZIP export of the project which contains 3 pipelines along with a SAMPLE bat file which starts the bcp utility. You will need to edit the bat file for your specific database requirements, the pipeline parameter for where the output of the bat file is stored and the location of the file you want written out. Pipeline: 1_Run_BCP_CLI NOTE: There is a pipeline parameter for the BCP output file that gets generated ‘Reference to BAT’ - points to a file on the local drive which executes the actual BCP process. (My files are located in c:\temp\bcp_testing) ‘DEL BCP Out’ will delete the bcp file if it already exists (optional as the bcp process will just overwrite the file anyway) ‘cli to run’ is renaming the bat key value (originally done as I was testing the cli and bcp execute - could be removed) ‘remove nulls’ will clear out the results from the ‘DEL BCP Out’ since it’s not part of the command line that needs to be executed. ‘Execute CLI’ is a script snap which will kick off the bat file and once completed, return a single record with the results. ‘Process BCP’ Out’ is a pipeline execute which calls 2_BULK_DAT_READ and passes the pipeline parameter for the file to read in the next step Pipeline: 2_BULK_DAT_READ ‘BCP Out File Read’ will use the pipeline parameter value specified for which file to read ‘CSV Parser’ self explanatory - does NOT have any headers on the data file (to enhance the pipeline, you could add the second input and define the file format with column names and types) ‘Group by Fields’ takes the first 2 field names (field001 and field002) and will create groupings for each set. This is the results of the initials for both first and last name from the BCP SQL Query. ‘Mapper’ will convert the JSON payload to $content as well as define $[‘content-location’] based on the grouped by fields. The expression is $groupBy.field001+"_"+$groupBy.field002 ‘Pipeline Execute’ will provide both $content and $[‘content-location’] to the next pipeline Pipeline: 3_DynamicFile_Write ‘Document to Binary’ with the option for ‘Document’ as the Encode or Decode setting allows the JSON records to be output as a binary stream. ‘File Writer’ will build the protocol, folder specification and the file name based on the provided $[‘content-location’] value from before. Execution Results The 3.6M records were actually processed in 16 seconds. The BCP process itself took 24 seconds. My group by was based on the first characters on both First name and Last name. This process ended up creating 294 files (locally) and used the naming convention of _.json Sample screen cap of the A_A.json file: Notes and File The file created contains a KEY for ‘content’ and is not pretty print json. For the screen cap above, I’m utilizing the JSTool → JSFormat plug-in for Notepad++. This approach will only create JSON formatted data (not CSV or other formatter options) BCP is required to be installed and this was only tested on a WINDOWS groundplex EricBarner-SQLSvr_BCP_CLI_Community.zip (4.4 KB)
Copy files from one location to other with changes into naming
I am trying to transfer all files in folder from one location to other. If I did simply as below screenshot then it works But as per my scenario I need to change filename and path in-between, so I changed it to below pipeline but it only export one file (1st file) not for all files in folder. Please advice, how can I export all files.Detecting schema change
Here are two pipelines that everyone can relate to. Detecting schema change in an upstream data source (SQL Server in this example) and identifying the exact nature of change. Also check out our blog post on this topic. SchemaChange_SaveOriginal.slp (5.7 KB) SchemaChangeDetection_Compare.slp (39.6 KB)
Expression Library
Hello All, If I have an expression library, having content: { FindObjectID:x => ( (x == ‘OBJ1’ ? ‘OBJ2’ : (x == ‘OBJ3’ ? ‘OBJ4’ : (x == ‘OBJ5’ ? ‘OBJ6’ : (x == ‘OBJ7’ ? ‘OBJ8’ : (x == ‘OBJ9’ ? ‘OBJ10’ : (x == ‘OBJ11’ ? ‘OBJ12’ : (x == ‘OBJ13’ ? ‘OBJ14’ : (x == ‘OBJ15’ ? ‘OBJ16’ : (x == ‘OBJ17’ ? ‘OBJ18’ : ‘UNKNOWN’)))))))))) } Is there any way to simplify this huge expression?
Mapper vs. JSON Splitter
Did you know that you can use the Mapper snap to the same effect as JSON Splitter? There are some other reasons why you may want to use JSON Splitter. First of all let’s look at how mapper can achieve similar functionality of JSON Splitter To achieve the same split, in the mapper, just drag the array into the left hand expression and map it to $ in the right hand side target path. Basically this will split the array elements into the outgoing documents. Using JSON Splitter however, you get additional benefits of including scalar parents as well as including different output paths as part of the split. Attached is an example (simple one of course). json split vs mapper_2017_08_08.slp (7.1 KB)
Common Error Handler
Hi, As requested in another thread, here is our common error handler which we’re trying to use across all projects. Feel free to share your own and any ideas! Here is the screenshot (pipeline definition is attached below): Key features: We have multiple potential notification mechanisms - currently e-mail and/or ServiceNow We have the option for either critical failures (fails the parent Pipeline) or non-critical (send error notification but does not fail the parent) We use a parameters file to configure error behavior for each calling/parent pipeline so that this can be changed for each environment (including things like the e-mail recipients) Parameters are also driven by an ‘error code’ which is passed in from the parent - this allows us to have different error behavior in different section / scenarios of the parent pipeline It is up to the pipeline designer to correctly map the document data which is input into the error handler, including identifying the relevant data to include in the message (e.g. the unique identified of the record which is in error). The package below includes the error handler, the parameters file and an example demonstrating its use. ErrorHandler.zip (6.8 KB) Cheers, C.J.
