1 / 19

Business Intelligence - 1

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.

pollackj
Download Presentation

Business Intelligence - 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Business Intelligence - 1 BUS 782

  2. Topics • Scenario Management • Chart • Online Analytical Process, OLAP • Excel’s Pivot table/Pivot chart • Import/Export Data

  3. Scenario • A scenario is an assumption about input variables. • Excel’s Scenarios isawhat-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

  4. Creating a Scenario • Add scenario • Changing cells • Scenario Summary • Resulting cells • Demo: benefit.xls

  5. Charts

  6. 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

  7. Comparing Decision Rules • Plan 2: • First 20 hours: $20 • Hours over 20 $1.5 • Plan 3: • $35 unlimited access.

  8. 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

  9. 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

  10. 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

  11. Slicing a data cube

  12. Summary report Example of drill-down Starting with summary data, users can obtain details for particular cells Drill-down with color added

  13. 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

  14. Access Tools for Import/Export • External Data • Import • Export • Exchange data between: • Other Access databases • Excel • Text file • XML • ODBC data sources

  15. 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

  16. 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.

  17. 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

  18. Export/Import XML File • Export: • External Data/More/XML • XML Data file • XML schema file • Import: • External Data/Import XML

  19. 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

More Related