Error Message on creating indexed view
-
I am trying to build an indexed view today and I've just created a view successfully. However, when I tried to add a cluster index on the view, it gives me the error message:
Server:Msg 1957, Level 16, State 1
Index on view 'InSightAmKgDW.micros.vwBKCMenuMix' cannot be created because the view requires a conversion incolving dates or variants.
Can someone help me to solve this issue?
-
Can you post the script indicating which columns are of dates and variants types?
-
The data types of 5 columns are :
LcdStoreNum char(7),
MidMINum int,
DtdBusdate smalldatetime,
MidMiName varchar (26),
LcdEntityName varchar (50)
The script I used to create the view is :
-----------------------------------------
SELECT
micros.LocationDimension.LcdStoreNum,
micros.MIDimension.MidMINum,
micros.DateDimension.DtdBusDate,
micros.MIDimension.MidMIName,
micros.LocationDimension.LcdEntityName,
SUM (micros.MenuMixFacts.MmfQtySold),
SUM (micros.MenuMixFacts.MmfNetSales)
FROM
micros.LocationDimension,
micros.MIDimension,
micros.DateDimension,
micros.MenuMixFacts
WHERE
(micros.DateDimension.DtdBusDate > '20030101 00:00:00:000' AND micros.MenuMixFacts.MmfQtySold != 0)AND micros.DateDimension.DtdDateKey = micros.MenuMixFacts.MmfDateKey
AND
micros.MIDimension.MidMIKey = micros.MenuMixFacts.MmfMIKey
AND
micros.LocationDimension.LcdLocKey = micros.MenuMixFacts.MmfLocKey
GROUP BY
micros.LocationDimension.LcdStoreNum,
micros.MIDimension.MidMINum,
micros.DateDimension.DtdBusDate,
micros.MIDimension.MidMIName,
micros.LocationDimension.LcdEntityName
--------------------------------
And the reason to create an indexed view is because I ran the estimated execution plan and found "Hash Match" in aggregate funcation. And our menumixfacts table (OLAP) is an over 5 millions rows table.
Thank you.
-
Try and change '20030101 00:00:00:000' to Cast('1 Jan 2003 00:00:00:000' as SmallDateTime)
-
I am sorry, can you tell me how to change the data type in a view?
I end up with drop the view and re-create by
---------------------------------
Create View vwBKCMenuMix WITH SCHEMABINDING
AS
SELECT
micros.LocationDimension.LcdStoreNum,
micros.MIDimension.MidMINum,
micros.DateDimension.DtdBusDate,
micros.MIDimension.MidMIName,
micros.LocationDimension.LcdEntityName,
SUM (micros.MenuMixFacts.MmfQtySold) AS TotQtySold,
SUM (micros.MenuMixFacts.MmfNetSales) AS TotNetSales
FROM
micros.LocationDimension,
micros.MIDimension,
micros.DateDimension,
micros.MenuMixFacts
WHERE
(micros.DateDimension.DtdBusDate > Cast('12/31/2002' as SmallDateTime)
AND micros.MenuMixFacts.MmfQtySold != 0)AND micros.DateDimension.DtdDateKey = micros.MenuMixFacts.MmfDateKey
AND
micros.MIDimension.MidMIKey = micros.MenuMixFacts.MmfMIKey
AND
micros.LocationDimension.LcdLocKey = micros.MenuMixFacts.MmfLocKey
GROUP BY
micros.LocationDimension.LcdStoreNum,
micros.MIDimension.MidMINum,
micros.DateDimension.DtdBusDate,
micros.MIDimension.MidMIName,
micros.LocationDimension.LcdEntityName
GO
------------------------
Then I changed the owner of view to match the base tables.
When I tried to add clustered index, it gave me a different error message :
Server: Msg 1949, Level 16, State 1
Index on view 'InSightAmKgDW.micros.vwBKCMenuMix' cannot be created because function 'convert' yields nondeterministic results.
Did I do something wrong?
-
Look in SQL Server Books Online 2000 and search for 'Deterministic and Nondeterministic Functions' The problem seems to be the determinism of the cast/convert function. If you use the convert/cast in the where on a SELECT of the VIEW then everything is fine. Other words leave the date/cast out of the view create part.
-
You can't do any conversion within the view. Check out 'Creating an Indexed View' in BOL.
Also, you'll need to add a COUNT_BIG(*) to your statement. Just another of those many requirements for indexed views
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I re-create the indexed view by:
----------------
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
setuser N'micros'
GO
Create View vwBKCMenuMix WITH SCHEMABINDING
AS
SELECT
micros.LocationDimension.LcdStoreNum,
micros.MIDimension.MidMINum,
CAST (micros.DateDimension.DtdBusDate AS SmallDateTime) AS BusDate,
micros.MIDimension.MidMIName,
micros.LocationDimension.LcdEntityName,
SUM (micros.MenuMixFacts.MmfQtySold) AS TotQtySold,
SUM (micros.MenuMixFacts.MmfNetSales) AS TotNetSales,
Count_big(*) as Cnt
FROM
micros.LocationDimension,
micros.MIDimension,
micros.DateDimension,
micros.MenuMixFacts
WHERE
(micros.DateDimension.DtdBusDate > '12/31/2002' AND micros.MenuMixFacts.MmfQtySold != 0)
AND micros.DateDimension.DtdDateKey = micros.MenuMixFacts.MmfDateKey
AND
micros.MIDimension.MidMIKey = micros.MenuMixFacts.MmfMIKey
AND
micros.LocationDimension.LcdLocKey = micros.MenuMixFacts.MmfLocKey
GROUP BY
micros.LocationDimension.LcdStoreNum,
micros.MIDimension.MidMINum,
micros.DateDimension.DtdBusDate,
micros.MIDimension.MidMIName,
micros.LocationDimension.LcdEntityName
GO
setuser
GO
CREATE UNIQUE CLUSTERED INDEX vwBKCMMInd ON vwBKCMenuMix ( LcdStoreNum, MidMINum, DtdBusDate, MidMIName, LcdEntityName)
----------------------------------------
The view created successfully, but gave me error message:
Server: Msg 1906, Level 11, State 1, Line 2
Cannot create an index on 'vwBKCMenuMix', because this table does not exist in database 'InSightAmKgDW'.
I went in Enterprise Manager to add clustered index, and still get error message:
Index on view 'InSightAmKgDW.micros.vwBKCMenuMix' cannot be created because the view requires a conversion involving dates or variants.
I am wondering which column I should cast or convert?
Thank you.
-
You cannot use CAST function with datetime data type in Indexed View.
It seem you have a problem while you are loading your fact table. Post the script that you used to create your dimensions and fact table. I try to help you with this stuff.
I ever have a problem while loading a data to my ten million row fact table.
Hendra
-
Thank you Hendra, here's the scripts for all 4 table creation:
------------------------------
The MenuMixFacts:
CREATE TABLE [micros].[MenuMixFacts] (
[MmfDateKey] [int] NOT NULL ,
[MmfLocKey] [int] NOT NULL ,
[MmfMIKey] [int] NOT NULL ,
[MmfQtySold] [int] NULL ,
[MmfWasteCnt] [int] NULL ,
[MmfNetSales] [decimal](10, 4) NULL ,
) ON [PRIMARY]
GO
------------------------------------
The MIDimension:
CREATE TABLE [micros].[MIDimension] (
[MidMIKey] [int] NOT NULL ,
[MidMINum] [int] NOT NULL ,
[MidMIName] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidReportName] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidMajGrpName] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidFamGrpName] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidMIGrpName] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidCustom3] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidCustom1] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidCustom2] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidCustom4] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
--------------
[DateDimension]:
CREATE TABLE [micros].[DateDimension] (
[DtdDateKey] [int] NOT NULL ,
[DtdBusDate] [smalldatetime] NOT NULL ,
[DtdFiscWeekNum] [tinyint] NULL ,
[DtdFiscWeekName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
------------------------
drop table [micros].[LocationDimension]
GO
CREATE TABLE [micros].[LocationDimension] (
[LcdLocKey] [int] NOT NULL ,
[LcdStoreNum] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LcdCity] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdStoreAddress] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdState] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdZipCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdCounty] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdDistrictNum] [smallint] NULL ,
[LcdDistrictName] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdAreaNum] [smallint] NULL ,
[LcdAreaName] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdMarketNum] [smallint] NULL ,
[LcdMarketName] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LcdStoreMgr] [varchar] (24) COLLATE ) ON [PRIMARY]
GO
------------------
The MenuMixFacs has around 5,200,000 rows currently.
-
Why do you need to use the CAST in the indexed view?
Surely the presentation/formatting of the data is something best left with the application?
Just a thought.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply