I have an w2k3 + Sql2k EE + AS + SP4

I try to build a ROLAP + realtime cube but when I try to process it it said
that it conuld not create a view for a specific agregation.

The dims and data are based on tables not on views.

The cube has 2 dim like product and customer and 2 measures like weight and
income.

Any help pls?

Thanks

Because of the detailed prerequisites for using a Real-Time ROLAP
partition with aggregations, I've typically used Real-Time ROLAP with no
aggregations (which doesn't require the creation of indexed views in SQL
Server 2000). But that may only perform well for small partitions. Do
your cube tables meet all these requirements listed in SQL 2000 BOL?

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agadvpart_3jzn.asp

If the storage mode of a partition is relational OLAP (ROLAP) and its
source data is stored in Microsoft® SQL Server™ 2000, SQL Server 2000
Analysis Services attempts to create indexed views to contain
aggregations of the partition. If Analysis Services cannot create
indexed views, it automatically generates and uses aggregation tables
instead of indexed views. While Analysis Services handles the session
requirements for creating indexed views on SQL Server 2000, the creation
and use of indexed views for aggregations requires the following
conditions to be met by the ROLAP partition and the tables in its
schema:

The partition cannot contain measures that use the aggregate functions
Min, Max, or Distinct Count.

Each table in the schema of the ROLAP partition must be used only once.
For example, the schema cannot contain "dbo"."address" AS "Customer
Address" and "dbo"."address" AS "SalesRep Address".

Each table must be a table, not a view.

All table names in the partition's schema must be qualified with the
owner name, for example, "dbo"."customer".

All tables in the partition's schema must have the same owner; for
example, you cannot have a FromClause like : "tk"."customer",
"john"."store", or "dave"."sales_fact_1999".

The source columns of the partition's measures must not be nullable.

All tables used in the view must have been created with the following
options set to ON:
ANSI_NULLS

QUOTED_IDENTIFIER

The total size of the index key, in SQL Server 2000, cannot exceed 900
bytes. SQL Server 2000 will assert this condition based on the fixed
length key columns when the CREATE INDEX statement is processed.
However, if there are variable length columns in the index key, SQL
Server 2000 will also assert this condition for every update to the base
tables. Because different aggregations have different view definitions,
ROLAP processing using indexed views can succeed or fail depending on
the aggregation design.

The session creating the indexed view must have the following options
on: ARITHABORT, CONCAT_NULL_YEILDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS,
ANSI_PADDING, and ANSI_WARNING. This setting can be made in SQL Server
Enterprise Manager.

The session creating the indexed view must have the following option
off: NUMERIC_ROUNDABORT. This setting can be made in SQL Server
Enterprise Manager.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

HI,

Thanks for the answer!

I used 0 agregations and every seems to work ok. The dimensions are not too
big 7 and 4 levels, each with around 15.000 elements. I must test it under
user load.

I use this for a planning application. The application will read and display
the data OLAP like (hierarchies) from AS and the user will type the data
having the impression that is writting in the cube but the app will write
data in the relational table.

I have up to 30-40 peoples planning concurenttly and for each manual input
value that the user type I generate up to 2000 records.

I've tried with write enabled cubes but when are more than 5 peoples in the
system the AS/SQL become verry verry slow.

I'm really interested to see how such an concept will work under user load.
Any experiences?

thanks
-radu


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> schrieb im Newsbeitrag
news:%23Y17dlqyFHA.1028 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Unfortunately, I haven't worked much with planning applications, but
it's a challenging scenario. Have you considered AS 2005 for this, since
it can implement real-time OLAP without resorting to a ROLAP partition?
That should facilitate better query performance .


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the answers,

For the next year planning process we have considered using MS SQL 2005 and
AS 2005.
I had a look how real-time OLAP is working with AS 2000 but there are some
things that I don't understand ( see the post with ObjIdUpdate(...) ).
I hope that MS SQL 2005 and especially AS 2005 will bring some performance
improvements.

-radu


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> schrieb im Newsbeitrag
news:O3CwMq1yFHA.1856 (AT) TK2MSFTNGP12 (DOT) phx.gbl...