What is an OLAP cube and its operations?
multidimensional analysis concepts.
a cube pre-aggregates measures across dimensions; operations are slice, dice, drill-down, roll-up, and pivot.
What's really being asked
This evaluates your grasp of multidimensional analysis and your judgment on when precomputation pays off versus when it wastes effort.
The full answer
An OLAP cube is a multidimensional structure that organizes a measure, such as sales, across several dimensions, such as time, product, and region, with aggregates precomputed at various intersections so reports return quickly. The standard operations are slice, which fixes one dimension to a single value to get a sub-cube, for example sales for one month; dice, which selects ranges across two or more dimensions to get a smaller cube; drill-down, which moves to finer granularity, like quarter to month to day; roll-up, which aggregates to coarser granularity, like city to region to country; and pivot, which rotates the cube to view dimensions along different axes. The decision to pre-aggregate depends on access patterns. Pre-aggregating into a cube or materialized aggregate suits repeated, predictable queries that demand low latency over stable dimensions, since you pay computation once and serve fast reads. Querying raw data directly suits ad hoc exploration, very high-cardinality dimensions where precomputing all combinations explodes in size, and cases needing the freshest data, since cubes lag until rebuilt.
The mistakes people make
Mixing up slice, which fixes a single dimension value, with dice, which selects multiple ranges. Reversing drill-down and roll-up. Saying pre-aggregation is always better, ignoring storage blow-up from too many dimension combinations and the staleness between rebuilds. Forgetting that modern columnar engines often query raw data fast enough to reduce the need for full cubes.
What usually comes next
How does cube cardinality explode with dimensions, and what is sparsity? When do materialized views replace cubes? How do you keep a cube fresh? What is ROLAP versus MOLAP versus HOLAP?
A concrete example
A finance dashboard shows revenue by region and quarter for executives every morning. Pre-aggregating those into a cube makes the dashboard instant. When an analyst then explores an unexpected dip down to individual transactions on rarely combined filters, querying the raw warehouse directly is the right tool, since no cube anticipates that ad hoc cut.
Interview question
Which describes the OLAP operations correctly, and when does pre-aggregating into a cube make most sense?
- a.Slice selects ranges across many dimensions; pre-aggregate only for one-off ad hoc queries
- b.Drill-down aggregates to coarser levels; pre-aggregate whenever data must be perfectly fresh
- c.Slice fixes one dimension value and roll-up aggregates to coarser granularity; pre-aggregate for repeated low-latency queries on stable dimensionsCorrect
- d.Dice fixes a single dimension; pre-aggregate to reduce storage usage
Why? this is the answer
Slice fixes one dimension and roll-up moves to coarser granularity, and cubes pay off for repeated low-latency queries on stable dimensions. The distractors swap slice and dice, reverse drill-down and roll-up, or misstate when precomputation helps.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #olap
- #data-warehouse
- #cube
- #aggregation
- #analytics
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles