Newbie Question - Attributes

View previous topic View next topic Go down

Newbie Question - Attributes

Post  GradStudent2015 on Mon Aug 03, 2015 9:21 pm


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

GradStudent2015

Posts : 3
Join date : 2015-08-03

View user profile

Back to top Go down

Re: Newbie Question - Attributes

Post  ngalemmo on Tue Aug 04, 2015 11:48 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Newbie Question - Attributes

Post  GradStudent2015 on Wed Aug 05, 2015 9:58 pm

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

GradStudent2015

Posts : 3
Join date : 2015-08-03

View user profile

Back to top Go down

Re: Newbie Question - Attributes

Post  ngalemmo on Thu Aug 06, 2015 9:04 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Newbie Question - Attributes

Post  sharvan.kumar.83@gmail.co on Fri Aug 07, 2015 11:12 am

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.




sharvan.kumar.83@gmail.co

Posts : 10
Join date : 2014-11-17

View user profile

Back to top Go down

Re: Newbie Question - Attributes

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum