Turning Data into Decisions: Warehousing and Mining for Business Intelligence

UPI Study › Courses › System Analysis and Design › Free lessons › Lesson 6.6 Free with a free account

Turning Data into Decisions: Warehousing and Mining for Business Intelligence

System Analysis and Design · Chapter 6: Database Design, Normalization, and Business IntelligenceSection 6.6 · lesson 44 of 698 min · free with a free account

← Normalization in Databases: Types and Practical IllustrationsNext lesson: The Implementation and Coding Phase in the SDLC →

Earn 3 college credits for System Analysis and DesignRecommended for credit by NCCRS · Computer Science 390 · self-paced · one payment, lifetime access
Enroll in System Analysis and Design · $250 →Download the syllabus (PDF)

In this lesson

OLTP and OLAP, ETL, star and snowflake schemas, data marts, mining, and dashboards.

  • Distinguish OLTP from OLAP, and explain why organizations need both
  • Explain the 3 stages of ETL, and compare star and snowflake schemas
  • Explain data marts, and 3 data mining techniques
  • Explain what makes a BI dashboard effective

Transcript

Everything said in the video, section by section (1,050 words). Read along, or search it.

Database Design, Normalization, and Business Intelligence

Warehousing and Mining for Business Intelligence

This section closes the chapter by asking what organizations do with all of their data, once it is stored. We will compare two kinds of system, O L T P and O L A P, and see how data gets into a warehouse through E T L. Then the schemas warehouses use, data marts, three data mining techniques, and what makes a business intelligence dashboard effective.

2 Different Jobs

OLTP vs. OLAP

Not every database serves the same purpose. O L T P, or online transaction processing, runs the business day to day, such as placing an order. O L A P, or online analytical processing, supports analysis and reporting on historical data. O L T P handles many small, fast reads and writes. O L A P handles fewer, larger, more complex queries. O L T P schemas are normalized, to keep transactions fast and consistent. O L A P schemas are denormalized, to make large-scale queries faster. A point-of-sale system recording a purchase is O L T P. A report showing quarterly sales trends by region is O L A P. A checkout recording thousands of small purchases a minute needs O L T P. The question, how did last quarter compare, needs O L A P. Because these are such different jobs, most organizations end up running both, side by side.

3 Stages

ETL: Getting Data Into a Warehouse

Data destined for a warehouse rarely arrives ready to use. It usually comes from several operational systems, in different formats. E T L, short for extract, transform, load, is the process that reconciles it. First, extract. Data is pulled from each source system, such as a sales platform, a C R M, or website logs. Second, transform. The extracted data is cleaned, standardized, and reshaped, so it fits a single, consistent structure. Third, load. The finished data is written into the data warehouse, where it is available for analysis and reporting. Why not load the data straight after extracting it? Because each source has its own formats and structures. Until it is transformed, it could not be analyzed together.

Figure 2

Fact and Dimension Tables

A data warehouse is typically organized around a central fact table. The fact table holds the measurements being analyzed. Here, sales. Around it sit dimension tables. They describe the context of each measurement. Which product was sold, to which customer, in which time period, and at which store. Arranged like this, with every dimension joined directly to the fact table, the design is called a star schema. A snowflake schema normalizes the dimensions further. Here, product is split so that its category lives in its own table, shown with the dashed line. The two arrangements differ only in how normalized the dimension tables are.

Choosing a Schema

Star vs. Snowflake

So which should a warehouse use? A star schema keeps each dimension as one flat table. That makes it simpler to query, and usually faster, because a query touches fewer tables. A snowflake schema normalizes dimensions into further tables. That reduces duplicated data, at the cost of needing more joins to answer the same question. Here is a quick test. If a team wants the fastest possible queries, and can accept some duplicated data, star fits better. If reducing duplication matters more than query speed, snowflake fits better.

Data Marts

A Focused Slice of the Warehouse

A full warehouse covers the whole organization. Not every team needs all of that. A data mart is a smaller, focused subset of a data warehouse. It is built around the needs of one business area, such as sales, marketing, or finance. Because it covers less ground, a data mart is typically faster to build. And it is easier for the team that owns it to query. The cost is coverage. A data mart holds nothing outside its focus area.

Data Mining

Classification, Clustering, and Association

Once data is organized in a warehouse, data mining looks for patterns that would be impractical to find by hand. Three techniques cover most of what organizations look for. Classification assigns each record to one of a set of predefined categories, such as flagging a transaction as fraudulent or legitimate. Clustering groups similar records together, without predefined categories, such as segmenting customers by purchasing behavior. Association finds relationships between items that tend to occur together. This is often called market basket analysis. A grocery store might find that customers who buy diapers often buy baby wipes in the same transaction. That is association. The distinction between the first two is worth remembering. Classification sorts records into categories someone already defined. Clustering discovers groupings that were not defined in advance.

BI Dashboards

4 Traits of an Effective Dashboard

A business intelligence dashboard turns warehousing and mining into something a decision-maker can use every day. It is a visual display of the metrics that matter most. Four traits make one effective. First, focus. It shows a small number of key metrics, rather than everything available. Second, clear visual hierarchy. The most important numbers are the easiest to notice. Third, the right refresh schedule. It updates as often as decisions actually get made. And fourth, drill-down. When a number raises a question the summary cannot answer, the viewer can open up that metric, and look underneath it.

Key Terms

The Vocabulary of This Section

Let's lock in the key terms. O L T P is online transaction processing, optimized for many small, fast, day-to-day transactions. O L A P is online analytical processing, optimized for complex queries over historical data. E T L, extract, transform, load, moves data from source systems into a consistent, warehouse-ready structure. A fact table is the central table holding the measurements being analyzed. A dimension table describes the context of a measurement, such as product, customer, or time. And a data mart is a smaller, focused subset of a warehouse, built for one business area.

Key Takeaways

What to Carry Forward

To pull this together. O L T P systems run day-to-day operations. O L A P systems support analysis of historical data. Most organizations need both. E T L extracts data from source systems, transforms it into a consistent structure, and loads it into a warehouse. A star schema keeps dimensions flat, for simpler, faster queries. A snowflake schema normalizes them further, to reduce duplicated data. A data mart is a smaller, focused subset of a warehouse, built for one business area. Classification sorts data into predefined categories, clustering discovers new groupings, and association finds items that occur together. And an effective dashboard focuses on a few key metrics, with clear visual hierarchy, and the ability to drill down.

About System Analysis and Design at UPI Study

System Analysis and Design (Computer Science 390) is a self-paced online course of 69 video lessons in 9 chapters, from the analyst's role and requirements to UML modeling, interface and database design, testing, maintenance and project management. It carries a NCCRS credit recommendation for 3 semester credits.

Every lesson transcript is free on pages like this one, and the videos are free with a free UPI Study account. Enrolling adds a practice quiz after each lesson, the graded work and final exam that earn the credit, lifetime access, and an official transcript on completion. UPI Study has helped over 130,000 students with college credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Enroll now → Course details Download the syllabus All Computer Science courses Every course with PRO →

Compare: Software Engineering vs Systems Analysis

Why take it here

Why students take System Analysis and Design with UPI Study

From business problem to working systemSystem Analysis and Design teaches how analysts study a business need and turn it into a system design: requirements, UML and data-flow models, interface and database design, implementation and project management. It is a core course in information systems and IT degrees.
Credit at a fraction of the priceThe same 3 credits can cost $750 or more at a US college. Here it is one payment, self-paced, with lifetime access.
Finish your degree soonerTransfer credits reduce the courses you still owe, so you can graduate earlier or lighten a heavy semester.
Strengthen an applicationA completed college-level course on an official transcript shows admissions and scholarship committees you can do the work.
Study on your own scheduleNo start dates, no deadlines. Watch the lessons, practice, and take the final when you are ready.
130,000+students helped with college creditsACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Where the credit goes

Universities that work with NCCRS

System Analysis and Design carries a NCCRS credit recommendation. These universities work with NCCRS; some are UPI Study's signed partners. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Thomas Edison State UniversityThomas Edison State UniversitySigned partnerUniversity of the PeopleUniversity of the PeopleSigned partnerCharter Oak State CollegeCharter Oak State CollegeSigned partnerStrayer UniversityStrayer UniversitySigned partnerColumbia Southern UniversityColumbia Southern UniversitySigned partnerIndiana TechIndiana TechSigned partnerPenn StatePenn StateWorks with NCCRSSouthern New Hampshire UniversitySouthern New Hampshire UniversityWorks with NCCRSSUNYSUNYWorks with NCCRS +many more cooperating universitiesFind my college →

Find my college → Not listed? Ask Athena in the corner of this page, or book a 15-minute call.

FAQ

Questions students ask us

About Turning Data into Decisions: Warehousing and Mining for Business Intelligence

What is the difference between OLTP and OLAP?
OLTP (online transaction processing) runs day-to-day operations with many small, fast reads and writes, while OLAP (online analytical processing) supports analysis of historical data with fewer, larger, more complex queries. OLTP schemas are normalized to keep transactions fast and consistent; OLAP schemas are denormalized to speed up large queries. A point-of-sale purchase is OLTP, and a quarterly sales trend report by region is OLAP.
What are the three stages of ETL in data warehousing?
ETL stands for extract, transform, and load, the process that moves data from source systems into a data warehouse. Extract pulls data from sources such as a sales platform, a CRM, or website logs. Transform cleans, standardizes, and reshapes it into one consistent structure. Load writes the finished data into the warehouse. Transformation is needed because each source has its own formats, which cannot be analyzed together until reconciled.
What is the difference between a star schema and a snowflake schema?
A star schema keeps each dimension table flat and joined directly to a central fact table, while a snowflake schema normalizes dimensions into further tables, such as splitting product category into its own table. A star schema is simpler to query and usually faster because queries touch fewer tables. A snowflake schema reduces duplicated data but needs more joins to answer the same question.
What is the difference between classification, clustering, and association in data mining?
Classification assigns records to predefined categories, clustering groups similar records without predefined categories, and association finds items that tend to occur together. Flagging a transaction as fraudulent or legitimate is classification. Segmenting customers by purchasing behavior is clustering. Finding that shoppers who buy diapers often buy baby wipes in the same transaction is association, also called market basket analysis.

About the System Analysis and Design course

How many college credits is System Analysis and Design?
System Analysis and Design (Computer Science 390) carries a NCCRS credit recommendation for 3 semester credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.
How much does System Analysis and Design cost and what is included?
$250, paid once, with lifetime access: all 69 video lessons, a practice quiz after each, the graded work and final exam, and an official transcript when you finish. Students who want several courses choose UPI Study PRO instead.
Which universities accept UPI Study credits?
UPI Study has signed partner agreements with universities such as Thomas Edison State University, University of the People and Charter Oak State College, and works with universities such as Penn State, SNHU and SUNY. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities. Use "Find my college" on this page to check yours.
How long does System Analysis and Design take?
It is self-paced with no deadlines. The 69 lessons total about 9 hours of video; most students add reading and practice time and finish in a few weeks to a few months, on their own schedule.
How are the exams done?
Quizzes and assignments are online in the course. The final exam is taken online with proctoring; a person on the UPI Study team checks assignments, usually within two days.
How do my credits reach my college?
When the course shows 100% complete, request the official transcript from your dashboard. It is sent to the college you name, and you can send it to more than one. Check your college first with "Find my college" on this page, or ask Athena.

Ready for the full course? Earn 3 credits in System Analysis and Design.

69 video lessons, a quiz after each one, and the graded work that earns the credit. Recommended for credit by NCCRS.

Enroll in System Analysis and Design →Download the syllabusEvery course with PRO →
System Analysis and Design
3 credits · NCCRSEnroll →