Have a cube that uses an indexed view for the fact table. During processing,
we find that the MSAS-generated SQL query does not have the "noexpand" hint
in the query to tell the query optimizer to utilize view indexes. As a
result, the performance is still slow, as bad as not having a view
alltogether. So, is there a way to configure the cube settings to drop a
"noexpand" in the genereated query -- OR, is there a way to set the SQL
connection properties utilized by MSAS in a way that properties such as
quoted_identifier, ansi_nulls, etc. are set properly so the query optimizer
knows automatically to utilized indexed views?

For reference, see
http://msdn.microsoft.com/library/de...ar_sa_28j7.asp

do you use the standard edition of SQL SErver ?

The standard edition doesn't use the indexed view if the no expand keyword
is not present in the query.
Because you can't control this in AS, you can't use your indexed view.

maybe you can try this:
create a view like this:

create MyNewView
as
Select * from MyIndexedView noexpand


"sandeep" <sandeep (AT) discussions (DOT) microsoft.com> a écrit dans le message de
news: 063C2485-4896-477C-A2D6-3B48B904724D...soft (DOT) com...

I am using the Enterprise edition with SP3a, so version restriction shouldn't
be an issue.

Your suggestion to create a view on top of the view is interesting -- but,
in a way it defeats the purpose of having an indexed view. I want to have an
indexed view as my fact table, because I have indexes built to support the
joins between the fact table and the dimension tables, and of course I'd like
to have the fact table data persisted on the disk.

The question boils down to 2 specific issues:
1. Is there a way to set SQL connection properties in MSAS so I can turn
ansi_nulls and quoted_identifier on as required by query optimizer to utilize
indexed view?
2. Is there a way to configure the SQL query generated by MSAS to process
the cubes so that one can drop the "noexpand" hint?

[working with Sandeep] Tried the nested/hinted view approach. When viewing
the execution plan for the cube building query, it looks like the indexed
view is now forced. Wonderful. However, when actually processing the cube, it
fails with this message:

"Data source provider error: SELECT failed because the following SET options
have incorrect settings: 'ARITHABORT'.;42000; Time:11/24/2004 10:40:12 AM"

This obviously refers to one of those several mandatory session settings.
This particular one doesn't appear to be settable in SQL Ent Mgr. I also
don't see a way to set this prop in MSAS connection settings. Any ideas?

Has anyone actually used indexed views to build cubes? I'm pretty skeptical.

"Jéjé" wrote: