Excel workbooks as dictionaries

Introduction

Dictionaries are commonly used to implement various types of customizations in the Insight platform: They can be used to do efficient string lookups, or be used as a mechanism for mapping strings to other strings. Exactly how the lookup operation should work might vary across applications, and many different types of dictionary lookup operations are supported.

In order to allow for a convenient data management mechanism for customers and to offer simple offline editing of dictionary data using tools that most customers already have, dictionaries can be supplied as Excel workbooks. Uploaded Excel workbooks are parsed and processed, and automatically converted into an internal representation that is suitable for the specified purpose.

This page describes the format that the Excel workbooks must adhere to if they are to be successfully imported into the Insight platform.

Concepts

 Some basic concepts when working with dictionaries and Excel workbooks:

• Each dictionary gets assigned a unique dictionary identifier by the platform. Services that make use of dictionaries must be configured to reference the relevant dictionary identifiers. This decoupling between the dictionaries and the clients of the dictionaries allows both easy reconfiguring of services, and sharing of dictionary resources across services.

• An Excel workbook might contain multiple worksheets. Although each worksheet constitutes an individual dictionary that can be separately configured, the set of all worksheets in a workbook can be thought of as forming a single, compound dictionary. Thus, a dictionary identifier is assigned to the workbook as a whole. For workbooks that contain multiple worksheets, all worksheets are normally consulted when doing lookups. Hence, one way to think of this is that we do parallel lookups across all worksheets, and report back the union of matches.

• A worksheet is either a property worksheet or a data worksheet. A data worksheet contains the actual dictionary entries. A property worksheet is meta data and defines how the entries in a data worksheet should be processed and used for lookup operations.

• A given property worksheet is associated with a given data worksheet via a naming convention. A property worksheet can also be said to be global (again controlled by a naming convention), in which case the global property worksheet applies to all data worksheets that don't have a specific property worksheet defined.

• A data worksheet contains the actual dictionary entries, one entry per row. Each row consists of a key and zero or more values. The key is the actual entry (i.e., what we match against the argument to the lookup operation) and the values are meta data associated with the key (i.e., what we map the key to if we're using the dictionary for mapping purposes.)

Workbook structure

An Excel workbook must to adhere to the following format:

• Worksheets must have names that match the regular expression pattern ^@@\\p{L}(\\p{L}|\\d)*(-(\\p{L}|\\d)+)*$

  • start with @@, e.g., @@foo-stuff

  • next lead by at least one letter, followed by a mix of letters (\\p{L}), digits (\\d), which can be structured by single dashes (-)

  • must have 30 characters or less

• Worksheets that have names that end with -properties are interpreted as being property worksheets, otherwise worksheets are interpreted as being data worksheets. Linking happens via a simple naming convention, e.g., the property worksheet @@foo-stuff-properties applies to the data worksheet @@foo-stuff.

• The optional worksheet named @@global-properties is interpreted as being a global property sheet.

Within each worksheet, the formatting rules are as follows:

• The worksheet should not have a special header row. If it does, this row will be treated as an ordinary dictionary entry. 

• Blank rows are ignored.

• Within a row, blank cells are ignored.

• If a cell value starts with //, that cell and all subsequent cells on the same row are ignored. I.e., the // prefix can be used for comments.

• Each row having non-blank (or non-ignored) cells translates into one dictionary entry.

• The first non-blank cell in a row is the dictionary entry's key, i.e., the piece of data we will be using for matching.

• The following non-blank cells (if any) define the values associated with the key, i.e., meta data.

• A worksheet must not contain duplicate keys. Checks for duplicate keys are applied after strings have been normalized per the specifications in the associated property worksheet.

Property worksheets

A property worksheet defines meta data for a data worksheet, i.e., it specifies how the entries in the data worksheet should be normalized and matched against. The properties in a property worksheet are simple key/value pairs.

Basic properties

The most commonly used properties are:

• tokenizer-context
  Defines the language of the entries in the data worksheet, typically expressed as an ISO 639-1 code. Required for some languages so that, e.g., tokenization and other linguistic operations will work as expected.

• mode
  Defines how the dictionary's keys should be matched against the input lookup argument. Note that different lookup modes have different scaling and performance characteristics. Currently supported lookup modes include:

  • complete
    The complete input string has to match a dictionary entry, i.e., we do exact matches. E.g., the input food processor triggers a match for the entry food processor.

  • prefix
    The input string is a prefix of one or more dictionary entries, i.e., we do plain prefix matching. E.g., the input foo triggers a match for the entry food processor.

  • overlap
    We find occurrences within the input text that are also present in the dictionary, i.e., we find the overlap or intersection between the input and the dictionary. E.g., the input the snazziest food processor in los angeles triggers matches for the entries food processor and los angeles.

  • didyoumean
    The input string is treated as a query string that is to be spellchecked against the dictionary. E.g., the input sppelling mistae triggers a match for spelling mistake.

  • phrase
    The input string is an infix of one or more dictionary entries, i.e., we are doing a general phrase search. E.g., the input spears triggers a match for the entry britney spears toxic.

  • ranked
    "Enough" of the terms in the input string must occur in the matching dictionary entries, i.e., we are doing N-of-M matching over an inverted index and rank the matches according to their natural relevance. Note that 1-of-M equals an OR search while M-of-M equals an AND search.

  • ngram
    Similar to the ranked mode, but operates on the level of shingles (i.e., small n-gram string fragments) instead of query terms. Generates and ranks approximate matches.

  • head
    Similar to the overlap mode, but anchors the matches to the start of the input. I.e., we only consider matches that start at the beginning of the input string. Only dictionary entries that end on token boundaries are considered.

  • tail
    Similar to the overlap mode, but anchors the matches to the end of the input. I.e., we only consider matches that end at the end of the input string. Only dictionary entries that start on token boundaries are considered.

  • keystroke
    A prefix matching mode geared towards Japanese keystroke-aware query completion. See also here.

• key-normalization-flags
  Defines how, if at all, the dictionary's keys should be normalized. E.g., if the worksheet contains Food Processor we can normalize that to food processor. Note that normalization flags for keys impacts the matching, e.g., if the matching should happen case-sensitively or not. The normalization flags defined through this property define a bitmask, so options can be combined by adding together multiple flag values. Currently supported normalization values include:

  • 0
    None. E.g., reSUMé is retained as reSUMé.

  • 4
    Case normalization. E.g., somETHing is normalized to something.

  • 8
    Accent normalization. E.g., André Previn is normalized to Andre Previn.

  • 64
    URL decoding. E.g., foo+%42ar is normalized to foo Bar.

  • 128
    URL normalization. E.g., http://foo.com/bar#blah is normalized to foo.com/bar.

  • 256
    Normalization specifically designed for Hindi and other Indic languages.

  • 512
    Normalization of accents and diacritics used in Scandinavian languages.

  • 1024
    Normalization of accents and diacritics specifically designed for Arabic.

  • 2048
    Case normalization specifically designed for Turkish and Azerbaijani.

  • 4096
    Normalization specifically designed for Japanese. Does Katakana to Hiragana transliteration.

• value-normalization-flags
  Defines how, if at all, the dictionary's values should be normalized. Similar to key-normalization-flags, but applies to the strings listed as meta data associated with the dictionary entries.

• swap-fields
  Defines what should be reported back to the client when we get a dictionary match. If set to true, we emit value/key pairs instead of key/value pairs. This is often useful for normalization or categorization purposes. E.g., if we match the key german shepherd we can report back the associated value dog as the match instead of the matching key german shepherd. Usage of this option implies that all entries in the worksheet should contain at least two columns. In cases where only some of the entries should be reported back as something else, the entries that should not be reported back as something else should have the same value in both columns. Using /processing/dictionary/search against dictionaries with this field configured is not supported.

Advanced properties

For some values of mode, additional commonly specified properties are listed below. These options can also be used together with other mode values, if relevant.

• count
  For some applications and values of mode, it might be desirable to impose an all-or-nothing filtering of the matches, based on the number of matches produced. This enables one to express that "enough" matches have to be returned or else all matches should be suppressed. This might be desirable in, e.g., categorization applications in order to improve on precision. What constitutes "enough" matches can be controlled here. We can impose count constraints both on raw counts as well as the number of unique match values. E.g., we can require that for anything to be reported back we would have to see at least 3 matches whereof at least 2 of these have to be unique.

• unique-count
  Similar to count. Whereas count imposes a constraint on raw counts, this property imposes an additional constraint on the number of unique values.

• count-field
  Defines how to interpret the count thresholds above, if count filtering is in effect. E.g., we could consider the result set as a whole or impose "group-by" logic to have the counts interpreted based on a grouping of the values in a given field. Currently supported values include:

  • none
    No grouping. I.e., counts are interpreted without regard to any specific field in the result set.

  • value
    Impose group-by logic over the match values in the result set. See also the swap-fields property.

  • extra
    Impose group-by logic over the match meta data values in the result set. See also the swap-fields property.

• expand-paths
  If the dictionary's values are taxonomy paths (separated by a forward slash "/"), then with this option set to false you would in some applications have to manually expand the paths to include all prefixes (and deduplicate these) in order to, e.g., get correct roll-up counts in Piano Insight Analytics. This gives you more control, but might also make your worksheets more complex than need be. With this option set to true you would only have to list the most specific/detailed paths, and all path prefixes will be automatically generated (and deduplicated) for you.

• annotate-paths
  If the dictionary's values are taxonomy paths (separated by a forward slash "/"), setting this option to true implies that the worksheet name (excluding the @@ prefix) would be injected as the top category in the taxonomy.

• group-prefix
  If this option is initialized with the Customer Prefix for the user, the workbook is considered a user interest taxonomy. User interests are further described here.

• lemmatize
  Default false. If true, matching takes place modulo linguistically normalized forms. E.g., for Norwegian, the dictionary entry røde biler would match the input rød bil. Currently not supported for English or Urdu. As the keys are normalized according to the linguistic configuration for the given language, key-normalization-flags should not be defined when this option is true.

Very advanced properties

Additional advanced properties are summarized below. These are often mode specific and used to configure details related to the dictionary lookup process.

• candidates
  Controls the maximum number of matches to emit back to the client. Might be ignored for some values of mode.

• sort-candidates
  For prefix and phrase modes, we might want to sort of the set of candidates before we emit them. If true, emitted keys are sorted in descending order according to the numerical value of the first value associated with the key (assumed to be an integer.)

• emit-exact-match
  For modes that have some notion of approximate matching. If true, only exact/complete matches will be emitted if such matches exist. I.e., no approximate matches will be computed if an exact/complete match exists.

• shingle-width
  For modes that rely on using some kind of shingle-based matching. Controls the shingle width, i.e., determines the value of n in n-gram. Low values provides higher recall but lower precision.

• recall-threshold
  For modes that rely on using an inverted index for doing at-least-N-out-of-M lookups, where M is the number of terms in the input query and N is the number of matching terms. Sets a threshold on the required ratio N/M, i.e., defines the minimum percentage of unique query terms that have to be present in a dictionary entry for it to be processed and ranked. Note that N=M implies AND-semantics, whereas N=1 implies OR-semantics.

• rank-threshold
  For modes that rely on using an inverted index for doing ranked lookups, sets a threshold on the required rank score. I.e., matches having rank scores lower than this are discarded.

• use-delimiters
  For modes that rely on using some kind of shingle-based matching. If true, special delimiters are implicitly applied to the start and end of each dictionary entry. This improves recall on the cost of performance.

• frequency-threshold
  For didyoumean mode. Dictionary entries having frequencies below this level will be disregarded as candidates, if frequency weighting is used. Frequency data, if provided, is assumed encoded as the first value associated with each dictionary key (assumed to be an integer.) If left unspecified, a suitable frequency threshold will be dynamically computed.

• distance-exponent
  For didyoumean mode. The initial set of candidates retrieved using shingle-based matching are post-processed and re-ranked using edit distance. This value controls how much emphasis is placed on edit distance versus the frequency for the given candidate. If left unspecified, a suitable distance exponent will be dynamically computed.

• distance-threshold
  For didyoumean mode. Controls the maximum allowed edit distance between a candidate and a dictionary entry. Can be used to control precision.

• identity-match
  Some applications might benefit from always emitting the original input as an artificial match in addition to the computed matches, if any. E.g., with this option set to true, a value for mode that for the input foo normally would emit the single match food processor would instead emit the two matches foo and food processor.

• leftmost-longest-match
  For some values of mode, if the dictionary contains entries that subsume or overlap each other, we can choose if we want to emit all matches or just the ones that are found through greedy leftmost-longest matching. E.g., for overlap mode, if your dictionary contains both entries chicago and chicago symphony orchestra and your input is chicago symphony orchestra, then with this option set to true we would suppress the match chicago since it is subsumed by the longer and more specific entry chicago symphony orchestra.

• allow-complete-match
  For head and tail modes. Complete matches are a valid special case that we may or may not want to suppress.

• emit-contextual-match
  For didyoumean mode. If true, we try to do simple contextual spellchecking by looking at surrounding words. Requires a dictionary that contains phrases, and has a performance cost.

• start-marker
  For didyoumean mode. The start of the sections of the query that changed are marked up with this value. A GUI can then choose to render those sections in some visually distinct way. E.g., the query the rollig stones worldd tour might be emitted as the <changed>rolling</changed> stones <changed>world</changed> tour.

• end-marker
  For didyoumean mode. See start-marker.

Data worksheets

A data worksheet defines the actual dictionary data, i.e., strings that you can actually match against. All worksheets that aren't identified as being property worksheets are data worksheets.

An entry in a data worksheet is a key/value pair. The key is what is used for the matching. The value is a list (possibly empty) of meta data associated with the key. See the section on workbook structure above for formatting rules for a row.

Last updated: June 25, 2026