Add business logic to the model - IBM Cognos

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:

  • Embedded filters are created within query subjects and their scope is restricted to that query subject. They are appropriate when the filter is intended for just one query subject or dimension. Embedded filters can be converted to stand-alone filters after they are created.
  • Stand-alone filters are filters available across the model. They are appropriate when required in multiple query subjects or dimensions or to make commonly used filters readily available for authors.

In the following steps, we apply an embedded filter to the SLS_PRODUCT_LOOKUP data source query subject to filter on English values:

  1. In the Project Viewer, in the gosalesdw namespace under the Physical View, double-click SLS_PRODUCT_LOOKUP.
  2. Click the Filters tab, click Add, and then in the Name box, type Language Filter.
  3. In the Available Components pane, double-click PRODUCT_LANGUAGE.
  4. Click in the Expression Definition pane at the end of the expression, and then type = ‘EN’. The result displays
  5. Filter Definition dialog box

    Filter Definition dialog box

  6. Click OK. The results display
  7. Embedded filter

    Embedded filter

    Notice the Usage column beside the Name column. Each filter has a Usage setting with the following options:

    • Always
    • The filter is applied in all instances regardless of whether the filtered query item is in the query.

    • Optional
    • 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.)

    • Design Mode Only

    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).

  8. Click the Test tab, and click Test Sample→The results display
  9. Test results with filter applied

    Test results with filter applied

  10. Click OK.
  11. 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.

  12. In the Project Viewer, in the Business View namespace, select Product Line (formerly PRODUCT_LINE_EN), select Product Name (formerly PRODUCT_NAME) from Products, and select Revenue (formerly SALE_TOTAL) from Sales Target.
  13. Right-click one of the selected items, and click Test. In the Test Results dialog box, select Auto Sum, and then click Test Sample. The results display
  14. Test results dialog box

    Test results dialog box

    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.

  15. Click Close.

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:

  • If you want to create a calculation specifically for one query subject or dimension, you can embed the calculation directly in that object. For query subjects, this calculation can be done for either data source query subjects or model query subjects. However, it is recommended that you apply calculations in model query subjects wherever possible. This allows for better maintenance and change management.
  • Create a stand-alone calculation when you want to apply the calculation to valuable if you need to do aggregation before performing the calculation. This aggregation can be accomplished by changing the stand-alone calculations Regular Aggregate property to Calculated.

To add an embedded calculation to a model query subject:

  1. In the Project Viewer, in the Business View, double-click Sales Fact.
  2. Click Add in the bottom-right corner.
  3. In the Name box, type Break Even Point, and then double-click Quantity in the Available Components pane.
  4. Under Available Components, click the Functions tab, expand Operators, and then double-click the multiplication operator (*).
  5. Notice there is a description of the function in the Tips pane

    Calculation Definition dialog box

    Calculation Definition dialog box

  6. under avaliable components, click the model tab, and double click the unit cost.the following results will display:
  7. [Business View].[Sales Fact].[Quantity] * [Business View].[Sales Fact].[Unit Cost]

  8. Click Test Sample (the blue triangle button above the Name box) to verify that the calculation works.
  9. Click OK, and then click OK again. The new calculated query item displays in the Sales Fact model query subject
  10. Break Even Point calculation

    Break Even Point calculation

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.

  • A session parameter returns session information at run time (for example, runLocale or account. UserName)
  • A parameter map is a two-column table, mapping a set of keys (source) to a set of substitution values (target)
  • A macro is a fragment of code that you can insert within filters, calculations, properties, and so on, that are to be evaluated at run time. Macros are enclosed by the number sign (#) character.

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:

  1. In the Project Viewer, right-click Parameter Maps, point to Create, and then click Parameter Map.
  2. 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.

  3. In the Name box, type Language_lookup, and then click Next.
  4. Click Import File→Then, next to the Filename box, click Browse. Navigate to <IBM Cognos BI install location>webcontent samplesmodels, click Language_lookup.txt, and then click Open.
  5. Click OK. The values in the file are imported.
  6. Parameter Map values

    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.

  7. Click Finish.
  8. In the gosalesdw namespace under the Physical View, double-click the SLS_PRODUCT_LOOKUP query subject.
  9. Click the Filters tab, and then click the ellipsis (...) in the Source column for the Language Filter. The Filter Definition window opens.
  10. In the Expression definition pane, remove the EN portion of the expression, and place the cursor after the equal sign (=).
  11. Under Available Components, click the Parameters tab, and then expand Parameter Maps.
  12. Double-click Language_lookup to add it to the expression.
  13. Expression definition with macro

    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.

  14. Under Available Components, expand Session Parameters, and then double-click runLocale to add it to the expression. Use the following syntax:
  15. 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.

  16. Place the cursor right after the first macro tag (#), and then type sql, which is the function for a single quotation mark. You can also find this function in the Functions folder under Available Components.
  17. Place the cursor just before the last macro tag (#), and then type ). The final syntax displays
  18. Macro expression for Language Filter

    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.

  19. Click OK, click the Test tab, and then click Test Sample to ensure that all values come back in your local language.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Cognos Topics