The new Smart Import Object

Board 2022 Summer release introduces the new Smart Import Object, bridging the gap between Developers and end-users when it comes to loading data into Cubes or Entities. In previous versions of Board, only Developers could load data into Data models via Data readers, and sometimes their configurations were quite complex.

With the new Object, the data loading process is done from a Screen of a Capsule in Play mode, from a familiar Excel-like interface: every Board user with data entry privileges with at least a User license can load data with just a few clicks.

The Smart Import Object can be used for the initial data load after a complete Data model has been created, for updating data stored in Cubes, and/or for adding new members to existing Entities.

The Object provides two methods for loading data:

1. By entering it manually in the table: users can enter values just as they would in an Excel spreadsheet or they can copy and paste data using the CTRL+C and CTRL+V keyboard shortcuts

2. By uploading a .xlsx file: this method is particularly useful when the amount of data is so large that a manual data entry would be too difficult or when the user prefers to manage data on a local machine. An empty Excel template file can also be downloaded from the Object contextual menu (sliding toolbar)

The Smart Import Object is easily configured in Design mode with a simple two-step mapping process. It also supports additional options in the contextual right panel of the Screen editing page, such as custom validation rules, suggested values, and the execution of a Procedure when data entry is performed, as well as the usual formatting and graphic customization properties common to all Screen Objects.

Currently, data entry actions made via the Smart Import Object are not logged into the DataEntry log file.

Creating a Smart Import Object

Once you're in the Screen editing page, locate the Smart Import Object from the Screen Objects list in the left panel and drag and drop it onto your Screen or Container.

The next step is to set up the mapping configuration which will define how the external data should be imported into the Data model and mapped to Entities and Cubes.

To configure a mapping, proceed as follows:

1. On the table, click a column header and type the desired column name. Repeat the procedure for all the columns you want to make available to users in Play mode. Column names are required for the mapping process

2. When at least one column name has been defined, the CONFIGURE MAPPING button in the contextual right panel becomes active. Click on it to open the mapping configuration pop-up window. The mapping configuration pop-up window can also be opened by clicking on the Configure mapping icon (  ) from the the Object's contextual menu
   

3. Configure all 3 steps to import your data:

   1. Starting from the "Data model" section on the left, map Entities and Cubes to the previously configured columns by clicking on the "+" button and selecting the desired items from the sliding panel: when you add an Entity to the list, you can decide to map the member code (required) or description (optional) using the dropdown menu next to the Entity name.

      Adding an Entity description field will automatically add the Entity code field as well. When you add a Cube, you must add all its dimensions (Entities) as well.

   2. If needed, drag items (columns) in the "Source" section up and down so that they are next to the Entity or Cube they will feed. You can also click on the pencil icon that appears when hovering over each item to select the desired column and map it to the corresponding Entity or Cube on the left using the dropdown menu.

      The "Client filters" option in the dropdown menu is only available for Entities: select it to dynamically apply the user's custom filters (active Selections and Pager synchronizations) to the corresponding Entities in Play mode. When the option is enabled, no action can be selected in the "Rules" section. To clear an item in the "Source" section, click on the recycle bin icon that appears when you hover over the desired item.

      In addition to the source columns defined in step 1, in the dropdown menu you also have the following options:

      • Client filters. This option is only available for Entities: select it to dynamically apply the user's custom filters (active Selections and Pager synchronizations) to the corresponding Entities in Play mode. When the option is enabled, no action can be selected in the "Rules" section

      • Fixed value. This option allows you to write a custom fixed value to a target Cube or Entity code/description for each row of data submitted. The "Fixed value" field supports numbers and text strings: for obvious reasons, the configured value should be entered in accordance with the mapped Cube/Entity type.
        If the fixed value is a date or a number, it must be entered in invariant culture format (culture-insensitive): this culture is associated with the English language but not with any country/region. For example, the date format must be MM/dd/yyyy, while for numbers a comma (,) must be used to separate groups of thousands and a period (.) must be used to indicate the decimal place (e.g 1,000,000.00 → one million with two decimal digits)
        If the fixed value is a text string, it must be enclosed in double quotes. For example, "budget".
        As with the Client filters setting, this mapping configuration is used to automatically write values in the selected Data model destination. For this reason, it is not displayed as a column in Play mode and no validation rules can be set on this specific source.
        Fixed values are logged in the Smart Import log right after those submitted by the user

      • Formula. This option allows you to map the result of a formula to a target Cube or Entity code/description for each row of data submitted. You can use logical expressions in the "Formula" field, using the same syntax as algorithms. You can also use letters or the column name in square brackets preceded by an at sign (@) to refer to a specific mapped column. Example: [@Gross Sales]*2.
        Numbers in formulas must be entered in invariant culture format (culture-insensitive).
        The Formula field also supports Board's substitution formulas. If the selected substitution formula returns a text string or a date, it must be entered enclosed in double quotes. For example, the formula "@User abc" may return a result such as Paul abc.
        As with the Client filters and Fixed value settings, this mapping configuration is used to automatically write values in the selected Data model destination. For this reason, it is not displayed as a column in Play mode and no validation rules can be set on this specific source.
        Results of a formula are logged in the Smart Import log right after those submitted by the user

   3. In the "Rules" section, set the desired action for each row. This option controls whether external data will be loaded or discarded and how it will be managed by the system.
      The available actions are described in the table below.

      Action type	Description	Applicable to
      Add new members	When the value from the data source is not the code of an existing member of the Entity, a new member is created. This option can be used to populate Entities while also feeding a Cube at the same time	Entity code
      Discard new members	The incoming record is only loaded if the value found in this field is the code of an existing member of the Entity, otherwise the record is discarded together with the data relating to it. This option is used to filter out unwanted members and values	Entity code
      Auto incremental	Automatically creates the code for new Entity members incrementally when an empty code is loaded	Entity code
      Replace description	This option always overwrites the description of Entity members with the incoming one, unless the new description is empty: in this case the existing one is retained	Entity description
      Add	If the target Cube is numeric, the incoming value from the data source is added to the Cube. If the Cube cell already contains a value, the new value is summed to the existing one. If target Cube is not a numeric Cube (i.e. a date Cube, a text Cube, etc.) the incoming value always overwrites the existing one	Cube
      Replace	This action clears the Cube cells considering the active selection and then loads incoming values into the Cube	Cube
      Merge	This action clears the target cell in the Cube and loads the incoming value. If the data source contains multiple values for the same combination, those values are added together, the target cell in the Cube is cleared, and the result is loaded	Cube

      The Discard outside selection checkbox is only available for Entity code fields. Select it to discard incoming members that are not in the active selection during the data entry process in Play mode.

      Global security settings and Cube visibility rules are automatically applied during the data entry process in Play mode.

      Example
      

4. Click on SAVE to save and close the mapping configuration.

The Smart Import Object includes additional properties and settings that can be applied to incoming data. See next paragraph for more detail.

Smart Import properties and settings

The new Object has its own specific settings and additional properties. It also supports the new Object toolbar and all options common to all Screen Objects, except for the Master object option and the Disable Drill down option.

Properties specific to the new Smart Import Object are highlighted in the following image:

Validation rules

Under the Validation rules menu in the contextual right panel of the Screen editing page, you can set up global validation rules in order to automatically accept or reject entered values. This allows, for example, to accept the input value only if it is positive (in case of a budget price), or only if it is lesser or greater than a given value or a value from another cell of the same row.

A validation rule is a logical expression, which checks whether the condition for the entered value is TRUE or FALSE: if the expression returns TRUE then the rule is met and the entered value is accepted; if the expression returns FALSE the entered value is not accepted, the row or cell is highlighted, and a a custom error message is displayed in a dedicated column that appears in the rightmost position in the table. In case the data loading process is done via an .xlsx file, errors will be displayed in a dialog.

Click on the "ADD VALIDATION RULES" blue button to bring up the configuration window: in it you can configure multiple validation rules that refer to mapped columns of the Object, just like when configuring validation rules for a block in a Layout.

Each validation rule is configured by filling out the following two fields:

• Valid when: the condition (formula) whose result can be TRUE or FALSE.

• Else show: the error message

Validation rules can only be set when the mapping is complete. Logical expressions in the "Valid when" field use the same syntax as algorithms. You can use letters or the column name in square brackets preceded by an at sign (@) to refer to a specific mapped column. Examples: B<0 LEFT(A,1)="3" LEFT([@Product],1)="6"

Suggested values

Under the Suggested values menu in the contextual right panel of the Screen editing page, you can configure suggested values that will be shown to the user during data entry actions. These values are taken from an Entity or a Cube in the Data model, or from a manually configured Custom List.

Click on the "SUGGESTED VALUES" menu to open the configuration interface: in it you can configure a suggested values source for each mapped column of the Object, just like when configuring suggested values for a block in a Layout. Once you have completed the configuration for each column, click on the "ADD SUGGESTED VALUES" blue button to save it.

Suggested values can only be set when the mapping is complete. In case of suggested values taken from an Entity or a Custom list, the user will be presented with multiple suggested values for a single data entry action. The list supports vertical scrolling and its elements are filtered as the user writes in the cell.

Other properties

In the contextual right panel of the Screen editing page the following additional properties are available.

• Data model. This option is available only if the "Multiple Data model" mode is enabled on the Screen. Click on the dropdown menu to choose the Data model you want to associate with the Smart Import Object. Selecting a new Data model will clear the existing mapping configuration

• File upload. If enabled, allows the user to upload a .xlsx data file. The option also allows the user to download and empty Excel template from the Object contextual menu (sliding toolbar)

• Coordinates. If enabled, column letters and row numbers are displayed

• Scrollbars. If enabled, vertical and horizontal scrollbars are displayed

• Hide error column. If enabled, the error column in the table is hidden

• Number of rows. You can manually define how many rows should be available in the table (maximum value: 10,000)

• Number of columns. You can manually define how many columns should be available in the table (maximum value: 200)

• Column. This option allows you to choose which columns are affected by the Column width setting

• Column width. You can manually define the width of the columns selected in the Column dropdown menu.

  You can also resize columns by placing the mouse pointer on the boundary line between column headers and dragging the double-headed arrow to widen or to make the column narrower.

• Under the Headers colors, menu you can define a custom background color and custom text color for each or all column headers

• Under the Set trigger menu, you can select a Procedure which will be automatically triggered immediately after the data entry action is performed

Smart Import Log

Board collects Smart Import data entry events (every single row processed) in the Smart Import log. This new log is enabled by default and can be disabled in the "Log settings" page under the System Administration section.
Each log line always includes the following information:

• Timestamp

• User

• Data model

• Capsule path

• Screen name/id

• Records (single fields, comma separated)

• OK for successful commits and KO for unsuccessful and partially unsuccessful commits

Log files are rolled at the end of each month or when they reach a file size of 50 Mb, whichever comes first. Log files are never deleted.

Interacting with the Smart Import Object

The new Smart Import Object offers an Excel-like user experience both in Design and in Play mode.

You can select a cell or a range of cells and paste data in the selection. You can also enter data manually, either by double-clicking on a cell and typing the desired value or by selecting a single cell and typing data.

The Object also supports formulas and functions to make complex and useful calculations before loading the data Cubes/Entities: formulas and functions always begin with the equal sign "=" and are built using the same syntax and operators used in Microsoft Excel.

To enter a formula that contains a function, type the equal sign and then enter a function, for example "=SUM". After the function, type an open parenthesis, enter/select a range of cells, and type a closed parenthesis. For example, the function "=SUM(A1:A12)" will return the total obtained by adding all values in the selected cell range.

After you have entered the desired values and formulas, press the "Run data entry" icon in the Object contextual menu (sliding toolbar) to actually save the values in the Cube.

If global validation rules and/or custom validation rules are not met, the data loading process stops and an error dialog box and/or the error column will display the necessary explanatory messages.

If global validation rules and/or custom validation rules are met, the external data is saved in the target Data model as per the defined mapping and the table is cleared.

Empty rows and columns are not considered during the data loading process.

File upload

The file upload feature allows you to load data by uploading a .xlsx file. This is particularly useful when your dataset exceeds the maximum value of rows (10,000) and columns (200) available in the Object itself, as the feature allows you to upload a .xlsx file with no limitations on rows and columns. You can also download an empty Excel template file from the Object by clicking on the download template icon (): fill in the desired data and upload it back to quickly enter data in bulk.

To upload a .xlsx data file, click on the file upload icon () from the Object contextual menu (sliding toolbar) and drag/choose the desired file from your machine.

If global validation rules and/or custom validation rules are not met, the data loading process stops and an error dialog box will display the necessary explanatory messages.

If global validation rules and/or custom validation rules are met, the loaded data is saved in the target Data model as per the defined mapping.

Empty rows and columns are not considered during the data loading process. Only the active tab of the uploaded worksheet is considered during the data loading process.

The Contextual menu (sliding toolbar)

When you hover over the Object, the contextual menu icon () appears in the top left corner. Click on it to reveal the contextual menu as a sliding toolbar. You can fully expand it by clicking on the downward facing arrow on the right. If the new "Always visible" setting is enabled on the contextual menu, all icons described below will be displayed above the Object.

The contextual menu includes the following icons by default (unless otherwise configured):

• Run Data Entry icon . Saves manually entered values

• Clear content icon . Clears manually entered values in the table

• Configure mapping icon . Opens the mapping configuration pop-up window (available only in Design mode)

• Download template file. Downloads an empty Excel template file based on the Object table

• Upload file . Opens the file upload dialog

• Comments icon . Displays any comment attached to the Object (if any). Depending on the comment configuration, the icon displayed can be a balloon icon, a question mark icon or an alert icon