Hi guys and gals!

I’m working on a warehouse project and I’m trying to create a design. I wrote some specifications for a table design there is some debate on the design. There are some members of the my team that believe that rather than creating a table we should create a view. I’ll go into the design a little here.

The proposed table design would have about 48 columns. 22 of these columns are keys back to dimensions; the rest are specific metrics (dollar amounts) that are attached to a claim. The metrics have to be calculated from the OLTP data.

The alternative design would create a view that joins a fact table (22 columns) and a separate table for each metric (26 tables) that would be joined back to the fact. All of the joins (for the metric tables) would have to be outer joins.

The reports that would be hitting the table (or view) would not be using all 48 columns at the same time. We might be using 10 of the foreign keys (to the dimensions) and maybe 3 of the metrics on a given report. The table will be about 8 million rows and we are using SQL Server 2005.

I’m being told that the view would be faster than the table; I’m not sold on this idea. But I realize that I have more to learn so I want to do my research first.

If any of this is not clear, please let me know; this is my first post .

Thanks in advance!

The best solution is not to create a bunch of fact to single dimension joins, but to create a view of the fact and all the necessary dimensions and attributes for the specific report. Then the report queries the view like a single table.