fact table as a dimension

View previous topic View next topic Go down

fact table as a dimension

Post  Phil_Lau on Wed Sep 29, 2010 1:37 pm

Hi

I'm presently modeling a data mart and I would like to use a fact table as a dimension for another fact table. Is it something doable and efficient or is there a better way to do that? if yes should i just create a surrogate key and link both table?

thanks

Phil

Phil_Lau

Posts : 3
Join date : 2010-09-29
Age : 34

View user profile

Back to top Go down

Re: fact table as a dimension

Post  BoxesAndLines on Wed Sep 29, 2010 1:43 pm

Yes it's doable. Hard to say whether there's a better way based on the details provided. Ideally, you'll have a degenerate dimension to link both tables.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: fact table as a dimension

Post  AaronLS on Wed Sep 29, 2010 1:50 pm

I am not an expert on this, so I can't say if what you want to do is doable or not, but I have a couple ideas depending on what your actual goal might be.

Is your existing fact table only measures? Or is there anything there that could be considered dimensional attributes? You could split it into two tables, a fact and dimension, and then the new fact table would relate to the dimension, and could drill across to the other fact table if a query involves measures from both fact tables.

The other thing to consider is whether there are any of your existing dimensions that the two fact tables could potentially share which you could use to drill across. This of course would only make sense depending on what type of query you are trying to support and what the dimension represents.



AaronLS

Posts : 8
Join date : 2010-02-24

View user profile

Back to top Go down

Re: fact table as a dimension

Post  Phil_Lau on Wed Sep 29, 2010 2:04 pm

ok
thanks for the quick reply

Phil_Lau

Posts : 3
Join date : 2010-09-29
Age : 34

View user profile

Back to top Go down

Re: fact table as a dimension

Post  ngalemmo on Wed Sep 29, 2010 4:30 pm

The thought of a fact table as a "dimension" of another fact table is simply wrong. There is no such thing in a dimensional model. What it is is integrating two facts along conforming dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table as a dimension

Post  Jeff Smith on Thu Sep 30, 2010 4:53 pm

This probably isn't what the original question is about, but... A date dimension can be used as a fact table. It's a poorly designed one, but there is no reason it can't be used as a fact table. It can be a factless fact table that can answer the number of days in a period or the number of Saturdays in a Year, for example

If I recall correctly, a dimension table with nothing but Type 1 changes can be used as a fact table.



Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: fact table as a dimension

Post  ngalemmo on Thu Sep 30, 2010 5:14 pm

I disagree.

One of the distinctions between relational modeling and dimensional modeling in a relational environment is that in dimensional modeling tables are not peers. They have a distinct purpose and relationships between such tables are clear and absolute. Fact tables hold measures, dimensions hold attributes. Dimensions provide context to facts. Facts have foreign keys to dimensions and are always a many to one relationship. When a many-to-many relationship exists, a bridge table is used between the fact and dimension.

It is this fundimental form of the dimensional model that leads to its simplicity in use in analytics. Query paths are symetrical and consistant and there are well defined methods to combine facts.

Reporting dimensional attributes without the use of a fact table does not make them 'facts'... nor does combining data across two fact tables make one of them a dimension.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table as a dimension

Post  Jeff Smith on Thu Sep 30, 2010 6:04 pm

Are you suggesting that if you wanted to count the number of Saturdays in 2010, a Date Fact Table would be needed.

Let's say you had a factless fact table for bank branches. Your branch dimension had the Name, address, and status of the branch. All of the columns were Type 1. This would give you a fact/dimension table with a one-to-one relationship. You would get the same answer querying only the Dimension Table as you would the Fact Table and the Dimension table.

I'm not suggesting that this be done on a regular basis. I'm just saying that there are times when a dimension table can be used as a fact table. The date dimension in particular is a dimension table that can be used on it's own to produce accurate facts.

I don't go out of my way to create such dimension tables, and this is certainly more of an intellectual discussion than advocating the practice. But there maybe times when a dimension table can be set up in a query tool as a fact table.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: fact table as a dimension

Post  ngalemmo on Thu Sep 30, 2010 6:21 pm

No, I am not saying that at all. What I am saying is the number of Saturdays in 2010 is an attribute of 2010 and not a measure.

We landed on the moon is a fact, but not a 'fact table' in a dimensional model. Don't confuse terminology.

Why would you have a factless fact table containing a single foreign key? Why do you believe you need to have a fact table to report attributes? You do not need measures to report dimensional attributes, but you do need dimensional attributes to report measures.

Reporting dimensional attributes, such as producing a list of customers, is not using the dimension as a fact table. It is simply reporting a dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table as a dimension

Post  Phil_Lau on Thu Sep 30, 2010 7:07 pm

thanks.
I will go with the conformed dimension and then drill accross.

Phil_Lau

Posts : 3
Join date : 2010-09-29
Age : 34

View user profile

Back to top Go down

Re: fact table as a dimension

Post  hang on Fri Oct 01, 2010 7:58 am

Phil_Lau wrote:I will go with the conformed dimension and then drill accross.
Way to go!

Always stick to the principle and that is, as ngalemmo stressed, fact table should be highly normalised with only measures, dimension keys and occasionally degenerate dimensions and flags. A table with dimensional attributes referenced by fact table should not be treated as fact in the context of dimensional modeling where a fact table or a factless fact table should be surrounded by dimension tables. Aggregation on individual dimension does not change the nature of a dimension into a fact.

The point is, think dimensionally instead of relationally when coming to dimensional modeling. Getting back to the initial post, relating two fact tables through FK rather than dimension conformance is a typical example of relational thinking.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: fact table as a dimension

Post  Jeff Smith on Fri Oct 01, 2010 2:11 pm

Saturday is an attribute of a Date Dimension, but Day is the fact. Can you get an accurate count of days from using the Date Dimension Table on it's own? Yes, of course. That means it can act like a fact table. Granted, it's not a fact table that is designed particularly well and it may not adher to rules for creating a fact table. But is has a measure and it can produce an accurate number.

Design isn't the only thing the determines if a table is a fact table or dimension table. Functionality plays a role in the definition as well.

If you created a view of a fact table and all of the attributes from all of the connected dimension tables, would it still be a fact table?

Ask Warren Thornthwaite if a dimension table with just Type 1 columns can be used as a Fact Table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: fact table as a dimension

Post  ngalemmo on Fri Oct 01, 2010 2:35 pm

Jeff Smith wrote:Design isn't the only thing the determines if a table is a fact table or dimension table. Functionality plays a role in the definition as well.

If you created a view of a fact table and all of the attributes from all of the connected dimension tables, would it still be a fact table?

Design IS the ONLY thing. That is what data modeling is. Its design. If you are developing a dimensional model, there is a terminology and approach to describing the model. It has nothing to do with what you are allowed or not allowed to query or what you do with the results.

A view is a query. It has nothing to do with the underlying model. It would not be a fact table... it is a view. If it is a materialized view, you wind up with a table from a relational DB standpoint. Not something I would be willing to show anybody and claim it to be a fact table.

Bottom line is, to entertain the notion of a fact table as a dimension implies there is a direct foreign key relationship between two fact tables. That is an ER model, NOT a dimensional model. A star (or snowflake) schema contains ONE and only one fact table and any FK relationship is from a fact table to its dimensions (or bridge table).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table as a dimension

Post  Jeff Smith on Fri Oct 01, 2010 2:53 pm

OK, so show me how you would count the number of days in a time period. Would you create a Date Fact Table? How many Dimension Keys would be in it? What would the measure be?

A date dimension table is a factless fact table with a bunch of digenerate dimensions.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: fact table as a dimension

Post  ngalemmo on Fri Oct 01, 2010 4:05 pm

If all I wanted to do was calculate the number of days in a month or some other time period, I would query the date dimension. I think I had made that clear already.

Why would you do anything different? Why would I have to call the date dimension a "fact table"?

There is nothing in dimensional modeling that dictates a query must involve a fact table. A fact table represents business events or states. The number of days in a time period involve neither of these. Whereas, the number of days in a time period where product X was sold IS a query that involves a fact table BECAUSE it involves a business event, a sale. But the date dimension is still a dimension and always will be.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table as a dimension

Post  Jeff Smith on Mon Oct 04, 2010 3:17 pm

If it walks like a duck and talks like a duck........

Ok, the date dimension is a dimension table. But if you can query a table and get a fact out of it, isn't it technically a fact table? The point is, it is possible to treat dimensions with only Type 1 changes as fact tables.

If you have a product dimension that has nothing but Type 1 changes, then you could go to the product dimension to count how many products the company has.

Knowing this is key when creating an interface for a query tool.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: fact table as a dimension

Post  ngalemmo on Mon Oct 04, 2010 3:34 pm

Jeff,

I though this discussion was about dimensional modelling? What is a 'fact' in that context? True, in a dimensional model there is a thing called a 'fact table', but it contains measures, not facts.

An entry for 10/4/2010 in a date dimension will have a day of week attribute indicating it is a Monday. Certainly that is a fact, and a true one, but does that now make it a fact table? I think not.

If you consider any value in any table as being the same stature as any other value in any other table, you are looking at tables as peers. You are looking at a database like a relational model. You are not thinking dimensionally.

The difference between ER modeling and Dimensional Modeling has everything to do with terminology and method. Achiving a correct dimensional model involves a different thought process than achieving a correct ER model. If you blur the two, you cannot be successful in achiving either... you wind up with a confused mess.

Don't confuse 'fact' with 'fact table'. Everything in a data warehouse is, hopefully, a fact. A customers name, how much they purchased last month, how many days in September. Sure they are facts, but that has nothing to do with how they are treated in a dimensional model.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table as a dimension

Post  hang on Tue Oct 05, 2010 8:17 am

Jeff Smith wrote:The point is, it is possible to treat dimensions with only Type 1 changes as fact tables.
Type 1 is not the reason why you can do aggregation on a dimension table. You could aggregate on a type 2 dimension as well, where you may need to specify a point of time or date range. So in that sense, any dimension table, regardless of type 1 or type 2, contains some kind of fact when you need to aggregate on it. But that's beside the point. Let's go back to the initial post.
Phil_Lau wrote:I'm presently modeling a data mart and I would like to use a fact table as a dimension for another fact table. Is it something doable and efficient or is there a better way to do that? if yes should i just create a surrogate key and link both table
I would say no, you don't just create surrogate key on a fact table in order to relate all its fields to another fact table, which is a common practice in relational modeling. Whereas in dimensional modeling, if the two fact tables share the same dimensionality, you consolidate them into a single fact table, although the load difference may require to keep them as two. If they share some common dimensions, or dimensions common at aggregate level, the common dimensions are said to be conformed across the fact tables and therefore provide a natural drill across path between them without any embellishment. To me, that's dimensional thinking.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: fact table as a dimension

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