Indexed Views Question

[ease20022002]

Hi, I understand that a unique clusterd index is required on a view before adding nonclustered indexes.

The problem I am having is that I bring through a primary key identifier field (unique field values only) from the FactTable (Table: FactActuals, Field: ActualsID). I thought I could crete a unique clustered index on the ActualsID field and then place nonclustered indexes on two other fields.

Unfortunately I am getting an error that says I should use Big_Count and when I try to use that, I get further errors. I have looked on the web cumulatively over the past two days for about an hour and can't find the solution.

Also, all of the dimension tables in my star schema db are indexed. Do I need to index the view when the view pulls all the data values back together in the view?

I also have schemabinding referenced.

Here is my view and any help would be appreciated.

USE [FinancialDB]
GO
/****** Object:  View [dbo].[ActualsProgramFiles]    Script Date: 10/16/2009 12:47:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[ActualsProgramFiles] 
With SchemaBinding
AS
SELECT     dbo.FactActuals.ActualsID, dbo.DimYears.Year, dbo.DimMonths.Month, dbo.DimPrograms.Program, dbo.DimProjects.Project, 
                      dbo.DimResources.Resource, dbo.DimShortNames.ShortName, dbo.DimUserRespCodes.UserRespCode, dbo.DimResourceTypes.ResourceType, 
                      dbo.DimResourceSources.ResourceSource, dbo.DimCompCodes.CompCode, dbo.DimProjectDescriptions.ProjectDescription, 
                      dbo.FactActuals.PartOfFinancials, SUM(dbo.FactActuals.ActualHrs) AS ActualHrs, SUM(dbo.FactActuals.ActualFTEs) AS ActualFTEs, 
                      SUM(dbo.FactActuals.ActualCost) AS ActualCost
FROM         dbo.FactActuals INNER JOIN
                      dbo.DimCompCodes ON dbo.FactActuals.CompCodeID = dbo.DimCompCodes.CompCodeID INNER JOIN
                      dbo.DimMonths ON dbo.FactActuals.MonthID = dbo.DimMonths.MonthID INNER JOIN
                      dbo.DimPrograms ON dbo.FactActuals.ProgramID = dbo.DimPrograms.ProgramID INNER JOIN
                      dbo.DimProjectDescriptions ON dbo.FactActuals.ProjectDescriptionID = dbo.DimProjectDescriptions.ProjectDescriptionID INNER JOIN
                      dbo.DimProjects ON dbo.FactActuals.ProjectID = dbo.DimProjects.ProjectID INNER JOIN
                      dbo.DimResources ON dbo.FactActuals.ResourceID = dbo.DimResources.ResourceID INNER JOIN
                      dbo.DimResourceSources ON dbo.FactActuals.ResourceSourceID = dbo.DimResourceSources.ResourceSourceID INNER JOIN
                      dbo.DimResourceTypes ON dbo.FactActuals.ResourceTypeID = dbo.DimResourceTypes.ResourceTypeID INNER JOIN
                      dbo.DimShortNames ON dbo.FactActuals.ShortNameID = dbo.DimShortNames.ShortNameID INNER JOIN
                      dbo.DimUserRespCodes ON dbo.FactActuals.UserRespCodeID = dbo.DimUserRespCodes.UserRespCodeID INNER JOIN
                      dbo.DimYears ON dbo.FactActuals.YearID = dbo.DimYears.YearID
GROUP BY dbo.FactActuals.ActualsID, dbo.DimYears.Year, dbo.DimMonths.Month, dbo.DimPrograms.Program, dbo.DimProjects.Project, 
                      dbo.DimResources.Resource, dbo.DimShortNames.ShortName, dbo.DimUserRespCodes.UserRespCode, dbo.DimResourceTypes.ResourceType, 
                      dbo.DimResourceSources.ResourceSource, dbo.DimCompCodes.CompCode, dbo.DimProjectDescriptions.ProjectDescription, 
                      dbo.FactActuals.PartOfFinancials

Thanks for any help.

 

[Emtucifor]

I don't think you can use SUM() in materialized views. I've tried. Unless I'm remembering wrong, you're out of luck here.