Basics to get started:



Data Warehousing, Business Intelligence and Data Mining

Developed by : Professor Eddie Ip

Modified by : Professor Arif Ansari

Exercise 2: Using Cognos BI tools to perform OLAP analysis

Objective

In this exercise, you will learn how to use your flat data and transform it into a multidimensional data cube using Cognos Transformer. The data cube will be used with Cognos PowerPlay to do slicing, dicing, drilling up and down dimensions, to navigate data around the cube. The results will be presented in a graphical chart or cross tab tables. Each dimension can be analyzed against each other with different measures.

Our purpose is to find out:

1. The sales trend of daily necessities for stores in location 2, using total profit and quantity as a measurement.

You will be using the data set you have extracted from the first exercise. The fields you should have in your data set are as follows:

Customer, gender, manufacturer code, 2 digit cat code, 4 digit cat code date, 6 digit cat code, store, location, quantity, cost, profit_amount, date.

|Field Name |Description |

|store |Store code |

|customer |Customer ID |

|date |Purchase date |

|quantity |Quantity |

|manufacturer |Manufacturer code |

|cat2 |2-digit category code |

|cat4 |4-digit category code |

|cat6 |6-digit category code |

|gender |Gender |

|cost |Cost of item |

|total_profit |quantity X profit/unit |

|product_ID |Product code |

|location |Location Code |

The master conversion code for category 2 code and category 4 code is located at::



Basics to get started

What is Transformer?

Transformer structure data from various sources into multi-dimensional PowerCubes. You will have the flexibility to design and define your cubes and create reports that let you draw comparisons and discover new patterns.

The program is located in Start > Programs>IOM>Cognos BI > PowerPlay Transformer.

Building a model

1. Start Transformer. From the File menu, select New.

2. In the Model Name text field, type Drug Store Sales Data.

3. Leave the data source name blank and in the source type box, select Delimited-field Text with Column Titles. Click Next.

[pic]

4. Click Browse to select the Data Source. Change the field delimiter to space “ “. The setting should match the following diagram. Go to the next step.

[pic]

5. Deselect Run Auto Design.

6. Click Finish.

Creating Your Dimensions

1. Click anywhere on the dimension map.

2. From the Edit menu, click Insert Dimension.

3. In the Dimension Name box, type Products Make, and click OK.

[pic]

4. To add a new level, drag the source column from the Data Sources window to the new dimension.

5. In this case drag Manufacturer, and Brand under the Products Name on the Dimension Map window.

[pic] [pic]

6. Now create a new dimension “Product Category”.

7. Click on the Dimension line.

8. From the Edit menu, click Insert Dimension.

9. In the Dimension Name box, type Product Category, and click OK.

10. Drag cat2, cat4, cat6 under the Products Category on the Dimension map window.

11. Now create a new dimension “Purchase date”.

12. Click on the Dimension line.

13. From the Edit menu, click Insert Dimension.

14. In the Dimension Name box, type Order Date.

15. In the Dimension Type box, select Time, and click OK.

[pic]

16. Click on the Time tab and click OK.

17. In the Source Column box, select Purchase Date that contains values for the dates in the dimension, and click OK.

18. In the Data Level Creation window, select Create standard levels, and choose the date column.

[pic] [pic]

Transformer creates a new time dimension, Year, Quarter, and Month

19. Create a calculated level to show the percentage growth of the current quarter with the prior quarter.

20. Highlight the Purchase Date Dimension, and click on the generate categories button on the menu bar.

[pic]

21. Right click on the “purchase date” dimension and open the property sheet that is to contain the calculated category and click the Calculation tab.

22. Click the Add button.

23. Enter “Percentage growth Q1 – Q2” for your calculated category in the Label box (Dimension Calculation Definition dialog box).

24. Click the Calculation button.

25. In the left pane of the expression editor, expand the Functions folder, select percentage growth function.

26. Click the Categories Diagrammer button and expand the levels under purchase by date, in the diagram, so you can select and drag “1998Q1” and “1998Q2” into your expression calculation. Click the Close button when you are done.

[pic]

Note: You can select, copy, or type a valid expression.

27. Click OK in the editor to save your finished expression.

[pic]

28. Repeat the same procedure to click percentage growth for Q2 – Q3, Q3 – Q4.

[pic]

29. Now create a new dimension Region.

30. Click on the dimension line.

31. From the Edit menu, click Insert Dimension.

32. In the Dimension Name box, type Region, and click OK.

33. Add new levels to the Region dimension by adding location and store into the Region dimension

34. Now create a new dimension Customer. Note: DO NOT add a customer level under this dimension.

35. Click on the dimension line.

36. From the Edit menu, click Insert Dimension.

37. In the Dimension Name box, type Customer and click OK.

38. Add new levels to the Customer Gender dimension by adding gender into the dimension.

39. Add levels to Measures window.

40. Add new levels to Measures by dragging Profit, Cost, Quantity into the Measures Window.

[pic]

Define a calculated measure

Calculated measure derives new numeric data from regular measures, calculated measures, functions, and constants.

1. Activate the Data Source window.

2. From the Edit menu, click Insert Column.

3. In the Column Name box, type “Revenue” for the new column.

4. In the Column Type box, click Calculated, and then click the calcuation button.

[pic]

5. Specify a numeric data when Transformer prompts you to specify whether the column consists of text, date, or numeric data.

6. In the pop-up menu, click a sequence of items for the expression, which is “Cost" + "Total Profit”. When the expression is valid, the OK button is enabled.

[pic]

7. Click OK.

8. Drag the Revenue under the Measures window.

9. Right click on Revenue under measures, click on the format tab, select “$#,##0”. Do the same to cost and total profit measure.

[pic]

10. From the File Menu, click Save As. You can either save it temporarily on C:/temp or in your floppy disk. Type in a name for your file, and select mdl format file type.

11. Click OK.

[pic]

Describe Your Columns

1. In the Data Source list, double-click Product Line.

[pic]

2. The property sheet will show up.

3. Click the description tab.

4. In the box, type Products in the drug store for the years 1998, and click OK.

Generate Categories for you model

1. In the Dimension Map, click Product Category.

2. In the toolbar, click the Show Diagram button. [pic]

3. In the toolbar, click Generate Categories. [pic]

4. Wait for the program to process. When prompted, click OK.

5. A tree will be generated. Click the expand and collapse icons beside its names to show or hide its descendant categories.

6. You can change names for the categories by double clicking on it, and type in the category label. For a list of description for the categories, please refer to

[pic]

[pic]

7. To go back to the original model view, go to Window menu, select Default to close the diagram.

8. From the toolbar, click Show Counts. [pic]

9. There should be:

• 117 product categories in total

• 12 2digit categories

• 101 4digit categories

• 496 6digit categories

In File menu, click save to save your hw.mdl model.

Creating a PowerCube

1. To be able to save the cubes (since you don’t have the permission to save on the M: drive), go to File menu and select Properties.

2. Then click on Directories sub-window and type or browse C:/temp on Models.

[pic]

3. From the toolbar, click create PowerCubes. [pic]

[pic]

A data cube will be generated and displayed in the PowerCube Window

4. Highlight the “Drug Store Data” Powercube, and click the Start PowerPlay button [pic] to display the cube report

Using Power Play

The Dimension Line

Dimensions are different aspects of your business stored in a data cube. Each dimension has its own folder on the dimension line.

Use the dimension line to find out where you are in the data. In Explorer reports, the dimension line changes when you drill down or up, or when you filter out unnecessary information.

For a visual map of where you are in the data, click the Channels folder. The dimension menu for Channels appears.

[pic]

If you are not sure what a category is, use the Explain command (View menu) to see a description of the category.

The Dimension Viewer

To display the dimension view, click on View Menu, then select Dimension Viewer

[pic]

In the dimension viewer, select the category that you want to add to your report.

Note: In an Explorer report, select the parent category of the categories that you want to add by clicking the Replace Rows or Replace Columns button.

Drilling up and Drilling down

Drill down allows you to look in to detail on your category

Click on Products on the dimension Line, and drill down to Environmental Line from its parent category, the dimension folder appears open and the name Environmental Line appears on it.

[pic]

You can further drill down the Bio-Friendly Soaps category by double clicking on the cell in the report

[pic]

The Child category under Bio-Friendly Soaps will then be displayed

[pic]

Drill up gives you a broader perspective of your dimension.

To drill up in a dimension, simply use the dimension line and select the parent level. You can also right click on the child category and choose drill up in the pop up menu

Slice and Dice

Add categories in a report

1. From the View menu, click Dimension Viewer.

2. In the dimension viewer, select the category that you want to add to your report.

Note: In an Explorer report, select the parent category of the categories that you want to add by clicking the Replace Rows or Replace Columns button.

Nested Categories

1. From the View menu, click Dimension Viewer.

2. In the dimension viewer, select the category you want to add.

3. Drag the category to your report. A vertical or horizontal black line indicates a valid drop position.

Swap rows, columns, or layers

From the Explore menu, click Swap, and then click Rows and Columns, Rows and Layers, or Columns and Layers.

Filter

Filtering allows you to take out unnecessary information which is not required in the report. You can filter data by using the dimension menu.

Click on a dimension folder, and a dimension menu will drop down. Select the appropriate category.

The current filter category appears in bold and is separated from its parent and child categories by horizontal lines.

[pic]

Exercise: Creating a performance trend over time report

Goal: To find out the profit of daily necessities category sold in 1998 Q1 to Q4 for each store in location code 2. In addition, we would also like to generate a trend line as a result of the data. Finally, we would also like to find out the quantity of products sold with the same criteria.

1. Using Purchase Date and Region as the dimension.

2. Double click on 1998 to drill down.

[pic]

3. In the dimension viewer, select Region “2” and click on the Replace Rows button on the left menu bar. [pic]

[pic]

Adding Nested Categories

1. In the dimension viewer, click the expand icon beside Product category.

2. Select Product category and drag this category and drop them to the right of the “Store”.

Note: You will see a thick black line when you reach the long bar drop zone

[pic]

3. Now we will hide the categories beside “Daily Necessities” cat 24. Highlight Daily Necessities. Go to Format menu, select Hide, and click on unselected categories.

[pic]

4. Hide the aggregate row and column, select the format menu, then select display options, deselect show summary rows and column, click OK.

Note: stores and daily necessities have been nested together, producing groups of information.

[pic]

Display the Trend over time

1. Hide the percentage growth columns to show the 1998 Q1, 1998 Q2, 1998 Q3, 1998 Q4

2. Hold down shift + ctrl, click on Percent-growth Q1 – Q2, Percent-growth Q2 – Q3, Percent-growth Q3 – Q4.

3. Select Hide.

[pic]

4. Click the Multiline button on the Display toolbar

5. A multiline graph will be generated.

6. Go to Format menu, and select display options

[pic]

7. Go to the display tab, deselect Show the summary line, column.

8. Click OK.

[pic]

To Change Report Labels

1. To change the store name. Click the crosstab button.

2. Right click on the “10257”, and select rename label.

[pic] [pic]

3. Type the desired name you want, and click OK.

To Filter Information

If we want to display the total profit for female customers in 1997, we can filter the information by doing the following:

1. Click the channels dimension folder.

[pic]

2. Choose 2 – female.

[pic]

The name of the dimension folder changes to show that you are filtering the report to include information for Mass Marketers.

To Format the values and measurements:

1. In the dimension line click on Measures

2. Select Revenue.

[pic]

3. Click the Format Measure Button on the left hand side.

4. In the Format Option box, select $#,##0, and click OK.

Note: the format of the revenue measure has changed

In order to find out the profit of daily necessities in different stores for 1998, we can do the following:

1. In the dimension line, click Measures

2. In the drop down box, choose Quantity Sold

[pic]

The report will then display the profit sold in 1998 in the stores located in region 2.

[pic]

Publishing your Report

Adding a title to your report

1. From the Format menu, click Title, Header, and Footer, and click Title.

2. In the text area, type “The Trend of Total Profit of Daily Necessities Products sold in Region 2 Stores in 1998”

Hit Enter to go to a new line

3. Click the Center alignment button

4. Click OK to close the Title box

[pic]

Saving your report

1. From the File menu, click Save as.

2. In the file name field, type in product1998.

3. In the file format drop down menu, choose the desired format (mdc, xls, ppr, asc)

4. You can also save the report in HTML format by choosing Publish as HTML in the File Menu

5. Choose the appropriate directory.

6. Type in a file name and select the report format as HTML table.

[pic]

7. Click OK.

Assignment:

1. Turn in a print out of the cross tab and multilane report you have created above. There should be two reports showing the profit trend and the trend of quantities sold over the four quarters in 1998.

2. The most profitable category sold among female shoppers and rank them in order.

Hint: Rank Order Your data

Example:

1. From the explore menu, click Rank.

[pic] [pic]

2. Select the row in the check box

3. In the by column drop down, choose the category that should be ranked by.

4. In the Show Ordinals box, select all

5. In which value is ordinal 1 box, select highest

6. In the sort ordinals box, select ascending

7. Press OK and the rank column will appear in the report

[pic]

3. Find out the top 3 most profitable Medicine manufacturers based on the Medicine category, write a summary to compare their category performance, differentiation, and variety of brands. You may generate 3-D graphs and chart to help you illustrate your comparison.

-----------------------

Cognos Transformer

[pic]

[pic]

Cognos Power Play

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download