Troubleshoot Missing Relationships
In this article we will focus on different types of relationship that would result in an unstable database and should be avoided at all cost.
1. Missing bottom-up relationships:
In a Board database where the above hierarchy is true, and you see different down totals when displaying reports with Customer by row as opposed to when displaying the same infocube with Channel by row, you might be in a "missing relationship" situation.
Within an existing hierarchy, missing bottom-up relationships can be identified by elements belonging to a lower level entity that are not related to any element belonging to a more aggregated entity.
Referring to the above example, a missing bottom-up relationship is a Customer that is loaded into Board, and is not related to any Channel (assuming that Channel is a grouping criteria for Customers). In this type of relationship, Customers items that do not related to any Channels are commonly referred to as "orphans" ((i.e Customer FreshWater does not have a parent Channel). Orphans can occur if Channels are loaded for Customers from an incomplete data source or where the Customer record is not present in the file used to load the Channel-Customer relationship.
Note: A "Not Available" item does not mean there are missing relationships. The "Not_Available" element is in itself an independent entity and, if we assume there are some Customers related to a "Not Available" Channel, their value will be displayed against the "Not Available" Channel. As a results, the totals would agree, regardless of the entity displayed by row. The down totals are equal, but a Channel equal to "Not_Available" would summarize all the Customers without a Channel defined.
What to do: Missing Bottom-up Relationships
Missing bottom-up relationships can be detected by using the ANALYZE button, available to all Administrators (or users with access to the database) and positioned in the Relationships tab. Entities which, after the scan is performed, are highlighted in red contain missing bottom-up relationships.
Assigning a "parent" entity to a "child" entity can be done either from the database interface, clicking on the entity highlighted in red and manually assigning relationships to elements, or it can be done through the Board front end interface by setting up a dataview with the entity containing orphans by row, and a block (enabled for data entry) containing the parent entity. This second option would also allow end users to maintain/complete this relationship.
You can use the DEFAULT button to fix all "Missing parent" relationships at once: just click on the button and select the member you want to assign to all incomplete relationships.
2. You have reports displaying different totals when drilling down or navigating the report
Consider the following example and hierarchy:
You have the Budget Infocube dimensioned by Customer and Salesman. You are also displaying the infocube by Salesman.
If you add the Customer entity by row and you see the following:
After performing a drill down to the Customer, Board will show only those Customers with at least one document that belongs both to Salesman A and any Customer. Board would then use the hierarchy to display elements.
You might see Customer 04 is missed, and the down total of the drill down is different from the down total in the initial view. This points to the fact that there is a discrepancy from the first report (total budget by Salesman "A" equal to 1000) and the second one where the drill down produces a total amount equal to 900
This behavior shows that Customer 04 is not related to any element of the Document entity.
Missing top-down relationships usually occur when higher level entities (i.e. Customer) are loaded into Board with a different DataReader protocol than the one used to load the tree relationship with the lower level entity (i.e. Document ). As a general rule of thumb, it is best to load an entire Tree (such as the Customer tree shown above) in one single DataReader.
What to do: Missing Top-down Relationships
Missing top-down relationships can be detected by using the NORMALIZE button, available to all Administrators (or users with access to the database) and positioned in the Relationships tab. Entities which, after the scan is performed, are highlighted in red contain missing top down relationships.
Missing top-down relationships can be navigated by selecting the "Summarized" navigation mode and clicking on the red entity : all of those elements that have a "Related Item Nr." (in summarize analysis mode) equal to "0" have missing top-down relationships.