Alright, let's get back to the discussion of best practices using CDI! I have a long list that I keep handy to help new users. Here are a few more of my favorites, this time on the "DO" side of things. 1. Treat creating a CDI job similarly to how you would create a new SAS program. Know your source data. Know your target data. Have good specifications. A great program (or CDI job) starts with a well-thought-out plan. 2. Understand your domain dependencies, and create your jobs in the order of those dependencies. For example, DM is an input to many other domains, so create your job to populate DM early. 3. Turn off automatic propagation! By default it is turned on. This means that when you connect one transformation to another in your job, all of the columns from the source will automatically map as is to the target. We typically want to be very specific about the columns that we carry through each transformation in our job, and automatic propagation makes that very hard. I suggest turning this off universally by selecting Tools -> Options and then on the Job Editor tab deselecting "Automatically propagate columns" under Automation Settings. 4. Use an Extract transformation as a buffer between every source table and the subsequent transformations in the job. This buffer preserves the metadata information about the source tables so that in the case that the source table is removed from the job or the table metadata object gets changed, moved, or deleted, your job will still retain the table metadata. Imagine if you connect two source tables directly to a Join transformation and code a variety of complex formulas in the Expressions. Then, another user deletes the source table objects from CDI. When you open your job, the source tables will be gone, and when you open your Join transformation, every reference to a source column in the Expressions will be changed to "[missing]". Even if you reconnect the source tables, the Expression will not know which columns belong to each expression. You will have lost that work. However, if you put an Extract transformation off of each source table and directly extract all the columns you might need for the job, without any further transformation or modification, the target of that Extract transformation now contains all the metadata of the source table columns. If the Join is done from those two Extract targets and you lose your source tables, the Join will still contain all of its Expressions and metadata. And all you need to do in order to make the job whole again is reconnect your source tables to their Extract buffers and remap the columns. This is an example of how it benefits you if something goes wrong, but this is also the best way to create a reusable job template. By setting up a job with Extract buffers, you can then remove the source tables used for the development of the job and save it that way. A user can copy that job to a new study and just connect the appropriate source tables into the job to reuse it. 5. Document your work! I know you're not writing code from scratch, but you can still add comments and documentation about your work. When you open any transformation, the General tab displays the Name and Description of the transformation. Modify them! Don't leave every Extract transformation named "Extract". Name it "Extract lab dataset", or "Map to DM domain columns" so that it's clear what that transformation is doing. You can also add important information into the Description. Another great tool is the Sticky Note. It adds a small yellow comment to the job, which can help alert other users to important information about the job. Whatever methods you choose, treat this as you would any other SAS program and document what you are doing as clearly as you can so that it is useful to other users and even yourself if you have to revisit your work weeks or months later. How many of us remember what we were thinking when we created a job six months ago? What other end-user best practices do you have? I can still go on for days! Melissa
... View more