Cubes and OLAP experimental

Cubes and OLAP Data arranged for analysis: dimensions and measures, star schemas, roll-up and drill-down, and the OLAP operations a board can read. Dimensions and measures. Dimensions and measures. Distinguish dimensions from measures and model them in a schema.. Facts, Dimensions, Measures, Grain Facts A fact is a business event worth measuring: a sale, a shipment, a support ticket closed, a page view. In a data warehouse, facts live in fact tables: long, narrow tables where each row is one occurrence of the event. A retailer's fact_sales table might have one row per line item on a receipt: millions of rows, but each one simple. A fact table's foreign keys are what tie each event back to its dimensions, one column per dimension the event can be sliced by; a well-designed fact table is often mostly foreign keys and a handful of numeric measures, with almost no descriptive text of its own. Dimensions Dimensions answer who, what, where, and when about a fact. A dim_product table carries product name, category, brand, and size. A dim_store table carries region, city, and store format. A dim_date table carries day, month, fiscal quarter, and holiday flags. Dimensions are wide and relatively short compared to facts: a chain with 500 stores has a 500-row dim_store table, but that table may be joined against tens of millions of fact rows. Dimensions are what you filter and group by; they rarely get summed. Measures Measures are the numeric columns on the fact table: units sold, revenue, discount amount, cost. Measures are what you aggregate: sum, average, count, min, max. A key design skill is knowing which measures are additive (units sold can be summed across stores and days without distortion), which are semi-additive (an account balance can be summed across accounts but not across time), and which are non-additive (a ratio like margin percentage must be recalculated from its additive components, never summed directly). Getting this wrong produces dashboards that are internally consistent but factually wrong: a summed percentage that looks plausible and is nonsense. Grain Grain is the single most important decision in fact table design: it is the precise statement of what one row represents. "One row per order" and "one row per order line item" are different grains, and every measure and every dimension attached to the table must be true at that grain. Declare the grain in a single sentence before adding a single column. A common failure mode is mixing grains in one table (some rows at order level, some at line-item level), which silently breaks every SUM. When a new requirement arrives ("we need promotion code per line item"), check it against the declared grain first: if the requirement needs finer detail than the grain provides, the grain must change, not just the column list. Grain also determines which joins are safe: a fact table at order-line grain can be joined to dim_product and summed correctly, but joining the same table to a dim_order attribute that only varies at the order level, such as shipping method, and then summing a measure risks fan-out duplication if that dimension is not also declared at line-item grain. Stating the grain explicitly in the fact table's documentation, and testing a handful of known totals against it after any schema change, catches this class of error before it reaches a dashboard. Related CCI capabilities A fact table's declared grain is not only a query-performance decision: it determines how precisely a single breach or a single misconfigured dashboard can expose individually identifying detail versus safely aggregated summaries. CCI's Secure Software Development Life Cycle (SDLC) at Scale engagement (https://www.cambridgecyberinternational.com/en/services/secure-sdlc/) reviews data-warehouse and dimensional-model design alongside application code, since a grain decision made early in a warehouse build has the same lasting security consequences as a schema decision made early in an application. Star schemas. Star schemas. Explain the star schema pattern and design one from a set of business questions.. Star vs snowflake, Surrogate keys, Slowly changing dimensions, Conformed dimensions Star vs snowflake A star schema puts one fact table at the center with denormalized dimension tables radiating out, each joined directly to the fact table by a single hop. dim_product carries category and brand as plain columns, even though category and brand are themselves normalizable into separate tables. The payoff is query simplicity and speed: a Business Intelligence (BI) tool joins the fact table to a handful of dimensions with no multi-hop joins, and business users can reason about the model without a data modeling background. A snowflake schema normalizes those dimensions further: dim_product joins to dim_category, which joins to dim_department, trading storage savings and stricter integrity for more joins per query and a harder mental model. For most analytic workloads, star wins on query performance and comprehensibility; snowflake earns its complexity only when a dimension is huge, changes structure often, or must guarantee referential integrity that the business genuinely cannot tolerate violating. Surrogate keys A surrogate key is a warehouse-generated integer (or similar) that identifies a dimension row, independent of whatever identifier the source system uses (the natural or business key). Never use the natural key alone as the dimension's primary key. Source systems reuse codes, retire and reissue customer IDs, and change formats when systems migrate; surrogate keys insulate the warehouse from all of that. Surrogate keys are also what make slowly changing dimensions possible: the same customer's natural key can appear on several dimension rows, each with a different surrogate key, representing that customer at different points in time. Slowly changing dimensions Dimension attributes change: a customer moves regions, a product is recategorized. A Slowly Changing Dimension (SCD) describes how a dimension table handles those changes over time. SCD Type 1 overwrites the old value: simplest, but destroys history, appropriate for correcting errors. SCD Type 2 inserts a new row with a new surrogate key and effective-date range (or a current-flag), preserving full history so that historical facts still join to the dimension values that were true when the fact occurred. SCD Type 3 adds a new column to hold the previous value alongside the current one, useful for a single prior state, rare in practice beyond that. Type 2 is the default choice for anything a board decision might hinge on, because it lets you truthfully say "this customer was in the Northwest region when that order was placed," even after they moved. Conformed dimensions A conformed dimension is shared, with identical structure and meaning, across multiple fact tables or data marts: the same dim_date used by sales, shipping, and support facts. Conformed dimensions are what let you compare or combine metrics from different business processes (revenue per day next to support tickets per day) without reconciliation gymnastics. Building conformed dimensions once, centrally, is what separates a coherent warehouse from a pile of inconsistent spreadmarts. Conforming a dimension is a one-time cost paid centrally rather than an ongoing cost paid by every downstream report: once dim_date is built and agreed, every fact table that joins to it inherits a consistent fiscal calendar, holiday flag, and quarter boundary without any team having to re-derive those rules. The alternative, letting each team build its own date logic inside a report, is how two dashboards showing the same quarter's revenue quietly disagree by a few percentage points, an outcome that is far more expensive to diagnose after the fact than the conforming work would have cost up front. Related CCI capabilities Surrogate keys, SCD Type 2 history, and conformed dimensions all affect how much of a customer's or an employee's history a single dimension-table breach can expose, and how quickly an incident can be scoped across every fact table that references it. CCI's Secure Software Development Life Cycle (SDLC) at Scale engagement (https://www.cambridgecyberinternational.com/en/services/secure-sdlc/) reviews dimensional design choices such as these as part of the same lifecycle discipline it applies to application-level identity and access design. OLAP operations. OLAP operations. Identify roll-up, drill-down, slice and dice operations and apply them to a cube.. Roll-up, Drill-down, Slice and dice, Pivoting Roll-up Roll-up aggregates data up a dimension's hierarchy, trading detail for a wider view: daily sales rolled up to monthly, monthly rolled up to quarterly, city rolled up to region rolled up to country. Each step sums (or otherwise aggregates) the finer-grained values into a coarser bucket. Roll-up is what turns a cube with a million fact rows into the twelve numbers a board can absorb in one slide. The operation only works cleanly when the underlying measure is additive along that dimension: rolling up an average by naively averaging averages is a classic and quiet error; Business Intelligence (BI) tools typically compute roll-ups on the fly from the finest stored grain rather than pre-materializing every level, trading a small amount of query latency for the guarantee that every roll-up is always consistent with the underlying detail. Drill-down Drill-down is the inverse: moving from a summary view to finer detail along the same hierarchy, from quarter to month to day, or from region to store to transaction. Drill-down is how an analyst investigates an anomaly: a board sees quarterly revenue dip, and drilling down reveals it concentrated in one region, then one store, then one week. A cube that supports drill-down needs the underlying fact grain to actually hold that detail; you cannot drill down past the grain you chose when you built the fact table, which is why grain decisions from earlier in this course matter here. Slice and dice Slicing fixes one dimension to a single value and looks at the resulting cross-section: "show me all metrics for Q3 only," collapsing a cube to a lower-dimensional view. Dicing selects a sub-cube by constraining two or more dimensions simultaneously: "show me the Northeast region, Q3, for the Electronics category," narrowing along several axes at once. Both operations are filters, not aggregations; they change which rows participate, not how the rows are summarized. Slicing and dicing are how the same cube answers different, narrow questions without rebuilding anything. Pivoting Pivoting reorients the cube's axes, rotating which dimension appears as rows, which as columns, and which as the filtered page. A sales cube might be viewed with products as rows and months as columns, then pivoted to show regions as rows and product categories as columns instead. Pivoting is purely a presentation change: the underlying data and its grain are untouched, but the same numbers become easier to answer a different question. Fluency with pivot, roll-up, drill-down, slice, and dice together is what lets an analyst turn one well-built cube into dozens of distinct, correct answers without writing new queries each time. None of these five operations require touching the underlying fact or dimension tables: roll-up, drill-down, slicing, dicing, and pivoting are all views computed on demand from the same stored grain, which is precisely what makes a well-designed cube reusable across many questions instead of requiring a bespoke extract for each one. A BI tool that exposes all five directly to an analyst, rather than hard-coding one fixed report layout, is what turns a single well-modeled cube into the interface for an entire department's ad hoc questions. Related CCI capabilities Roll-up, drill-down, slice, and dice all read from the same underlying grain, so scoping who may invoke which operation is an access-control decision layered on top of the model, not something the Online Analytical Processing (OLAP) operations enforce by themselves. CCI's Secure Software Development Life Cycle (SDLC) at Scale engagement (https://www.cambridgecyberinternational.com/en/services/secure-sdlc/) reviews exactly this kind of presentation-layer access boundary as part of building a dashboard or reporting layer securely from the start.