CS 157B: Database Management Systems II

lavishgradeSoftware and s/w Development

Nov 25, 2013 (3 years and 6 months ago)

130 views

CS 157B: Database Management Systems II

April 3 Class Meeting

Department of Computer Science

San Jose State University


Spring 2013

Instructor: Ron Mak

www.cs.sjsu.edu/~mak


Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

2

Cognos


Business intelligence (BI) tool from IBM.


Queries and reports


Dashboards and scorecards


OLAP


Data mining


predictive analysis



Cognos Business Intelligence 10

is available

in the IBM Academic Cloud along with

a sample data warehouse.


Each of you has a student account.


There are online tutorials you can try.

_

Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

3

Cognos Lab


URLs to your Cognos accounts:


Section 1:
http://170.224.160.168/ibm/cognos




Section 2:
http://170.224.164.77/ibm/cognos



Log in with your user name and password.


Emailed to you previously.



Become familiar with OLAP operations in the

Cognos Analysis Studio.


Click on “Analyze my business”.


Click on “Samples”.




Click on “Cubes”.


Click on “Sales and Marketing (cube)”.


Select “Default Analysis” and click the OK button.


Now you can do OLAP operations.

Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

4

Cognos Lab: OLAP Operations


Set dimensions and measures


Drag and drop


2
-

and 3
-
dimensional cubes



Drill up and drill down


Slice and dice


Pivot

Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

5

Cognos OLAP Documentation


Analysis Studio User Guide


Access from the Help menu item


Also at:
http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fco
m.ibm.swg.im.cognos.ug_cr_pps.10.1.0.doc%2Fug_cr_pps_id4763id_
ans_src_tree.html



IBM Cognos Business Intelligence V10.1 Handbook


Download from:
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4
&cad=rja&ved=0CFUQFjAD&url=http%3A%2F%2Fwww.redbooks.ibm
.com%2Fredbooks%2Fpdfs%2Fsg247912.pdf&ei=cghZUeP9C_XD4A
O5
-
IEg&usg=AFQjCNHXoKXy_zncHkYOartqkCM_8uZIFw



YouTube videos


What is OLAP?

http://www.youtube.com/watch?v=2ryG3Jy6eIY



What are Dimensions and Measures?
http://www.youtube.com/watch?v=qkJOace9FZg


Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

6

Other Cognos Features


Report generation


Dashboard


Scorecards


Predictive analytics


data mining

Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

7

Project #4


Dimensional modeling

and OLAP


Implement a
star schema

suitable for OLAP operations.


Use MySQL.


Use Cognos for inspiration and as an example.


Fact table


Tall and skinny, normalized.


Choose your measure and grain.


Dimension tables


At least three, can be unnormalized.


Each dimension should be hierarchical.


Populate your tables


Use data from Cognos or create your own data.

Department of Computer Science

Spring 2013: April 3

CS 157B: Database Management Systems II

©
R. Mak

8

Project #4


OLAP operations with two
-
dimensional reports


Choose dimensions


Generate reports that show combinations of two dimensions.


Drill up and drill down


Generate base report, drill up report, drill down report


Slice and dice


Generate base report, slice report, dice report


Pivot


Generate two reports, one a pivot of the other


Generate reports with SQL scripts or with

Java programs (Hibernate or JDBC)


Do not have to be interactive.


No GUI required.