MAY 3, 2002 1:00am ET

Related Links

Which CDC method is the best to achieve staging database with changed data?
March 7, 2008
Apart from bloated dimension, what are the negatives of using all known attributes in your SCD?
March 7, 2008
When is it better to have normalized data to create data marts and when is it better to have dimensional data?
March 7, 2008

Web Seminars

Essential Guide to Using Data Virtualization for Big Data Analytics
September 24, 2014
Integrating Relational Database Data with NoSQL Database Data
October 23, 2014

What is the difference between summary and aggregate data?


This is a simple data mart question. What is summary data? Most books and white papers state that the star schema contains aggregated and summarized data. I understand aggregated data (i.e., monthly sales), but what is summarized? Is it a subset of the detail data or is there some type of transformation to the data to make it summarized?


Les Barbusinski’s Answer: In my experience, the two terms are used interchangeably most of the time. However, I have heard some people make the following distinction between the two: aggregate tables simply sum up transactional metrics against one or more dimensions, while summary tables store "synthesized" metrics. An example of this would be an accounting data warehouse where historical general ledger transactions are stored. An aggregate table would simply summarize these transactions by account, department and month. A summary table, on the other hand, would provide P&L metrics (revenue, gross margin, net income and other KPIs) by department and month.

Scott Howard’s Answer: I also have problems with this phasing. It's something that someone penned many years ago and seemed to stick. Yes summarization is a form of aggregation, thus the basis for our confusion. I think what was intended was an attempt to compare a typical application oriented or OLTP model to a typical DW or data mart model. OLTP models are generally very specific containing current detailed data. On the other hand, DW models contain very summarized historical information materialized and maintained in a way not possible in OLTP models. Now how do we get from one model to the next? Aggregation: average, sum etc.

Chuck Kelley’s Answer: I believe that aggregate and summarized are the same thing. A synonym for aggregate is summative (according to the Thesaurus in Microsoft Word). Some people use the term summarized and others use aggregate (including me!). Some of us try to be a bit more precise and use the word "or" instead of "and" (i.e., "… contains aggregated or summarized data …"), but it doesn’t always happen. Sorry for the confusion!

David Marco’s Answer: Summarized and aggregation are the same thing.

Clay Rehm’s Answer: In its simplest form, summary data is data that has been "summarized," or aggregated. This means that some form of detailed data has been rolled up to less detail. How this is physically stored depends on the preference of your data analysts and DBAs. Summary data can reside in a star schema, and it can reside in a single table that has been "flattened;" that is the key element of each dimension and the fact table are built into one "flat" table.

Summarized can mean that the data was simply rolled up (added up) or there were complex transformation rules to summarize the data. Additionally, summary tables can be at whatever level of detail the user needs, just as long as it is in one easy to access place.

Get access to this article and thousands more...

All Information Management articles are archived after 7 days. REGISTER NOW for unlimited access to all recently archived articles, as well as thousands of searchable stories. Registered Members also gain access to:

  • Full access to including all searchable archived content
  • Exclusive E-Newsletters delivering the latest headlines to your inbox
  • Access to White Papers, Web Seminars, and Blog Discussions
  • Discounts to upcoming conferences & events
  • Uninterrupted access to all sponsored content, and MORE!

Already Registered?

Filed under:


Comments (0)

Be the first to comment on this post using the section below.

Add Your Comments:
You must be registered to post a comment.
Not Registered?
You must be registered to post a comment. Click here to register.
Already registered? Log in here
Please note you must now log in with your email address and password.
Login  |  My Account  |  White Papers  |  Web Seminars  |  Events |  Newsletters |  eBooks
Please note you must now log in with your email address and password.