Database View

harry24 · ‎09-15-2020

What is database view?Practical demo of how to create Database view and how to use it.

MD AQUIB KHAN · ‎09-15-2020

Hi Harry,

A database view defines table joins for reporting purposes.

For example, a database view can join the Incident table to the Metric Definition and Metric Instance tables. This view can be used to report on incident metrics and may include fields from any of these three tables.

A number of useful database views are installed with the Database View plugin and the Database Views for Service Management plugin. These database views cover most metric reporting needs and greatly reduce the need to define new ones.

Note: In general, as the number of tables that are included in the view and the number of records that those tables contain increases, the accumulated impact on performance grows. In addition, to optimize the performance of the database view ensure that the ‘where’ clauses that are defined in the database view are based on indexed fields.

For Example:

Database view Name:Incident SLA

you can create database view and the two tables (incident, task_sla) in the related tabs (View Tables). Once you add them. You can use the same database view in the report as table.Once done you can run the report.

Please find the screenshot below:

Report:

Using the database view in Table:

View solution in original post

harry24 · ‎09-15-2020

but this can be done using dot walking on related list? so why need of database view?

MrMuhammad · ‎09-15-2020

Let me give you a very recent use case.

Let say you need a report to list all the Computers and VMware that are NOT associated with any incident record. Now in this case three tables are involved that require comparison.

incident table

task_ci table [M2M table that holds the association between task and Ci].

cmdb_ci or computer and vmware table.

In this scenario, you cannot dot walk between all the tables. so you need to create database views and using queries you will display the limited required data. In our example case the Information of CI's that are not associated with any incident.

Regards,
Muhammad

Sajilal · ‎09-15-2020

Yes you are right, but think of Database views as more complex needs of aggregation between tables, like left join or outer join between two etc.

Even between three tables like Incident, Metrics, SLA and this you cannot achieve by related list.

Check this old wiki Documentation for more info

https://old.wiki/index.php/Database_Views

Thanks,

Saji

Jaspal Singh · ‎09-15-2020

Hi Harry,

Database view is a combination of multiple tables so as to generate a view (virtual table) that an be used for reporting.

Consider an example of RITM & Incident table where you want to check or display all records that have been opened for same user. This though can be directly reported on Task table but you will to look for Requested for field (from RITM) & Opened by (field from Incident).

Since, Requested for does not exists on task table report cannot be directly generated on task table & thus clalls in a need to create a database view which can be used for reporting.

harry24 · ‎09-15-2020

but this can be done using dot walking on related list? so why need of database view?