business intelligence - 1 bus 782. topics scenario management chart online analytical process, olap...
Post on 28-Dec-2015
213 Views
Preview:
TRANSCRIPT
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: $20– Hours over 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
Example of drill-down
Summary report
Drill-down with color added
Starting with summary data, users can obtain details for particular cells
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
top related