Please enable JavaScript.

Coggle requires JavaScript to display documents.

business analytics (Relational Databases and Introduction to Data…

- - - - Read: SELECT
        select column(s)
        from table(s)
        where condition(s)
      - Aggregate Functions
        GROUP BY
        ORDER BY
        LIMITS
      - JOINS - match the records from A table to the B
        
        Natural Join (Same Column Name)
        
        Inner Join (Different Column Name)
  - - - Create, Store, Organize and retrieve data from one or many databases( a software application)
      - query language
  - - - Entity Relationship (ER) Diagram
        
        A semantic, graphical data model
        Visually expresses business riles
        A Technology independent communication tool between business people and IT people
        
        Building Blocks 3
        
        Entities: Terms/ Nouns
        
        Attributes
        
        Relationships: Verbs
        
        ER Process
        
        Identify entity types
        
        Properties of characteristics of entities
        
        should not change 0ver ages
        unique, non-null values
        use as few attributes as possibile
        
        Identify relationships
        
        PK
        
        Every record must have a key
        
        Often Numeric
        
        Foreign Key
        
        Non-key attribute
        
        Composite Key
        
        Consists of two of more attributes
        
        Identify constraints
        
        Two Integrity Rules
        
        Safeguard Keys
        
        Entity Integrity
        
        Referential Integrity
        
        Iterate
        
        Purpose
        
        to understand and describe data requirements
        
        to communicate requirements between various stakeholders
        
        to form a basis for database and IT system design
        
        Quality of ER Models
        
        Correct
        
        Complete
        
        Simple
        
        Understand
        
        Flexible
        
        Integrity
        
        Integration
        
        Implementability
      - Star Schemas
- - - - Executives
        
        ( Summative and Global)
        
        Tools
        
        DSS Application(EIS)
        
        Managed Query
        
        Interested in business Indicators
      - Support Staff
        
        Tools
        
        Managed Query
        
        Pro-formatted Reporting
        
        Interested in Current Data
      - Analysts
        
        Tools
        
        Ad Hoc Query
        
        Enhanced Reporting
        
        Managed Query
        
        Interested in Routine Analysis
      - Skilled Analysts
        
        Tools
        
        Ad Hoc Query
        
        Complex Analysis
        
        Application Builder
        
        Interested in Highly ad hoc Analysis
      - Special Purpose Analysts
        
        Complex and Specific
        
        Interested in Knowledge Discovery
        
        Tools
        
        Knowledge Discovery (Data Mining)
    - - Pre-defined Queries ans Report
        
        Set of query templates and report templates
        
        Users add parameters and hit GO
        
        Users may (not) have permission to alter the templates
      - Ad Hoc Constructions
        
        Usually only power users use this functionality
        
        Users create reports and queries as needed
      - Pre-processed Information
        
        Routine Information already created and available
        
        eg. Sales reports, summary reports, routine charts ...
      - Applications
        
        Data feed to downstream decision support application is very common
        
        Feeds to other specialized applications on a user group basis is also common
        
        eg. In house application for the sales group
    - - Casual / Novice
        
        Intuitive Interface Needed
        
        The Information Delivery Prompts the User
        
        "Big" Navigation button (Click on things)
      - Regular
        
        Daily User
        
        Uses Predefined Reports and Queries to access data
        
        needs query templates
      - Power
        
        Highly Proficient with Technology
        
        Create Reports and queries to access data
        
        Write Marcos and Scripts and import data into application
    - - Business Analysts
        
        Business Planners
        
        Department Management
        
        Senior Executives
        
        Sales Managers
        ...
  - - - Sub-setting
        
        Slicing
        
        Selecting a specific time-slice, region, or product
        
        Set dimensions to specific value
        
        Restricting a dimension
        
        Subset of dimensions
        
        Dicing
        
        Carving the cube into smaller cube
        
        Replace a dimension with a subset of values
        
        Dice operation often follows a slice operation
        
        Dice using the result of the previous slice operation
      - Navigation
        
        Drill-down
        
        More detailed level
        
        Specific event / transaction cubes enable drilling down
        
        Drilling can end in the atomic transaction or lead to more specific summary facts
        
        Drilling may also end in an entity's facts around dimensions
        
        Roll-up
        
        More general level
        
        Summarized Data
        
        Aggregate along hierarchies of dimensions
        
        Navigating to a more general level
        
        Measure values calculated every time
        
        Drill-across (MAY)
        
        Different cubes may share dimensions
        
        Dimensions may be at different levels of granularity for different purpose
        
        Shared dimensions often called 'conformed'
      - Pivoting
        
        Presenting the N-dimension cube in various ways, rotating view between rows, columns, pages
        
        Rearrange dimensions
        
        Presenting data with new views
    - - Concepts
        
        Enables OnLine Analytical Processing (OLAP):
        
        An approach for processing analytical queries that are dimensional in nature
        
        Transaction databases are called OLAP: Online Transaction Processing
        
        Business Data can be visualized as Cubes that can split into sub-cubes
      - Representing
        
        Facts( Measures)
        
        Dimensions with Hierarchical
  - - - Tabular/ Structured
        
        Proforma Tabular reports which conform to an expected structure
        
        eg. Profit & Loss, Balance Sheet
      - Dashboard-Type Reports
        
        Graphical presentation of several performance indicators in a single page using dials/gauges
        
        Customized for a specific person, group or function
        
        Focus on monitoring and measuring
        
        Small, concise, clear, intuitive and often interactive display
        
        Consists of a combination of charts and tables
      - Balanced Scorecard- Type Reports
        
        Include financial, customer, business process, and learning & growth indicators
        
        Views the org. from 4 perspectives
        
        Financial
        
        Effective Resource Use
        
        Financial Performance
        
        Customer/ Stakeholder
        
        Customer Value
        
        Satisfaction and/or Retention
        
        Internal Process
        
        Efficiency
        
        Quality
        
        Organizational Capacity of learning and Growth
        
        Human Capital
        
        Infrastructure & Terminology
        
        Culture
        
        Develops objectives, KPIs, targets, and initiatives relative to each perspective
        
        A strategic tool to connect high-level business strategy to bottom line performance
    - - Fulfill Functions
        
        To ensure proper department functioning
        
        To provide information
        
        To provide the results of an analysis
        
        To persuade others to act
        
        To create an organizational memory
      - Data Acquisition (Internal & External)~>
        Information Processing & Generation~>
        Decision Making~> Process Management
      - Any Communication Artifact
        (prepared to convey specific information)
        
        Format: Text+tables+graphs/charts
        
        Distribution: in-print, email, portal/intranet
    - - Enable by tracking Key Performance Indicators (KPIs)
      - KPI is a measure of how effectively a company is achieving key business objectives
      - Processes and tools for measuring, monitoring, and managing business performance
      - KPIs are generally communicated using business reports
  - - - A tool to interact with data and understand patterns in data
      - A visual representation of data to communicate a particular message
    - - Principle 4: Be consistent in formatting
        
        Line charts may include 0
        
        Line graphs can obscure important patterns if their axes that start from 0
        
        Choose the right scaling for axis
      - Principle 5: Choose the right type of visualization
        
        Example
        
        Column Chart; Scatter Plot Chart;
        Line Chart; Dual Axis Chart;
        
        Area Chart; Stacked Bar Chart; Pie Chart;
        
        Bubble Chart; Waterfall Chart; Funnel Chart; Bullet Chart;
        
        Heat Map
        
        Visualization Types - Graphs
        
        The message is contained in the shape of the values
        
        The Objective is used to reveal relationships among multiple values
        
        3 Steps
        
        Values arranged in a grid forma
        
        Values are encoded in text
        
        Rows and columns can be defined by grid lines or white spacet
      - Principle 3: Be consistent in formatting
        
        Be consistent across various charts
        
        Bar chart axes should include zero
        
        Choose the right scaling for axis
      - Principle 6: Save the Pies for Dessert
        Avoid Pie Chart
      - Principle 7: Don't use 3D charts
        
        Require A lot of Visual Processing
        
        Stage 2: Pattern Perception
        
        Stage 3: Image Interpretation
        
        Stage 1: Parallel Image Processing
      - Principle 8: Be Minimalistic
        
        Reduce the non-data ink
        
        Enhance the data ink
      - Principle 2: Correlation is not causation
        
        Correlation ——the value of one variable increases or decreases so does the value of the other variable
        
        Causation—— one event is the result of the occurrence of the other event
      - Principle 9: Tell a Story
        
        Communicate the big picture
        
        Connect to a particular context
        
        Frame a business problem
        
        Highlight important drivers
      - Principle 1. Don't lie with visualizations
        
        Visuals communicate particular messages
        
        Choose the measures/indicators very careful
        
        Do not visualize relationships that you cannot explain
- - - - consistently superior performance, ROI
        
        compared to competitors
        
        based on unique resources that cannot be easily imitated, long-lived,sustainable
      - innovation key source of CA
        
        product/service and process innovation
        
        innovation process, information, R&D investment
        
        irreversible resource commitment, asset renewal
    - - competing with strategic data
        
        complexity of the business world
        
        global environment
        
        information drives the bottom line
        
        Strategic information becomes important in creating Competitive Advantage
        
        Who needs Strategic Information
        
        Formulate business strategy; Establish goals;
        Set objectives; Monitor result
        
        Usually the Executive and Manager levels (extending)
        
        Theory: Information that helps to make decisions on the formulation and execution of business strategies and objectives
        Not information for daily business operations
        
        Characteristics
        
        Integrated: A single view of the firm. An enterprise wide view
        
        Data Integrity: Accurate and conform to business rules
        
        Accessible: Easily accessible, intuitive access, responsive analysis
        
        Credible: Trusted values - every business value has = 1 value
        
        Timely: Must be available with the correct timeliness for the data
      - How a company can get ahead
        
        become an data focused competitor
        
        use sophisticated data-collection technology and analysis
        
        don't only know what your customers want
        Know how much they will pay and how to keep them loyal
        
        don't just track existing inventories
        Predict and prevent future inventory problems
      - Impossible to differentiate one company from their competitors based on products alone
        
        Similar offerings
        
        cheap offshore labour
        
        hard pressed to beat overseas competitors on product cost
  - - - Data Staging
        
        Store, conform and put information in the Data Warehouse
        
        Prepare Data for DW upload
        
        Remember sources and formats
        
        Need space to store data while we play
        
        ELT (3 steps)
        
        Extraction;
        Transformation;
        Loading
      - Source Data System
        
        Internal Data - Operational Systems Producing Data
        
        CRM
        Sales
        Production
        Finance
        
        External Data
        
        Social Media
        Stock Markets
        News
        Currency
        ABS
        
        API's allow for the extraction of data from external systems
      - Data Storage
        
        Meta-Data
        
        A Data Warehouse
        
        Data Marts
      - Analytics and Reporting
        
        visualized for the user
        
        further analyzed to create insights
        
        Diagnostic analytics
        
        Predictive analytics
        
        Prescriptive analytics
        
        Descriptive analytics
  - - - Harrah's, Capital One
    - - Barclays
    - - Dell, Wal-Mart,
        Amazon
    - - Progressive, Marriott
    - - New England Patriots
    - - Honda, Intel
    - - MCI, Verizon
    - - Novartis, Amazon, Yahoo
- - - - Time Variant Data: (current true data, historical data, snapshots of the current value)
      - Non Volatile Data: ( No daily Update but periodically, Store extracted data snapshots, Only read access to users)
      - Integrated Data: ( systems, formats, validated and high Data quality)
      - Data Granularity: (operational systems, Informational systems, Data granularity- the level of detail)
      - Subject Oriented Data
        
        Operational Applications
        
        Order Processing
        
        Supplier Orders
        
        Saving Accounts
        
        Accounts Receivable
        
        Customer Billing
        
        Loans Processing
        
        Data Warehouse Subjects
        
        Supplier
        
        Sales
        
        Claims
        
        Product
        
        Customer
        
        Account
      - Supports Management needs
        
        used by end users
  - - - Operational Qs
      - Analytical Qs
    - - Transactional
        
        Recording orders
        
        Processing claims
        
        Making shipments
        
        Generating invoices
        
        Receiving cash
        
        Reserving airline seats
      - Informational (scope and purpose)
        
        Show the top products
        
        Tell me why
        
        Alert if calls are high
        
        Focus on Getting information at a higher level suitable for decision-making
  - - - Choose a Business Process ( Fact table)
        
        Choose the grain of the fact table
        
        Choose the dimensions
        
        Choose the measured facts_ Numeric, additive quantities
        
        Complete the dimension tables
    - - Essentially a simple and restricted type of ER madel
      - Consists of
        
        A fact table
        
        Several dimensional tables
        
        Hierarchies in the dimensions
    - - easy for end users and to be understandable
      - optimize query performance ( Not update Performance)
- - - - High-level Star Schema Design
        
        Identity Star Schema Required
        
        Define level of summarization
        
        Identify relevant dimensions
      - Detailed Fact Table Design
        
        Define Key
        
        A composite key consisting of the keys of all dimension tables
        
        Transaction level key for lowest level of granularity
        
        Define Facts
        
        The measures that can be analyzed using numerical functions
        
        Use additive facts(Used to prevent errors in queries), but may also be semi additive
      - Classify Entities
        
        Transaction Entities
        
        Component Entities
        
        Classification Entities
      - Detailed Dimensional Table Design
        
        Define dimensional key
        
        Collapse hierarchies
    - - Chunking
      - Hierarchical Structuring
        
        Used to manage complexity
        
        Each dimension consists >=1 hierarchies
        
        Provides the means to "roll up" & "grill down" in OLAP tools
  - - - Integrated DW
    - - Create a new dimension record(usually the best approach)
        
        Result: Provides complete history
        
        eg. Customer address changes
        
        Advantages: Maintains and partitions history
        
        Disadvantage: More complex than Overwrite
      - Create a current value field
        
        Segments history between the old and the new description
        
        eg. Change in sales districts
      - Overwrite old values
        
        Result: History is lost
        
        ag. Error, history doesn't matter
    - - Transaction (Quantity, Extended Price)
        Usually additive measures
        Track details of transactions
        eg. Typical: sales, web page hits, purchases
      - Snapshot(Balance, Transaction Count)
        Usually semi-additive
        Also called inventory level fact table
        eg. Inventory, account, account receivable, accounts payable
      - Factless
        Event occurence
        No measures
        eg. attendance (work, school...), room reservation
    - - Ways of reducing combinations
        
        Use discretely valued attributes
        
        Group continuously valued attributes into bands
      - Features of a Good Dimensional Model
        
        Simple: 5-9 dimensions
        
        Understandable: user-oriented data names and values
        (descriptive text rather than codes)
        
        Complete: all relevant dimensions and facts
        
        Appropriate Level of Dimensions
        
        Integrated: conformed dimensions
        
        Error Proof: avoid errors in queries by using additive facts
        ( not mixing levels of aggregation)
      - Contain a subset of attributes that can be efficiently browsed
    - - Additive measures can be summed across any of the dimensions associated with them
      - Semi-additive measures can be summed across some dimension, but not all
      - Some measures are completely non-additive, such as ratios