Again, typically business logic such as filters and calculations are applied in the Business View layer. However, there are instances where it makes sense to implement items in the Physical View where the cost of performance and maintenance is low. One example of this is the requirement we saw earlier to apply a filter on the SLS_PRODUCT_LOOKUP query subject, which had multiple rows for the same product to support multiple languages. Another example might be the requirement to implement aliases in the Physical View to control query paths between objects. If model query subjects are used to create these aliases or to consolidate multiple underlying query subjects, then applying filters and calculations on these query subjects makes sense because they are not data source query subjects.
Add filters to the model
The first filter John Walker applies is a filter on a data source query subject. Although adding this filter goes against the general rule of thumb, the filter must be applied a level lower than the Business View layer because the filter is required only when Product Name is included in the report. For example, does it make sense to apply a product name language filter when only querying Product Line? If the filter is place in the Products model query subject in the Business View, the filter would be applied in every instance an item from that query subject was used, regardless of whether it was required. The performance hit of scanning an extra table unnecessarily in such cases is greater than the performance hit that can occur from an additional metadata call to the database.
Filters come in two forms in IBM Cognos Framework Manager:
In the following steps, we apply an embedded filter to the SLS_PRODUCT_LOOKUP data source query subject to filter on English values:
Filter Definition dialog box
Notice the Usage column beside the Name column. Each filter has a Usage setting with the following options:
The filter is applied in all instances regardless of whether the filtered query item is in the query.
The filter is not mandatory and users can choose to enter a filter value or leave it blank. (This option applies only to filters that use a prompt value or macro.)
This option limits the amount of data that is retrieved when testing in IBM Cognos Framework Manager or at report design time (when authoring reports in IBM Cognos Query Studio, IBM Cognos Report Studio, and IBM Cognos Business Insight Advanced).
We conduct the same test that we conducted in the previous section between Products and Sales Fact again in the Business View to ensure that the results are as expected.
The overall summary totals in the studios for Product Name are now accurate and are not double counted for each product name language in the data source.
Add calculations to the model
You can create calculations to provide report authors with values that they regularly use. For example, you might want to include a product break even value as one of the measures in the Sales Fact query subject in the form of Quantity * Unit Cost.
Calculations can use query items, parameters, and functions.
There are two types of calculations:
To add an embedded calculation to a model query subject:
Notice there is a description of the function in the Tips paneCalculation Definition dialog box
[Business View].[Sales Fact].[Quantity] * [Business View].[Sales Fact].[Unit Cost]
Make the model dynamic using macros
You can modify query subjects and other model properties to control dynamically the data that is returned using a combination of session parameters, parameter maps, and macros. Let us quickly examine each piece.
These items can be used to return data dynamically from specific columns or rows or even from specific data sources.
For the scope of this book, we show one example that incorporates all three elements in an embedded filter. In this example, we alter the filter that was created for the SLS_PRODUCT_LOOKUP query subject. Authors and analysts want to be able to see product names in their language based on their local settings.
To alter the filter, we import a parameter map. The parameter map substitutes a user’s runLocale session parameter with a language code value found in the database, such as EN for English or FR for French. This substitution is wrapped in a macro that also encloses the substitution value in single quotation marks because the filter expects a string value.
To implement a macro to change a filter dynamically at run time, follow these steps:
The Create Parameter Map wizard opens. You can type in the values if you support only a small set of languages, or you can import the values from a file. You can also base the parameter map on query items within the model. For this exercise, we use a text file that has the mappings already entered.
Parameter Map values
Note that en-us (and all other English variants) map to EN. The same mapping applies for other languages and their locales.
Expression definition with macro
Notice that the parameter map is enclosed in the macro tags (#) automatically. The macro now requires a value to pass to the parameter map for substitution. In this case, the runLocale session parameter is passed to the parameter map.
This syntax still shows that there is an error, indicated by a red squiggly underline. The value that the parameter map returns needs to be wrapped in single quotation marks.
Macro expression for Language Filter
Notice the results in the Tip pane. This pane shows what the macro resolves to based on the current runLocale. In this case, it is en, which is substituted for EN in the filter.
IBM Cognos Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|IBM DB2 Interview Questions||Data Warehousing Interview Questions|
|SQL Interview Questions||IBM Cloud Computing Infrastructure Architect V1 Interview Questions|
|Framework7 Interview Questions||IBM BPM Interview Questions|
|IBM Cognos TM1 Interview Questions||Cognos ReportNet (CRN) Interview Questions|
|Structured Query Report (SQR) Interview Questions||IBM DataPower Interview Questions|
Ibm Cognos Tutorial
Introduction To Ibm Cognos Business Intelligence
Overview Of The Ibm Cognos Business Intelligence Architecture
Business Scenario And Personas Used In This
Create Reporting Packages With Ibm Cognos Framework Manager
Business Intelligence Simplified: An Overview
Individual And Collaborative User Experience
Self Service Interface For Business Users
Actionable Analytics Everywhere
Enterprise Ready Performance And Scalability
Ibm Cognos System Administration
Integrating Ibm Cognos Bi With Ibm Cognos Business Analytics Solutions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.