A070 – Physical data model
Using the graphical diagram view to see physical data model.
STEP 1: Create a new diagram
- Connect with designer account
- Click the + icon on Diagrams gadget to the right of the screen
- Select the template
- Give a unique name for your diagram
- Click on
Create, an empty model is generated
- create a model
MPDClientto represent the physical model of Sales application
STEP 2: Create a new diagram
Use contextual menus (via right click) to access the actions of the modeller :
- The working area: actions to insert objects, align them, save the template, add notes or containers, etc.
- An object: actions to access the object or add contents, change its color, etc.
- A link actions to access the object of the relationship, change the properties of graphics connector
- Right-click the blank area
- Click on
- Select all the objects starting with
Appin the list
- Change the tables position on the diagram
- Via the action bar, zoom in and save the template
- Print the template on one or more pages
STEP 3: Using diagram to navigate
The diagram view is interacting with the central working area, and can be opened in its own window:
- Double-clicking on a table to open the associated business object
- Double-clicking on a column to open the definition of the associated field
- An update is automatically updated in the open model
If an update is performed by another user in another session, it is necessary to use the action Refresh to update the model
- Open objects, links and attributes from the diagram view
STEP 4: Indexes
Indexes are determined by:
- All “functional key” fields of the object (unique index)
- All foreign keys (single index)
- All fields with a possible search
- Some timestamps
In a general way:
- Any business object contains a technical unique key : the “row_id”.
- This row_id is used in the joins between tables. When the foreign key is optional, Simplicité platform generates a full outer join, if the join is strictly an inner join
A business object whose property Use of a timestamp is checked will have 4 additional physical columns:
a) created_dt: creation date of the data
b) created_by: login of the user who created the data
c) updated_dt: last updated date
d) updated_by: login of the user who last modified the data
- View the indexes on the
- Change indexes through the Database Access action on Operation / Home_ page (Others page).
STEP 5: SQL requests
The platform allows a DBA to trace and therefore optimise database :
- Consult the SQL requests generated in the logs by activating probes
- Easily access to the database from a page
- To list the 10 longest queries since server startup
To activate probes:
- Move the “LOG_SQL_USER” system parameter to “yes” and clear all caches
- Access times will be longer and the logs are large, so you have to activate this probe only in design / test.
- Server logs will contain all user queries
- Move “LOG_SQL_USER” parameter to “no” and clear the cache to stop the tracing.
- Enable the probe
LOG_SQL_USER/ clear cache
- Reconnect and open a customer form
- Consult the server logs
- Disable the probe and clear cache
STEP 6: Simple access to database
Simplicité platform has a very simple page to access the database. The objective is to troubleshoot or allow a simple access :
- Open the home page of the Operation menu
- Click the Database Access button from the Others tab
- Enter your SQL query
- Run and see the result of the selection or updating
- Change indexes on your object using this function.
STEP 7: Top 10 longest queries
- Open the Operation / Monitoring menu
- Go to the Data tab
The list shows the times of the 10 longest queries, those it is recommended to optimise if possible (analysis and creation Index by a DBA).
This list remains informative and is not intended to replace the tools DBMS vendors.
Sometimes very simple queries are slow simply because the server is busy CPU, which has nothing to do with the application itself.
Simplicité platform can only measure elapsed time of each request between submission and the response of the DBMS.