Dynamic SQL Views supports the creation and use of freely entered SQL statements executed on a specific database connection that can be used as underlying data-sources in Dynamic AI.
Example: If a database includes an order table and a customer table, a Dynamic View could be used to create a Customer_orders_view based on the following SQL statement:
Select * from ORDERS O inner join CUSTOMERS C ON O.CustID=C.CustID
The SQL language rules must comply with the SQL standards imposed by the underlying connection.
Dynamic Views has the following advantages:
Support for Free SQL statements including joins, unions,
sub-selects etc.
All the advantages that a normal database view
or query includes in relation to the creation of computed columns, case
statements etc.
No impact on back-end database definitions, as
the view definition is stored only in Dynamic AI
The possibility to create views without having
permissions or tools installed to access and modify back-end databases
Re-use views (depending on SQL language used) across different connection types. A view defined and executed on a SQL Server in development can later on be executed on e.g. a Oracle Database Server in production. Only the connection type must be changed centrally.
Capture Free input parameters from end-user
before execution on back-end
Dynamic partitions can be used to allow end-users to select which data-partition on which the view should execute.
Dynamic SQL Views supports Free parameters, which is the opportunity to capture input from end-users and merge / resolve the input into the Dynamic SQL View SQL Statement, before executing the SQL statement. Consider the below example:
Select * from @PAR1@ O inner join CUSTOMERS C ON O.CustID=C.CustID
Dynamic AI will detect that @PARn@ parameters have been used and automatically append the parameters as FREE input parameters to the dictionary definition for the Dynamic View.
Clicking the @PAR@ button during creation of the Dynamic View, default values and captions for the Free parameters can be entered and maintained.
At execution time (whenever the Dynamic View needs to be executed on the database) input controls will appear on the Report allowing the end-user to enter values to be used as parameters. Using above example, @PAR1 could be used as a variable for the table name that is used as part of the join to the Customer table. Depending on the underlying data-model, the above example could be executed with the following parameters:
@PAR1@ = ORDERS
Select * from ORDERS O inner join CUSTOMERS C ON O.CustID=C.CustID
@PAR1@ = SALE
Select * from SALE O inner join CUSTOMERS C ON O.CustID=C.CustID
Be aware that the above examples primarily illustrate the functionality of Free parameters.
Dynamic SQL Views used in combination with the Dictionary and the definition of partitioning gives a number of opportunities.
Views can be created with variable base table references controlled by an external table, from which specific table names etc. are being parsed to the Dynamic SQL View as partition parameters at execution time.
Example:
Select * from @DYNPARTITION@ O inner join CUSTOMERS C ON O.CustID=C.CustID
Assuming that Orders are stored in a new table every business day, so that the tables e.g. would be named:
ORDERSJAN15
ORDERSJAN16
ORDERSJUL04
Etc.
The partition definition can be used to replace the @DYNPARTITION@ variable of the Dynamic SQL View with ORDERSJAN15, ORDERSJAN16 etc. depending on end-user selection.
The benefit is that one central Dynamic SQL View definition can be used as the basis for all sorts of Reports all available for any number of underlying data-tables.
Please refer to the “Data Partition definitions” section for further details on partition definition and use.
Use Dynamic AI SQL View's (DAIVIEWS) to prepare data for reporting when you need to JOIN, UNION or in other ways investigate or manipulate underlying database tables or views.
As a general rule - you shouldn't have to use DAIVIEWS to sort (ORDER BY), group (GROUP BY) or aggregate (SUM, COUNT, AVERAGE) data.
Sorting, grouping and aggregations are dynamically implemented on Dynamic AI reports and listings.
Dynamic AI reports also provide several ways to FILTER (both required and optional at execution time) your data as part of report execution.
Reports also provides the functionality needed to set fixed report CONDITIONS, that can only be changed by the report OWNER. Report CONDITIONS and FILTERS means that you should only have to create WHERE statements in your DAIVIEWS as part of JOINS or lookup functions, in SUBSELECTS and UNIONS.
As a general rule - you shouldn't have to set captions (e.g. CompName AS "Company Name") in your DYNVIEWS. The reports as well as the Dynamic Dictionary give you the flexibility to set and maintain column and field captions with optional language detection as well.
Tip: Use User/Dictionary Roles to restrict access to specific data-columns (COLUMN restrictions) of tables/views and DYNVIEWS.
Tip: Use User/Dictionary Aliases to restrict access to specific segments of records (ROW restrictions) of tables/views and DYNVIEWS.
Tip: Use report level SQL variables to add Computed columns to your reports. Computed columns acts similar to adding additional columns to a TABLE/VIEW by using a DYNVIEW but has the benefit that the Computed Column will only execute upon use - and will be more efficient and flexible than if the column is defined as a static part of a DYNVIEW.
DYNVIEWS provides special Dynamic AI functionality and must be used to get the benefit of:
Free Parameters
Redirected FILTERS
Partitioning
Special @alias filtering
Syntax: @PARn@
Example:@PAR1@
Syntax: @FILTER_T:columnname@
or
@FILTER_F:columnname@
Example:@FILTER_T:EmployeeNumber@
Syntax: @DYNPARTITION@
Example:@DYNPARTITION@
Syntax: @aliasn
Example:@alias1
You can create a new view: A. From the Explore menu "New/Dynamic SQL View", B. With the "Build View" function (in the drop-down) on a specific table or view, or C. Based on a copy of a previously saved DYNVIEW, from the Explore menu or from the Tools drop-down on a report based on a DYNVIEW.
Several New or existing DYNVIEWS can be opened at the same time in multiple Windows or Tabs. Use the drop-down or "Right Click" to open a new Tab/Window. Optionally use multiple DYNVIEWS to build, test and run individual parts of you DYNVIEW SQL statement.
Use the Run button to execute the total SQL statement entered OR select and mark part of the statement for execution.
The returned records are unformatted - i.e. unlike a Dynamic Report or listing, the values are shown as received (for investigation purposes) from the database.
Use the Columns button to retrieve columns from the results of the SQL statement.
Example: SELECT * FROM COMPANIES
Will e.g. return the columns: CompanyNumber, CompanyName, CompanyAddress, CompanyTown etc.
TIP: Just type/select the TABLE/VIEW object to achieve the same result.
Example: COMPANIES
Will e.g. return the columns: CompanyNumber, CompanyName, CompanyAddress, CompanyTown etc.
When a table/object selection is detected (e.g. "Companies"), the returned columns are prefixed with the first letter of the object name ("E.g.: C.Companyname"). The use of table/view synomyms can help making the SQL statement simpler to investigate as well as ease maintenance.
Colors will indicate the data-types of returned columns for information.
Use standard Copy/Paste or drag/drop to insert selected columns into the SQL Statement.
Use the Format button to return formatted SQL for easy reading and investigations. Detected keywords will be displayed Bold and together with actual execution tests, helps validating the SQL statement for the DYNVIEW.
As illustrated above a Dynamic SQL View allows for free-format SQL execution. A developer having access to modify or create Dynamic Views will have the possibility to create SQL statements that could exceed the permissions intended.
The possibilities of security violations depend on the capabilities of the back-end database provider as well as the permissions of the user account used on the specific database connection definition.
In general, Dynamic SQL Views has a number of benefits. The security considerations referred to above are theoretical and will only be relevant if security rules are not kept strict on the back-end database.
It is possible to insert comments enclosed in /* and */ tags in Dynamic AI View source.
Comments are not by default allowed by all underlying data providers therefore Dynamic AI now provides generic comment functionality.
Information enclosed within “/*” and “*/ “tags are stripped before execution and will not appear in “Show SQL” or in the DNMCLOG table.
/* this is an example of a comment */
RightID’s are now included for information on “Explore connections” – “Dynamic AI Views” listings.