Virtual properties are generated by performing calculations of existing properties through SQL expressions. SolarEngine supports both Virtual Event Property and Virtual User Property.

Through virtual properties, you can perform the following operations:

1) Numeric Calculations

Generate a new property by performing calculations on several numeric properties, for example, Discount Rate= (Previous Price-Present Price)/Previous Price.

2) Content Extraction

Generate a new property by extracting specific content from a string property, such as the ID value from a URL.

3) Property Merging

Generate a new property by combining similar properties into one, such as merging GPID and GID into group_id.

4) Date Formatting

Generate a new property by formatting a timestamp (hh:mm:ss) into a date (Monday, yy/mm/dd).

Create Virtual Event Property

       Entry: Select one of the products - Data Management - Metadata - Event Property - Create Property

• Specify Events

   Only the specified event(s) will be associated with the virtual property.

• Data Type

   The data type of virtual properties will determine their calculation operations available in the analysis model.

• Event Property Array

   The right-side event property array displays all properties shared by the specified events. When editing the SQL expression, you can click the property name to add it quickly.

Create Virtual User Property

Entry: Select one of the products - Data Management - Metadata - User Property

Virtual user properties are not associated with events, so there is no option to specify events.

The rest settings are basically the same as virtual event properties.

SQL expressions

       1) SQL expressions only support functions and syntax related to ClickHouse 21.8 and below.

       2) Before the referenced event/user property, add its table name "events." or "users." as the prefix. For example, if the property "vip_level" needs to be referenced, enter "events.vip_level" or "users.vip_level".

       3) SQL expressions only support "sql as" standard syntax. For example, "select xxx as 'abc' from events" means that SQL expressions only support "xxx" parts.

       4) One SQL expression should quote at least one existing property.

       5) Only shared properties of associated events are supported.

Result Verification

       1) The SQL expression is checked first.

       2) If the SQL expression is correctly written, some real data will be extracted from the database for result verification.

       3) The data type of the calculated result should accord with the specified data type. Otherwise, the validation would fail and the virtual property cannot be saved.

       4) The result needs to be re-validated every time the settings are modified. Only when the result passes the validation can the virtual property be successfully created.

The Calculation Logic of Virtual Properties

The value of virtual properties can only be calculated by performing operations on properties within the same data record or between properties and constants. Calculations across multiple data records are not allowed.

Only when the referenced properties appear complete within the same data record, the SQL expression can calculate the value of virtual properties.

In analysis models, virtual event properties only appear after associated events. If none of its associated events are selected, it will not appear at all.

The Application of Virtual Properties

All created virtual properties will be added to the property list in "Event Property" or "User Property". Their property type will show "virtual". Clicking on the "SQL" icon behind the Property ID will display its SQL expression. You can also modify its SQL expression and the other settings flexibly by clicking on "Edit".

In analysis models, you can select the created virtual properties after selecting its associated events, and then operate calculations based on their data type. There will be a "virtual" icon behind them in order to distinguish them from the other properties.

The operations would be the same when it involves the selection of virtual user properties.

In analysis models, just like the other event properties and user properties, virtual event properties and virtual user properties can also be used as filtering conditions and for grouping the display results.