Is there a reason why someone should not use views to create Fact tables?

No there aint.
The SQL Statement of the view only shouldn't be too complex to slow the
processing down, but aside of that, no limitations but a perfect layer for
small changes and easy development.

Greetings Monte

"SAM" wrote:

Sam,

after years of experience and tests in many combinations of the
factors, I have these guidelines..

1) Unless you have only a few Mb of database, it is ALWAYS BETTER to
create your data mart to feed dimensions and cubes

2) When you present dimension and fact tables to SSAS, it is better to
create a VIEW on the database that is easy to maintain if you have to
implement some logic on the query to present data to SSAS (for example,
a join to decode some fields - it should not be the standard case, but
it happens in the lifetime of a project when you need to add some
attribute and temporarily want to show data that are still not
implemented well in the denormalized star schema) - the ideal case is
that all these views are SELECT * FROM table, because you only decouple
the physical fact table from the logical view for SSAS

3) Use named query on DSV only to apply some cosmetic change to data
just to correctly populate dimensions and cubes. For example, a
lastname + ' ' + firstname expression is right to use here, while I
can't see many reasons to use a WHERE or a JOIN, because it imply some
transformation logic that is better to place into the VIEW (that could
be used by other SQL queries, for example for Reporting Services).

Feedback from the community would be very appreciated.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi


SAM wrote:

Recently is was creating a new cube in 2005. I started by using a view as
my fact table. Processing the cube took around 30 seconds for only 69K
rows. I then created a table and a job that that copies all records from
the view into the table. Processing the cube then took 2 seconds. Not sure
if anyone else sees this kind of performance change, but that is my most
recent experience.

Chris
"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

What is your idea about to have al business logic in 1 place in the
named views in SSAS.

Marco


Marco Russo schreef:

did you 'unprocess' the cube after processing it for the first time?

it wouldn't surprise me.. also.. were the dimensions already processed?
the 'first time you processed' that could include things like
processing dimensions; etc

i just want to make sure you're comparing apples to apples *(no pun
intended)

-Aaron


Chris wrote: