Transcript Online Analytical Processing

Business Intelligence - 1
BUS 782
Topics
• Scenario Management
• Chart
• Online Analytical Process, OLAP
– Excel’s Pivot table/Pivot chart
• Import/Export Data
Scenario
• A scenario is an assumption about input variables.
• Excel’s Scenarios is a what-if-analysis tool. A scenario
is a set of values that Microsoft Excel saves and can
substitute automatically in your worksheet.
• You can use scenarios to forecast the outcome of a
worksheet model. You can create and save different
groups of values on a worksheet and then switch to
any of these new scenarios to view different results.
• Data/What If analysis/Scenario
Creating a Scenario
– Add scenario
• Changing cells
– Scenario Summary
• Resulting cells
• Demo: benefit.xls
Charts
Charting Decision Rules
• An Internet Service Provider charges
customers based on hours used:
– First 10 hours
$15
– Each of the next 20 hours $2 per hour
– Hours over 30 hours
$1 per hour
Comparing Decision Rules
• Plan 2:
– First 20 hours:
– Hours over 20
$20
$1.5
• Plan 3:
– $35 unlimited access.
Charting Functions
• Demand function:
– P = 150 – 6*Q^2
• Supply function:
– P = 10* Q^2 + 2*Q
• Note:
– Positive area
– Value axis maximum/minimum value:
• Format Value Axis
Chart Stock Market Data
• Download Dow Jones Historical Data
– Yahoo/Finance/Dow/Historical Prices
– Enter criteria
– Copy/Paste to Excel
• To chart:
– Insert/Chart/Other Charts/Stock chart
On-Line Analytical Processing (OLAP) Tools
• The use of a set of graphical tools that provides users
with multidimensional views of their data and allows
them to analyze the data using simple windowing
techniques
• OLAP Operations
–
–
–
–
Cube slicing–come up with 2-D view of data
Drill-down–going from summary to more detailed views
Roll-up – the opposite direction of drill-down
Reaggregation – rearrange the order of dimensions
Slicing a data cube
Summary report
Example of drill-down
Starting with summary
data, users can obtain
details for particular
cells
Drill-down with
color added
Excel’s Pivot Table
• Insert/Pivot Table or Pivot Chart
– Drill down, rollup and reaggregation
– Filter
• Pivot Chart
– Filter
– Drilldown, rollup, reaggregation
• Import queries from Access to perform analysis.
– Sales related to: Customer’s location, Rating and
Products
Access Tools for Import/Export
• External Data
– Import
– Export
• Exchange data between:
– Other Access databases
– Excel
– Text file
– XML
– ODBC data sources
Data Exchange with Excel
• Import from Excel:
– The first row of Excel’s list should contain field
names
• Or define a name for the list range.
– External Data/Import/Excel
• Export to Excel:
– External Data/Export/Excel
• Note: Saved imports/exports
Open Database Connectivity (ODBC)
• Provide a standard to retrieve data from a
database.
• It manages one or more "database drivers“
that enables the communication between
database and applications.
• To access a database, we use ODBC facilities to
define a ODBC data source name for the
database.
ODBC Demo:
Connecting to SQL Server
• Define an ODBC data source name:
– Control Panel/Administrative tools/Data
source(ODBC)
– Note: ODBC32
• Import from an ODBC data source
– External Data/Import/ODBC data source
Export/Import XML File
• Export:
– External Data/More/XML
– XML Data file
– XML schema file
• Import:
– External Data/Import XML
Excel’s Tools for Import/Export
• Data/Get External Data
– From Access
– From Web
– From Other Sources
• ODBC
• XML
– Existing Connections
• Saved Queries
• Save Query
• Refresh: Data/Refresh All
– Compare the difference between Copy/Paste and Import