Minimized SQL concept is one of major design consideration in Cognos framework manager. In case when a model is complicated, it will have very big impact on performance, including DMR model and relational model. This chapter will provide a sample to demonstrate that all relationship should not be defined at database layer, and report will only retrieve involved table when absolutely necessary.
Sample
Given the data model below, we could consider G1 and G2 as Multi-valued dimensions, ID from Fact self as fact dimension. Please note that G1 and G2 Columns are not real primary keys. G1 and G2 tables are bridge tables, meaning that each group has multiple D1 or D2 records. 
Model without minimized SQL
In order to make a clear STAR scheme at business layer, we can define table Fact, Fact_detail, G1 and G2 together to behave as a fact table. However, this approach will result in a poor performance, as these joins among these tables are always there.
Framework manager layers
Database layer
Make clear relationships for G1, G2, Fact and Fact_Detail
Business layer
Create a model query Fact that is from 4 tables, G1, G2, Fact and Fact_Detail, where column D1 and column D2 is from dimension D1 and D2 respectively. Then build a clear STAR scheme as below.
Dimension layer
Dimension view is very much close to business layer with STAR scheme with exception of a fact dimension FD.
Test result
Using fact dimension
As matter of fact, we need only Fact and Fact_Detail. But we can see the generated SQL is to use G1, G2, Fact and Fact_Detail together.
Using D3 dimension only
When using D3 dimension, we should only involve Fact_Detail table only, as only Fact_Detail contains D3 dimension. The generate SQL uses all tables G1, G2, Fact and Fact_Detail.
Using D1, D2 and D3 dimensions
All tables should be involved in this case. But the sub-query could impair the performance. 
Model with minimized SQL
The minimized SQL concept is to bring only involved table(s), and therefore achieve the better performance.
Framework manager layers
Database layer
No any relationships are created at database layer.
Business layer
All relationships in detail are created at business layer. Certainly, we need to follow Cognos cardinality to make sure that path is predictable. There are many best practises in this regard. Please note that the relationship may NOT follow exact Entity /Relationship Diagram. 
Dimension layer
It is same as old dimension layer. It is a good news to us as we don`t need to change report, and we can change framework manager to improve performance.
Test result
Using fact dimension
Using D3 dimension only
Using D1, D2 and D3 dimensions
No comments:
Post a Comment