Data mining exercise with BlueQuery Enterprise V Lab 2

1
Data mining exercisewith BlueQuery Enterprise
VLab 2

• Winnie Lam
• Email cswinnie_at_comp.polyu.edu.hk
• Website http//www.comp.polyu.edu.hk/cswinnie/
• The Hong Kong Polytechnic University
• Department of Computing

Last update11/04/2006
2
Data Warehousing
3
Data Warehousing Process
DATA SOURCES STAGING AREA
DATA WAREHOUSE
DECISION SUPPORT
Application Databases
__________________________________________________
____
Reports
Packaged application/ERP Data
DATA MARTS
INCOME ANNUAL REPORT ___ ___ ____ _____ ___ __
___ ___ ____ _____ ___ __ ___ ___ ____ _____
___ __
EXTRACTION TRANSFORMING CLEANING AGGREGATION
DATA WAREHOUSE
EIS
Desktop Data
OLAP
External Data
Statistical Financial Analysis
Web-based Data
Adapted from SunExpert Magazine, October 1998.
4
BlueQuery Enterprise V
Data Layer
Oracle, SQL, Sybase, DB2
Database
BlueQuery ETL Engine
JSP, PHP, ASP, .net
EIS Platform
Enterprise Data Warehouse
Web
Excel, Word, XML
End Users
Local File
SQLOLAP
OLAP
ERP, CRM, SCM
Other Application
5
Knowledge Discovery in Database

• KDD Process

Evaluation
Data Mining
Transformation
Preprocessing
Knowledge
Selection
Patterns
Transformed Data
Preprocessed Data
Target Data
Data Warehouse
6
Practical Session
7
Login Page
Open BI Desktop from BlueQuery Program
1
User ID admin Password default blank Server
127.0.0.1
2
8
(No Transcript)
9
Language and GUI Setting
English version Tools gt Language GUI Setting
10
Access Database Connection
2
1
3
4
Microsoft Jet 4.0 OLE DB Provider
5
11
Access Database Connection
1
BROWSE C\Program Files\SunNet\BlueQuery
Server\DemoDB\Northwinde.mdb
3
4
CLOSE SAVE
5
Update Tables Fields
2
12
Role Creation
1. Object Authorization You can authorize for
any objects such as table, field, query, report
and infosphere objects
1
3
Drag
2
13
Role Creation

• 2. Function Authorization
• After this authorization, the role should not be
  allowed
• Print and export any query or report files

2
5
3
4
Drag
1
14
Role Creation
3. Table Constraint
The role can only see product data which
productID between 10 and 20
5
4
Close and Save
2
3
Drag
Highlight table
1
15
Create New User
2
Set User Name and Password
1
3
Drag the predefined Role into the role
authorization
Logout and login with NEWUSER User ID
NEWUSERPassword test
4
16
View of NEWUSER
Open Table Products
1
Export the table
2
Only Product 11 to 19 are shown and export cannot
be performed. Refer Slide 13, 14
17
Connection of Fact and Dimensional tables
2
Drag Tables Orders, Products, Customers,
Employees
1
18
Connection of Fact and Dimensional tables
Drag
3
4
(right click) gt Delete all Relations Connect the
tables as shown
19
Insert Data Object in Infosphere
5
20
6
7
Drag
Drag from Relational Datasource of Northwinde
(OrderDetails) in System Repository
8
Task 1 Create another data object TotalPrice
under Orders
21
Insert Data Object in Infosphere
Task 2 Create another data object Amount under
Orders
22
Create New Cube
Get Multi-dimension data from database
1
Right Click Cubes to Create NewCube
3
2

• Drag
• OrderDate (from Orders) to Dimensions
• ShipRegion (from Orders) to Dimensions
• CompanyName (from Customers) to Dimensions
• Amount (from Orders) to Measures
• Freight (from Orders) to Measures

23
Create Multi-Dimensional Report (MD Report)
24
Create New MD Report
1
Right click to create new folder COMP417
2
Right click folder COMP417 New gt MDReport
25
Create New MD Report - import cube to create a
new MD Report
Drag
26
Create New MD Report
Re-order the Dimensions OrderDate ? CompanyName
? ShipRegion
27
Create New MD Report - add measure (Amount
Percentage)
2
1
Highlight Amount column
3
28
Create New MD Report - Calculate average measure
values of 3 years
1
Highlight all 3 rows
2
4
3
Highlight the row to rename
29
Create New MD Report
Double Click the Dimensions and Measures one by
one
30
Drill up/down in Charts
31
Drill up/down in Charts
Full Screen
4
1
2
Right Click to edit Chart Type
3
32
Drill up/down in Charts
Choose different measure
Double click to Drill Down in Dimension
33
Drill up/down in Charts
Right click to Drill Up/Down in/between Dimensions
34
Drill up/down in Charts
Drill Down between Dimensions
35
Query
2
1
36
Query
Drag
37
Query
2
Double Click
1
3
38
Query
2
Double Click
1
Rename
3
39
Query
3
Create a Union query
1
Drag
2
4
In the union query, drag the same query
objects. Modify the following
40
Query
Return to form view
1
Refresh data
Price after adjustment is 50
Price after adjustment is 80
41
Create DynaReport
42
Create DynaReport
43
Create DynaReport
44
Create DynaReport

• Drag from NewInfosphere
• CompanyName (Customers)
• Region (Customers)
• Country (Customers)
• PostalCode (Customers)
• Amount (Orders)

45
Create DynaReport
2
1
3
Close and Save
46
Create DynaReport
1
2
Drag
3
47
Create DynaReport
1
3
Drag
2
Place in the report area
48
Create DynaReport
4
1
Double click a column
2
Resize the column width
3
Right click the Amount column
49
Create DynaReport
1
Create new text
Right click the data table and edit Layout Item
Properties
2
50
Create DynaReport
2
1
Drag
51
Today, youve learnt

• Database Connection
• Role Creation
• Create New User
• Connection of Fact and Dimensional tables
• Create New Data Object in Infosphere
• Create Cube
• Create Multi-Dimensional Report
• Drill up/down in Charts
• Query
• Create DynaReport
• You may find more demos in
• http//www.bluequery.com.cn/et

52

• QA
• session