Newbie Question - Attributes

Hi Community,

New to the forums and I have a newbie question.

Building my first real data warehouse (non-academic) and had a data model/panning question.

Can you use an aggregate (rolled-up data) as as attribute in a dimension?

For example, if I don't have the lowest level of data for a dimension called Product Sales Transactions but instead have only the rolled-up totals, like # of Product Sold, # of products returned, etc.. Can I just use that as the data attributes? So when I create a fact table for those particular attributes I just need to insert those attributes without any need for summation?

I haven't see this done, and haven't seen any examples, but for some of the data I have the lowest level and for some I only have rolled-up/aggregates.

Thanks in advance.

GradStudent2015

Product Sales Transactions is not a dimension.

Dimensions represent business entities, such as product, customer, department, etc...

Facts represent business transactions or states, such as sales, enrollment, etc...

To your question, can you have aggregate fact tables? Yes, but it is bad practice to only have aggregates. Ideally you want to load and maintain atomic level detailed facts. This provides the richest source for analytics. Aggregates may be created from the atomic level facts, usually to support a specific use case or improve performance.

Thank you for responding, you did answer my question. I thought that as well, at least that's what I've been led to believe, that the attributes ideally should be the lowest (row-level) form of data to leverage the full power of a data warehouse.

Just a follow up, I would think a Table/Dimension that represents a sale of a product (sale ID, date of sale, type, product, customer name, clerk, etc...) would represent a valid entity? Is that incorrect?

Thanks

Facts represent business actions while dimensions provide the context of that action.  Dimensions have attributes that provide context.  Facts contain measures which represent the magnitude of the action, and references to dimensions.

A sale is an action, an event. It is a fact. Dimensions provide the who,what, where, when and sometimes why.

flow of fact table-

Transaction table ( or transaction fact ) Contains details level or atomic. --> Aggregate or Snapshot ( contains Consolidated data for reporting layer ).

Always try to model the fact on lowest grain/Cardinal data then go up.