Business Objects provides a very good integrity checking mechanism. This integrity checking includes several things like universe structure, context and syntactical definition of objects, conditions, joins. But the developers face some more hurdles in universe designing which are not detected by this integrity checking.
Fan Trap and Chasm Traps are very common problems in relational database schemas. It is a Join path which returns more data than expected. Thus it generates erroneous data in Business Objects reports. Business Objects Enterprise suggests visually analyzing the design and detecting the traps. But in a universe which contains lots of tables and joins, it is very difficult to visually detect these traps.
Another area is object definition or property setting which is not checked by integrity checking of Business Objects but if the developer makes a mistake it will lead to erroneous data in the reports.
I will give a details description about these situation and explain how this tool will help to detect all these design errors.
FAN TRAP :
A fan trap is a type of join path between three tables when a “one-to-many” join links a table which is in turn linked by another “one-to-many” join. The fanning out effect of “one-to-many” joins can cause incorrect results to be returned when a query includes objects based on both leading and trailing tables. Incorrect data comes for the measure objects linked with first or second table in the join path. Also the measure objects will produce incorrect data if it has aggregate function as ‘sum’ or ‘average’ or ‘count’ (if distinct is not used).
This tool will find out all the three table join paths where a “one-to-many” joins links a table with another “one-to-many” join. It will also show the list of objects which can generate erroneous data. After getting all these details a developer can take corrective action to get rid of the fan traps.
CHASM TRAP :
The Chasm trap occurs when two “many to one” joins converge on a single table. A “many to one to many relationships” exists among three tables in the universe structure. There are multiple rows returned for a single dimension. Same like fan trap the measure objects will produce incorrect data if it has aggregate function as ‘sum’ or ‘average’ or ‘count’ (if distinct is not used).
This tool will find out all “many to one to many relationships”. It will also show the list of objects which can generate erroneous data. After getting all these details a developer can take corrective action to get rid of the chasm traps.
OBJECT DEFINITION & PROPERTY :
1. SQL aggregate functions to BO projection functions
Any time you use a SQL aggregate function the resulting object becomes a measure object in Business Objects. Measure objects by default have a projection function of Sum ().
The mapping of SQL aggregate functions to the proper Business Objects projection function is very important. The developer need to be very careful on setting proper Business Objects projection function. The table below shows the best mapping.
| SQL Aggregate Function | Business Objects Projection Function |
| SUM | SUM or NONE |
| COUNT | SUM or NONE |
| MIN | MIN or NONE |
| MAX | MAX or NONE |
| AVG | NONE |
2. Count & Count (Distinct())
The distinct qualifier for the count () operation avoids the over-counting problem. You should only use
the distinct qualifier to count a unique key (identifying) value from a table.
3. All measure objects should have a aggregate function
All measure objects are numeric, but all numeric objects are not measures. If a numeric object is a measure or used as a measure then that object must have a aggregate function, otherwise it can generate wrong data for the object in Business Objects report.
As example a sql query is generated in club database
SELECT
Service.service,
Sum (Invoice_Line.nb_guests),
Invoice_Line.days
FROM
Service,
Invoice_Line
WHERE
(Invoice_Line.service_id=Service.service_id)
GROUP BY
Service.service,
Invoice_Line.days
Here Invoice_Line.days comes in Group By clause but it is a measure should come in select with a aggregate function. Thus in the report generated, if you take the sum of number of days it will be less than expected. Thus all measures must have a aggregate function.
All these three error prone situations should be take care by the developers. This tool will clearly show the list of objects where you need to change the object definition or properties.
No comments:
Post a Comment