All-in-One Revision Notes: Data Warehousing &
Tableau for MCQs
This document provides highly condensed key points for rapid revision of all essential Data
Warehousing and Tableau concepts for your MCQ exam.
Part 1: Data Warehousing & Related Concepts
1. Data Warehouse (DW) Basics
● Definition: Central, integrated repository of historical data for analysis.
● Characteristics (S.I.T.N.):
○ Subject-Oriented: Focused on business subjects (e.g., Customer, Product).
○ Integrated: Data from disparate sources is unified, inconsistencies resolved.
○ Time-Variant: Historical data retained, time-stamped.
○ Non-Volatile: Data is loaded, not updated/deleted.
2. DW Architecture (Three-Tier)
● Bottom Tier: Source systems (OLTP), ETL tools.
● Middle Tier: Data Warehouse database, OLAP server (MOLAP/ROLAP/HOLAP).
● Top Tier: Reporting/BI tools (e.g., Tableau), end-users.
3. ETL (Extract, Transform, Load)
● Extract: Get data from sources.
● Transform: Clean, standardize, aggregate, apply rules, convert data types.
● Load: Put data into DW.
● ETL vs. ELT:
○ ETL: Transform before loading (traditional).
○ ELT: Load raw, then Transform within DW (common in cloud).
4. OLTP vs. OLAP
● OLTP (Operational):
○ Purpose: Day-to-day transactions.
○ Data: Current, detailed, volatile.
○ Schema: Normalized.
○ Operations: Read, write, update, delete (fast writes).
● OLAP (Analytical):
○ Purpose: Decision support, complex analysis.
○ Data: Historical, summarized, non-volatile.
○ Schema: Denormalized (Star/Snowflake).
○ Operations: Primarily reads (fast reads of large data).
5. Data Marts & ODS
● Data Mart: Subset of DW for a specific department/function. Can be dependent (from
EDW) or independent.
● Operational Data Store (ODS): Integrated, current data for operational reporting; often a
staging area for DW.
6. Dimensional Modeling
● Purpose: Optimize DW for query performance and intuitive understanding.
● Fact Table: Contains measures (quantitative data like Sales, Profit) and foreign keys to
dimensions.
● Dimension Table: Contains attributes (descriptive data like Product Name, Customer
City) that provide context to facts.
● Star Schema: Central fact table + denormalized dimension tables. Simple, fast queries.
● Snowflake Schema: Fact table + normalized dimension tables (dimensions linked to
sub-dimensions). Less redundancy, more complex.
7. OLAP Operations
● Roll-up (Drill-up): Aggregate to higher level (e.g., Day to Month).
● Drill-down: Go to lower level (e.g., Year to Quarter).
● Slice: Filter for a single dimension (e.g., only "East" region).
● Dice: Filter for multiple dimensions (e.g., "East" region, "Q1").
● Pivot (Rotate): Change orientation of view.
8. Metadata
● Definition: Data about data (source, transformations, structure, lineage). Essential for
understanding and managing DW.
Part 2: Tableau
1. Data Pane Sections
● Dimensions: Categorical fields (blue).
● Measures: Quantitative fields (green).
● Sets: Custom groups (In/Out).
● Parameters: User-defined dynamic values.
● Groups: Combined members of a dimension.
● (Note: "Calculations" are a type of field, not a separate top-level section like
Dimensions/Measures/Sets/Parameters.)
2. Dimensions vs. Measures
● Dimensions (Blue Pill):
○ Qualitative, categorical.
○ Used to categorize, segment, define granularity.
○ Create headers when on shelves.
○ E.g., Customer Name, Region, Order Date (as discrete year/month).
● Measures (Green Pill):
○ Quantitative, numerical.
○ Used for calculations and aggregations.
○ Create axes when on shelves.
○ E.g., Sales, Profit, Quantity.
3. Discrete vs. Continuous Fields
● Discrete (Blue):
○ Individual, distinct values.
○ Create headers (separate labels).
○ E.g., Year(Order Date), Customer ID.
● Continuous (Green):
○ Form an unbroken range of values.
○ Create axes (numerical scale).
○ E.g., SUM(Sales), Order Date (as a timeline).
4. Data Types
● Number (Whole/Decimal), Date/Datetime, String, Boolean, Geographic Role.
5. Data Connections
● Live Connection: Direct query to source. Real-time data. Performance depends on
source.
● Extract (.hyper): Snapshot of data in Tableau's engine. Faster performance. Data is
static until refreshed. Best for large data, slow sources, offline work.
6. Data Structuring
● Joins: Combine columns from tables based on common fields (Inner, Left, Right, Full
Outer).
● Unions: Append rows from multiple tables vertically (same structure needed).
● Data Blending: Combines data from different data sources at an aggregated level. Used
when joins are not possible or practical.
● Pivoting: Transforms rows to columns.
● Splitting: Divides a string field by a delimiter.
7. Filters (Order of Operations - Key!)
1. Extract Filters
2. Data Source Filters
3. Context Filters: Create a temporary subset of data. Operate FIRST among most filters.
(Right-click filter -> "Add to Context").
4. Top N / Conditional Filters
5. Dimension Filters (regular filters)
6. Measure Filters
7. Table Calculation Filters
8. Calculated Fields
● Create new fields using formulas (math, logic, string, date, aggregate).
● Level of Detail (LOD) Expressions:
○ FIXED: Computes value independent of view's dimensions.
○ INCLUDE: Computes value including specified dimensions in addition to view's.
○ EXCLUDE: Computes value excluding specified dimensions from view's.
9. Parameters
● Dynamic values controlled by user.
● Can be used in calculations, filters, reference lines, or to swap elements.
● Cannot be dragged directly onto the Filters Shelf (they control filters, but aren't filters
themselves).
10. Sets
● Custom fields that define "In" or "Out" groups based on conditions or selections.
● Used for comparisons (e.g., High vs. Low Profit Customers).
11. Dashboard Components & Actions
● Dashboard: Collection of worksheets, text, images, web pages.
● Dashboard Objects: Text, Image, Web Page, Blank, Extension, Navigation, Download.
● Dashboard Actions:
○ Filter Action: Use selection in one sheet to filter others.
○ Highlight Action: Highlight marks.
○ URL Action: Navigate to URL.
○ Go to Sheet Action: Navigate to another sheet/dashboard/story.
12. Tableau Story
● Definition: A sequence of worksheets or dashboards that work together to convey a
narrative or information.
13. Tableau Chart Types
● Bar: Comparison.
● Line: Trends over time.
● Scatter: Relationships between two measures.
● Heatmap: Patterns using color intensity (e.g., active users by hour).
● Symbol Maps: Points on a map (size/color for measures).
● Filled Maps (Choropleth): Areas filled with color based on measures.
● Dual Axis: Two measures on same chart with independent axes.
14. Tableau Products
● Tableau Desktop: Authoring tool.
● Tableau Server/Cloud: Sharing, collaboration, governance.
● Tableau Public: Free platform for public sharing.
● Tableau Reader: Free viewer for .twbx files (no editing).
● Tableau Prep Builder: Data preparation tool.
15. Tableau File Extensions
● .twb: Workbook (no data).
● .twbx: Packaged Workbook (workbook + data extract).
● .hyper: Data Extract (new format).
● .tde: Data Extract (old format).
● .tds: Data Source (connection info, no data).
● .tdsx: Packaged Data Source (data source + extract).
● .tfl: Tableau Prep Flow.
● .tflx: Tableau Packaged Prep Flow.
General MCQ Strategy:
● Read Carefully: Note keywords like "best reason," "cannot," "first," "always."
● Scenario-Based: Understand the problem, then apply the most appropriate Tableau/DW
concept.
● Eliminate: Rule out incorrect options first.
● Context is King (Filters): Remember the order of operations for filters.
Good luck with your exam! You've got this!
