Cognos Cubes How do I get started - PDF by amberp

VIEWS: 1,967 PAGES: 38

									Institutional Analysis and Planning: Cognos Cube Tutorial Level II




                                    Cognos Cube Tutorial

                                                       Level 2

                  Institutional Analysis and Planning




                                                                     1
Institutional Analysis and Planning: Cognos Cube Tutorial Level II




                                                     Contents

Introduction to the advanced Cognos Functions………………………………………….3

Creating Charts………………………..…………………………………………………………………….4

Performing Calculations………………………..……………………………………………………….13

Ranking Data…………..………………………………………………………………………………………16

80/20 Suppression………………………………………………..……………………………………….20

Automatic Exception Highlighting……………………..……………………………………………21

Custom Exception Highlighting……………………..……………………………………………….22

Custom Subsets………………………………………………………………………………………………25




                                                                     2
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                     1. Introduction to the Advanced Cognos Cube Functions




                                                                             3
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                                  2. Creating Charts

Cognos allows the user to display the cube data in six different chart forms; Simple Bar charts, Pie
charts, Clustered Bar charts, Stacked Bar charts, Multiline charts, and 3D bar charts.

For the following examples, the following crosstab will be used.




The column dimension is Faculty, while the row dimensions are Fiscal Year and Attendance.




                                                                                                       4
Institutional Analysis and Planning: Cognos Cube Tutorial Level II




2.1 Simple Bar Charts
1. Click on the “Chart” icon on the toolbar at the bottom of the screen. This will bring up a menu of chart
types. From this menu select “Simple Bar”




2. The page will refresh to show a column of bar charts. Each chart represents a rows worth of data. For
example, each bar chart shows all FTE’s (Full Time Equivalents) in each faculty. There are ten bar charts
in total; one for each fiscal year from 1996/1997 to 2005/2006.




                                                                                                          5
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


3. To change the bar charts properties one can access the “Display Options” dialogue box. This can be
done in two ways.

Method 1.
Click on the “Options” icon from the toolbar at the bottom of the screen and select “Options” from the
menu.




Method 2.
Right click on any of the column labels in a chart and select “Display Options…”




                                                                                                         6
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


Both will open the following dialoge box in the browser.




4. Once changes have been made to suit your needs click the “Apply” button. The page will refresh with
the updated chart. To close the Options dialogue box click the X in the top right hand corner.




                                                                                                         7
Institutional Analysis and Planning: Cognos Cube Tutorial Level II
2.2 Pie Charts
1. Click on the “Chart” icon and select “Pie” from the menu.




2. The page will refresh to show a column of pie charts. Each pie chart represents the data from the
summary row for each fiscal year. Each slice of the pie represents the column subdimension, in this
case, faculties. The “Other” slice represents Faculties which are too small to be seen in the pie chart
display.




                                                                                                          8
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


2.3 Clustered Bar Charts
1. Click on the “Chart” icon on the toolbar at the bottom of the screen and select “Clustered Bar”.




2. The page will refresh to show a column of Clustered Bar charts.




                                                                                                      9
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


2.4 Stacked Bar Charts
1. Click on the “Chart” icon on the toolbar at the bottom of the screen and select “Stacked Bar”.




2. The page will refresh to show a column of Stacked Bar charts.




                                                                                                    10
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


2.5 Multiline Charts
1. Click on the “Chart” icon on the toolbar at the bottom of the screen and select “Multiline”.




2. The page will refresh to show a column of Multiline charts.




                                                                                                  11
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


2.6 3D Bar Charts
1. Click on the “Chart” icon on the toolbar at the bottom of the screen and select “3D Bar”.




2. The page will refresh to show a column of 3D Bar charts.




                                                                                               12
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                            3. Performing Calculations

1. There are two ways to open the Calculations dialogue box.

Method 1:
Left click the heading of the row or column you wish to use for the calculation.




Click on the “Calculations” icon from the toolbar at the bottom of the screen.




Method 2:
Right click the heading of the row or column you wish to use for the calculation and select “Insert
Calculation…” from the menu.




                                                                                                      13
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


Both will open the Calculations dialogue box.




2. Select the type of calculation you wish to perform from the Operation drop down list.




3. Give the calculation a new name by typing in the Calculation Name box. The name will be used as the
heading for the new calculated row or column.




4. Select the categories you wish to include by selecting them from the menu’s next to the Category
radio buttons. If you wish to use a constant, click the Number radio button and type a value into the box
beside it.




                                                                                                       14
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


5. Once finished, click “OK”. The cube will refresh with the new calculated row or column displayed on
the crosstab in italics.




6. To delete the calculated row or column, right click on the heading and select “Delete”. To edit the
calculation, select “Edit Calculation…” or “Rename Calculation…” . To add another calculated row or
column select “Insert Calculation…”.




                                                                                                         15
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                                    4. Ranking Data

The ranking function creates ordinal values to indicate the relative positions of cube data. The ordinal
values are displayed beside the row or column they are ranking. Totals and summary data cannot be
ranked.


4.1 Ranking Rows

1. There are two ways to open the Rank dialogue box.

Method 1.
Left click the heading of the row you wish to use for the calculation.




Click on the “Rank” icon from the toolbar at the bottom of the screen.




Method 2:
Right click the heading of the row you wish to rank and select “Insert Rank…” from the menu.




                                                                                                           16
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


Both will open the Rank dialogue box.




2. Confirm or change the row you wish to rank by selecting a title from the “Rank the rows by the
following column” menu.




3. Select which ordinals to show from the Show ordinals list box. Type in the number of ordinals you
would like displayed into the box beside it.




4. Give the ranking scheme a name by typing in the Rank name box. Press “OK” when you are finished.




                                                                                                       17
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


The Cognos cube will refresh to show the ranking ordinals in a row next to that of the ranked data.




4.2 Ranking Columns
1. Select a column to rank and click on the “Rank” icon on the toolbar




Unlike rows, column ranks can be sorted in ascending or descending order. After selecting a column and
opening the Rank dialogue box, two more options are available.

2. Select which ordinals to show and then type in the number of ordinals into the box beside it.




                                                                                                      18
Institutional Analysis and Planning: Cognos Cube Tutorial Level II
3. Select how to sort the ranked data. Press “OK” when you are finished.




The Cognos cube will refresh to show the ranking ordinals in a column next to that of the ranked data.




                                                                                                     19
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                                5. 80/20 Suppression


In the previous tutorial, the function Zero suppression was introduced. When this option is selected,
rows or columns which consist entirely of zero’s are hidden. The Cognos cubes offered by the University
of Waterloo have this option automatically selected and have the phrase “Zero suppression rows and
columns” below each cube.


80/20 Suppression hides rows or columns whose absolute values do not contribute to the top 80% of
results. The data is sorted from highest value to lowest value. The values are then summed down the list
till they add up to 80% of the total.

1. Click on the “80/20” icon on the toolbar at the bottom of the screen.




2. The cube will refresh with the lower values collapsed into a single category titled “Other”. The phrase
“80/20 Suppression” is added below the cube.




                                                                                                         20
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                     6. Automatic Exception Highlighting

Exceptions are highlighted if they are significantly higher or lower than the expect values. This is
determined by the row and column totals. Significantly low values are displayed in a red bolded font,
while significantly high values are displayed in a green bolded font.

1. Click on the “Automatic Exception Highlighting” icon on the toolbar at the bottom of the screen.




2. The cube will refresh with the exceptions highlighted in red or green. The phrase “Exception
Highlighting” is added below the cube.




                                                                                                        21
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                       7. Custom Exception Highlighting

1. To create a custom exception, click on the “Custom Exception Highlighting” icon on the toolbar at the
bottom of the screen.




2. The Custom Exceptions dialogue box will open at the bottom of the screen.




3. To create a new custom exception click “Add…” in the dialogue box. Each Cognos cube can have up to
20 different custom exceptions.




4. A new dialogue box will open. Give the new exception a name by typing in the Exception Name text
box.




                                                                                                      22
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


5. For each custom exception, upto five value ranges can be specified. For each value range, a text
colour and cell colour can also be chosen. In the From box, type in a minimum value. In the To box, type
in a maximum value. From the Text box, select a font colour. From the cell box, select a background
colour.




6. Define up to four more ranges. Once finished click “OK”. The Cognos cube will refresh to display the
previous dialogue box with the new custom exception listed under Defined Exceptions.




7. Select the row or column you wish to apply the exception to. If you wish to apply the exception to the
entire table, click in the title of the cube.




                                                                                                          23
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


8. Click the “Apply” button in the Custom Exceptions dialogue box to show the excepted values. Click
“OK” to close the dialogue box. The cube will now look like this:




9. To remove the custom exception, re-open the Custom Exceptions dialogue box. Reselect the area you
wish to clear. Select (none) from the Defined Exceptions list. Click “Apply”.




                                                                                                       24
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


                                                   8. Custom Subset

Custom subsets allow users to isolate certain segments of data and view them together in the Cognos
cube. There are three different ways to create custom subsets; Using search criteria, using measure
values, and selecting categories. After defining the criteria for your custom subset, Cognos returns all
values which match your criteria.


8.1 Creating a Custom Subset using Search Criteria
1. Select the “Custom Subsets” icon from the toolbar at the bottom of the page.




2. This will open the Custom Subsets dialogue box. Type a name into the “Custom Subset Name” box.




3. From the “Dimension” drop down list, select the dimension from which you wish to create the custom
subset.




                                                                                                           25
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


4. Select the “Define Rule by Search Criteria” radio button and click “Next”.




5. The “Create a Custom Subset By Name” dialogue box will open. Click “Add…”




6. From the “Search String” drop down list, select the criteria for your search. Type the text you wish to
search for in the box beside the drop down list. The string is not case sensitive.




                                                                                                        26
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


7. From the “Choose a Starting Point for your Search” box, select the category you wish to search.




8. In the “Select a scoping option for your search” drop down list, select the level of categories you wish
to search. Click “OK”.




9. The new definition will be visible in the “Search Definitions” box. The number of results returned will
be displayed below. Once all relevant definitions have been added, click “Next”.




                                                                                                         27
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


10. The returned categories will be displayed in the “Results Set” box.




11. To add categories that were not returned in the results set, add them from the “Available Categories”
box using the forward arrow. Once done, click “Finish”.




                                                                                                      28
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


12. The cube will refresh to show only the categories defined by the custom Subset. Note how the
custom subset appears as a subcategory filter along the dimension selector line under the “FACULTY”
dimension.




                                                                                                      29
Institutional Analysis and Planning: Cognos Cube Tutorial Level II
8.2 Creating a Custom Subset using Measure Values

1. From the Custom Subsets dialogue box, select the “Define Rule by Measure Value” radio button.




2. The “Create Custom Subset By Measure Value” dialogue box will open. Select the measure on which to
base the rule from the box. Typically, there will only be one measure available. In this case, the measure
is FTE (Full Time Equivalents).




3. From the “Include only the following values” drop down list, select the appropriate value range.




                                                                                                       30
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


4. The following options will be displayed depending on whether you choose “By Range” or “By top or
bottom”.




5. Choose a starting point for your search. All subcategories within the selected dimension (Faculty) will
be displayed. However, these subcategories can only be selected if they have subcategories of their own.
Typically there will only be one possible starting point for the search, in this case, Faculty.




6. Select the scope for the search from the “Select a scoping option for your search” drop down list.




                                                                                                        31
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


7. Apply any appropriate filters from the dimension selector line. Once finished, click “Next”.




8. The returned categories will be displayed in the “Results Set” box.




9. To add categories that were not returned in the results set, add them from the “Available Categories”
box using the forward arrow. Once done, click “Finish”.




                                                                                                      32
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


10. The cube will refresh to show only the custom subset. Note how the custom subset appears as a
subcategory filter along the dimension selector line under the “FACULTY” dimension.




                                                                                                    33
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


8.3 Creating a Custom Subset using Category Selection

1. From the Custom Subsets dialogue box, select the “Select Categories” radio button and click “Next”.




2. To create the custom subset, select categories from the “Available Categories” box and add them to
the Results Set using the forward arrow. Use the backwards arrow to remove any unwanted categories.
Once done, click “Finish”.




                                                                                                     34
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


3. The cube will refresh to show only the custom subset. Note how the custom subset appears as a
subcategory filter along the dimension selector line under the “FACULTY” dimension.




                                                                                                   35
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


8.4 Removing a Custom Subset

1. To remove the custom subset click on “Waterloo Custom Subset” from the dimension selector line.
Select “FACULTY” from the drop down menu. This will restore the Cognos cube to its original layout.




2. To reselect the custom subset, click on “FACULTY” on the dimension selector line. Scroll down and
click on the “Waterloo Custom Subset”.




                                                                                                       36
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


8.5 Modifying a Custom Subset

1. Open the Dimension Selector Tree by clicking on the tab at the top left corner of the web page.




2. Right click on the name of the subset you wish to edit. Select “Edit Subset…” from the menu.




                                                                                                     37
Institutional Analysis and Planning: Cognos Cube Tutorial Level II


3. The Custom Subsets dialogue box will open with the settings for the specific subset selected from the
Dimension Selector Tree. After making changes to the subset settings, click “Finish”.




4. The cube will refresh with the modified subset.




   Prepared by Institutional Analysis and Planning, June 1, 2006.
                                                                                                      38

								
To top