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