Spreadsheet and Database Choice Backing

Presentation Transcript

Spreadsheet and Database Decision Support Power Excel and Access for Business Analysts

High Level Structure Data Warehousing (Access) Data Cleaning and Transformation (Excel) Online Analytical Processing (OLAP) (Excel) Session 1 Advanced Data Querying and Analysis (Excel and Access) Session 2 Models and Modeling (Excel) Building DSS With VBA (Excel) Take MIS 436

Module Overview and Session Themes Data is great. Information is frequently insufficient , require models as well. Models+Data+VBA = Decision emotionally supportive network

More Themes Spreadsheets are true the most broadly utilized stage for displaying and examination in business today Excel has rich arrangement of demonstrating and investigation apparatuses End client DSS improvement A completely open door for fame "Things ought to be made as straightforward as could be expected under the circumstances, yet no simpler." Albert Einstein

Data Extraction, Transformation, and Loading Playing in the Mud

The Many Roles of Knowledge Workers Brilliant IS thought

The Gist of the Problem Getting information out of some framework to: Analyze it (e.g. Exceed expectations, Access, details bundle) Get it into another framework (e.g. ERP or Data Warehouse) "Brilliant" control of electronic reports with installed information Don't have any desire to do it physically (why?)

ACD Report Example Report Header Date Split Blank lines Data! Aggregates Lines Next report

Why Talk About This? Deloitte & Touche – Data Quality and Integrity Beaumont Oracle ERP extend Huge measure of time squandered doing manual handling Really valuable spreadsheet and database aptitudes (and outlook) It's amusing to play in the mud once in a while

OK, Let's begin with a little toy information extraction issue Download PatientLocationLog.txt from the ATiB Downloads area (right snap – Save Target As… ) Somehow, get it into Excel so you can examine it This is a table of sections of patients to different areas in a facility. Take note of that every patient may visit various areas.

About the Data A patient sort code Location ID When patient entered and left the area. One of a kind Patient Arrival ID

Your Challenge what number times did a patient with PatientType=2 enter Location 38? In the event that you understand that, by what means may you rapidly discover what number of patients of every sort entered every area? What date, time and day of the week did the primary patient land to area 31? What amount of aggregate time (in minutes) did the patient with ArrivalID=16 spend in the facility?

Data Warehousing and OLAP Data is great A Dimensional Modeling Manifesto An Overview of Data Warehousing and OLAP Technology

What is a Data Warehouse? Subject situated Integrated Time-Variant Nonvolatile Data enters DW from operational environment, exchange preparing frameworks (TPS). Information is: Source: Inmon, W.H., "What is a Data Warehouse?", PRISM, Vol 1. No. 1 http://www.cait.wustl.edu/cait/papers/crystal/vol1_no1/

Source: Inmon, W.H., "What is a Data Warehouse?", PRISM, Vol 1. No. 1. Subject Orientation TPS sorted out around procedures, capacities charging, saving money, obtaining, finance, and so forth. DW composed around subjects clients, merchants, experiences, deals Transactions TPS forms exchanges DW stores rundown information identified with exchanges TPS - keeps information required for exchange DW - keeps information required for examination

Source: Inmon, W.H., "What is a Data Warehouse?", PRISM, Vol 1. No. 1 2. Reconciliation DW must incorporate information from various applications Create consistency crosswise over applications naming traditions estimation of factors (units) information sorts encoding DSS examiner - utilize the information, not stress over believability/consistency of information frequently best individual to discover unobtrusive information issues

Source: Inmon, W.H., "What is a Data Warehouse?", PRISM, Vol 1. No. 1 3. Time Variancy TPS precise at snapshot of get to DW exact starting some minute in time Operational - current esteem information Data distribution center - depiction information Time skyline 5-10 years Key contains a component of time Once preview made, information can't be overhauled Time skyline 60-90 days Key could conceivably have a component of time Data can be redesigned

4. Nonvolatility Change Replace Insert Load Access Delete Change Operational Data distribution center Source: Inmon, W.H., "What is a Data Warehouse?", PRISM, Vol 1. No. 1

The DW and OLAP Value Chain

Multi-dimensional Data Modeling Designed to encourage investigation (not exchanges) Common in information warehousing Intuitive idea of numerous measurements or viewpoints on business measures or truths see deals from client, item and time point of view Conceptual model Hypercube – a "n-sided 3D shape"

Data (Hyper) blocks 2-d to 3-d 3D square Rotating the 3D shape

A Call Center Example Tech Support for MS Office Technology empowered business forms Massive measure of information caught by ACD Some information examination done by ACD Difficult operational inquiries identified with staffing/planning sway on administration level Created recreation model to produce parcels o' information Many call focuses in numerous enterprises

Steps in Multi-dimensional Modeling Call Center Example Choose business handle Servicing specialized bolster calls Choose grain of process Individual telephone calls Choose measurements Customer, application, issue, time Choose measured realities time on hold, benefit time of call

Time measurement Customer measurement Fact table Problem measurement Application measurement The Star Schema A multi-dimensional information display Non-standardized Non-standardized Non-standardized Normalized Non-standardized

A Sales Star

Transaction centered Focus on numerous connected, standardized tables One major complex information show Very little excess Analysis centered Normalized certainty table joined to a couple exceptionally non-standardized measurement tables Many straightforward, natural information models Lots of repetition Data Models Relational versus Multi-dimensional

One E-R versus Numerous Stars Analysis center Transaction center One E-R show for all the business procedure. One star for every displayed business prepare.

What is OLAP? Programming device giving multi-dimensional perspective of information for business examination Example of "Choice Support" or "Business Intelligence" device Fast information get to and quick calculations Interactive, adaptable UI "Cut, dice, bore down" Excel Pivot Table and Pivot Chart

Data Warehousing and OLAP WWW Resources A Dimensional Modeling Manifesto – Kimball, R. http://www.dbmsmag.com/9708d15.html Kimball and Associates http://www.ralphkimball.com./html/articles.html DSS Resources http://dssresources.com/Data Warehousing Information Center http://www.dwinfocenter.org/Intelligent Enterprise http://www.intelligententerprise.com/

Some Good Books The Data Warehouse Toolkit – Kimball, R. Conclusive OLAP Solutions – Thomsen, E. Conclusive Unlocking OLAP with Microsoft SQL Server and Excel 2000 – Freeze For amateurs Microsoft OLAP Unleashed – Peterson and Pinkelman For techasauruses

CallCenter_Scenario01_DW.zip CallCenterPivot.zip Let's OLAP Download and unfasten the accompanying from the ATiB page open from my landing page: http://www.sba.oakland.edu/staff/isken/isken.htm http://www.sba.oakland.edu/personnel/isken/ATiB406_Downloads.htm Let's take a gander at Excel Pivot Tutorial