Views as facttables

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.

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.

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.

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.

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.

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?

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 !