I am trying to create a very simple RT cube (the cube works fine with MOLAP)
with just one time dimension and one measure. The table I am creating it
from does have a UNIQUE Clustered index and satisfies all documented
requirements as it was created with:

SET NUMERIC_ROUNDABORT OFF
go
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS ON
go
CREATE unique CLUSTERED INDEX clu1
ON dbo.wndw_tmng_rt(end_tm,usr_id,data_wndw_nm,start2 end)
go


When I try to process this cube with ROLAP it gets to:

CREATE VIEW rt_rt_6 ( Year_L2, Month_L3, Day_L4, Hour_L5, Minute_L6,
SUM_Start2end, COUNTBIG ) WITH SCHEMABINDING AS SELECT
DatePart(year,"dbo"."wndw_tmng_rt"."end_tm") as Year_L2 ,
DatePart(month,"dbo"."wndw_tmng_rt"."end_tm") as Month_L3 ,
convert(CHAR,"dbo"."wndw_tmng_rt"."end_tm", 112) as Day_L4 ,
DatePart(hour,"dbo"."wndw_tmng_rt"."end_tm") as Hour_L5 ,
DatePart(minute,"dbo"."wndw_tmng_rt"."end_tm") as Minute_L6 ,
SUM("dbo"."wndw_tmng_rt"."start2end") As SUM_Start2end , COUNT_BIG(*) as
COUNTBIG FROM "dbo"."wndw_tmng_rt" GROUP BY
DatePart(year,"dbo"."wndw_tmng_rt"."end_tm") ,
DatePart(month,"dbo"."wndw_tmng_rt"."end_tm") ,
convert(CHAR,"dbo"."wndw_tmng_rt"."end_tm", 112) ,
DatePart(hour,"dbo"."wndw_tmng_rt"."end_tm") ,
DatePart(minute,"dbo"."wndw_tmng_rt"."end_tm")

and looks like creates this view successfully. Than the process tries to
create the following index:

CREATE INDEX [rt_rt_61] ON [dbo].[rt_rt_6]([Year_L2], [Month_L3],
[Day_L4], [Hour_L5], [Minute_L6])
WITH FILLFACTOR = 100 ON [PRIMARY]

and chokes on it with:

Server: Msg 1940, Level 16, State 1, Line 1
Cannot create index on view 'rt_rt_e'. It does not have a unique clustered
index.

After that it happily drops this view, creates a table instead with the same
index and produces the rest of the aggregations in tables as opposed to
views. So now this is not a RT ROLAP cube.

My questions is:
How is it supposed to have an index if it was just dropped and re-created by
cube process without this unique clustered index?? Am I missing something?

Thanks in advance,
Koni.

Hi,

Have you set your environment as documented in Books On line topic:
"Indexed Views for ROLAP Partitions". If yes could you please create a
profiler trace with the problem captured in there and post it so that I can
have a look.

-start the profiler on the machine where you process from (start, all
programs, Microsoft SQL server,
Profiler)
-select File, New, Trace
-set the Analysis server name and connect
-on General tab set Trace name: to ROLAPclient
-on events tab add -Errors and Warnings, -Stored procedures and -TSQL
-RUN
-process the ROLAP cube
-save ROLAPclient trace

Which servicepack are you running for Analysis server? Are you processing
this local on the Analysis server itself or are you doing this from a
client?

Regards,
Bas

Hi, BAs.
Thanks for your response.
I did set everything according to the Books Online - at least as far as I
can tell.
When I try to create a trace and start the Profiler I can't see any option
for connecting to an OLAP server. The only window I get when I start a new
trace is for SQL servers with a drop-down list of what is available. So I
am not quite sure what you mean by the 'set the Analysis server name and
connect'. BTW, the name of the Analysis server is the same that SQL server
running on the same machine where I am running the Enterprise Manager with
OLAP plug-in on.
That's what you mean by the client, right?
The OLAP services as well as MS SQL version is 2KSP3. BTW, is there a way
to tell this for OLAP without going into the registry?

Thanks,
Koni.
""Bas Kersten [MS]"" <bask (AT) online (DOT) microsoft.com> wrote

Hi Koni,

Sorry for not being completly clear. With setting the name for the AS
server do I mean to set it to the SQL server where we store the ROLAP
structure at this is the same as your AS server if I understand you
correctlry. So I am still happy to give it a look if you can create the
trace with the SQL server involved.

You can check version number of Analysis server by going into Analysis
manager (local), click Analysis Servers, r. click, about Analysis services,
you can read the version number there.
AS2000 RTM is 8.00.194
AS2000 sp1 is 8.00.384
AS2000 sp2 is 8.00.534
AS2000 sp3 is 8.00.760

Regards,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

This group did not allow the attachment. I tried to email it to the address
below and got the following error:
This is an automatically generated Delivery Status Notification.

Delivery to the following recipients failed.

bask (AT) online (DOT) microsoft.com
Please let me know what would be a good way to send you the .trc file.
Thanks,
Koni.

"Koni Kogan" <k_kogan (AT) hotmail (DOT) com> wrote

Hi,

I have send you an email to your hotmail account.

Regards,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

I have not received it yet. Please try koni_kogan (AT) concentra (DOT) com as well.

""Bas Kersten [MSFT]"" <bask (AT) online (DOT) microsoft.com> wrote

"Koni Kogan" <k_kogan (AT) hotmail (DOT) com> wrote

This is also an interest of mine. How would I create a partition and
use ROLAP so just every hour on the hour a cube would rebuild?

Thanks in advance

Pete
Pete Hohenhaus
Woodinville, WA, East Side Seattle, Good Old USA

Hi,

I have worked of line with Koni on this. I restored the database from Koni
(on sql2ksp3 out of the box) and restored the cube. After this did I
process the cube, I noticed the exact problem right away, with the same
profiler trace.

After this did I run this command to set the correct user options on the
SQL server as documented and talked trough earlier.

"Indexed Views for ROLAP Partitions"

1. Set the following SQL Server options to ON:
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_PADDING
ANSI_WARNING

2. Set the following Source SQL Database settings to ON:
QUOTED_IDENTIFIER
ANSI_NULLS

Command in query analyzer:

USE master
EXEC sp_configure 'user options', 4472
RECONFIGURE WITH OVERRIDE

After this you have to make sure that the AS manager that is used is closed
(needs to pick up new connection settings). Restart SQL server, Restart
Analysis server. Start Analysis manager and re-process.

That worked.

HTH,
Bas


"This posting is provided "AS IS" with no warranties, and confers no
rights."