Data Warehouse & Business Intelligence

Lectures & Data Lab

Note:

-An R Notebook is an R Markdown document with chunks that can be executed independently and interactively, with output visible immediately beneath the input.

-Notebook output are available as HTML, PDF, Word, or Latex.

-This Notebook as HTML is preferably open with Google Chrome.

-R-Code can be extracted as Rmd file under the button “Code” in the notebook.

-This Notebook using iterative development. It means the process starts with a simple implementation of a small set of idea requirements and iteratively enhances the evolving versions until the complete version is implemented and perfect.

Glossary:

• BI-> Business Intelligence
• DBMS-> Database Management System
• RDBMS-> Relational Database Management System
• SQL-> Structured Query Language
• OLTP-> Online Transactional Processing. OLTP typically involves inserting, updating, and/or deleting small amounts of data in a database.
• OLAP-> Online analytical processing.

Overview

1. References:

• Feel free
• This modul (only for test)

2. Methods: ENJOY THE MOMENT

3. Tools:

• Trello Apps-> please Sign up the Trello (trello.com or App Store: Trello)
• Who is the admin? as a gift: 5% additional point

4. Contents:

• Lectures
• Data lab (implementation of software)
• MEQA (Mock Exams Questions and Answers)
• Test-> one BI project 50% + (Multiple Choice AND/OR Explanation Test AND/OR Use Case) 50%
• One optional BI project (10%)

5. Software: (On Premises & Cloud)

Note! The biggest difference between these two systems is how they are deployed. Cloud-based software is hosted on the vendor’s servers and accessed through a web browser. On-premise software is installed locally, on a company’s own computers and servers.

• Excel Business Inteligence
• Power BI
• Tableau
• Pentaho
• Sisense
• Alteryx
• Looker
• Oracle BI
• MySQL BI
• Microsoft SQL Server
• SAP BI
• MicroStrategy
• TIBCO Spotfire
• Domo BI
• QlickView
• SAS BI
• Klipfolio
• Geckoboard
• Hadoop
• Jaspersoft
• Zoho Analytics
• PostgreSQL
• IBM Db2
• Mongo DB . . .etc

6. We need data!

• https://opendata.socrata.com/
• https://www.data.gov/
• https://data.worldbank.org/
• https://academictorrents.com/
• https://data.world/
• https://www.quandl.com/
• https://archive.ics.uci.edu/ml/index.php
• https://www.kaggle.com/
• https://data.go.id/dataset?q=subang
• http://data.jakarta.go.id/
• http://data.bandung.go.id/
• https://opendata.subang.go.id/
• http://satudata.jabarprov.go.id/#

7. Extras:

• How to choose the one that fit our needs!

1. Focus on Your Business
2. Analyze Your Needs
3. Do Research
4. Don’t Scrimp
5. Ask for Help
6. Customize Apps for Your Needs
7. Integrate Everything
8. Get Everyone Onboard
9. Communicate, Communicate, Communicate
10. Share Your Favorite Software

• Anybody wants to get certificate? (optional)

1. https://www.edx.org/
2. https://www.lynda.com/
3. https://www.udacity.com/
4. https://www.udemy.com/
5. https://cognitiveclass.ai/

Database

What is a Data?

• Data is a collection of facts, such as numbers, words, measurements, observations or even just descriptions of things.

• In general, data is any set of characters that is gathered and translated for some purpose, usually analysis. It can be any character, including text and numbers, pictures, sound, or video. If data is not put into context, it doesn’t do anything to a human or computer.

• Data can be qualitative or quantitative. Put simply: Discrete data is counted, Continuous data is measured.

  • Qualitative data is descriptive information (it describes something)
  • Quantitative data is numerical information (numbers)-> Discrete(5,6,10); Continuous (3,14)

• Example: What do we know about Indonesia’s flag, length=width 10 cm, white and red color?

#Qualitative: The flag has white and red color

#Quantitative: The flag has length 10 cm and width 10 cm (only quantitative discrete)

What is a Database?

• A database is a collection of related data which represents some aspect of the real world. A database system is designed to be built and populated with data for a certain task.

• A database is an electronic administration system that has to handle large amounts of data efficiently, without contradiction, permanently and can represent logical relationships digitally.

• A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database.

• Data within the most common types of databases in operation today is typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data.

• A database typically requires a comprehensive database software program known as a database management system (DBMS). A DBMS serves as an interface between the database and its end users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized. A DBMS also facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, and backup and recovery. Some examples of popular database software or DBMSs include MySQL, Microsoft Access, Microsoft SQL Server, FileMaker Pro, Oracle Database, and dBASE.

What Is Structured Query Language (SQL)?

• SQL is a programming language used by nearly all relational databases to query, manipulate, and define data, and to provide access control. SQL was first developed at IBM in the 1970s with Oracle as a major contributor, which led to implementation of the SQL ANSI standard, SQL has spurred many extensions from companies such as IBM, Oracle, and Microsoft. Although SQL is still widely used today, new programming languages are beginning to appear (example: NoSQL).

What’s the Difference Between a Database and a Spreadsheet?

Databases and spreadsheets (such as Microsoft Excel) are both convenient ways to store information. The primary differences between the two are:

• How the data is stored and manipulated
• Who can access the data
• How much data can be stored

Spreadsheets were originally designed for one user, and their characteristics reflect that. They’re great for a single user or small number of users who don’t need to do a lot of incredibly complicated data manipulation. Databases, on the other hand, are designed to hold much larger collections of organized information-massive amounts, sometimes. Databases allow multiple users at the same time to quickly and securely access and query the data using highly complex logic and language.

What is DBMS?

• Database Management System (also known as DBMS) is a software for storing and retrieving users’ data by considering appropriate security measures. It allows users to create their own databases as per their requirement.
• It consists of a group of programs which manipulate the database and provide an interface between the database. It includes the user of the database and other application programs.
• The DBMS accepts the request for data from an application and instructs the operating system to provide the specific data. In large systems, a DBMS helps users and other third-party software to store and retrieve data.
• A database typically requires a comprehensive database software program known as a database management system (DBMS). A DBMS serves as an interface between the database and its end users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized. A DBMS also facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, and backup and recovery.
• Some examples of popular database software or DBMSs include MySQL, Microsoft Access, Microsoft SQL Server, FileMaker Pro, Oracle Database, and dBASE.

Example of a DBMS (exactly: RDBMS, see on the types of databases)

Let us see a simple example of Unsub database. This database is maintaining information concerning students, courses, and grades in a university environment. The database is organized as five files:

• The STUDENT file stores data of each student
• The COURSE file stores contain data on each course.
• The GRADE file stores the grades which students receive in the various sections
• The TUTOR file contains information about each tutor.

->To define a database system:

1. We need to specify the structure of the records of each file by defining the different types of data elements to be stored in each record.

2. We can also use a coding scheme to represent the values of a data item.

3. Basically, your Database will have 5 tables with a foreign key defined amongst the various tables.

->Example: Create the Unsub database with the following data (see the above pict. as example)

STUDENT: A, B, C, D, E

COURSE: Math, Bio, Art, BI, Data

GRADE: 100, 90, 80, 50, 95

TUTOR: F, G, H, I, J

#See on the WHITEBOARD!

History of DBMS

Here, are the important landmarks from the history:

• 1960 - Charles Bachman designed first DBMS system
• 1970 - Codd introduced IBM’S Information Management System (IMS)
• 1976- Peter Chen coined and defined the Entity-relationship model also know as the ER model
• 1980 - Relational Model becomes a widely accepted database component
• 1985- Object-oriented DBMS develops.
• 1990s- Incorporation of object-orientation in relational DBMS.
• 1991- Microsoft ships MS access, a personal DBMS and that displaces all other personal DBMS products.
• 1995: First Internet database applications
• 1997: XML applied to database processing. Many vendors begin to integrate XML into DBMS products.
• 2000s: New interactive applications were developed for PDAs, point-of-sale transactions, and consolidation of vendors. Presently, the three leading database companies in the western world are Microsoft, IBM, and Oracle.
• Today, databases are everywhere and are used to enhance our day-to-day life. From personal cloud storage to predicting the weather, many of the services we utilize today are possible due to databases. Presently, there are many new players in the non-relational database space offering specific solutions. Some of the current relational databases include giants such as Oracle, MySQL, and DB2.

The boom in unstructured data that the world has seen in the last few years is one of the main reasons relational databases are no longer sufficient for many companies’ needs. It comes up non-relational database (not in this lecture!).

Characteristics of Database Management System

• Provides security and removes redundancy
• Self-describing nature of a database system
• Insulation between programs and data abstraction
• Support of multiple views of the data
• Sharing of data and multiuser transaction processing
• DBMS allows entities and relations among them to form tables.
• It follows the ACID concept ( Atomicity, Consistency, Isolation, and Durability).
• DBMS supports multi-user environment that allows users to access and manipulate data in parallel.

DBMS vs. Flat File

Popular DBMS Software

Here, is the list of some popular DBMS system:

• MySQL
• Microsoft Access
• Oracle
• PostgreSQL
• dBASE
• FoxPro
• SQLite
• IBM DB2
• LibreOffice Base
• MariaDB
• Microsoft SQL Server etc.

Application of DBMS

Types of Databases

There are many different types of databases. The best database for a specific organization depends on how the organization intends to use the data.

• Relational databases. Relational databases became dominant in the 1980s. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information.

• Object-oriented databases. Information in an object-oriented database is represented in the form of objects, as in object-oriented programming. Object oriented data model is one of the developed data model and this can hold the audio, video and graphic files.

• Distributed databases. A distributed database consists of two or more files located in different sites. The database may be stored on multiple computers, located in the same physical location, or scattered over different networks. See also distributed computing or distributed system on big data!

• Data warehouses. A central repository for data, a data warehouse is a type of database specifically designed for fast query and analysis.

• NoSQL databases. A NoSQL, or nonrelational database, allows unstructured and semistructured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed). NoSQL databases grew popular as web applications became more common and more complex.

• Graph databases. A graph database stores data in terms of entities and the relationships between entities.

• OLTP databases. An OLTP database is a speedy, analytic database designed for large numbers of transactions performed by multiple users.

These are only a few of the several dozen types of databases in use today. Other, less common databases are tailored to very specific scientific, financial, or other functions. In addition to the different database types, changes in technology development approaches and dramatic advances such as the cloud and automation are propelling databases in entirely new directions. Some of the latest databases include

• Open source databases. An open source database system is one whose source code is open source; such databases could be SQL or NoSQL databases.

• Cloud databases. A cloud database is a collection of data, either structured or unstructured, that resides on a private, public, or hybrid cloud computing platform. There are two types of cloud database models: traditional and database as a service (DBaaS). With DBaaS, administrative tasks and maintenance are performed by a service provider.

• Multimodel database. Multimodel databases combine different types of database models into a single, integrated back end. This means they can accommodate various data types.

• Document/JSON database. Designed for storing, retrieving, and managing document-oriented information, document databases are a modern way to store data in JSON format rather than rows and columns.

Database Challenges

Today’s large enterprise databases often support very complex queries and are expected to deliver nearly instant responses to those queries. As a result, database administrators are constantly called upon to employ a wide variety of methods to help improve performance. Some common challenges that they face include:

• Absorbing significant increases in data volume. The explosion of data coming in from sensors, connected machines, and dozens of other sources keeps database administrators scrambling to manage and organize their companies’ data efficiently.
• Ensuring data security. Data breaches are happening everywhere these days, and hackers are getting more inventive. It’s more important than ever to ensure that data is secure but also easily accessible to users.
• Keeping up with demand. In today’s fast-moving business environment, companies need real-time access to their data to support timely decision-making and to take advantage of new opportunities.
• Managing and maintaining the database and infrastructure. Database administrators must continually watch the database for problems and perform preventative maintenance, as well as apply software upgrades and patches. As databases become more complex and data volumes grow, companies are faced with the expense of hiring additional talent to monitor and tune their databases.
• Removing limits on scalability. A business needs to grow if it’s going to survive, and its data management must grow along with it. But it’s very difficult for database administrators to predict how much capacity the company will need, particularly with on-premises databases.

Addressing all of these challenges can be time-consuming and can prevent database administrators from performing more strategic functions.

Autonomous Database

Enterprise databases are traditionally managed by database administrators (DBAs), who create, modify, and tune databases to ensure maximum performance both when storing new data in a database, and then retrieving that data.

An autonomous database is a cloud database that uses machine learning to eliminate the human labor associated with database tuning, security, backups, updates, and other routine management tasks traditionally performed by database administrators (DBAs).

Oracle Autonomous Database brings together decades of database automation, decades of automating database infrastructure, and new technology in the cloud to deliver a fully autonomous database.

The database that is self-driving, self-securing, and self-repairing. Let’s explore what each of those terms means for you.

• Self-Driving: Oracle Autonomous Database automates all database and infrastructure management, monitoring, and tuning. This reduces your full-stack admin costs, although admins will still be needed for tasks such as managing how applications connect to the data warehouse and how developers use the in-database features and functions without their application code.
• Self-Securing: Oracle Autonomous Database protects you from both external attacks and malicious internal users, which means you can stop worrying about cyberattacks on unpatched or unencrypted databases.
• Self-Repairing: Oracle Autonomous Database protects from all downtime, including unplanned maintenance, with fewer than 2.5 minutes of downtime a month, including patching.

Essentially, there’s now full, end-to-end automation for:

• Provisioning
• Security
• Updates
• Availability
• Performance
• Change management
• Errors
• With Oracle Autonomous Database, the world’s best database is now also the simplest.

The DBA is responsible for other tasks, many of which must be executed on a daily or other regular basis on all enterprise databases, of which there could be dozens or hundreds. That list of tasks includes:

• Backing up the database in case of disaster or data loss
• Testing the backups to make sure the database can be recovered
• Recovering lost data in the event of an incident
• Reviewing security logs to ensure that the database has not been accessed inappropriately
• Monitoring the database software’s vendor information feeds for security alerts, patches, and upgrades
• Scheduling and applying patches and upgrades when required
• Adjusting the security settings of the databases to respond to threats
• Authorizing new users and applications to access the database
• Monitoring processor and memory utilization of the database server
• Creating and managing schemas-that is, categorization of data
• Assisting software developers with database questions
• Managing extract, transform, and load (ETL) tools
• Monitoring disk utilization of the database server
• Adding more storage or migrating the storage, if necessary
• Planning for future capacity requirements for the database
• Troubleshooting errors and other database problems
• Working with business users and developers to define data models for new applications or modules

Advantages of DBMS=Software

• DBMS offers a variety of techniques to store & retrieve data
• DBMS serves as an efficient handler to balance the needs of multiple applications using the same data
• Uniform administration procedures for data
• Application programmers never exposed to details of data representation and storage.
• A DBMS uses various powerful functions to store and retrieve data efficiently.
• Offers Data Integrity and Security
• The DBMS implies integrity constraints to get a high level of protection against prohibited access to data.
• A DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time
• Reduced Application Development Time

Disadvantage of DBMS

• Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization. Most database management systems are often complex systems, so the training for users to use the DBMS is required.
• In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media
• Use of the same program at a time by many users sometimes lead to the loss of some data.
• DBMS can’t perform sophisticated calculations

When not to use a DBMS system?

Not recommended when you do not have the budget or the expertise to operate a DBMS. In such cases, Excel/CSV/Flat Files could do just fine.

Users

A typical DBMS has users with different rights and permissions who use it for different purposes. Some users retrieve data and some back it up. The users of a DBMS can be broadly categorized as follows ???

Administrators: Administrators maintain the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used. They create access profiles for users and apply limitations to maintain isolation and force security. Administrators also look after DBMS resources like system license, required tools, and other software and hardware related maintenance.

Designers: Designers are the group of people who actually work on the designing part of the database. They keep a close watch on what data should be kept and in what format. They identify and design the whole set of entities, relations, constraints, and views.

End Users: End users are those who actually reap the benefits of having a DBMS. End users can range from simple viewers who pay attention to the logs or market rates to sophisticated users such as business analysts.

DBMS - Architecture

• The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier architecture divides the whole system into related but independent n modules, which can be independently modified, altered, changed, or replaced.

• DBMS architecture helps in design, development, implementation, and maintenance of a database. A database stores critical information for a business. Selecting the correct Database Architecture helps in quick and secure access to this data.

DBMS Schemas: Internal, Conceptual, External

Database systems comprise of complex data structures. Thus, to make the system efficient for retrieval of data and reduce the complexity of the users, developers use the method of Data Abstraction.

There are mainly three levels of data abstraction:

1. Internal Level: Actual PHYSICAL storage structure and access paths.
2. Conceptual or Logical Level: Structure and constraints for the entire database
3. External or View level: Describes various user views

Let’s study them in detail!

INTERNAL-LEVEL/SCHEMA->

1. Internal Level/Schema: The internal schema defines the physical storage structure of the database. The internal schema is a very low-level representation of the entire database. It contains multiple occurrences of multiple types of internal record. In the ANSI term, it is also called "stored record’.

2. Facts about Internal schema:

   • The internal schema is the lowest level of data abstraction
   • It helps you to keeps information about the actual representation of the entire database. Like the actual storage of the data on the disk in the form of records
   • The internal view tells us what data is stored in the database and how
   • It never deals with the physical devices. Instead, internal schema views a physical device as a collection of physical pages

CONCEPTUAL-LEVEL/SCHEMA->

1. The conceptual schema describes the Database structure of the whole database for the community of users. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, etc.

2. Facts about Conceptual schema:

   • Defines all database entities, their attributes, and their relationships
   • Security and integrity information
   • In the conceptual level, the data available to a user must be contained in or derivable from the physical level

EXTERNAL-LEVEL/SCHEMA->

1. An external schema describes the part of the database which specific user is interested in. It hides the unrelated details of the database from the user. There may be “n” number of external views for each database. Each external view is defined using an external schema, which consists of definitions of various types of external record of that specific view. An external view is just the content of the database as it is seen by some specific particular user. For example, a user from the sales department will see only sales related data.

2. Facts about external schema:

   • An external level is only related to the data which is viewed by specific end users.
   • This level includes some external schemas.
   • External schema level is nearest to the user
   • The external schema describes the segment of the database which is needed for a certain user group and hides the remaining details from the database from the specific user group

GOAL OF 3 LEVEL/SCHEMA

• Every user should be able to access the same data but able to see a customized view of the data.
• The user need not to deal directly with physical database storage detail.
• The DBA should be able to change the database storage structure without disturbing the user’s views
• The internal structure of the database should remain unaffected when changes made to the physical aspects of storage.

ADVANTAGES DATABASE SCHEMA

• You can manage data independent of the physical storage
• Faster Migration to new graphical environments
• DBMS Architecture allows you to make changes on the presentation level without affecting the other two layers
• As each tier is separate, it is possible to use different sets of developers
• It is more secure as the client doesn’t have direct access to the database business logic
• In case of the failure of the one-tier no data loss as you are always secure by accessing the other tier

DISADVANTAGES DATABASE SCHEMA

• Complete DB Schema is a complex structure which is difficult to understand for every one
• Difficult to set up and maintain
• The physical separation of the tiers can affect the performance of the Database

ADVANTAGES OF USING RELATIONAL MODEL

• Simplicity: A relational data model is simpler than the hierarchical and network model.
• Structural Independence: The relational database is only concerned with data and not with a structure. This can improve the performance of the model.
• Easy to use: The relational model is easy as tables consisting of rows and columns is quite natural and simple to understand
• Query capability: It makes possible for a high-level query language like SQL to avoid complex database navigation.
• Data independence: The structure of a database can be changed without having to change any application.
• Scalable: Regarding a number of records, or rows, and the number of fields, a database should be enlarged to enhance its usability.

DISADVANTAGES OF USING RELATIONAL MODEL

• Few relational databases have limits on field lengths which can’t be exceeded.
• Relational databases can sometimes become complex as the amount of data grows, and the relations between pieces of data become more complicated.
• Complex relational database systems may lead to isolated databases where the information cannot be shared from one system to another.

Entity Relationships Diagram

For more info ER Diagram: https://www.guru99.com/er-diagram-tutorial-dbms.html

Gartner Magic Quadrant DBMS

Data Warehouse

What is Data Warehousing?

• A data warehouse is constructed by integrating data from multiple heterogeneous sources. It supports analytical reporting, structured and/or ad hoc queries and decision making.
• A data warehousing is defined as a technique for collecting and managing data from varied sources to provide meaningful business insights. It is a blend of technologies and components which aids the strategic use of data.
• It is electronic storage of a large amount of information by a business which is designed for query and analysis instead of transaction processing. It is a process of transforming data into information and making it available to users in a timely manner to make a difference.
• The decision support database (Data Warehouse) is maintained separately from the organization’s operational database.
• The data warehouse is not a product but an environment. It is an architectural construct of an information system which provides users with current and historical decision support information which is difficult to access or present in the traditional operational data store.
• The data warehouse is the core of the BI system which is built for data analysis and reporting.
• Data warehouse system is also known by the following name:
  • Decision Support System (DSS)
  • Executive Information System
  • Management Information System
  • Business Intelligence Solution
  • Analytic Application
  • Data Warehouse

• The different between database and datawarehouse are following,

Understanding a Data Warehouse

• A data warehouse is a database, which is kept separate from the organization’s operational database.
• There is no frequent updating done in a data warehouse.
• It possesses consolidated historical data, which helps the organization to analyze its business.
• A data warehouse helps executives to organize, understand, and use their data to take strategic decisions.
• Data warehouse systems help in the integration of diversity of application systems.
• A data warehouse system helps in consolidated historical data analysis.

Why a Data Warehouse is Separated from Operational Databases

A data warehouses is kept separate from operational databases due to the following reasons.

• An operational database is constructed for well-known tasks and workloads such as searching particular records, indexing, etc. In contract, data warehouse queries are often complex and they present a general form of data.
• Operational databases support concurrent processing of multiple transactions. Concurrency control and recovery mechanisms are required for operational databases to ensure robustness and consistency of the database.
• An operational database query allows to read and modify operations, while an OLAP query needs only read only access of stored data.
• An operational database maintains current data. On the other hand, a data warehouse maintains historical data.

History of Datawarehouse

The Datawarehouse benefits users to understand and enhance their organization’s performance. The need to warehouse data evolved as computer systems became more complex and needed to handle increasing amounts of Information. However, Data Warehousing is a not a new thing.

Here are some key events in evolution of Data Warehouse-

• 1960- Dartmouth and General Mills in a joint research project, develop the terms dimensions and facts.
• 1970- A Nielsen and IRI introduces dimensional data marts for retail sales.
• 1983- Tera Data Corporation introduces a database management system which is specifically designed for decision support
• Data warehousing started in the late 1980s when IBM worker Paul Murphy and Barry Devlin developed the Business Data Warehouse.
• However, the real concept was given by Inmon Bill. He was considered as a father of data warehouse. He had written about a variety of topics for building, usage, and maintenance of the warehouse & the Corporate Information Factory.

How Datawarehouse works?

A Data Warehouse works as a central repository where information arrives from one or more data sources. Data flows into a data warehouse from the transactional system and other relational databases.

Data may be:

• Structured
• Semi-structured
• Unstructured data

The data is processed, transformed, and ingested so that users can access the processed data in the Data Warehouse through Business Intelligence tools, SQL clients, and spreadsheets. A data warehouse merges information coming from different sources into one comprehensive database.

By merging all of this information in one place, an organization can analyze its customers more holistically. This helps to ensure that it has considered all the information available. Data warehousing makes data mining possible. Data mining is looking for patterns in the data that may lead to higher sales and profits.

Data Warehouse Features

The key features of a data warehouse are discussed below

• Subject Oriented: A data warehouse is subject oriented because it provides information around a subject rather than the organization’s ongoing operations. These subjects can be product, customers, suppliers, sales, revenue, etc. A data warehouse does not focus on the ongoing operations, rather it focuses on modelling and analysis of data for decision making.

• Integrated: A data warehouse is constructed by integrating data from heterogeneous sources such as relational databases, flat files, etc. This integration enhances the effective analysis of data.

• Time Variant: The data collected in a data warehouse is identified with a particular time period. The data in a data warehouse provides information from the historical point of view.

• Non-volatile: Non-volatile means the previous data is not erased when new data is added to it. A data warehouse is kept separate from the operational database and therefore frequent changes in operational database is not reflected in the data warehouse.

Note! A data warehouse does not require transaction processing, recovery, and concurrency controls, because it is physically stored and separate from the operational database.

Data Warehouse Applications

Many of the points expressed here are not truly applications but ways in which the DW (including data mining=get the insight from data) is used by these industries.

• Consumer Goods

  • Forecasting
  • Inventory replenishment
  • Effects of marketing campaigns, advertising, coupons and store displays
  • Sales and marketing analysis
  • Sharing information with distribution sites, business partners and product managers
  • Packaged Goods: Identify required product features

• Distribution

  • Supply chain analyses
  • Understand pipeline issues

• Finance/Banking

  • Evaluating business concentration and risk exposure leading to modified credit policies and loan loss reserves
  • Consumer asset data
  • Spot market trends
  • Government regulation reporting
  • Marketing
  • Mergers
    • Geographic overlap and saturation
    • Information for government regulation and approval
  • To support management planning, marketing and financial decision making
  • Ability to track and cut costs
  • Manage resources more effectively
  • Provide feedback to bankers regarding customer relationships and profitability
  • Information on spending patterns on a segment of issuing members card base (co-brand)
  • Target marketing & Promotion performance
  • Members use it to analyze performance by product, geography, interchange rates, volume by merchant, location, promotions, operational performance
  • Cardholder spending by state and merchant classification to develop direct mail promotion with key merchants.
  • Ability to select better marketing partners, build innovative and successful new product and build brand loyalty
  • Co-branding - determining where their cards (Sierra Club) are being used and develop target marketing plans
  • Combine with proprietary data to determine purchase patterns to develop marketing programs
  • Agent alerts:
    • Early warnings for changing spending patterns
    • Opportunity for special offer based on abnormal cardholder activity
    • When response rate to promotions hits target
  • Test promotional opportunities
  • Their member banks or co-brands can add data from the DW to their own proprietary databases
  • Mortgage Loan portfolio
  • New loans in process (pipeline)
  • Secondary marketing
  • Credit Cards
    • Identify new customers
    • Manage and control collections
    • Develop new products
    • Determine optimal marketing efforts for new products
    • Manager customer service performance
    • Identify potential risk of default
  • Brokerage, Commodity Trading
    • Identify target investment services, products and promotions
    • Risk management
    • Quality of trader transactions
  • Financial Services: Tracking investment managers
  • Personal Trust
    • Tracking managers on performance relative to indexes
    • Tracking managers on the types of stocks they purchase
  • Data Mining
    • Product analysis
    • Customer segmentation
    • Customer profitability
    • Target marketing campaigns
    • Fraud detection (credit cards)

• Finance General

  • Expense evaluation - trends
  • Customer Information Systems
  • Customer profitability
  • Fee tolerance

• Government and Education

  • Federal Government: Compliance research
  • State Government:
    • Accounting
    • Payroll
    • Procurement
    • Human Resources
  • State Government: Auditing and fraud investigation of social services
  • University:
    • Provide information to be used in a grant proposal
    • University finances
    • Human resources
    • Student demographics
    • Financial aid
    • Room and course scheduling
    • Data would be captured on the characteristics/demographics of students to better understand retention and success among minorities.
  • Government: Social Services
    • Fraud detection
    • Effectiveness of services
    • Profiles of who is using what services
  • Government Taxes: Auditing tax records for patterns and anomalies
  • Government Health Information
    • Program policy effectiveness (Cost and outcomes)
    • Provider care adequacy
  • Government: Police
    • Patterns and trends of criminal activity
    • Justice resource deployment
    • Consolidation and integration of data from multiple agencies
    • Effectiveness of programs and patterns of policing

• Health Care

  • Financial
  • Clinical
  • Strategic and outcomes data
  • Helps measure and track and analyze how well the hospital is providing services
  • Reports on percentage of patients being fed intravenously
  • Compares physicians to peer group on how long his patients occupy a bed, and the cost of surgery by physician
  • Finance department gets statistics by patient type, revenue code and insurance carrier
  • What percentage of hospital’s billings are from Medicare, Medicaid and each major insurance company
  • Ability to be more proactive about research
  • Respond to managed care contracts more knowledgeably
  • Ability to track costs and cut costs, manage resource more effectively, provide feedback to physicians regarding outcomes and the cost of realizing these outcomes.
  • Outcomes analysis
  • Providing feedback to physicians on procedures and tests
  • Using data mining, detecting inappropriate tests
  • Reports to doctors of testing trends and practices within their specialty
  • Results of study given to doctors so they can refine their decisions for ordering tests
  • Evaluating service to determine if should be providing or outsourcing (ex. dialysis, organ transplantation)
  • Nosocomial analysis
  • Continuing education and certification of health care professionals
  • Health Care: General
    • Pairing clinical to financial records to determine cost effectiveness of care
    • Utilization review
    • Contract management
    • Data mining to identify data patterns that could predict future individual health problems
    • Data mining to identify patients who will probably not respond well to specific procedures and operations
    • Discover “best practices” to improve quality and reduce costs
    • Analysis of care delivery
    • Research (prescribing patterns, use of antibiotics)
    • Physician performance
    • Resource utilization
    • Information for bids on managed care contracts
    • Information to support audits and external reports
    • Disease state support (cardiovascular, end-stage renal, etc.)
  • Pharmaceutical
    • Outcomes analysis
    • Doctor access

• Hospitality (Hotels, car rental, timeshares)

  • Cross-brand promotions - target customers with promotional offers tailored to their demographics and travel patterns
  • Estimate response to promotions and products through demographic analysis
  • Cross marke

• Insurance

  • Incorporate both internal and external data (i.e. information on competitors and the insurance industry trends)
  • Forecast and monitor changes in the industry thereby allowing better positioning in the marketplace
  • Identify characteristics of profitable business
  • Analyze information related to retention of business at renewal including patterns of customers who do not renew, determine reasons why, and resolve issues that will assist in retaining valued clients.
  • Accurate, consolidated view of customer portfolios
  • Analysis of profitability by customer, product, geography, and sales hierarchy
  • Analysis of sales offices for profit and loss
  • Property and Liability Insurance: Data mining review of claims by actuarial department
  • Workers’ Compensation Insurance:
    • Recommend health insurance deductibles.
    • Analysis of claims by the employer, cause of injury, body part injured and the percentage of * employees who have suffered similar injuries.
    • Fraud analysis using data mining
  • Health Insurance:
    • Impact on subscriber services and cost
    • Employee costs per employer
    • Service usage
    • Provider evaluation:
      • Physicians profiles
      • Cost
      • Length of hospital stay
      • Procedure evaluations
    • Fraud detection, Searching for claims where the service has not actually been provided. * Looking for patterns that would suggest further inquiry into the claim.
    • Abuse detection, Searching for patterns indicating that certain providers are performing unnecessary procedures, prescribing expensive medication where a less expensive drug would be as effective, performing unnecessary tests and keeping a patient in the hospital longer than necessary.
  • Blue Cross / Blue Shield:
    • Analysis of claims
    • Providers analysis
    • Reporting to groups, government agencies, trade associations
    • Analysis of quality of care and costs
    • Marketing managed-care contracts
    • Actuarial
    • Underwriting
    • Financial analysis (actual expenses vs. planned expenses)
    • Profitability of manager care arrangements
    • Capitated contract performance
    • Fraud analysis based on the provider’s health care specialty and the geography of the claim
  • Pharmaceutical Insurance:
    • Sales and marketing
    • Provider profiling
    • Government reporting
    • Utilization
    • Claims
    • Actuarial
    • Integrating pharmaceutical information with medical claims
    • Cost analysis by patient demographics and geographical distribution
    • Cost analysis by provider, provider specialties and treatment protocols
    • Analysis by diagnosis/prescription
    • Generic/brand name drug comparisons
  • Insurance Research: Claims information (Zurich-American - RiskIntelligence)

• Manufacturing and Distribution

  • Company M&D (Marketing & Development):
    • Analyze emerging business trends
    • Examine product bookings
    • Determine product shipments, backlogs and cancellations
    • Better manage product portfolio
    • Sharper contract negotiations
    • Better manufacture forecasting
    • Earlier detection of warning conditions
    • Ability to eliminate products from portfolio
    • Allows product mangers to more quickly identify product lines that are not longer required or profitable
    • Information about margins, product backlogs or historical sales data to critical decisions.
    • Visibility to its customers - on an individual customer level, what products the company sells, where it sells the products and at what price point.
    • Worldwide view of marketing developments
    • Common global language
    • Marketing executives are better able to manage their product lines
    • Better visibility into product and customer profit margins
    • Information on customers, products, costs, invoices
    • Access to market demand data (orders and shipment data) by both finance and materials groups
    • Legal department used DW to substantiate trademark claims in foreign markets
    • Sales analysis for product movement
    • Sales decision support * are the company’s products not being stocked * market share * competitors market share * when our company increases its share of the market, it is coming from competitors or are we cannibalizing our own line
    • Computer component manufacturer * Provide selected DW access to both customers and suppliers * Extensive measurements of the quality of the product, quality of components supplied by their vendors * Extensive feedback from customers on quality of the products
    • Sales history/sales trends
    • Customer profitability
    • Plant capacity management
    • Variances between standard and actual product costs
    • Inventory turnover
    • Human resources
    • Core competency, skills and distribution of skills
    • Analysis of production patterns to improve inventory and pricing practices
    • Demand forecasting to determine optimal inventory
    • Analysis of product pricing to establish discounts and margins
  • Automobile Manufacturer:
    • Tracks assembly and warranty quality information by supplier
    • Plans to use this information for product planning and design
    • Supplier quality information will give the manufacturer a better position for negotiation with supplier
    • Plans to provide suppliers with information on their products through the Internet. The goal is to give them enough information so they will improve the quality of their products. [The supplier could be provided with data on the cost of defective parts to the automobile manufacturer.]
    • The data provides a quantitative measure of quality for both the manufacturer and the supplier
  • Appliance Manufacturer:
    • Customer service
    • Suppliers’ quality
    • Negotiation with suppliers
  • Clothing Manufacturer:
    • Analyze sales and product trends by location to understand customer buying patterns
    • Analyze sell through, what was selling at retailers
    • Analyze and understand cancellations, the reasons for the cancellations to identify steps to remedy manufacturing problems
  • Computer Manufacturer: Market demand projections
  • Food Manufacturer:
    • Measure against competition
    • Ability to project how a new product will do
    • Ability to show grocery managers how product is selling at competitive stores, at stores within the same chain and against competitive products
    • Using agents, monitors conditions that require attention including variances in prices and volumes in company’s and competitors’ products
    • Analyses fixed costs, equipment utilization
    • Analyses manufacturing costs and performance
    • Analyses productivity
    • Analyses inventory levels
  • Pharmaceutical Manufacturer
    • Analysis of physicians (along with managed-care connections) and their prescribing patterns
    • Target marketing
    • Identify emerging prescribing trends
    • Generate reports for pre-clinical approval stage
    • Measurement of toxicology parameters
    • Research analysis
    • Testing analysis
  • Steel Manufacturer
    • Control (reduce) inventory
    • Understand item-level detail by cost, revenue, profit, inventory, customer, location
    • Analysis of production problems
    • Reduce accounts receivable
    • Understand profitability by customer
    • Understand margins and profitability by manufacturing facility leading to decisions about where to manufacture each item
    • Evaluate possibilities for renegotiating contracts with customers
    • Identify opportunities for new products
    • Identify opportunities for new locations
    • Compare plans to actual performance
    • Improve product and customer mix
  • Data Mining
    • Quality analysis
    • Profitability and problems with suppliers
    • Profitability and problems with customers
  • Customer profitability
  • Product profitability analysis
  • Strategic partnering: Negotiating with suppliers, customers
  • Customer purchases to identify who should get new Material Safety Data Sheets

• Marketing General

  • Comparing product lines
  • Media research (Nielsen Media Research)

• Retailers

  • Tracking an items contribution to margin for its category
  • Tracking how promotions and one-time buys are doing including using trend analysis
  • Buyers using it to analyze sales data
  • Allowing slow selling lines and items to be dropped from the stores while giving their shelf space to more profitable ones
  • Better understand customers and their buying patterns:
    • Drives promotions by having access to all data, marketing staff can make more accurate and effective promotion decisions
    • Feeds targeted and mass mailings
    • Enable retailer to proactively develop a relationship with its best customers or individuals that should be buying more than they currently are.
  • Marketing staff can ask “what if” marketing questions and get fast response
  • Meets the objectives of increasing sales, increasing profit, increasing marketing analyst productivity and decreasing mail expense and promotion cycle time.
  • Mail promotion effectiveness is easily and quickly quantified
  • Sales analysis
  • Target marketing
  • Cardholder base
  • Evaluate technician training
  • Evaluate maintenance tool and equipment inventory
  • Merchandising
  • Inventory management
  • Flow of goods management
  • Relationship marketing
  • Supplier integration
  • Marketing strategy
  • Buying strategy
  • Merchandizing strategy
  • Sales tracking by item
  • Sales analysis
  • Forecasting
  • Inventory tracking
  • Market basket analysis - e.g. Do products on sale generate other sales?
  • Individual items contribution to profits
  • Vendors (suppliers) have access to how their products are selling
  • Shelf space allocations
  • Effectiveness of promotions, advertising
  • Product analysis:
    • restocking
    • profitability
    • inventory turns
    • price changes
  • Category management (determining optimal product in each category and the optimal price for that product)
  • Merchandising strategy sales tracking
    • analysis by SKU (stock keeping unit) by store
    • trend analysis
  • Competitive analysis
  • Understanding complaints, claims and returns
  • Analyze customer contacts to determine preferences, attract and retain profitable customers
  • Understand customer and predict behavior
  • Goal-> improve gross margins
  • Better merchandising
  • Better buying decisions
  • End-of-season vendor negotiations
  • Inventory management
  • Price management for markdowns and promotions
  • Vendor analysis
  • Department Store:
    • Merchandising and buying decisions
    • Information to negotiate on end-of-season merchandise
    • Inventory and price management for promotions and markdowns
    • Vendor analysis
  • Automobile Convenience Store
    • Better product mix by location and demographics
    • Understanding price zones, profit, margins,
    • Understanding the competition
    • Understanding customers - images of the customers (who are they)
      • Results of surveys
      • Brand recognition
      • Customer demographics
      • Price sensitivity
    • New locations
    • Modeling/testing profitability of new items, different product mixes
    • Effectiveness of advertising and promotions
    • Understanding shrinkage by product, location,
    • Understanding product tie-ins (coffee and doughnuts, hot dogs and soft drinks, gasoline and oil, smog check and tune-up) and trying out ways to exploit those tie-ins. Shelf allocation
    • Use of ATM cards vs cash (are credit cards being considered?)
    • Effectiveness of alternative business controls
    • Statistical sampling when not all data is available
      • Suppliers/vendors
      • Price
      • Support/service
      • Delivery
      • Profitability
      • Quality
      • Alternative suppliers
      • Supplier negotiations
      • Joint promotions
      • Demographic data from suppliers
    • Fast Food
      • Analysis of food costs
      • Analysis of labor costs
      • Analysis of sales data
      • Service quality
      • Customer information
    • Data Mining
      • Advertising effectiveness
      • Market basket analysis
      • Profitability
    • Retailing General
      • Product profitability analysis
      • Merchandise planning
      • Analyze sales fluctuations
      • Selling marketing data to suppliers
      • Markdown management
      • Identify markets to target for newspaper advertising inserts
    • Catalog: Identify low profit products

• Services

  • Finance
  • Revenue
  • Purchase orders
  • Human resources
  • Customer profiles
  • Materials management

• Sports

  • Winning player combinations
  • Analyzing strategies, patterns of plays, defenses, players involved
  • Player negotiation
  • Internet statistical searches by fans

• Telephone

  • Fixed asset analysis
  • Facility for sales reps to market to volume customers different long-distance packages based on their latest calling patterns.
  • Integrated customer and financial data
  • Work request tracking
  • Customer information systems:
    • Exploring product “churn”
    • Managing strategic accounts
    • Building customer loyalty
    • Market segmentation
    • Target marketing
  • Product development
  • Risk and fraud assessment
  • Customer analysis:
    • Which customers are most likely to respond to an offer
    • Which customers are likely to accept new technology
    • Which customers are likely to respond to competitors’ offers
    • How much can we expect customers to spend on various products
    • Which prospects (non-customers) are likely to accept our offers
  • Identifying deadbeat customers and not marketing to these customers
  • Identifying potentially profitable market segments
  • Fee tolerance
  • Telemarketing
  • Predictive modeling
  • Merge lifestyle and demographic data to existing customer information
  • Data Mining
    • Analyzing customers most likely to switch to another carrier
    • Understanding customers
    • Understanding customers’ desires and expectations in contrast to what they have ordered and what the company can provide

• Transportation

  • Target marketing
  • Understanding customer requirements
  • Railroad:
    • Customer satisfaction
    • Train performance
    • Derailment prevention
    • Crew management
  • Railroad
    • Fleet management, locomotive information
    • Customer financial analysis
    • Rate analysis
    • Profitability analysis
    • Fleet maintenance analysis
    • Identify tax-exempt purchases
    • Understanding competitor’s cars on railroad’s line
  • Airline
    • Customer service program - financial data
    • Cargo volume analysis to understand source of revenue
    • Fraud analysis - issuing tickets on bad credit cards
  • Multi-industry (conglomerates)
    • Planning and forecasting
    • Make comparisons to plans

• Cross Industry

  • Legacy system retirement
  • Reliability of sales forecasting
  • Call centers, evaluating productivity and costs associated with varying responses to customer problems
  • Control of property and income tax
  • Financial
    • Budgets
    • Uncover problems in financial numbers before they are reported to upper management

• Utilities

  • Pricing
  • Supply chain
  • Asset management
  • Human Resources, Medical benefits package evaluation
  • Finance, Wholesale pricing models
  • Accounts Receivable, maximizing collections for customers who missed a payment
  • Accounts Receivable, analyzing processes to maximize collections for overdue accounts
  • Fixed Assets
  • Customer Information Systems
    • Marketing
    • Customer Satisfaction
    • Financial
    • Conservation
  • Marketing Queries
    • Determining customer usage
    • Understanding demographics and usage by demographics
    • Tracking marketing programs
    • Modeling a new program
    • Cost justifying a new program
    • Commercial customers, price sensitivity
    • Data mining, looking for patterns that may be marketing opportunities
    • Data mining, looking for patterns that predict delinquencies
  • Customer Satisfaction Queries
    • Tracking customer comments and complaints (By demographics & By psychographics)
    • Tracking customer surveys
    • Reporting to the Utility Commission and the Press
    • Data mining
  • Financial Queries
    • Customer profitability
    • Evaluating relationship pricing
    • Assessing alternative delivery channels
    • Assess outsourcing possibilities
    • Track profits across divisions
  • Conservation Queries
    • Monitoring conservation programs by customer
    • Identify pattern of customer (demographics) that could be candidate for conservation program
    • Identifying customers who have (or have not) signed up for special programs
    • Peak-period A/C disabled
    • Old refrigerator surrender
    • Florescent bulbs

Types of Data Warehouse

• A. Enterprise Data Warehouse:

Enterprise Data Warehouse is a centralized warehouse. It provides decision support service across the enterprise. It offers a unified approach for organizing and representing data. It also provide the ability to classify data according to the subject and give access according to those divisions.

• B. Operational Data Store:

Operational Data Store, which is also called ODS, are nothing but data store required when neither Data warehouse nor OLTP systems support organizations reporting needs. In ODS, Data warehouse is refreshed in real time. Hence, it is widely preferred for routine activities like storing records of the Employees.

• C. Data Mart:

A data mart is a subset of the data warehouse. It specially designed for a particular line of business, such as sales, finance, sales or finance. In an independent data mart, data can collect directly from sources.

• D. Information Processing

A data warehouse allows to process the data stored in it. The data can be processed by means of querying, basic statistical analysis, reporting using crosstabs, tables, charts, or graphs.

• E. Analytical Processing

A data warehouse supports analytical processing of the information stored in it. The data can be analyzed by means of basic OLAP operations, including slice-and-dice, drill down, drill up, and pivoting.

• F. Data Mining

Data mining supports knowledge discovery by finding hidden patterns and associations, constructing analytical models, performing classification and prediction. These mining results can be presented using the visualization tools.

Datawarehouse (OLAP) vs Datbase (OLTP)

General stages of Data Warehouse

Earlier, organizations started relatively simple use of data warehousing. However, over time, more sophisticated use of data warehousing begun. The following are general stages of use of the data warehouse:

• Offline Operational Database: In this stage, data is just copied from an operational system to another server. In this way, loading, processing, and reporting of the copied data do not impact the operational system’s performance.

• Offline Data Warehouse: Data in the Datawarehouse is regularly updated from the Operational Database. The data in Datawarehouse is mapped and transformed to meet the Datawarehouse objectives.

• Real time Data Warehouse: In this stage, Data warehouses are updated whenever any transaction takes place in operational database. For example, Airline or railway booking system.

• Integrated Data Warehouse: In this stage, Data Warehouses are updated continuously when the operational system performs a transaction. The Datawarehouse then generates transactions which are passed back to the operational system.

Who needs Data warehouse?

Data warehouse is needed for all types of users like:

• Decision makers who rely on mass amount of data
• Users who use customized, complex processes to obtain information from multiple data sources.
• It is also used by the people who want simple technology to access the data
• It also essential for those people who want a systematic approach for making decisions.
• If the user wants fast performance on a huge amount of data which is a necessity for reports, grids or charts, then Data warehouse proves useful.
• Data warehouse is a first step If you want to discover ‘hidden patterns’ of data-flows and groupings.

Steps to Implement Data Warehouse

The best way to address the business risk associated with a Datawarehouse implementation is to employ a three strategy as below

• Enterprise strategy: Here we identify technical including current architecture and tools. We also identify facts, dimensions, and attributes. Data mapping and transformation is also passed.
• Phased delivery: Datawarehouse implementation should be phased based on subject areas. Related business entities like booking and billing should be first implemented and then integrated with each other.
• Iterative Prototyping: Rather than a big bang approach to implementation, the Datawarehouse should be developed and tested iteratively.

Here, are key steps in Datawarehouse implementation along with its deliverables.

Best practices to implement a Data Warehouse

• Decide a plan to test the consistency, accuracy, and integrity of the data.
• The data warehouse must be well integrated, well defined and time stamped.
• While designing Datawarehouse make sure you use right tool, stick to life cycle, take care about data conflicts and ready to learn you’re your mistakes.
• Never replace operational systems and reports
• Don’t spend too much time on extracting, cleaning and loading data.
• Ensure to involve all stakeholders including business personnel in Datawarehouse implementation process. Establish that Data warehousing is a joint/ team project. You don’t want to create Data warehouse that is not useful to the end users.
• Prepare a training plan for the end users.

Advantages of Data Warehouse:

• Data warehouse allows business users to quickly access critical data from some sources all in one place.
• Data warehouse provides consistent information on various cross-functional activities. It is also supporting ad-hoc reporting and query.
• Data Warehouse helps to integrate many sources of data to reduce stress on the production system.
• Data warehouse helps to reduce total turnaround time for analysis and reporting.
• Restructuring and Integration make it easier for the user to use for reporting and analysis.
• Data warehouse allows users to access critical data from the number of sources in a single place. Therefore, it saves user’s time of retrieving data from multiple sources.
• Data warehouse stores a large amount of historical data. This helps users to analyze different time periods and trends to make future predictions.

Disadvantages of Data Warehouse

• Not an ideal option for unstructured data.
• Creation and Implementation of Data Warehouse is surely time confusing affair.
• Data Warehouse can be outdated relatively quickly
• Difficult to make changes in data types and ranges, data source schema, indexes, and queries.
• The data warehouse may seem easy, but actually, it is too complex for the average users.
• Despite best efforts at project management, data warehousing project scope will always increase.
• Sometime warehouse users will develop different business rules.
• Organisations need to spend lots of their resources for training and Implementation purpose.

The Future of Data Warehousing

• Change in Regulatory constrains may limit the ability to combine source of disparate data. These disparate sources may include unstructured data which is difficult to store.
• As the size of the databases grows, the estimates of what constitutes a very large database continue to grow. It is complex to build and run data warehouse systems which are always increasing in size. The hardware and software resources are available today do not allow to keep a large amount of data online.
• Multimedia data cannot be easily manipulated as text data, whereas textual information can be retrieved by the relational software available today. This could be a research subject.

Business Intelligence

What is Business Intelligence?

• BI(Business Intelligence) is a set of processes, architectures, and technologies that convert raw data into meaningful information that drives profitable business actions.It is a suite of software and services to transform data into actionable intelligence and knowledge.
• BI has a direct impact on organization’s strategic, tactical and operational business decisions. BI supports fact-based decision making using historical data rather than assumptions and gut feeling.
• BI tools perform data analysis and create reports, summaries, dashboards, maps, graphs, and charts to provide users with detailed intelligence about the nature of the business.

Why is BI important?

• Measurement: creating KPI (Key Performance Indicators) based on historic data
• Identify and set benchmarks for varied processes.
• With BI systems organizations can identify market trends and spot business problems that need to be addressed.
• BI helps on data visualization that enhances the data quality and thereby the quality of decision making.
• BI systems can be used not just by enterprises but SME (Small and Medium Enterprises)
• To Improve Visibility:
  • Do you know who the best performers are?
  • Can you easily identify areas for improvement?
  • Is stock going missing and you can’t identify where it’s going?
• To Turn Data into Actionable Information
• To Improve Efficiency
• To Gain Sales & Market Intelligence
• To Gain Competitive Intelligence

How Business Intelligence systems are implemented?

• Step 1) Raw Data from corporate databases is extracted. The data could be spread across multiple systems heterogeneous systems.
• Step 2) The data is cleaned and transformed into the data warehouse. The table can be linked, and data cubes are formed.
• Step 3) Using BI system the user can ask quires, request ad-hoc reports or conduct any other analysis.

Four types of BI users

Following given are the four key players who are used Business Intelligence System:

1. The Professional Data Analyst: The data analyst is a statistician who always needs to drill deep down into data. BI system helps them to get fresh insights to develop unique business strategies.

2. The IT users: The IT user also plays a dominant role in maintaining the BI infrastructure.

3. The head of the company: CEO or CXO can increase the profit of their business by improving operational efficiency in their business.

4. The Business Users: Business intelligence users can be found from across the organization. There are mainly two types of business users

   • Casual business intelligence user
   • The power user.

The difference between both of them is that a power user has the capability of working with complex data sets, while the casual user need will make him use dashboards to evaluate predefined sets of data.

Advantages of Business Intelligence

Here are some of the advantages of using Business Intelligence System:

1. Boost productivity

2. To improve visibility

3. Fix Accountability

4. It gives a bird’s eye view

5. It streamlines business processes

6. It allows for easy analytics

BI System Disadvantages

1. Cost

2. Complexity

3. Limited use

4. Time Consuming Implementation

Trends in Business Intelligence

• Artificial Intelligence: Gartner’ report indicates that AI and machine learning now take on complex tasks done by human intelligence. This capability is being leveraged to come up with real-time data analysis and dashboard reporting.
• Collaborative BI: BI software combined with collaboration tools, including social media, and other latest technologies enhance the working and sharing by teams for collaborative decision making.
• Embedded BI: Embedded BI allows the integration of BI software or some of its features into another business application for enhancing and extending it’s reporting functionality.
• Cloud Analytics: BI applications will be soon offered in the cloud, and more businesses will be shifting to this technology. As per their predictions within a couple of years, the spending on cloud-based analytics will grow 4.5 times faster.
• Self-service BI

What is Self-Service BI?

• Self-service analytics or self-service business intelligence refers to tools used to connect and analyze data, and which are operated primarily by business departments in the organization , rather than IT professionals or dedicated data analysts.

Traditional BI vs Self-Service BI?

Business Intelligence software

Extras-1: Big Data

Data is everywhere

Do you have a Smartphone? Of course, this kind of questions is not relevant in this era. From our laptop, tablet, PC or our smartphone. These device creates more data today. Data is getting bigger. The world has an astronomical amount of data, an amount that grows larger and larger each day. This Big Data has changed the way the world interacts, uncovered breakthroughs in fast all of our life, from e-commerce, medicine, genetic, financial, laws, and crime, etc.

The principle of a Big Data works that the more the company has data, the more accurately the company get new insights and make predictions about what will happen in the future. Also revealed new ways to understand trends in business. By comparing more and more data and creating relationships that were previously hidden enable the company to learn and make smarter decisions on targeting business values such as sales, production, marketing or financial situations.

Based on forbes.com only 53% have big data strategies around the world. The top three use case is retail, social analysis, and predictive maintenance. Key points include the following:

• Reporting, dashboards, advanced visualization, self-service business intelligence, and data warehousing are the top five technologies.

• Only 53% of companies are using big data analytics today, it grew 17% in 2015. Telecommunications and Financial Services Industries are the fastest adoptions.

• The most important big data analytics use case in 2017 is data warehouse optimization. The seconds are customer/social analysis and predictive maintenance.

• Big data analytics use cases vary significantly by industry. Data warehouse optimization dominated Financial Services and Healthcare Industry. Customer / Social Analysis is the leading use case in technology-based companies.

• The first big data infrastructure is Spark. And The other two most popular software for big data infrastructure is MapReduce and Yarn.

• Spark SQL, Hive, HDFS, and Amazon S3 are top three the big data access methods by respondents.

• Machine learning with Spark Machine Learning Library (MLib) got more industry support and investment plans. The growth projected by 60% in the next 12 months.

The Big Four Consultant (Deloitte, Ernst & Young, KPMG and PricewaterhouseCoopers) are the four biggest professional services networks in the world. They did also so many research about this theme. One of the interesting information is in the paper "Gut & gigabytes" from PricewaterhouseCoopers (PwC).

The point is that executives rely more on experience and advice than data to make business-defining choices but the data-driven cultures could be impacted more for decision-making.

The summary of this paper:

• Highly data-driven companies are three times more likely to report significant improvement in making big decisions, but only 1 in 3 executives say their organization is highly data-driven.

• More big decisions are made opportunistically than deliberately, and big decisions have a big impact on future profitability; nearly 1 in 3 executives value those decisions at $1 billion+

• Many executives skeptical or frustrated by the practical application of data and analytics for big decisions, especially in emerging markets

And the five important issues in this survey are growing the existing business, collaborating with competitors, shrinking the existing business, entering a new industry or starting a new business, and corporate financing.

The last questions of this introduction are how data analytics can improve your businesses? Below are the answered.

1. Make data-driven business decisions:

• Making evidence-based rather than intuition-based decisions

2. Grow your business – discover new opportunities

• Quickly identify future markets and the best areas for new investments

• Boost growth through strategic pricing models and data-driven marketing

3. Create a more efficient and smarter organization

• Predict and anticipate the impacts of economic, market and regulatory forces on business strategy and results

• Use automation and advanced statistical software to handle and analyze huge volumes of data

4. Manage risk and regulatory

• Minimize compliance risks by ensuring the completeness, accuracy, and availability of data sources

Big Data Definition

As data becomes bigger, manipulating of available data to get insights and make business decisions can be useful. Statistical methods, artificial intelligence, machine learning, and data manipulation are some of the new terms that every business leaders at every level need to become data literate and be able to understand data and analytical concepts.

Before going further, what is actually Big data?. Big data term comes from John Mashey in 1990, a computer scientist from Pennsylvania State University. It all starts with the big bang explosion in the amount of data we have created since the rise of the digital era. Side effects of the rise of computers, the Internet and technology that capable of capturing data from all kind of electronic processing.

Big Data has 3 defining properties call 3V. This term introduces by Gartner analyst Doug Laney in a 2001 MetaGroup research publication. He published that publication with the title “3D Data Management: Controlling data volume, variety, and velocity”. Also, not all data is big data except they have 3 properties. Some other data expert added another 2V, value, and veracity, but the main term is always 3V.

1. Volume: Volume refers to the huge amounts of data generated each time from all the electronic device such as website, social media, cell phones, cars, credit cards, sensors, photographs, video, etc. Volume is the ‘V’ most associated with the term of big data because volume can be incredible exploding big. Below is the explanation from sisense.com.

For Example in the internet world. Facebook is storing 250 billion images As far back as 2016, Facebook had 2.5 trillion posts. Can you imagine for the other online media such as twitter, blog, instagram and our searching on google? It is an extreme amount of data. This link shows the live statistics of some online area such as facebook, twitter., internet users, etc.

http://www.internetlivestats.com/

Or

http://www.worldometers.info/

The world information such as population, health, economics, food, water, energy, etc.

3. velocity: There is four velocity type in big data.

• Batch
• Periodic
• Near real-time
• Real-time

“Firehose” data sources such as social media and e-commerce need quickly analytics. Using real-time alerting, Wal-Mart company was able to find a particular sport shoes where it wasn’t selling at all. More than realtime but predictive analytics.

The infographics & animations to understand more of the 3V of big data from IBM are below

Extras-2: Introduction to Data Viz

• Introduction:
• Classic Examples:
• Visualization Basics
• Visual System
• Visual Perception
• What-is-color
• Color Vision
• Effective charts
• Various Examples
• Graphing Process
• Entertainment

Extras-3: Computing with Data

• The Data Analysis Cycle-DAC
• Data Perspectives
• DAC and the filesystem
• Role of Data Visualization

Data Lab

WS 2019: Tableau software: itsmecevi.github.io/tableaupublic

Summary + MEQA

Database

1. What is the different between on premises and cloud software?

The biggest difference between these two systems is how they are deployed. Cloud-based software is hosted on the vendor’s servers and accessed through a web browser. On-premise software is installed locally, on a company’s own computers and servers.

2. Please explain what is qualitative data, quantitative data, discrete, and continuous data? You can explain with diagram or example.

Data can be qualitative or quantitative. Put simply: Discrete data is counted, Continuous data is measured.

• Qualitative data is descriptive information (it describes something)
• Quantitative data is numerical information (numbers)-> Discrete(5,6,10); Continuous (3,14)

3. Create the diagram workflow from data to BI reports.

4. Explain a use case implementation of DBMS in the industries? 2 industry such as banking and finance.

5. What is different between database and data warehouse? 5 different point of views

6. What are the similarities in a database and data warehouse? 5 similarities point of views

Other responses have given the definition of the two, similarities are:

• They are both data storage systems.
• Both can be used by multiple users
• Both can be complex with many tables and large amounts of data.
• Both can be queried to get answers or pull out data sets based on commonality or exclusion.
• Databases and data warehouses can be on premise or cloud-based.

7. Give the 5 types of databases?

• Relational databases
• Object-oriented databases
• Distributed databases
• Data warehouses
• NoSQL databases
• Graph databases
• OLTP databases
• Open source databases
• Cloud databases
• Multimodel database
• Document/JSON database

8. What is the list of Database Challenges?

• Absorbing significant increases in data volume
• Ensuring data security
• Keeping up with demand
• Managing and maintaining the database and infrastructure
• Removing limits on scalability

9. Explain the 3 users of DBMS?

• Administrators: Administrators maintain the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used. They create access profiles for users and apply limitations to maintain isolation and force security. Administrators also look after DBMS resources like system license, required tools, and other software and hardware related maintenance.

• Designers: Designers are the group of people who actually work on the designing part of the database. They keep a close watch on what data should be kept and in what format. They identify and design the whole set of entities, relations, constraints, and views.

• End Users: End users are those who actually reap the benefits of having a DBMS. End users can range from simple viewers who pay attention to the logs or market rates to sophisticated users such as business analysts.

10. What is advantages and disadvantages of database schema?

ADVANTAGES DATABASE SCHEMA

• You can manage data independent of the physical storage
• Faster Migration to new graphical environments
• DBMS Architecture allows you to make changes on the presentation level without affecting the other two layers
• As each tier is separate, it is possible to use different sets of developers
• It is more secure as the client doesn’t have direct access to the database business logic
• In case of the failure of the one-tier no data loss as you are always secure by accessing the other tier

DISADVANTAGES DATABASE SCHEMA

• Complete DB Schema is a complex structure which is difficult to understand for every one
• Difficult to set up and maintain
• The physical separation of the tiers can affect the performance of the Database

11. What is advantages and disadvantages of using relational model?

ADVANTAGES OF USING RELATIONAL MODEL

• Simplicity: A relational data model is simpler than the hierarchical and network model.
• Structural Independence: The relational database is only concerned with data and not with a structure. This can improve the performance of the model.
• Easy to use: The relational model is easy as tables consisting of rows and columns is quite natural and simple to understand
• Query capability: It makes possible for a high-level query language like SQL to avoid complex database navigation.
• Data independence: The structure of a database can be changed without having to change any application.
• Scalable: Regarding a number of records, or rows, and the number of fields, a database should be enlarged to enhance its usability.

DISADVANTAGES OF USING RELATIONAL MODEL

• Few relational databases have limits on field lengths which can’t be exceeded.
• Relational databases can sometimes become complex as the amount of data grows, and the relations between pieces of data become more complicated.
• Complex relational database systems may lead to isolated databases where the information cannot be shared from one system to another.

12. Explain the relational model 1:1, 1:n, n:n with the example?

Data Warehouse

1. Explain the implementation of Data Warehouse in 5 industry?

• Banking
• Retailer
• Insurance
• Manufacturing
• Healthcare

2. Explain the 6 types of Data Warehouse?

• 1. Enterprise Data Warehouse:

Enterprise Data Warehouse is a centralized warehouse. It provides decision support service across the enterprise. It offers a unified approach for organizing and representing data. It also provide the ability to classify data according to the subject and give access according to those divisions.

• 2. Operational Data Store:

Operational Data Store, which is also called ODS, are nothing but data store required when neither Data warehouse nor OLTP systems support organizations reporting needs. In ODS, Data warehouse is refreshed in real time. Hence, it is widely preferred for routine activities like storing records of the Employees.

• 3. Data Mart:

A data mart is a subset of the data warehouse. It specially designed for a particular line of business, such as sales, finance, sales or finance. In an independent data mart, data can collect directly from sources.

• 4. Information Processing

A data warehouse allows to process the data stored in it. The data can be processed by means of querying, basic statistical analysis, reporting using crosstabs, tables, charts, or graphs.

• 5. Analytical Processing

A data warehouse supports analytical processing of the information stored in it. The data can be analyzed by means of basic OLAP operations, including slice-and-dice, drill down, drill up, and pivoting.

• 6. Data Mining

Data mining supports knowledge discovery by finding hidden patterns and associations, constructing analytical models, performing classification and prediction. These mining results can be presented using the visualization tools.

3. Explain the different between OLAP by datawarehouse and OLTP by database?

4. Explain the advantages and disadvantages of using Data Warehouse?

-> Advantages of Data Warehouse:

• Data warehouse allows business users to quickly access critical data from some sources all in one place.
• Data warehouse provides consistent information on various cross-functional activities. It is also supporting ad-hoc reporting and query.
• Data Warehouse helps to integrate many sources of data to reduce stress on the production system.
• Data warehouse helps to reduce total turnaround time for analysis and reporting.
• Restructuring and Integration make it easier for the user to use for reporting and analysis.
• Data warehouse allows users to access critical data from the number of sources in a single place. Therefore, it saves user’s time of retrieving data from multiple sources.
• Data warehouse stores a large amount of historical data. This helps users to analyze different time periods and trends to make future predictions.

-> Disadvantages of Data Warehouse:

• Not an ideal option for unstructured data.
• Creation and Implementation of Data Warehouse is surely time confusing affair.
• Data Warehouse can be outdated relatively quickly
• Difficult to make changes in data types and ranges, data source schema, indexes, and queries.
• The data warehouse may seem easy, but actually, it is too complex for the average users.
• Despite best efforts at project management, data warehousing project scope will always increase.
• Sometime warehouse users will develop different business rules.
• Organisations need to spend lots of their resources for training and Implementation purpose.

Business Intelligence:

Read the modul https://itsmecevi.github.io/tableaupublic/ too.

1. Explain the advantages and disadvantages of using BI Systems?

-> Advantages of Business Intelligence

Here are some of the advantages of using Business Intelligence System:

1. Boost productivity

2. To improve visibility

3. Fix Accountability

4. It gives a bird’s eye view

5. It streamlines business processes

6. It allows for easy analytics

-> BI System Disadvantages

1. Cost

2. Complexity

3. Limited use

4. Time Consuming Implementation

2. Explain the different between Traditional BI vs Self-Service BI?

3. Why is BI important?

• Measurement: creating KPI (Key Performance Indicators) based on historic data
• Identify and set benchmarks for varied processes.
• With BI systems organizations can identify market trends and spot business problems that need to be addressed.
• BI helps on data visualization that enhances the data quality and thereby the quality of decision making.
• BI systems can be used not just by enterprises but SME (Small and Medium Enterprises)
• To Improve Visibility:
  • Do you know who the best performers are?
  • Can you easily identify areas for improvement?
  • Is stock going missing and you can’t identify where it’s going?
• To Turn Data into Actionable Information
• To Improve Efficiency
• To Gain Sales & Market Intelligence
• To Gain Competitive Intelligence

4. Explain the different between Star Schema, Snow Flake Schema, and Galaxy Schema? and create an example of those schema (3 schema)!

Overview of Star Schema

• What is star schema? The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star. The center of the star consists of fact table and the points of the star are the dimension tables.
• Fact Tables: A fact table typically has two types of columns: foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact’s data on detail or aggregated level.
• Dimension Tables: A dimension is a structure usually composed of one or more hierarchies that categorizes data. If a dimension hasn’t got a hierarchies and levels it is called flat dimension or list. The primary keys of each of the dimension tables are part of the composite primary key of the fact table.

Typical fact tables store data about sales while dimension tables data about geographic region(markets, cities) , clients, products, times, channels.

The main characteristics of star schema:

• Simple structure -> easy to understand schema
• Great query effectives -> small number of tables to join
• Relatively long time of loading data into dimension tables -> de-normalization, redundancy data caused that size of the table could be large.
• The most commonly used in the data warehouse implementations -> widely supported by a large number of business intelligence tools.

Overview of Snow flake schema

• A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. It is called snowflake because its diagram resembles a Snowflake.

• The dimension tables are normalized which splits data into additional tables. In the following example, Country is further normalized into an individual table.

Characteristics of Snowflake Schema:

• The main benefit of the snowflake schema it uses smaller disk space.
• Easier to implement a dimension is added to the Schema
• Due to multiple tables query performance is reduced
• The primary challenge that you will face while using the snowflake Schema is that you need to perform more maintenance efforts because of the more lookup tables.

Star schema vs Snow Flake schema

What is a Galaxy schema?

A Galaxy Schema contains two fact table that shares dimension tables. It is also called Fact Constellation Schema. The schema is viewed as a collection of stars hence the name Galaxy Schema.

Characteristics of Galaxy Schema:

• The dimensions in this schema are separated into separate dimensions based on the various levels of hierarchy.
• For example, if geography has four levels of hierarchy like region, country, state, and city then Galaxy schema should have four dimensions.
• Moreover, it is possible to build this type of schema by splitting the one-star schema into more Star schemes.
• The dimensions are large in this schema which is needed to build based on the levels of hierarchy.
• This schema is helpful for aggregating fact tables for better understanding.

5. What is Business Intelligence and Self-service business intelligence (SSBI)

• The term Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information. The purpose of Business Intelligence is to support better business decision making.
• Essentially, Business Intelligence systems are data-driven Decision Support Systems (DSS). Business Intelligence is sometimes used interchangeably with briefing books, report and query tools and executive information systems.
• Business intelligence (BI) combines business analytics, data mining, data visualization, data tools and infrastructure, and best practices to help organizations to make more data-driven decisions.

Self-service business intelligence (SSBI) involves the business systems and data analytics that give business end-users access to an organization’s information without direct IT involvement. Self-service Business intelligence gives end-users the ability to do more with their data without necessarily having technical skills. These solutions are usually created to be flexible and easy-to-use so that end-users can analyze data, make decisions, plan and forecast on their own.

Over the past few years, business intelligence has evolved to include more processes and activities to help improve performance. These processes include:

• Data mining: Using databases, statistics and machine learning to uncover trends in large datasets.
• Reporting: Sharing data analysis to stakeholders so they can draw conclusions and make decisions.
• Performance metrics and benchmarking: Comparing current performance data to historical data to track performance against goals, typically using customized dashboards.
• Descriptive analytics: Using preliminary data analysis to find out what happened.
• Querying: Asking the data specific questions, BI pulling the answers from the datasets.
• Statistical analysis: Taking the results from descriptive analytics and further exploring the data using statistics such as how this trend happened and why.
• Data visualization: Turning data analysis into visual representations such as charts, graphs, and histograms to more easily consume data.
• Visual analysis: Exploring data through visual storytelling to communicate insights on the fly and stay in the flow of analysis.
• Data preparation: Compiling multiple data sources, identifying the dimensions and measurements, preparing it for data analysis.

Change log update

• 25.09.2019
• 26.09.2019
• 28.09.2019
• 29.09.2019
• 02.09.2019
• 04.10.2019
• 07.10.2019
• 10.10.2019

Preferences

• Authoring Books and Technical Documents with R Markdown
• R Markdown: The Definitive Guide
• Datenbanken Verstehen
• Tutorialspoint
• Software Advice
• Oracle
• Math is Fun
• Java T Point
• Medium
• Alooma Data Pipeline
• SQl Database Modeler
• Datenbanksysteme: Eine Einf?hrung
• Datenbanken und SQL
• Datenmanagement:Daten-Datenbanken-Datensicherheit
• Zapier-Automate anything
• Capterra-Finding software
• Inside Big Data
• Multi-model Data Management
• The Data Administration Newsletter
• Yass Al-Hassani MSc BSc
• Sisense
• Predictiveanalyticstoday
• Tech Target
• Roosboard
• Forbes
• PricewaterhouseCoopers
• Internet Live Stats
• Worldometers
• Gaston Sanchez

License

MIT