Fields - Data360_DQ+ - Latest

Field types

Big Integer

Integer values where a Precision greater than 19 is needed.

Boolean

True and False values.

Currency

Numeric values with currency signs. For example, $1.99 or €1.50.Like the decimal type, currency will give you complete control over rounding by enabling the Rounding Type and Scale options.

Date

Values that represent some combination of a year, month, and day, and no hour-minute-second time stamp. Values with timestamps will have all times set to 00:00:00 if the date data type and a format pattern without a timestamp is applied.

DateTime

Values that represent some combination of a year, month, day, hour, second, and time zone.

Time

Values that represent some combination of hour, minute, second, period (AM or PM), and time zone.

Decimal

Non-integer numeric values where complete control over rounding for values to the right of the decimal point is needed. Applying the decimal data type will give you the ability to specify a Rounding Type and Scale. This type should be applied when slight changes to values due to computational rounding are not tolerable.

Floating Point

Non-integer numeric values where control over rounding for values to the right of the decimal point is not needed. Can be applied when slight changes to values due to computational rounding are tolerable.

Integer

Integer values with a maximum Precision of 19.

String

Text values. For example, a person's name or the name of a product.

Semantic Type

Applying a Semantic Type to any type of field can help to further categorize the field's content and enable additional functionality throughout Data360 DQ+.

For example, adding the Airport semantic type to a string field containing airport codes could help to categorize a dataset containing unlabeled airport codes within an Analysis.

For information on supported semantic types, see [%=General.Product DQ+%] Semantic Types.

Displaying URLs as hyperlinks in dashboards with semantic type

As another specific example, the URL Semantic Type can be applied to string fields that contain string values which should be treated as URLs. Doing so will actually cause the URL field's strings to be displayed as clickable hyperlinks in data grid dashlets. If the URLs are valid, clicking on these links will open the associated website in another browser tab.

Default Value

For data files that contain less fields than are present in the data store definition, a default value field can be added to each data store field definition. When a file containing less fields than are present in the data store definition is used, the default value will be used to populate records in missing fields.

Settings for numeric fields

Format Pattern

This option allows you to specify how numbers are formatted for processing. The best way to determine whether you need to explicitly define the format pattern is to use the data store field test feature. If the test fails at a numeric field, you most likely need to define a format pattern for that field. If the test passes, you can simply leave format pattern blank.

For more help with Format Patterning, see the Number Format Java Doc.

Display Format Pattern

This setting determines how numbers will be displayed in the data store UI and in analysis sheets. If left blank, display format pattern will default to format pattern settings.

Precision

This setting controls the number of digits to include in an integer value. This will affect integers in other data stages that use the field as well, such as analyses, data views, and dashboards.

If you have customized precision for a data store field and you use that data store field in a data view, you should set the data view field's precision to match that of the data store's, to avoid execution errors.

If you intend to use a field in a data view, it is also recommended that you do not use precision as a means of removing digits in a data store because this will lead to errors in data view execution. For more on this, see the Precision section of data view fields.

If Precision is left blank, it will default to 19, which is also the maximum value that precision can be set to.

Scale

This setting controls the number of digits to the right of the decimal point that will be included in numeric values. For example, if value = 1.234 and Scale is set to 2, value will equal 1.23 within the data store.

Additionally, setting Scale higher than the amount of digits to the right of the decimal point that are present will add zeros. For example, if value = 1.234 and Scale is set to 4, value will be displayed as 1.2340.

If Scale is left blank, the field will pick up all digits to the right of the decimal point.

Scale when a field is edited in-line or used in a view/edit screen

In the special cases of a numeric field being used within case management and being edited in-line or being displayed in a view/edit screen, the following rules apply:

• Decimal fields respect the Scale setting specified for the field or will have a decimal precision of 4 if no Scale is specified.
• Currency fields respect the Scale setting specified for the field or will have a decimal precision of 2 if no Scale is specified.
• Floating point fields will have a decimal precision of 4.

Rounding Type

• Round Up - Rounds up the last decimal digit before the cutoff specified in Scale, if the cutoff digit is greater than or equal to 5. For example if value = 1.05, then choosing Round Up and setting Scale to 1 rounds value to 1.1. If value = 1.04, however, the same settings would round value to 1.0.
• Truncate - Cuts off all values that are to the right of the decimal point and beyond the number's Scale. For example, if value = 1.245, then choosing Truncate and setting Scale to 2 would cause value to equal 1.24. If on the other hand Scale was set to 1, value would equal 1.2.

Currency Code

For data stores, currency fields are predominantly configured through the Format Pattern option. Specifying a Currency Code is not required for currency fields, however, if one is specified it will be passed on to any data view that uses the data store currency field.

Specifying a Currency Code in a data store can therefore save you an extra step down the line, if you know that the field will be used in a data view. This is because currency codes are required to display currency signs in dashboards and the visualizer.

To make more sense of this, consider the following example, using a data store currency field called amt.

Example: Formatting currency fields

If amt = $100, you would first need to select the US currency format in the Format Pattern option ($#,##0.00;-$#,##0.00) to make $100 a parsable value. If this was the only thing you did, your data store would pass testing and be usable in analyses, however the dollar sign ($) would not appear in analysis sheets.

To have the dollar sign ($) displayed in the data store UI or analysis sheets, you would also need to specify the US currency format as the field's Display Format Pattern.

To have the dollar sign ($) displayed in dashboards or visualizations, you could then specify USD as the field's currency code in the data store, so that the setting will be passed with the field when the field is used in a data view. Alternatively, you could leave currency code blank in the data store and then just specify USD in your data view later.

Settings for Date, DateTime, and Time fields

Time zone

This setting is used to define a Time Zone for values in Date, DateTime, and Time fields. It should be used to apply a time zone to values that do not already have a time zone within their format pattern.

Specifying a Time Zone will alter values in the data store and in other data stages that use the data store's field. This Time Zone setting will also interact with the System Time Zone that is specified in Data360 DQ+ Administrator Preferences.

The following examples illustrate how this interaction plays out, using a data store field called dateTime.

Time Zone: Example 1

If dateTime = 2016/02/02 12:00:00 and the field's Time Zone option is set to +0100, then dateTime becomes 2016/02/02 11:00:00 in a system using GMT time.

Specifying +0100 means that the original value in dateTime is one hour ahead of GMT time, so the system, which is set to GMT time, displays this value as 2016/02/02 11:00:00 in GMT, instead of 2016/02/02 12:00:00.

Time Zone: Example 2

If the System Time Zone is different than GMT, this setting will interact with the data store field Time Zone.

For example, if dateTime = 2016/02/02 12:00:00, and field Time Zone is set to +0100, and System Time Zone is set to +0200, then dateTime becomes 2016/02/02 13:00:00.

Again, specifying +0100 means that the original value in dateTime is one hour ahead of GMT time (12:00:00 - 1:00:00 becomes 11:00:00 GMT) but since the system is set to two hours ahead of GMT time, dateTime becomes 2016/02/02 13:00:00 (11:00:00 + 2:00:00 = 13:00:00).

Time Zone: Example 3

As mentioned above, if there is already a Time Zone in a field's value and format patterning has been configured to parse it, selections in the field Time Zone dropdown will be ignored.

For example, if dateTime = 2016/02/02 12:00:00 -0100, and Format Pattern is set to dd/MM/yyyy HH:mm:ss Z, and the system is using GMT time, then dateTime will become 2016/02/02 13:00:00 regardless of any setting made in the field Time Zone dropdown.

In this situation, a System Time Zone other than GMT will affect dateTime as well.

For example, if dateTime = 2016/02/02 12:00:00 -0100, and Format Pattern is set to dd/MM/yyyy HH:mm:ss Z, and the system is using -0200 time, dateTime will become 2016/02/02 11:00:00 regardless of any setting made in the field Time Zone dropdown.

Here, the -0100 in the original dateTime value means that dateTime is one hour behind GMT (12:00:00 + 1:00:00 becomes 13:00:00 GMT), but since the system is set to two hours behind GMT, dateTime becomes 2016/02/02 11:00:00 (13:00:00 - 2:00:00 = 11:00:00).

Settings for string fields

Max Length

This setting controls the maximum number of characters allowed in values for String fields. Max Length will be set to 255 by default.

If you have customized Max Length for a data store field and you use that data store field in a data view, you should set the data view field's Max Length to match that of the data store's, to avoid execution errors.

For more on this topic, see the Max Length section of data view fields.

Generating Fields

When creating a new data store, Fields can be automatically generated through use of the Generate button.

If generating on an internal data store that has been pushed to Data360 DQ+ from another application, fields will be created as they exist in the source application. If generating on an external data store, Data360 DQ+ will use the specified Delimited Layout settings to generate the fields.

Importing Fields

For ease of use, the Import Fields feature can be used to create fields by pulling their definitions from another data store. Imported fields will have matching names, field types, and all other configurations.

Testing Fields

Once fields have been generated, you should use the Test button to ensure that the field values are being properly read. If there are issues with a specific field, the test will display details that can help you fix the problem.

After generating fields, you should always test until you've ran a successful test to make sure the data store will work.

Showing sample data

After a successful test, you may use the Show Sample Data option to see a sample of the data in your data store. Note that this sample is just a subset of your data store's contents.

Setting up a single field data store for parsing

Data stores that have a single field that needs to be parsed in an analysis should be configured as follows:

Set the Field Delimiter in the Details tab to a blank entry, to specify no delimiter.

Set the Field Name in the Fields tab to line.

Since it is also common for records to be parsed to be very long strings, the field's Max Length attribute may also need to be increased.

Encrypted fields

Specifying a data store field to be encrypted will cause the field's records to be encrypted in storage.

Encryption limitations

Only internal data stores may have encrypted fields.

Permissions required to modify encryption

Any user with access to a data store can encrypt a data store field if it hasn't yet been encrypted.

Once a field has been encrypted, only administrators and users with Administer or Manage Security permission to the data store will be able to modify the field's encryption settings.

Inheritance when used by other data stages

If a field is encrypted, this will carry over when the field is used in other data stages.

In the case of a data view, the encryption will be inherited and will not be able to be changed by anyone, unless it is changed in the data store.

Places where you'll see the effects of encryption

When you use the data store in an analysis and view its contents in a sheet. Here, encrypted values will be masked, even when they are not secured. Also note that you will not be able to unmask encrypted values within an analysis.

Effects on data when changing field encryption

If changing field encryption on an internal default data store, any existing data in the data store will not be encrypted/decrypted.

If changing field encryption on an internal database data store, a migration job will be performed upon save to encrypt/decrypt the data store's existing data.

Tokenize fields

When tokenization is enabled and the Secure check box is displayed, a Tokenize check box and Tokenize Data Element Name text field are available.

When Tokenize is selected:

• The Tokenize Data Element Name text field is enabled.

  Enter the appropriate data element name.

• The Secure check box is selected and disabled from further changes.

  The remaining secure fields are still enabled.

Secure fields

Administrators and users with Administrator permissions to a data store can restrict access to data store fields using the Secure check box and environment groups. If a field is secured, only users within the selected environment group will be able to access it. If a field is left non-secure, anyone will be able to access it. Fields that are secured can also be masked, if they contain sensitive information like credit card numbers.

Secure field properties

• Hide this field when user does not have access - Users who have access to a Secure Field are those who belong to the environment groups chosen in the Secure Field's Environment Groups property. If this option is selected, and a user who is not part of the Secure Field's environment groups uses the data store, they will not see the Secure Field at all. If this option is not selected, all users will see the Secure Field, but its contents will always be masked.
• Mask - Determines how the Secure Field will be masked.
• Mask Pattern - If the User Defined Mask option is chosen, you can use Mask Pattern to define a preset or custom mask for the content of your Secure Field.
• Display Mask Pattern - If the User Defined Mask option is chosen, you can use Display Mask Pattern to define a preset or custom display mask for the content of your Secure Field.
• Environment Groups - Users in a Secure Field's selected environment groups will be able to see the field with its contents initially masked, as per Mask settings. Unlike other users, however, users within the selected environment groups have the option to unmask Secure Fields and see their actual contents. Unmasking can be performed throughout the product, at the individual record level or in bulk via Unmask All.If a user is not in a Secure Field's selected environment groups, they will either see the field as always masked or they will not see the field at all. This will depend on whether Hide this field when user does not have access is selected.

Permissions required to create or modify data store secure fields

Any user with access to a data store can make a data store field secure, if it has not yet been marked as secure.

Once a field has been marked as secure and the change has been Accepted, only Administrators and users with Administer or Manage Security permission to the data store will be able to modify the field's security settings.

Inheritance when used by other data stages

If a field is set as secure, all the security settings will carry over when the field is used in other data stages.

In the case of a data view, the secure flag's setting will be inherited and will not be able to be changed by anyone - unless it is changed in the data store.

When data view field security is inherited from a data store, Data360 DQ+ Administrators and users with Administer or Manage Security permission to the data view will be able to modify settings besides the secure flag. Other users will not be able to modify any of these settings.

Places where you will see the effects of secure fields

When you download the contents of a data store and view the file outside of Data360 DQ+

When you use the data store in an Analysis and view its contents in a sheet.

When viewing a secure field within a data store search screen. Here, you will also be given the option to unmask the value of a masked secure field, if you have been given permission by being placed in the secure field's environment group.

Dashboards and the visualizer. Here, some functionality will give you the option to unmask the value of a masked secure field, if you have been given permission by being placed in the secure field's environment group.

Code sets

You can use a code set to create a more user friendly display value for fields that contain non-human readable codes. Once a code set is created, its effects will be seen throughout the system, such as when the field's values are displayed in a search screen or a dashboard. Code sets can be either static or imported:

• Static - You can add values one at a time by clicking Add, or you can add multiple values by clicking Add Multiple.
• Imported - You can import values from a data store, and choose the relevant data store fields to use for the value and display name. Imported code sets cannot be edited directly, but you can ensure that the latest values are imported at any time by clicking Refresh.

Display Name - This is the value that will replace the raw value that is actually stored in the data store field, when the field is displayed throughout the system.

Value - This is the raw value that will be replaced by the display name. Typically, it will be a non-human readable code.

Code set example

Assuming a field that contains a set of three non-human readable codes, you could create a code set for each one, in order to produce a more user-friendly display.

Assuming you created the following:

• Display Name: Dental, Value: GHTY45RD
• Display Name: Medical, Value: JJAK09WQ
• Display Name: Home, Value: QWRE21PP

'Dental', ‘Medical', and 'Home' would be displayed for GHTY45RD, JJAK09WQ, and QWRE21PP (respectively) whenever these values were encountered.

Synonyms

When creating a data store field, synonyms allow you to add words to the field definition that will also be interpreted as the field's name, when the word is encountered as a field name in a data source.

When this part of a field definition is displayed, such as for Microsoft Excel or Delimited layout data stores, you may either directly specify alternate field names for the field as synonyms, or may look up synonymous field names in Data360 Govern. When synonym field names are defined, the system will handle inputs that specify these alternative field names, and provide the values from the alternate named fields when "Lenient Parsing" has been enabled for the data store. See Business Asset Type for Synonyms for a way to choose the business asset that defines synonymous terms for fields.

For example, if you had some files where an employeeId field was represented as employeeId and other files where employeeId was represented as id, you could name the data store field employeeId and add id as a synonym.

With id added as a synonym, source data with an employeeId column or an id column would be treated as if it belonged to the same column. In this example, this common column would be employeeId, since that is the name defined for the data store field.

As a result, data would reside in the data store as follows: