SAS 9 Profile for Data Integration Jobs
SAS 9 Content Assessment is one of the key tools used in planning a migration from SAS 9.4 to the SAS Viya Platform. The most recent release of Content Assessment includes significant improvements in the Profile for SAS Data Integration Jobs report and some changes in the terminology used to understand the readiness status of Data Integration jobs and transformations. In this post, I will review and explain these changes. The SAS Profile Content Assessment application provides detailed information on the attributes of SAS 9 content. Profile delivers a series of reports for each SAS 9.4 content type.
The reports include details like:
- Tasks used in Enterprise Guide projects
- Transformations used in Data Integration Studio Jobs
- Calculations used in cubes and information maps.
The Content Assessment Profile application and reports help you to understand the details of SAS 9 Content and how those details impact migration to SAS Viya. The SAS Profile for Data Integration Jobs report is the main report that covers SAS Data Integration Studio Jobs. SAS Data Integration studio jobs are made up of transformations (the nodes in the jobs). Jobs migrate to SAS Studio flows which include steps (the nodes in the Studio flows).
Select any image to see a larger version.
Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.
The migration process will attempt to migrate a transformation to an equivalent step or steps in Studio. If a transformation cannot be migrated to equivalent step(s), it is often migrated to a Studio Program step that includes SAS code to perform the Transformations function. A transformation may be prevented from migrating to an equivalent step because there is no matching step in Studio, or some attribute of the transformations is not yet supported. The SAS 9 Profile for Data Integration report helps you to understand what may happen to the Data Integration Studio Jobs and their Transformations during the migration process from SAS 9 to Viya.
Terminology
Let’s look at the new terminology around the status of Data integration Studio jobs and transformations.
Transformations
Transformations are classified as Ideal Fit, Code Fit, No Fit, or Undetermined Fit.
A transformation is considered:
- Ideal Fit if the transformation migration is automatic and it migrates to equivalent step(s) in SAS Studio.
- Code Fit if the transformation migration is automatic and it migrates to a Program step in SAS Studio.
- No Fit if the transformation does not currently have a planned feature replacement in SAS Viya.
- Undetermined Fit if the transformation was unable to be profiled or evaluated for migration status.
Transformations within a Data Integration job that are profiled will fall into one of these categories.
Jobs
In the case of jobs, we can measure the fit of a job by looking at the fit of its constituent transformations. A simple formula can be used to calculate the Ideal Fit Percentage of a job. The ideal fit percentage is calculated by dividing the number of transformations in the job that are ideal fit by the total transformations in the job to get the ideal fit percentage.
ideal fit percentage = (the number of transformations in a job that are ideal fit / by the total number of transformations in the job) *100
The Percentage of Transforms with Ideal Fit shows how much of a job will migrate to equivalent objects in SAS Viya. Jobs with a higher percentage will take advantage of the equivalent steps in Studio, jobs with a lower percentage should still migrate, but some of the functionality in Studio will be provided by code within Program steps.
The Report
Overview
Let’s look at the report to see the new terminology in action.
The Overview tab in the report is a summary of the status of all the Jobs and Transformations in the deployment. You can see in this deployment there are 22 jobs with 46 Transformations. Of the 22 jobs, all are “Migration Candidates”. A job is considered a migration candidate when all of its transformations can be migrated (either ideal fit or code fit).
Transformation Migration Summary
It can be useful to start with the overview of the transformation usage in the deployment provided by the Transform Migration Summary tab. From this page, we can see the greatest success and common issues in the assessed environment. The table on the left of the page shows the most frequently used SAS 9 transformations in the SAS 9 environment and the Steps they become in SAS Viya. In this report, the File Reader was the most frequently used transformation and we can see that it is migrated to a Program Step. The table to the right shows the most common issues that will cause transformations to convert to a Program Step.
In this case, the issues are related to a mismatch between the definition of columns in the source and target tables. With the issue identified, changes could be made to the jobs to make them a better fit for migration to SAS Studio. In addition, customers can share these results with SAS resulting in a potentially improved migration experience for this and other common scenarios.
Transformation Migration Details
The Transformation Migration Details tab allows you to further explore the detail of the migration of transformations. It also allows the selection of individual jobs, and the exploration of what will happen to the specific transformation in the jobs. Let us look at a couple of jobs.
Ideal Fit Job: 01_Split_Finance_Data
Here on the Transformation Migration Details tab, we have selected 01_Split_Finance_Data. The job has a 100% of its transformations ideal fit meaning that all transformations migrate to an equivalent step in Studio. The report shows the:
- File Writer Transformations will migrate to an Export Studio step
- Extract Transformation will migrate to a Query step
- Splitter Transformation will migrate to a Branch Rows step.
Code Fit Job: DIFT Profit
Let’s look at another job that was classified as code fit and use the report to understand why. The Job DIFT Profit has one transformation that is Ideal Fit and two are Code Fit.
The report shows the :
- Extract transformation will migrate to a Query step
- File Reader will migrate to a Program step
- Table Loader will migrate to a Program step.
The report provides the conversion message explaining why the transformation will migrate to a program step. Focusing on the Table Loader transformation (which could potentially migrate to a Load Table Studio step) we can further investigate why it will migrate to a Program step. The conversion message notes at the bottom of the screen indicate that the Table Loader will migrate to a Program step because the Append uses the FORCE option.
Selecting a File Reader transformation, the conversion message indicates there is an issue with a mismatch of the SAS informat for columns in the source and target table. Selecting a row in the table will filter the lower table where object-specific details are provided. In this case, the object-specific details table identifies the table and column where the problem is occurring.
Job Migration
The Job Migration tab provides a status for each Job that has been profiled. The tab also provides links to help explain the report and the terminology used. The Job listing in this tab can be downloaded to a CSV file that will drive the migration process. The CSV file is used as an input to the CreateSASPackages application which will automate the export of the Data Integration Studio jobs that you select for migration. You can use the slider to investigate the impact of lowering the threshold for the "percentage of transforms with ideal fit". When you adjust the slider Jobs higher than the ideal fit threshold are color-coded blue in the table listing. The buttons can be used to filter the jobs in the table, and the checkboxes to select group(s) of jobs to export for package creation.
Summary
I hope this summary of the updates to the SAS 9 Profile for Data Integration Studio report and transformations has been useful. The report allows you to explore the migration status of the Data Integration Jobs and Transformations in your SAS 9 environment. The new terminology and additional functionality in the SAS 9 Profile for Data Integration report will be very helpful in planning for the migration of Data Integration Studio Jobs to the SAS Viya platform. I would like to thank Millie Lombardo and Scott McCauley for their input on this post. For more information:
- Content Assessment Documentation
- SAS DEMO | Migrating SAS Data Integration Studio Jobs to SAS Studio Flows
- Content Assessment delivers utility applications to support SAS Viya Content Migration
Find more articles from SAS Global Enablement and Learning here.
