I'm trying to build a ROLAP cube, which must be available for real-time
updates. During processing of the cube in Analysis manager I get a 'Failed
to create index' Error.
I am using SQL Server 2000 Enterprise edition.

Hello,

I noticed that the issue was posted in several groups
(microsoft.public.sqlserver.olap & microsoft.public.dotnet.general). I have
added a reply to you in the microsoft.public.dotnet.general and the
response is below:

//////////////////////////////
I learned you got the error message "Failed to create index" when you
process the Real-Time ROLAP cube using Analysis Manager.

Please try the following steps to see if they are able to fix your problem.

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

In order to be able to process Real-Time cubes, that has aggreagations, the
settings described in the Book On-Line Topic "Indexed Views for ROLAP
Partitions" have to be implemented.

Indexed Views for ROLAP Partitions
http://msdn.microsoft.com/library/de...us/olapdmad/ag
advpart_3jzn.asp

//////////////////////////////

Because this is an Analysis Services problem, if you have follow up
questions, please post here and I will work with you. Thanks.

I am looking forward to hearing from you soon.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hello Michael,
Thanks for answer but:
1- I cannot see CONCAT_NULL_YEILDS_NULL under SQL Server Properties -
Connections - Default Connection Options. Am I looking at the wrong place?

2- Where is the Source SQL Database settings?

3- In "Indexed Views for ROLAP Partitions" document I learned that all
tables used in the view must have been created with the ANSI_NULLS and
QUOTED_IDENTIFIER options set to ON. But I have already created the tables,
Do I have to create them again?

I set the following SQL Server Properties - Connections - Default Connection
Options to ON:

ARITHMATICS ABORT
QUOTED IDENTIFIER
ANSI NULLS
ANSI PADDING
ANSI WARNING

and I don't get the "Failed to create index error" any more, but when I look
to the cube using Cube Browser I cannot see the right data. Also when I
change the value of a dimension from ALL to a specific value in Cube
Browser, Sometimes it clears all cells.

Tooraj



""Yuan Shao"" <v-yshao (AT) online (DOT) microsoft.com> wrote

Hi Tooraj

You can perform "SET CONCAT_NULL_YIELDS_NULL ON" to configure this option.
Also, because ANSI_NULLS and QUOTED_IDENTIFIER options are not table
specific, you do not need to recreate tables. For more information, please
refer to the following article:
SET CONCAT_NULL_YIELDS_NULL
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
set-set_2z8s.asp
SET ANSI_NULLS
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
set-set_9rec.asp
SET QUOTED_IDENTIFIER
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
set-set_9jxu.asp

According to your description, I learned the "Failed to create index" error
did not occur again. However, you mentioned you cannot see the right data.
Can you please tell me what are changed? What's the difference after
setting these options? Please describe it in detail. In addition, please
make sure you have full processed your problematic cube after the option
changed.

I am looking forward to hearing from you soon.
Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Michael,
I have a ROLAP Real-Time Update enabled cube with a fact table called
General_Ledger with one measure called Amount. Also there are three shared
dimension tables called Account, Date and Currency. At first time when I
created the cube I got "Failed to create Index" error. Then I ran the
following script in Query Analyzer:

SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

Also In Dimension Editor I set the Storage Mode to ROLAP and Enable
Real-Time Updates to True for all dimensions. After these changes I do not
get the "Failed to create Index" error anymore but when I browse the cube ,
assume I have Account dimension in rows and Date Dimension in columns, the
sum of all measures in a column is not equal to the value in "All Acount"
cell.

The following table discribes the issue.

All Date 2002 2003
2004
All Acount 300 100
89
Cash 10
General Expense 20 2
9
Inventory 100 40 70
80

The other problem is if I query the cube for a specific Currency, it doesn't
show anything in the grid. It only shows data in the grid when I query on
"All Currency".

That was all I did in detail.

Do you know any document or book about real-time ROLAP?

Tooraj

Hi Tooraj,

I learned you want to create Real-Time Cube, I found the following article
for your reference.

Building a Real-Time Cube
http://msdn.microsoft.com/library/de...us/olapdmad/ag
buildcubes_0axx.asp

While ROLAP dimensions can easily support real-time updates, ROLAP
partitions must meet certain qualifications in order to support this new
functionality. They must either have zero aggregations or be based on SQL
Server 2000 indexed views, and they cannot be remote partitions.

Real-time OLAP can be enabled by using either Dimension Editor or the
Dimension Wizard to enable real-time updates on ROLAP dimensions, or by
using the Storage Design Wizard to enable real-time updates on ROLAP
partitions that meet the qualifications discussed earlier in this topic.

For more information on the requirements of indexed views for ROLAP
partitions, see
"Indexed Views for ROLAP Partitions"
http://msdn.microsoft.com/library/de...us/olapdmad/ag
advpart_3jzn.asp

Thanks

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.