Using the Query subject usage property
One of the advanced properties of a query subject is the usage property.
The usage property controls how the query subject should be understood and how the query engine should treat the query subject, and its child objects, in a query.
There are three setting values for the property. They are automatic, bridge, and summary.
If the usage has not been set at any point, then the property will display the text ‘Select an option’. This is understood by the query engine to be the automatic setting.
Automatic is the default setting.
The automatic setting tells the query engine that the query subject is an ordinary table, and no special treatment needs to be done. Once you have set the usage at any point, you will need to explicitly set the usage to automatic to have the query subject be treated as a regular query subject.
The bridge setting tells the query engine that the query subject is a bridge table not a fact table, even though the relationships to it are on the many end for the query subject.
The summary setting tells the query engine to summarize the rows in the query subject.
There is an analogue usage property for query subjects in Framework Manager. There, the values are default, Bridge table, and summary query.
This paper describes functionality of the usage property and how it can be used to model objects which can help report authors and consumers.
You access the advanced properties of a query subject by selecting the query subject, clicking the more button, choosing properties, and expanding the advanced section in the properties panel.
Summary tables
Purpose and use of summary tables
You can use the summary tables in reports.
The usage of summary pre-summarizes the values of the query. This gives you further control over the timing of the aggregation of objects. The values will be already summarized when you use them in conjunction with the rest of your model or with other objects in your report.
Summary tables, like unions, joined views, excepts, intersects, and SQL statements, are often modeled in reports.
It is probably better if these sorts of things are modeled in your model, either your data module or your Framework Manager model.
This is because, if it is in the module, it only needs to be modeled once and is available in all your reports and dashboards.
Since it is modeled once, there is only one possible failure point. Since there is only one definition, there will be consistency in the numbers generated. This is one aspect of the meaning of the phrase “a single version of the truth”.
Example of a summary table
Here is a simple example. This view was created from a fact table (the product forecast table from the sample GOSALES data warehouse data base). It has month key, base product key, and product forecast expected volume. In addition, the source query subject has a calculation creating a data value, which I created so that I could use it as a lookup reference for relative time measures. I’m including it as well.
I have added 3 calculations to the view. The purpose of the calculations is to show you the result of the summary.
One is a count of the rows in Month key. Another is a count of month key. The third is a count distinct of month key. Each row represents a record in the source table. The count of the number of records is shown, 129,096. This is the same value as the count of month key. The calculation count (distinct) month_key has a value of 43 as there are 43 values of month key.
I have set the aggregation property for these calculations to calculated. This is because the query engine will complain if I try to set on the usage to summary otherwise.
When the usage property is set to summary, the value for count (distinct month_key) continues to be 43. The value of count (distinct MONTH_KEY) and count (MONTH_KEY) become 5778. This is because there are multiple month key values for each base product key. You will notice that the value of expected volume has been aggregated as well.
If the view did not have base product key in it there would be the same number of rows when the view has its usage set to automatic. When the usage is set to summary then the count will be 43 for all three calculations.
An example of that is below. In this picture, I have removed base product key from the view. There is still 129,096 rows in the view.
In this picture, there are 43 rows once the usage property has been set to summary.
Bridge usage
A common modeling pattern is the bridge table scenario.
The bridge table exists to model a many-to-many relationship between two objects. The bridge table exists to remove the many-to-many relationship between the objects. The many side of each relationship is set to the bridge table.
Here is a ginned-up example.
The Cognos query engine understands tables which are at the many end of relationships to be fact tables. The bridge table isn’t a fact, however. The bridge table usage setting tells the query engine how to understand the role of the table and properly generate the query.
The key is to recognize the pattern and model it. Once you have recognized that the pattern is a bridge table and defined the bridge table, either in the data base or in the module, the setting will allow the query engine to deal with the situation, in a relatively elegant manner, as the hard part was to recognize the pattern and the requirements have been reduced to just setting the usage property to the appropriate setting.
This makes modeling for bridge table situations easier.
It is preferable to create the bridge table in the database rather than the model.
#CognosAnalyticswithWatson