dwh problem

Hi,
I have to choose between
a) using one single fact table
b) using one fact table and several dimension tables
I know that if you have no dimension tables (i.e. you store all the
information on one single fact table) your sql will not include any joins. I
understand joins penalize performance so it would make sense to go for this
option. Can someone tell me if a star schema approach with surrogate keys on
the fact table pointing to each dimension table would be less performant tha
n
the single fact table approach? What would be more efficient if I want to us
e
Business Objects as the reporting tool? What are the pros and cons of each
option?
If anyone has previously had to make a similar decision I would very much
appreciate the help.
Thanks a lot,
RobertoHi rufian,
I can't completely answer your question, but my experience so far has been
that using the surrogate keys is worth doing. I have built fact tables befor
e
that should everything in one table and it didn't significantly improve
performance.
Analysis services 2005 also seems to be happier with the star schema, but I
don't know much about it.
One of the biggest benefits of the star schema is that you can add more
attributes and dimensions much more easily.
If you know how big your fact table will be, then the decision may be made
for you. As soon as your fact table starts getting big, you should use
dimension tables. If you have 1 million rows in the fact table, there is no
way I would skip the dimension tables. Even for 100,000 I probably wouldn't
skip them.
I hope that helps.
"rufian" wrote:

> Hi,
> I have to choose between
> a) using one single fact table
> b) using one fact table and several dimension tables
> I know that if you have no dimension tables (i.e. you store all the
> information on one single fact table) your sql will not include any joins.
I
> understand joins penalize performance so it would make sense to go for thi
s
> option. Can someone tell me if a star schema approach with surrogate keys
on
> the fact table pointing to each dimension table would be less performant t
han
> the single fact table approach? What would be more efficient if I want to
use
> Business Objects as the reporting tool? What are the pros and cons of each
> option?
> If anyone has previously had to make a similar decision I would very much
> appreciate the help.
> Thanks a lot,
> Roberto
>|||I completely agree: the single fact table is not easy to manage in the
future; moreover, it is less performant due to the very high
denormalization (you create an higher I/O work for SQL Server).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Eager to Learn ha scritto:
[vbcol=seagreen]
> Hi rufian,
> I can't completely answer your question, but my experience so far has been
> that using the surrogate keys is worth doing. I have built fact tables bef
ore
> that should everything in one table and it didn't significantly improve
> performance.
> Analysis services 2005 also seems to be happier with the star schema, but
I
> don't know much about it.
> One of the biggest benefits of the star schema is that you can add more
> attributes and dimensions much more easily.
> If you know how big your fact table will be, then the decision may be made
> for you. As soon as your fact table starts getting big, you should use
> dimension tables. If you have 1 million rows in the fact table, there is n
o
> way I would skip the dimension tables. Even for 100,000 I probably wouldn'
t
> skip them.
> I hope that helps.
> "rufian" wrote:
>