Transcript

IBM Cognos Analytic Applications
Slide 1 - Adaptive Application Framework 8.4 Extending the Target Model – Part 2
Slide notes
Hello and welcome to Part 2 of the demo on Extending the Target Model in Adaptive Application
Framework. In this demo we will look at how to add Target Model calculations and filters.
Page 1 of 125
IBM Cognos Analytic Applications
Slide 2 - Slide 2
Slide notes
In our model, the Order Activity star schema allows the users to report on Order Activity measures by
various dimensions.
Page 2 of 125
IBM Cognos Analytic Applications
Slide 3 - Slide 3
Slide notes
Inside the Order Activity Fact are fields defined as business keys, foreign keys as well as measures and
calculations. These measures and calculations are fields that are designed to be evaluated during the
ETL or Load Management process. We call these persisted calculations because their results are
stored directly in the warehouse. There are situations however that require a different type of
calculation. For example, we want to create a report that will display the Average Order Net Amount by
Customers over a certain period of time. This type of calculation cannot be pre-calculated and stored in
the warehouse; we need a dynamic calculation that will calculate the average amount at report time.
Let's head over to the Target Model to create a dynamic calculation.
Page 3 of 125
IBM Cognos Analytic Applications
Slide 4 - Slide 4
Slide notes
Let's expand the Target Namespace.
Page 4 of 125
IBM Cognos Analytic Applications
Slide 5 - Slide 5
Slide notes
And look inside the Order Activity Namespace.
Page 5 of 125
IBM Cognos Analytic Applications
Slide 6 - Slide 6
Slide notes
Adaptive Warehouse generated a Model Query Subject as well as a Measure Dimension to assign
scope to its measures. Let's expand the Order Activity Measure Dimension.
Page 6 of 125
IBM Cognos Analytic Applications
Slide 7 - Slide 7
Slide notes
These measures are fields that were defined by AW when it generated the Target Model. The folder
called "Measures (Base Currency)" was also generated automatically and contains all the measures
which were defined as Monetary Items in the Warehouse view.
Page 7 of 125
IBM Cognos Analytic Applications
Slide 8 - Slide 8
Slide notes
Inside the Model Query Subject called Order Activity_ are the generated surrogate keys as well as all
the fields that were defined in the Warehouse view. In our demo, we want to create a new calculation
that will return the Average Order Net Amount. Our model has a measure called Order Net Amount
which gives us the amount per Order. We'll need to divide the sum of those Amounts by the number of
orders in the report. We cannot use the existing Order Quantity field since it represents the number of
items in an order. Let's create a new calculation to count the number of orders. Double-click the Order
Activity Model Query Subject to edit its definition.
Page 8 of 125
IBM Cognos Analytic Applications
Slide 9 - Slide 9
Slide notes
Click on Add.
Page 9 of 125
IBM Cognos Analytic Applications
Slide 10 - Slide 10
Slide notes
Let's call it Order Count
Page 10 of 125
IBM Cognos Analytic Applications
Slide 11 - Slide 11
Slide notes
Let's pick a field that belongs to the Order Header
Page 11 of 125
IBM Cognos Analytic Applications
Slide 12 - Slide 12
Slide notes
Such as Order Number
Page 12 of 125
IBM Cognos Analytic Applications
Slide 13 - Slide 13
Slide notes
Add it to the expression.
Page 13 of 125
IBM Cognos Analytic Applications
Slide 14 - Slide 14
Slide notes
Click ok.
Page 14 of 125
IBM Cognos Analytic Applications
Slide 15 - Slide 15
Slide notes
Our Order Count field has been added to the Query Subject, click ok.
Page 15 of 125
IBM Cognos Analytic Applications
Slide 16 - Slide 16
Slide notes
Let's change its aggregation rule to allow the Cognos Query Engine to use it as a Count at report time.
Page 16 of 125
IBM Cognos Analytic Applications
Slide 17 - Slide 17
Slide notes
Select the field
Page 17 of 125
IBM Cognos Analytic Applications
Slide 18 - Slide 18
Slide notes
and display its properties.
Page 18 of 125
IBM Cognos Analytic Applications
Slide 19 - Slide 19
Slide notes
In the Aggregation Rule property
Page 19 of 125
IBM Cognos Analytic Applications
Slide 20 - Slide 20
Slide notes
Select it to bring up the drop box
Page 20 of 125
IBM Cognos Analytic Applications
Slide 21 - Slide 21
Slide notes
and select Count Distinct.
Page 21 of 125
IBM Cognos Analytic Applications
Slide 22 - Slide 22
Slide notes
Now let's add it to the Measure Dimension to give it scope and enable us to use it in a report. Doubleclick on the Measure Dimension.
Page 22 of 125
IBM Cognos Analytic Applications
Slide 23 - Slide 23
Slide notes
Expand the Target Namespace to find our calculation.
Page 23 of 125
IBM Cognos Analytic Applications
Slide 24 - Slide 24
Slide notes
Expand the Model Query Subject.
Page 24 of 125
IBM Cognos Analytic Applications
Slide 25 - Slide 25
Slide notes
Let's scroll down to find it.
Page 25 of 125
IBM Cognos Analytic Applications
Slide 26 - Slide 26
Slide notes
select it
Page 26 of 125
IBM Cognos Analytic Applications
Slide 27 - Slide 27
Slide notes
and drag it to the right hand side to add it as a Measure in the Measure Dimension.
Page 27 of 125
IBM Cognos Analytic Applications
Slide 28 - Slide 28
Slide notes
Click ok.
Page 28 of 125
IBM Cognos Analytic Applications
Slide 29 - Slide 29
Slide notes
Our Order Count field has been created, now let's create another calculation called "Average Order
Amount" which will use this Order Count field to divide the sum of the amounts.
Page 29 of 125
IBM Cognos Analytic Applications
Slide 30 - Slide 30
Slide notes
Let's collapse the Model Query Subject
Page 30 of 125
IBM Cognos Analytic Applications
Slide 31 - Slide 31
Slide notes
Page 31 of 125
IBM Cognos Analytic Applications
Slide 32 - Slide 32
Slide notes
In order to calculate the average, we need to define a calculation that will be evaluated after the autosummarization has taken place. This requires the creation of a Stand-Alone calculation. Right-click on
the Order Activity Namespace.
Page 32 of 125
IBM Cognos Analytic Applications
Slide 33 - Slide 33
Slide notes
Select Create
Page 33 of 125
IBM Cognos Analytic Applications
Slide 34 - Slide 34
Slide notes
And then Calculation from the sub menu.
Page 34 of 125
IBM Cognos Analytic Applications
Slide 35 - Slide 35
Slide notes
Let's call it Average Order Amount
Page 35 of 125
IBM Cognos Analytic Applications
Slide 36 - Slide 36
Slide notes
Let's select our fields from the Measure Dimension since we want them to have scope to the
Dimensions.
Page 36 of 125
IBM Cognos Analytic Applications
Slide 37 - Slide 37
Slide notes
Let's choose the Order Net Amount measure from the Base Currency folder. This will ensure that we
are adding up values that have been converted to the same currency.
Page 37 of 125
IBM Cognos Analytic Applications
Slide 38 - Slide 38
Slide notes
Page 38 of 125
IBM Cognos Analytic Applications
Slide 39 - Slide 39
Slide notes
Select the Order Net Amount
Page 39 of 125
IBM Cognos Analytic Applications
Slide 40 - Slide 40
Slide notes
and add it to the expression.
Page 40 of 125
IBM Cognos Analytic Applications
Slide 41 - Slide 41
Slide notes
Let's divide that number by the Order Count
Page 41 of 125
IBM Cognos Analytic Applications
Slide 42 - Slide 42
Slide notes
Select Order Count
Page 42 of 125
IBM Cognos Analytic Applications
Slide 43 - Slide 43
Slide notes
And add it to the expression.
Page 43 of 125
IBM Cognos Analytic Applications
Slide 44 - Slide 44
Slide notes
Our calculation is compete. Click Ok
Page 44 of 125
IBM Cognos Analytic Applications
Slide 45 - Slide 45
Slide notes
In order for our calculation to return the average, we need to make sure that it gets evaluated after the
auto-summarization. Let's set its Regular Aggregate property to do this.
Page 45 of 125
IBM Cognos Analytic Applications
Slide 46 - Slide 46
Slide notes
Select it
Page 46 of 125
IBM Cognos Analytic Applications
Slide 47 - Slide 47
Slide notes
And choose Calculated.
Page 47 of 125
IBM Cognos Analytic Applications
Slide 48 - Slide 48
Slide notes
Let's also set its display format to be more visually pleasing at report time.
Page 48 of 125
IBM Cognos Analytic Applications
Slide 49 - Slide 49
Slide notes
Select Format
Page 49 of 125
IBM Cognos Analytic Applications
Slide 50 - Slide 50
Slide notes
In our case, since it's a monetary item,
Page 50 of 125
IBM Cognos Analytic Applications
Slide 51 - Slide 51
Slide notes
let's choose Currency.
Page 51 of 125
IBM Cognos Analytic Applications
Slide 52 - Slide 52
Slide notes
And set the number of Decimals to 2
Page 52 of 125
IBM Cognos Analytic Applications
Slide 53 - Slide 53
Slide notes
Page 53 of 125
IBM Cognos Analytic Applications
Slide 54 - Slide 54
Slide notes
Click ok
Page 54 of 125
IBM Cognos Analytic Applications
Slide 55 - Slide 55
Slide notes
The definition to our calculation is now complete.
Page 55 of 125
IBM Cognos Analytic Applications
Slide 56 - Slide 56
Slide notes
To showcase another method in which to extend the Target Model, let's add another requirement to our
report. Let's say the users only want to see US based Customers in their report. Rather than displaying
a prompt at report time, let's create a Target Model filter that we'll add to the report later. Let's create it
inside the Customer Namespace.
Page 56 of 125
IBM Cognos Analytic Applications
Slide 57 - Slide 57
Slide notes
Let's open up the Model Query Subject to see which field we can use to filter.
Page 57 of 125
IBM Cognos Analytic Applications
Slide 58 - Slide 58
Slide notes
In our case, we'll use the Customer Country Code. Right-click on the Namespace.
Page 58 of 125
IBM Cognos Analytic Applications
Slide 59 - Slide 59
Slide notes
Select create
Page 59 of 125
IBM Cognos Analytic Applications
Slide 60 - Slide 60
Slide notes
and select filter from the sub menu
Page 60 of 125
IBM Cognos Analytic Applications
Slide 61 - Slide 61
Slide notes
Let's call it US Customers
Page 61 of 125
IBM Cognos Analytic Applications
Slide 62 - Slide 62
Slide notes
Expand the Model Query Subject.
Page 62 of 125
IBM Cognos Analytic Applications
Slide 63 - Slide 63
Slide notes
Select the Customer Country Code field
Page 63 of 125
IBM Cognos Analytic Applications
Slide 64 - Slide 64
Slide notes
And add it the the expression.
Page 64 of 125
IBM Cognos Analytic Applications
Slide 65 - Slide 65
Slide notes
To facilitate this demo, I had previously looked up the values to see which Country Code represented
the US. The value is 1003.
Page 65 of 125
IBM Cognos Analytic Applications
Slide 66 - Slide 66
Slide notes
The expression is done, click ok.
Page 66 of 125
IBM Cognos Analytic Applications
Slide 67 - Slide 67
Slide notes
Our stand-alone Target Model filter appears in the Namespace and is now ready to be added in a
report.
Page 67 of 125
IBM Cognos Analytic Applications
Slide 68 - Slide 68
Slide notes
Since there were no changes to the design of the Warehouse and we don't need to load data, let's
manually publish the Framework Manager package.
Page 68 of 125
IBM Cognos Analytic Applications
Slide 69 - Slide 69
Slide notes
Right click on the AAF Demo package
Page 69 of 125
IBM Cognos Analytic Applications
Slide 70 - Slide 70
Slide notes
And select Publish.
Page 70 of 125
IBM Cognos Analytic Applications
Slide 71 - Slide 71
Slide notes
Follow the steps through the Wizard and select Finish.
Page 71 of 125
IBM Cognos Analytic Applications
Slide 72 - Slide 72
Slide notes
We have extended our Target Model by creating two new Calculations and a filter. Let's save our
project and create a report in Adaptive Analytics to use these new objects. Click on Warehouse.
Page 72 of 125
IBM Cognos Analytic Applications
Slide 73 - Slide 73
Slide notes
And click on Save.
Page 73 of 125
IBM Cognos Analytic Applications
Slide 74 - Slide 74
Slide notes
Now let's switch over to Adaptive Analytics.
Page 74 of 125
IBM Cognos Analytic Applications
Slide 75 - Slide 75
Slide notes
In our case, since Adaptive Analytic was already open, let's click on the button called "Refresh from
Target Framework Manager model". This will force Adaptive Analytics to read the metadata saved by
Adaptive Warehouse.
Page 75 of 125
IBM Cognos Analytic Applications
Slide 76 - Slide 76
Slide notes
Page 76 of 125
IBM Cognos Analytic Applications
Slide 77 - Slide 77
Slide notes
Let's add our newly created calculation to the list of available Metric Types.
Page 77 of 125
IBM Cognos Analytic Applications
Slide 78 - Slide 78
Slide notes
Let's define it under Orders.
Page 78 of 125
IBM Cognos Analytic Applications
Slide 79 - Slide 79
Slide notes
Select the folder
Page 79 of 125
IBM Cognos Analytic Applications
Slide 80 - Slide 80
Slide notes
And from the properties, edit the Metric Types.
Page 80 of 125
IBM Cognos Analytic Applications
Slide 81 - Slide 81
Slide notes
The Metric Type Mapping window opens. Let's expand the Order Activity Namespace to find our
calculation.
Page 81 of 125
IBM Cognos Analytic Applications
Slide 82 - Slide 82
Slide notes
Select it
Page 82 of 125
IBM Cognos Analytic Applications
Slide 83 - Slide 83
Slide notes
and drag it to the right hand side to create a new Metric Type.
Page 83 of 125
IBM Cognos Analytic Applications
Slide 84 - Slide 84
Slide notes
A new Metric Type has been defined with an Actual Value Type pointing to the Average Order Amount
measure. Click ok
Page 84 of 125
IBM Cognos Analytic Applications
Slide 85 - Slide 85
Slide notes
Let's define a new Report Pack and create a new report within it.
Page 85 of 125
IBM Cognos Analytic Applications
Slide 86 - Slide 86
Slide notes
Select Create
Page 86 of 125
IBM Cognos Analytic Applications
Slide 87 - Slide 87
Slide notes
And then Report Package from the sub menu.
Page 87 of 125
IBM Cognos Analytic Applications
Slide 88 - Slide 88
Slide notes
Let's rename it to Average Order Amount Reports
Page 88 of 125
IBM Cognos Analytic Applications
Slide 89 - Slide 89
Slide notes
Give it focus by pressing Enter or double clicking on it.
Page 89 of 125
IBM Cognos Analytic Applications
Slide 90 - Slide 90
Slide notes
Now let's drag in the Customer hierarchy.
Page 90 of 125
IBM Cognos Analytic Applications
Slide 91 - Slide 91
Slide notes
Expand the folder.
Page 91 of 125
IBM Cognos Analytic Applications
Slide 92 - Slide 92
Slide notes
Select the Customer hierarchy
Page 92 of 125
IBM Cognos Analytic Applications
Slide 93 - Slide 93
Slide notes
and drag it over into the Report Pack on the right.
Page 93 of 125
IBM Cognos Analytic Applications
Slide 94 - Slide 94
Slide notes
We have now selected the Measure and the hierarchy, we need an Analytic Type. Right click to bring
up the menu
Page 94 of 125
IBM Cognos Analytic Applications
Slide 95 - Slide 95
Slide notes
From the Add Analytic Type Reference menu
Page 95 of 125
IBM Cognos Analytic Applications
Slide 96 - Slide 96
Slide notes
Let's select the Relative Time Variance. This will display the Average Order Amount and variances per
customer for the selected period and compare it to the previous period.
Page 96 of 125
IBM Cognos Analytic Applications
Slide 97 - Slide 97
Slide notes
Select the checkbox to tell Adaptive Analytics to generate a report spec.
Page 97 of 125
IBM Cognos Analytic Applications
Slide 98 - Slide 98
Slide notes
Let's choose our Time grain. Select the Analytic Type column header.
Page 98 of 125
IBM Cognos Analytic Applications
Slide 99 - Slide 99
Slide notes
And in the Additional Levels
Page 99 of 125
IBM Cognos Analytic Applications
Slide 100 - Slide 100
Slide notes
Select the Year level from the Calendar hierarchy to represent Time in our report.
Page 100 of 125
IBM Cognos Analytic Applications
Slide 101 - Slide 101
Slide notes
Page 101 of 125
IBM Cognos Analytic Applications
Slide 102 - Slide 102
Slide notes
Page 102 of 125
IBM Cognos Analytic Applications
Slide 103 - Slide 103
Slide notes
Page 103 of 125
IBM Cognos Analytic Applications
Slide 104 - Slide 104
Slide notes
Page 104 of 125
IBM Cognos Analytic Applications
Slide 105 - Slide 105
Slide notes
Page 105 of 125
IBM Cognos Analytic Applications
Slide 106 - Slide 106
Slide notes
Let's also add our US Customers filter to this report. Select the report
Page 106 of 125
IBM Cognos Analytic Applications
Slide 107 - Slide 107
Slide notes
and select the filters from the properties.
Page 107 of 125
IBM Cognos Analytic Applications
Slide 108 - Slide 108
Slide notes
From the Filter Editor window, select Add
Page 108 of 125
IBM Cognos Analytic Applications
Slide 109 - Slide 109
Slide notes
Since we defined our filter in the Target Model, let's select Target model.
Page 109 of 125
IBM Cognos Analytic Applications
Slide 110 - Slide 110
Slide notes
and click Next.
Page 110 of 125
IBM Cognos Analytic Applications
Slide 111 - Slide 111
Slide notes
Expand the Target Model to find the Filter under Customer.
Page 111 of 125
IBM Cognos Analytic Applications
Slide 112 - Slide 112
Slide notes
Select it
Page 112 of 125
IBM Cognos Analytic Applications
Slide 113 - Slide 113
Slide notes
And click finish.
Page 113 of 125
IBM Cognos Analytic Applications
Slide 114 - Slide 114
Slide notes
We don't want to add any more filters at this time, click OK
Page 114 of 125
IBM Cognos Analytic Applications
Slide 115 - Slide 115
Slide notes
Let's also rename this report to represent the US Customers.
Page 115 of 125
IBM Cognos Analytic Applications
Slide 116 - Slide 116
Slide notes
Page 116 of 125
IBM Cognos Analytic Applications
Slide 117 - Slide 117
Slide notes
Page 117 of 125
IBM Cognos Analytic Applications
Slide 118 - Slide 118
Slide notes
The report is now ready, let's preview it.
Page 118 of 125
IBM Cognos Analytic Applications
Slide 119 - Slide 119
Slide notes
Page 119 of 125
IBM Cognos Analytic Applications
Slide 120 - Slide 120
Slide notes
Page 120 of 125
IBM Cognos Analytic Applications
Slide 121 - Slide 121
Slide notes
At the Year promt, select 2008
Page 121 of 125
IBM Cognos Analytic Applications
Slide 122 - Slide 122
Slide notes
Page 122 of 125
IBM Cognos Analytic Applications
Slide 123 - Slide 123
Slide notes
Page 123 of 125
IBM Cognos Analytic Applications
Slide 124 - Slide 124
Slide notes
Our Relative Time Variance report displays our newly created Average Order Amounts calcualtion per
US Customers. Our calculation was evaluated at report time rather than displaying a measure stored in
the warehouse. This allows the report to recalculate the values based on the time period or the selected
customers in the report.
This concludes our demo on how to extend the Target Model by creating calculations and filters. I hope
you have found it useful.
Thank you.
Page 124 of 125
IBM Cognos Analytic Applications
Slide 125 - Resources:
Slide notes
Page 125 of 125