Using Workday RaaS to Extract Data
Using Workday RaaS to Extract Data Workday has a Reporting as a Service (RaaS) interface which allows you to export most data from Workday by creating a custom report within Workday then exposing it as a web service. The output can be various formats such as RSS, XML or JSON format and you can even modify the filters within the URI. RaaS are faster because they are pre-gathered because the actual data gathering happens in Workday cloud whenever the report runs and delivered as the preferred output format of XML or JSON. That’s why it’s faster. Reports are a live look into Workday, and pulls the data as of the current moment just like the WWS which is used by the Workday snap. If you depend on live data, use the Workday snap. Also remember that using reports you’re just shifting the load-debt. It’s still calling Workday, just at a time you may or may not be expecting instead of at the time of the call. Below are high-level instructions on how to retrieve data from Workday within SnapLogic using Workday’s RaaS interface. Step 1 - Create Custom Report in Workday You need to login to a Workday instance Click on Reporting & Analytics Button Click on Create Custom Report Provide some Report Name Report Type: Advanced Data Source: Journal Lines Under Custom Report Data Source Filter: Journal Lines for Financial Reporting and Reporting Time Period Under Columns Journal Number Company Accounting Date Ledger/Budget Debit Amount Ledger/Budget Credit Amount Ledger Currency Journal Source Ledger Line Memo Created By Be sure to share it with the account that you are querying the report with. Some reports Data Sources will enable filters on them so that the data retrieved is indexed and offers some performance benefits. In this report it will prompt you to enable Data source filter Now click on the Prompts tab to populate the default prompts. Once the checkbox is clicked, the prompt defaults are populated. Accept the defaults and if you wish to change the names, please do rename that makes sense for your project. Click OK at bottom to save Step 2 - Ask customer what data do they want. The default behavior is to pull the data as of the current moment. If the customer has a different requirement then please follow this link (Workday Report Data Prompts) to understand how you can control what type of data can be pulled from Workday reports. The above behavior is only possible with Workday Advanced Reports. Step 3 - Get Web Service URL Next to the name of your custom report you’ll see a button … click it and under Actions select Web Services and View URLs You need to set filters for the ones that are mandatory (red asterisk *) Period: 2013 - Jan Ledger: Actuals Amount Type: Activity Time Period: Current Period Company: Global Modern Services, Inc. (USA) Click OK button Workday delivers in multiple formats. (REST - Workday XML, WSDL, JSON, etc). Choose the format you like and copy the URL. It is important to copy the URL for that specific format. REST https://wd2-impl-services1.workday.com/ccx/service/customreport2/tenant_name/nganapathiraju/NG_Journal?Perform_Intercompany_Eliminations=0&Perform_Interworktag_Eliminations=0&Company!WID=cb550da820584750aae8f807882fa79a&Time_Period!WID=ac6e82a2e2d01000180ca7cad7770051&Calculate_Current_Year_Retained_Earnings=0&Calculate_Translation_Gain_or_Loss=0&Amount_Type!WID=dcfe0be6bdf044da8781b873631c71c4&Ledger!WID=93553555942b4b448defb264c084d0fa&Eliminations_Only=0&Period!WID=4facd2281c9a4b2794afc7559475359f WSDL https://wd2-impl-services1.workday.com/ccx/service/customreport2/tenant_name/nganapathiraju/NG_Journal?wsdl JSON https://wd2-impl-services1.workday.com/ccx/service/customreport2/tenant_name/nganapathiraju/NG_Journal?Perform_Intercompany_Eliminations=0&Perform_Interworktag_Eliminations=0&Company!WID=cb550da820584750aae8f807882fa79a&Time_Period!WID=ac6e82a2e2d01000180ca7cad7770051&Calculate_Current_Year_Retained_Earnings=0&Calculate_Translation_Gain_or_Loss=0&Amount_Type!WID=dcfe0be6bdf044da8781b873631c71c4&Ledger!WID=93553555942b4b448defb264c084d0fa&Eliminations_Only=0&Period!WID=4facd2281c9a4b2794afc7559475359f&format=json Refresh URL it will download the report in JSON format after you enter the credentials Step 4 - Create SnapLogic Pipeline to access URL Download Get WD Report.slp included on this page Import pipeline into SnapLogic Modify REST - Get Snap Change Service URL to the URL from Step 2 Create New Account - Basic Auth Enter your Workday Username and Password Validate Pipeline You should see preview data Note you might need to change Mapper if you are using different fields The sample pipeline with basic structure Get WD Report.slp (5.6 KB)
Using JWT to Authenticate to Box from SnapLogic
SnapLogic has a Box Snap Pack which works very well for the majority of day to day Box operations. The snaps are easy to use and connecting to Box using the Box Account is very simple. I would recommend using this Snap Pack if the snaps meet your needs. However, the Box snaps (at this writing) won’t allow you to take advantage of the full functionality that the Box API provides. So, to access the additional functionality will require the use of the SnapLogic REST snaps. When I decided to investigate the possibility of using the Box REST api, the first decision that I had to make was how to authenticate to Box. Box provides multiple options for authentication, but OAuth2 with JWT turned out to be the clear choice because it connects server-to-server without any user interaction. Unfortunately, however, using OAuth2 with JWT presented several challenges for me. I confess that most of the challenges were due to my ignorance about JWT and SSL. I spent a lot of time researching both of these technologies. If you don’t already have a good understanding of how JSON Web Tokens work, then it would be a good idea for you to read up on them. I found this site to be particularly helpful; https://jwt.io/introduction/. My first challenge came in the fact that Box and SnapLogic use different ways to manage the SSL Keys that are necessary to sign the JWT. SnapLogic uses a Java Keystore (jks) where Box uses Privacy Enhanced Mail (pem) keys. As I discovered, converting from jks to pem is really not that difficult, but the documentation in Box assumes you are using pem and doesn’t address jks at all. The reverse is true for SnapLogic. My second challenge was in figuring out what data should be included in the JWT and where to put the data within the JWT Generate snap. If you’re also struggling with some of these challenges, I hope the steps below can save you some time. Before you start, you’ll need the following; The JWT Snap Pack installed in your SnapLogic environment. An installation of the Java Runtime Environment (jre). An installation of OpenSSL. I used the Cygwin package suggested by Box on their JWT Application Setup page. Access to the Box enterprise account, either directly or through a system administrator. Create an OAuth2 with JWT authenticated app in Box You can read in the Box documentation about how to do this, it’s a pretty simple process. When you get to the “Add and Manage Public Keys” section, follow the instructions below to generate your own public/private keypair. This is not the Box recommended method, but it is the only way that I could find to generate both the JKS and PEM files needed by SnapLogic and Box. Create the Java Keystore and Public Key PEM files keytool.exe -genkey -keyalg rsa -keystore .jks -alias keytool.exe -export -keystore .jks -file .der -alias openssl.exe x509 -in .der -inform der -outform PEM -pubkey -noout > .pem Add the Public Key to Box In your Box app, under the “Add and Manager Public Keys” section, click the “Add a Public Key” button. Then, copy the entire contents of the public key PEM file (created in the previous step) and paste it into the Public Key area, then click the “Verify and Save” button. Change the Alias of the Java Keystore Note: At the time of this writing, SnapLogic erroneously associates the key alias with the key id (kid) attribute in the JWT header, thus requiring us to make them the same. The key id is assigned by Box when we add the public key to the app. Since we have no control over the Public Key that Box chooses, we must change the alias of the keystore to match our Box app’s Public Key ID. keytool.exe -changealias -alias -destalias -keystore .jks Download the App Settings from Box Use the “Download as JSON” button at the bottom of the app config page to get a copy of the app settings. (We’ll use this later.) Grant Access to the Application in Your Box Enterprise Instructions for this are in the Box docs at https://developer.box.com/docs/setting-up-a-jwt-app under Step 3. Upload the JKS file and App Settings to the sldb or another storage location accessible by SnapLogic Use the SnapLogic Manager tab to upload files to the SnapLogic Database (sldb) if you want to store the files there, or you can store them on FTP, AWS S3, Google, etc. Create a JWT Account to Sign the JWT Go to the SnapLogic Manager tab and add a new JWT Account to your project. Set the Key Store to the location of your jks file then enter the KeyStore password and Key Alias (i.e. the Pubic Key Id from your Box App). Use the Validate button to verify that everything is working then apply the changes. Create the Authentication Pipeline in SnapLogic Create a pipeline in SnapLogic which will generate the JWT, send it to Box for authentication, and return the Bearer Token. Add and configure the following snaps in the pipeline. A File Reader snap to load the app settings Json. A JSON Parser snap to parse the app settings. A Mapper snap to format the JWT Claims. A JWT Generate snap to create the JSON Web Token. NOTE: Be sure to go to the Account tab and select the JWT Account that we created earlier. A Mapper snap to build the token request. Here is the full Expression for the encodedUri. ‘grant_type=’+encodeURIComponent(‘urn:ietf:params:oauth:grant-type:jwt-bearer’)+‘&client_id=’+encodeURIComponent($original.claims.iss)+‘&client_secret=’+encodeURIComponent($original.clientSecret)+‘&assertion=’+encodeURIComponent($access_token)
How to give multiple key columns in join?
I have multiple tables and I need to join them all with different key columns by using the join snap, but it is resulting in the following error:-Failure: Right join path not found for input2, Reason: Right join path required, Resolution: Please address the reported issue. Below you can see the screenshot that we are joining with different key columns Please help me how can we do the join with different key columns?
Flatten JSON files into CSV files
Created by @schelluri The pipeline pattern flattens a JSON file, which has multiple objects, and turns it into a CSV file. Configuration Sources: JSON Generator Targets: CSV file Snaps used: JSON Generator, JSON Formatter, JSON Parser, Script, CSV Formatter, File Writer Downloads MS_Flatten_Script.slp (31.4 KB)Performing an Action when there is no data
A common integration pattern is to do something when no data is received. For example, we might read a file, parse it, and find that no records meet some filter criteria. As a result, we might send an email, or insert a ticket into a ticket management system like ServiceNOW. However, in SnapLogic, this can be somewhat more difficult than it seems initially because of the streaming architecture. In fact, many snaps will not execute without input documents - rather hard to accomplish when there is no document to flow through: So, how can we take an action, even though there’s no document to run the snap? The trick is to make a document and force it into the stream with a Join Snap (set to Merge): Note in this figure that even though nothing flows into the top view of the Join Snap, it still produces an output. This enables us to use the Router snap to check whether or not a field that we know will exist in the source does in fact exist. If it does, we know that data has passed through the Filter snap and then been merged with the Sequence data. If it does not, we know that only the Sequence data has been passed through and therefore nothing made it through the Filter . Only one of these views will have anything pass through it. The magic here is in the Merge option in the Join snap. As long as it is receiving data on a view, it will output - even if it has nothing to ‘join’ to. Meanwhile, the Sequence snap will always output the number of documents that you set it to. In this case, only one is required. (This pipeline here: Do something after no doc filter_2017_04_26.slp (11.1 KB) )Require API to build a pipeline to find the high CPU and memory utilization of an individual pipeline during its runtime
We are trying to design a pipeline in Snaplogic to identify the high CPU and memory utilization of an individual pipelines that ran in a month using public API. We tried using runtime API but we are not able to get the expected output. API used: “https://elastic.snaplogic.com/api/1/rest/public/runtime/ ” “https://elastic.snaplogic.com/api/1/rest/public/runtime/Environment/ ”+$Runtime_ID Is there an API for it? which can give the expected output.
Rest API service endpoint returned erros result: Status Code =403
When configuring a REST Post of the mailjet API, it indicates the following message. Error: Reason: REST API service endpoint returned error result: status code = 403, reason phrase = Forbidden, refer to the error_entity field in the error view document for more details Resolution: Please check the values of Snap properties. Error Fingerprint[0] = efp:com.snaplogic.snap.api.rest.89xhsFt7 When performing the test in Postman, the execution is correct