Long Queries (Long Queries) algorithm is a universal algorithm that can be used with any Portfolio object. It runs one big SQL statement including node conditions and actions. The logic is applied in the SELECT part, not in the WHERE part, of the SQL statement. There is a distinction between SET and UPDATE actions. SET actions initially run as part of the big SELECT statement. UPDATE actions run when the database is being updated, after SET actions have been already executed, and all transactions have been already assigned to the nodes.
Optimized_for_no_aggregations (Optimized for no aggregations) algorithm is designed for Portfolio objects with no aggregated conditions. It is similar to the Long Queries algorithm and has the same distinction between SET and UPDATE actions.
However, unlike the Long queries algorithm, it runs an SQL statement for each node, and SQL JOIN statements can be different for various nodes.
Optimized_for_few_aggregations (Optimized for few aggregations) algorithms is designed for Portfolio objects with less than five aggregated conditions. It produces results identical to those of the Optimized for no aggregations algorithm. This algorithm runs SQL DELETE and SELECT statements and runs all actions as UPDATE statements.
Optimized_for_many_aggregations (Optimized for many aggregations) algorithm is designed for Portfolio objects with more than five aggregated conditions. It is similar to the Optimized for a few aggregations algorithms, but it processes aggregated conditions differently. With this algorithm, for each aggregated condition, a temporary table is created and aggregations are calculated inside this table. After that, unmatched table records are deleted from the main temporary table.
Optimized_for_no_aggregations_and_simple_actions (Optimized for no aggregations and simple actions) algorithm is designed for Portfolio objects with no aggregated conditions and actions where SUBSELECT SQL statements are applied. This algorithm can also be used if a Portfolio object is fairly simple or if most of the queries are executed against indexed fields.Allow Multiple Nodes Per Record option button allows the same value from the DataModel instance to be used in more than one node in the Portfolio object, if ‘Yes’ is selected. The node_path column in the resulting Portfolio instance becomes part of the unique key. All algorithms except the Long queries one support this parameter. Make As Of Date column nullable option button selects the Allow Nulls checkbox, instructing the system to accept missing data in the Portfolio-result modified DataSource instance, if ‘Yes’ is selected. Resulting Data Source Instance Rebuild Restriction combo box is used to specify methods to rebuild resulting Portfolio DataSource instances to meet possible layout changes in the objects2. An instance must be rebuild in order to save the changes after executing the Portfolio task. Two instances of rebuild methods exist: “Alter” and “Full Rebuild”. “Alter” is a faster method that rebuilds an instance by running the ALTER TABLE SQL statement, which creates an Alter Table for processing the changes. This method is applicable, for example, when a new column is added to the instance table after the execution. “Full Rebuild” is a more complicated method that takes a longer period of time to rebuild the resulting Portfolio DataSource instance. It creates a temporary table and saves the existing instance data to this table. This method can replace the “Alter” method and is also applicable to other cases in which the “Alter” method fails, such as:
- A Portfolio instance has been moved to another DB source.
- The Data Storage Type parameter has been changed.
- Any instance key of a Portfolio instance with CONTINUOUS or CONTINUOUS_PARTITION Data Storage Type has been changed.
- Any column has been renamed.
Alter Only utilizes only the “Alter” method. Full Rebuild Only utilizes only the “Full Rebuild” method even in the cases in which the “Alter” method would be more preferable because of its simplicity. Alter, Full Rebuild tries the “Alter” method first, and if it fails, utilizes the “Full Rebuild” method.Compute Statistics On Results allows you to specify a method for calculating statistics in the database structure to facilitate the execution of the database queries. The combo box lists the following methods: NONE, FOR_ALL_COLUMNS, and FOR_KEY_COLUMNS.
- NONE does not calculate any database statistics.
- FOR_ALL_COLUMNS calculates database statistics for every field in the Data Table.
- FOR_KEY_COLUMNS calculates database statistics for only the Key fields.
1) Save the Portfolio object based on the DataModel object, with Translate Conditions on Levels set to ‘Yes’ on the Parameters tab.
2) Execute a task of this Portfolio object and set conditions for the Portfolio instance.
3) Click the Drill-down to the previous level toolbar button on the Portfolio Viewer screen to see the transferred condition records on the Data Model Viewer screen.
Note: If “Translate Conditions on Level” were set to ‘No’, the content of the underlying DataModel instance would be displayed without filtering.