Conceptual Dynamic AI, Security and Scalability Eddy Kleinjan, Data Access Europe

1. Conceptual Dynamic AI, Security and Scalability Eddy Kleinjan, Data Access Europe

2. Keep it simple... Serve yourself! Compatible ... Fast access to relevant information! Generic and Flexibility iterative ... in unknown scenarios Best practices functionality ... Effective! Makes you look good! The concept behind creating Dynamic AI….

4. Dynamic AI ”layers” 5 ai 1 Small SQL request Small user request HTML rendering Script execution Keyboard and mouse handling 4 database 6 Dynamically Generate SQL Request 7 8 Aggregated dataset Dynamically format dataset Aggregated and formatted HTML 2 3 Request User rights + eventually Object elements Permissions + Object Elements DAI repository

5. Steps to implement Dynamic AI • Create Database Connection • Create Database View or Dynamic View • Create Data Dictionary • Define Relationships between Data Sources • Create Collections • Aggregated Data • Lists • Forms

6. Remark Object type Level of re-use Step Central Connection definition (1 per database) - Offers defaults for stylesheets, global read only control, global customize preview definitions Available from Explore connections 1 2 3 4 8... 6,7... 5 Central definition is being re-used for all underlying objects Dynamic View definition (a few to represent underlying tables/views) - Virtual name that allows extension control from within Dynamic AI. Offers sophisticated partion variables as well as non-data bound parameter input before execution. Available from Explore Connections. Modify existing via ”Explore my rights” Dynamic View ID Central definition is being re-used for all underlying objects Used where fieldnames=captions and as default definitions for SELECT type filters. Forced order by will automatically be included on detail list level sorting. User alias filtering will automatically be additionally applied to selects. Dictionary definition (1 per data-source) - Offers global definition for @alias security filtering, global defined forced order by, global COMBO box definitions, global caption definitions 1 n Central definition is being re-used for all underlying objects n n Relationship definition (several in-between data-sources) - Offers universal definition of related fields across Dynamic AI datasources Available on collections as Drill-down to targets. Available as condition for inserted subforms on forms. Avaialble from right side menu on forms for on-the-fly drill-through. n n n n Central definition is being re-used for all underlying objects Report Collections - Aggregated level definition (Multiple as required to offer immidiate access to relevant report and analysis dimensions) Drill-down on related fields or inherit filters on matching fields After the first aggregated report has been defined multiple reports can be created re-using the already defined use of styles, filters, sum variables, drill-down defintions, access rights etc. Pop-up in own window or Double-click to maximize frames List Collections - Detail record level definition (a few in order to display most relevant details) A few lists can act as the lower level details for multiple higher level aggregations ”Back to list button” offers re-use of the already retrieved list of records for navigating to other records Drill-down on ID Form Collections - Detail record level definition (1 per data-source) One core form layout can be used as the drill-down destination from several detail listings

7. Central Connection definition (1 per database) - Offers defaults for stylesheets, global read only control, global customize preview definitions Read only on all collections under this connection ID dispite user rights etc. Stylesheet defaults

8. Dynamic View definition (a few to represent underlying tables/views) - Virtual name that allows extension control from within Dynamic AI. Offers sophisticated partionvariables as well as non-data bound parameter input before execution. This button will appear after the view has been inserted the first time. Use this button to see your SQL view code with inserted sample values (for Parameter and Partition variables only) This button will appear after the view has been inserted the first time. Use the button to check that your SQL view definition returns a valid set of records. Examples of view definitions

9. Dictionary records will appear on the Dictionary maintenance screen under the appropriate connection ID. Dictionary records (1 per data column) are added throgh the Explore Connection ”Update Dictionary” option. Dynamic Views will be represented with an internal number (drill-down to see the columns....). Dictionary definition (1 per data-source) - Offers global definition for @alias security filtering, globally defined forced order by, global COMBO box definitions, universal caption definitions Additional row added for use with COMBORID reference to a collection specifying partitions or free SQL statements that will be inserted based on user selection from a COMBO, into the Dynamic VIEW SQL Statement, before execution. Language 1,2,3 that shows up as captions depending on the language selected on the user account and available through the user profile. The corresponding HLPTEXTLNG1-3 is for yellow box help descriptions when users hold the mouse over data column captions. Use this column to check the column that holds a unique ID. For Oracle tables and views this is necessary to ensure consistent display of sorted records.

10. Relationship definition (several in-between data-sources) - Offers global definition of related fields across Dynamic AI datasources Typical 2 field relationship between 2 data-sources. Also an example of a relationship to be used from aggregated DEPBST (position level) report collections to drill-down to underlying detail records kept in the DEPORDERS (transaction level) table. Typical ID type of relationship used on ”own” data-source to effectively manage 1 central FORM collection and ”bind” the form collection to detailed level record listings by the ID. The use of this type of relationship will add a WHERE ID=unique ID to the select that Dynamic AI executes to build a dataform. Selecting only 1 specific record will make the form presentation fast and ressource effective.

11. Report Collections - Aggregated level definition (Multiple as required to offer immidiate access to relevant report and analysis dimensions) Because the aggregated level report collection should drill to lower level details, the enabled options in the customize interface is reduced to an absolute minimum. Here the available related collections are showing up A typical aggregated level report will group on data-dimensions and summarize measures

12. As the role of the list collections (in the ”free-dimensional data-model”) are primarily used to display ungroup records, the definition is mainly a question of selecting relevant columns. Select also relevant Totals columns and set eventually the SORT ORDER under the ”List options” tab. (ID doesn’t have to be displayed in order for the relationship to work!) List Collections - Detail record level definition (a few in order to display most relevant details) Because the detail level list collection should drill to 1 centrally defined form, the enabled options in the customize interface is reduced to an absolute minimum. Select stylesheet here Here the available related collections are showing up (select the collection that represent your FORM and use the unique ID relationship).

13. Use the ”layout” tab to design a presentable and custom form. As the form will be the lowest level for all detail listings as well as parent aggregated reports (through the link to detailed listings) effort can be spend on the design! Form Collections - Detail record level definition (1 per data-source) Because the role of the collection is only to display a form ”List” and ”List options” are disabled, making the customize simpler. Later on – when new fields are added, only 1 central form has to be updated. The defined relationships will appear automatically on the right side relate menu. Re-using the definitions already defined.

14. Demo

15. Security

16. Security • All ”rights” and actions in DAI are attached to a user account • Security is implemented using a combination of user-specific assigned rights together with assigned connection, dictionary rules and collection specific settings.

17. Security • User account is protected with: • Minimum password length, Minimum alphanumeric, duration, history • Account valid for specific period only • Failed login max attempts • Specific Server ID required • Certificate required – organization ID mapped to client certificate • Last login information • Logging • Encrypted hashed/salted password storage • Random session ID’s • User must change password on next login • Assigned user rights (read-only, administration, customize etc.) • Assigned user groups as well as user share groups • Dictionary roles

18. Security • The use of alias values set on the specific user account is a very strong and effective way to provide multi-department, multi-company, customer, vendor, employee type of forced filtering • Aliases can be used as part of: • connection settings • directly in a Dynamic AI View source • forced set through the dictionary for all underlying collections • in style-sheets • As part of specific collection conditions

19. Scalability • Database as a DAI object repository • Server application memory arrays that synchronize with repository • Any number of application servers can load from a central repository • Multi-server environment requires Enterprise version (repository on Oracle, SQL Server or DB2) • Load balancing with ”sticky sessions” can be used to create a scalable cluster • DMZ (Demilitarized zone) type of server installation with no administration object

20. Enterprise type installation

21. Benefits • Access all your data (Compatible with market leading Databases) • Easy to use (100% Web-interface, no coding) • Better-informed decisions (Even managers can (and do!) use it) • Easy to deploy and manage (No Client install, very stable) • Fast to develop (RAD) • Supports reality-based implementation (small, bigger, enhanced, cross-department, internal/external…) • Build-in Data maintenance functionality (Forms, List-edit, Portal pages) • All-in-one package (Security, User maintenance, Styles, Graphs, Excel integration, Development, Alarms, Drill-down…)

22. Benefits • Connecting to a Data Source • Dynamic Linking to Excel • Set Up Relationships • Build a Dictionary • Restrictive Access to Data • Sharing Reports • Advanced Reports

23. Free Training next Thursday

24. Questions? Thank you!