OK, I'm in a new job, working on a new application that uses Analysis
services. I suggested a change to the database to use a View in SQL Server
rather than having an almost duplicate table stored in the database. One of
my colleages stated 'Views are a bad idea in OLAP'. Could anyone shed light
on their experiences with views in OLAP applications to help me figure this
statement out.

Absolutely totally incorrect. In many cases the only way to do something is
to use a view. To Analysis Services, a view is as good as a table. We don't
differentiate at all.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


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

The use of relational views provides a layer of abstraction and is
recommended with AS 2000. Data Source Views (DSV's) in AS 2005 provide
an additional abstraction layer:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/sql_analservbp.asp
Warehousing Framework

Microsoft Corporation

July 2002
...
Directing User Access Through Views

If you must give users direct access to the relational data store
(instead of access only through cubes), do not grant direct access to
any of the tables in your relational data store. Instead, create views
on each base table and grant SELECT privileges on the views, directing
all user access through those views. Make sure the object names (views
and columns) make sense to the business users; these can be the same
names you will use in the Analysis Services cubes.

Using views is good system design for a number of reasons:

Using views improves flexibility by providing a buffer between the
physical database and downstream systems, like user access and cube
population.

It increases the likelihood that you can make significant physical
changes to the system while minimizing disruption to other applications.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

(In answering this I'm assuming you are talking about MOLAP. The
considerations are a little different with ROLAP.)

Short answer: No, views are not a bad idea in OLAP.

Long answer: There's nothing wrong with using views with OLAP and they're
often very desirable, but sometimes the alternatives are better. For
example, creating views against your transactional DB to make it look better
for loading data into dimensions and cubes might work OK, but it is generally
better to ETL your data into a separate DB, reshaped how you want it, and use
that as the datasource for your OLAP DB. So views are great as long as
you're not using them to avoid doing the "right" thing. Data Set Views are a
new feature of AS2005 and they are essentially views (in-line rather than
named), so views are certainly not discouraged. I couldn't live without them.

Dean.

"Joe" wrote:

I am having a very real problem with a cube built on top of a view. You may
want to see my post w/ subject 'cube refresh is causing inconsistent results
in sql views that are run during the refresh'


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

I too have used views a lot and generally have no problems. Just
occasionally, when I have had performance problems (slow processing) I have
found it simpler just to create a new table and INSERT into that rather than
play around with the indexes of a whole bunch of feed tables. This seems to
be especially the case when I am using complex UNION views.