|
<< Click to Display Table of Contents >> Reporting DB Schema |
  
|
|
<< Click to Display Table of Contents >> Reporting DB Schema |
|
The Reporting DB Schema is comprised of a number of SQL views and underlying tables. Users will only normally need to access the views.
There is one view per Item Type and one view per Valid Link (which link the Item Types together).
The name of the view will match the name of the Item Type or Valid Link but any non-SQL friendly characters (for example spaces) are replaced with underscores. The Item Type Product Build will therefore have a view named Product_Build.
The unique primary key on all Item Type views is ciobjid.
The Problem Item Type has a corresponding view Problem:
Each of the Item Type's attributes are represented by columns within the view and each column is given the attribute's Field Name. However there is special treatment of Group/User and User attributes to improve searching:
| • | A Group/User attribute is represented by three columns, one for the group name, one for the userid and one for the user name. In the above example the Group/User attribute ciowner is represented by ciowner_Group, ciowner_Userid and ciowner_UserName |
| • | A User attribute is represented by two columns, one for the userid and one for the user name. In the above example the User attribute pr_raisedby is represented by pr_raisedby_Userid and pr_raisedby_UserName. |
Using the SQL Select * from Problem results in:
All Date and Time columns are stored in the Reporting DB in local time (to the Reporting Server).
Two views are created for versionable items. One for the Item's stem and one for the version. The version view also includes all of the stem columns except for the stem's created and updated dates.
The stem view for Product Build:
The version view for Product Build:
Notice the inclusion of the stem columns. Also, the civciid column contains the object id of the version's stem.
The Valid Link views are all prefixed with LK_ followed by the Valid Link name and there is one per Valid Link, e.g.
Using the SQL Select * from LK_product_resolves_problem results in:
The parent and child id columns are foreign keys to the ciobjid column on the Item Type views.
Not all valid links are parent / child but instead are peer-to-peer. For example for a network connection where A is connected to B and also B is connected to A there is no parent or child, the things at each end of the link are peers.
The Valid Link LK_networkconnection is an example of a peer-to-peer Valid Link:
Notice that the ParentId and ChildId columns have been replace by SourceId and TargetId.
Also note, shown in the query results below, that there are two entries for each peer link with the source and target ids reversed.
Some valid links have data - for example a link between products might have some attributes on the link that are used to describe the interface between the products:
Most queries will require joining a number of views together. Item Type views will typically be joined by Valid Link views.
The ciobjid field
So for example to find Product versions that resolve problems the following join would be used:
In SQL this would look like:
SELECT <required-columns>
FROM Product_Version INNER JOIN
LK_product_resolves_problem ON Product_Version.ciobjid = LK_product_resolves_problem.ParentId INNER JOIN
Problem ON LK_product_resolves_problem.ChildId = Problem.ciobjid
There is one table that contains data that the normal user may want to look at, and that is the SYS table. This has a column SYSLastUpdated that contains the date and time (UTC) that the reporting server last checked for changes in the main Cimera database. This may be a few minutes behind the current time but if more than, say, 10 minutes then this may indicate that the reporting server is no longer operational and the Reporting DB is not being updated and therefore any queries may not return the latest data.