You can export the value frequencies of a particular column to a dictionary from the detailed view. A dictionary is a reference data set that you can use to evaluate data in a mapping. You can use a dictionary to verify the accuracy and format of the value frequencies on a data source or an object in a mapping.
After you export the value frequencies to a dictionary, you can add the dictionary to an asset in Data Quality, and then use the asset as a rule in Data Profiling. For example, you can configure a rule specification or cleanse asset to read a dictionary.
You can export one column and the values at a time to a dictionary. If you want to export multiple columns values under different columns, you need to design the dictionary with the required columns, and then use the dictionary to export all the value frequencies from Data Profiling.
When you export value frequencies to the dictionary, Data Quality adds the values to the first empty column in the dictionary. When you export value frequencies to an existing dictionary, Data Quality adds the values to the blank rows of the column that you selected.
Example 2. Example
You want to create a reference table of different countries, currencies, and capitals using a profile, and then use the dictionary in a rule specification or cleanse in Data Quality. To achieve this, you need to export the value frequencies to a dictionary named Country_Details. The Country_Details dictionary includes columns such as Country, Capital, and Currency.
To export the value frequencies to a dictionary, you can create and run a profile on the source object that includes the required information. After the profile job completes, you can then export each value frequencies of a particular column to the columns in the Country_Details dictionary. The following image displays the Export Values to a Dictionary window:
Note: You can export the value frequencies of nested columns of the Avro and Parquet source objects to a dictionary from the detailed view.
Exporting column values to a dictionary
You can export one or more value frequencies of a column to a dictionary. To export the value frequencies of different columns, you need to design the dictionary with the required columns, and then export the values one at a time. Before you export the value frequencies to a dictionary, verify that you have enabled the dictionary permissions and privileges for the user role in Administrator.
1From the summary view of the profile results section, select a column that you want to export to a dictionary.
2Click the column name.
The column appears in the detailed view.
3Optionally, if you want to export specific value frequencies, select the values from the value distribution table. By default, Data Profiling exports all the value frequencies.
4In the detailed view section, click the export values to a dictionary icon .
The Export Values to a Dictionary window appears.
5Specify the following dictionary details:
- Export Mode. You can create a new dictionary, or you add to an existing dictionary. By default the Create a dictionary option is selected.
- Name. Enter a name for the dictionary.
- Description. Optionally, enter a description.
- Location. Click Browse and then select a project or folder where you want to save the dictionary.
6If you choose the Add to an existing dictionary option, specify the following details:
- Dictionary. Click , and then select an existing dictionary from the Select Dictionary window.
Note: Before you select an existing dictionary, verify that you have the required permissions for the specific dictionary.
- Add to Column. Select the column to which you want to export the value frequencies in the dictionary.
7Select the value range. You can choose to export All, Selected, Unique, or Non-unique value frequencies depending upon the values you selected in the value distribution table.
8Click Export.
Data Profiling exports the value frequencies to the dictionary in Data Quality. To view the exported values in the dictionary, click the notifications link that appears after you export.
View exported column values in a dictionary
When you export the value frequencies to a dictionary, the values appear on the Configuration view in Data Quality. You can define and update the content and structure of a dictionary on the Configuration view.
To open the dictionary on the Configuration view, open the folder where you saved the dictionary, and then click the dictionary name link.
The following image shows a sample dictionary on the Configuration view:
.
, and then select an existing dictionary from the 