Indexed views, with (NOEXPAND), and SSAS cubes

Question

I've set up an indexed view which I intend to use as a data source view for a SSAS cube. The indexed view looks something like this:

create view vw_indexed1 with schemabinding
as

select 
key1, 
key2, 
count_big(*) as bigcount 
from
table1
group by
key1, 
key2, 
GO


CREATE UNIQUE CLUSTERED INDEX [PX_vw_indexed1] ON [dbo].[vw_indexed1] 
(
key1, 
key2, 
)WITH 
(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Now, when I go select * from vw_indexed1 it takes ages, presumably because it is trying to expand the indexes in the underlying table, so I would then use select * from vw_indexed1 with (noexpand), which performs just like a select directly on a table with a clustered index.

My question is this: if I use this view as the DSV for a SSAS project, is it going to try to use the underlying indexes every time? And if so, how can I force it to consider vw_indexed1 as a table with its own indexes?

Answer 1

Sort of. You can force the issue a little bit by having the DSV that points to a view that itself is a select * from vw_indexed1 statement.

But the reason it may not have chosen it is because the SQL Server engine itself is the one that decides whether to use the indexed view or not. Even though your DSV may have pointed to it, all it does is send a query to the table and/or indexed view. In fact, you can point your DSV to the underlying fact table, and if you look at the profiler traces, for some situations, it will chose the indexed view even though you specified the table itself.

There are more details behind how this work, please check out the whitepaper Analysis Services ROLAP for SQL Server Data Warehouses at http://msdn.microsoft.com/en-us/library/ff956108(v=sql.100).aspx