Views as facttables

View previous topic View next topic Go down

Views as facttables

Post  ObjectiveC on Thu Apr 07, 2011 3:19 am

Hello everyone,

What is the convention for creating multiple views that act as fact_tables ? Is this considered to be bad practice or not ? Would you do it or have you done it ?

I'm new to data warehousing and databases altogether, so bare with me if this sounds like a silly question.

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Views as facttables

Post  Jeff Smith on Thu Apr 07, 2011 9:54 am

I don't think there is enough info to go on.

If you want to create an aggregate fact table from a detailed fact using a materialized or indexed view, then I think that would be OK, even preferable if the aggregation is based on dimension tables where the hierarchy is subject to change.

If you want to create views that union data across different fact tables (say you were a bank and had ATM transactions, branch transactions, call center transactions, etc, which were different enough that you wanted to put them in their own fact tables but wanted to analyze all of the transactions based on common dimensions), then I could see that as long as everything was indexed really well - this is sort of a poor mans partitioned table with a twist. This might be needed because of poor dimension table design.

But, if you are talking about creating a view of a fact table where the view involves joins from a source to the dimension tables to get the dimension keys, then I think it's a really bad idea.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Views as facttables

Post  ngalemmo on Thu Apr 07, 2011 1:56 pm

It is not an uncommon practice. Some shops as a matter of course create views of everthing as a means to implement security and control user's access to columns. Performance would be a matter of what is in the views and how your database treats them. In most cases, the database will push predicates to the views so that performance is basically the same as if they were queries against the tables. However, certain analytic functions, embedded in views, that use windowing (such as LEAD and LAG) will often require the entire view be materialized before predicates are applied. In such cases, performance against the view would be terrible.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Views as facttables

Post  ObjectiveC on Thu Apr 07, 2011 4:31 pm

Hello there,

First off, I thank you both for taking the time to reply !

Moreover, I apologize if the question is too obscure. I have a hard time explaining myself since all this database stuff is still new to me.

The situation I have is that there is a fact_table where a record/row belongs to a certain group/type. Let's say we have the groups A, B and C. Now what I want to do is create a view that will contain only the records that belongs to group A. I just didn't know if this is common practice for data warehousing (since fact_tables tend to get very large), but If I understand you guys well, it's not that unusual.

As for performance, I thought that could be "fixed" with aggregate tables.


I hope that I explained myself well. If not, do let me know.


ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Views as facttables

Post  hang on Thu Apr 07, 2011 6:05 pm

What you are doing is perfectly valid from dimensional and relational modeling stand point. Both aggregate and Cube will love it too, so go for it.

hang

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

View user profile

Back to top Go down

Re: Views as facttables

Post  Jeff Smith on Fri Apr 08, 2011 8:56 am

Is it better to create 1 fact and multiple views off the fact, or multiple facts and one UNION view across the facts?

If it's asingle fact table, should it be partitioned based on Group to make the views run faster?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Views as facttables

Post  ObjectiveC on Mon Apr 11, 2011 2:42 am

I don't know what common practice is, but at the moment I prefer multiple views on the fact_table.

Now, it is a single fact_table, but I don't quite know what you mean by partioning, but I'll look in to it.

This was really helpfull by the way. I created the view and everything appeared to work just fine. The performance I get by creating aggregate tables.

Thanks !

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Views as facttables

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