Problem with ROLAP dimension updates

Simple scenario:
Cube based on a single table. (FactContacts)
One HOLAP count of rows measure.
One ROLAP fact dimension with an attribute based on a nullable string column. (Status)

Everything works fine as long as I process the cube manually through VS. However, I often get the following error if I rely on the cube to update automatically as my source table changes. (Interestingly the error doesn't always occur.)

The query could not be processed:
o Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_FactContacts, Column: Status, Value:aaaa.
o Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

My first guess was that things are getting updated in the wrong sequence (measure before dimension), so I've tried changing the ProcessingPriority properties on the dimension and on the measure group but that didn't have any effect.

Any ideas?
I should probably also clarify I have proactive caching enabled, both on the dimension and on the measure group. My notification method is SQL Server.

I'd really like to know if this could possibly be a bug of SSAS, or if I'm just missing something. This problem is pretty much a showstopper for my project, as real-time data is a requirement.

Problem with ROLAP dimension and UnknownMember

I've set up a simple test cube with one fact table, "FactContacts" and one dimension table, "DimStatus". I also have a degenerate dimension "Contact ID" based on a column in the fact table. The only measure is the count of rows in "FactContacts". I've also set up a drillthrough on this measure group to return "Contact ID" and "Status". The "StatusID" column in the fact table allows nulls, so the UnknownMember is enabled on DimStatus and null processing is set to UnknownMember in the dimension usage tab.

Everything works fine as long as my dimensions are set to MOLAP mode. However, when I set them to ROLAP, it seems the nulls are not handled properly. When I slice on "Status" the Unknown count is non-zero, but the drillthrough doesn't return any rows. When I slice on "Contact ID" the count of Contacts for each ID is 1, but when I drillthrough only the cells that correspond to Contacts with a non-null StatusID return a row.

Essentially, the drillthrough does an inner join on the tables while I want it to do an outer join. Is there a way to achieve this? Any help will be greatly appreciated.

There is a similiar issue with materializing referenced dimensions. I'm not aware of a work around for this.

Our general recommendation to folks to to make sure your references are always populated. (Set your foreign key fields to NOT NULL.) Of course, you may have a reference to a NULL member in a dimension. You can easily support this as demonstrated in the following example:

create table DIM_Date (

DateID int not null identity(1,1),

Date datetime null

)

alter table DIM_Date add

constraint PK_Date primary key (dateid),

constraint AK_Date unique (date)

set identity_insert DIM_Date on

insert into DIM_Date (DateID, Date) values(-1, NULL)

set identity_insert DIM_Date off

In this example, any NULL date references would be set to a foreign key value of -1. Your inner joins between your fact table and your dimension table will always resolve correctly.

B.

|||Thanks for your reply Bryan. I was hoping to avoid this workaround but I guess this is what I'll have to do.

Problem with Prepared Statements

Hi
we are developing a java application, which connects to a MS SQL Server 2000
via the Microsoft JDBC Drive Service Pack 3.
Due to the fact that we have massiv performance problems with MS SQL. I
started profiling our MS SQL Server.
I've recognized during analyzing the traces, that the variables of every
prepared Sql command are defined as nvarchar(4000) or Decimal(38,0). Quite
confusing by the fact that the lenght of the fields is not exceeding 50
signs in the tables as well as in the variable used within our application.
I not understand the behaviour of the JDBC Driver. Is there a problem
defining the length of prepared parameters ?
Cause within ODBC i am able to define the length of input values in order to
reduce the amount of data being in access by the sql engine.
kind regards
chris
Christian Billig wrote:

> Hi
> we are developing a java application, which connects to a MS SQL Server 2000
> via the Microsoft JDBC Drive Service Pack 3.
> Due to the fact that we have massiv performance problems with MS SQL. I
> started profiling our MS SQL Server.
> I've recognized during analyzing the traces, that the variables of every
> prepared Sql command are defined as nvarchar(4000) or Decimal(38,0). Quite
> confusing by the fact that the lenght of the fields is not exceeding 50
> signs in the tables as well as in the variable used within our application.
> I not understand the behaviour of the JDBC Driver. Is there a problem
> defining the length of prepared parameters ?
> Cause within ODBC i am able to define the length of input values in order to
> reduce the amount of data being in access by the sql engine.
> kind regards
> chris
Hi. The declared length of the varchar variables are not crucial, but
if you have real VARCHAR columns, and the driver is sending data as
NVARCHARs, then your varchar indexes won't be used, so you will have
slow table scans. The solution for that is to set the JDBC connection
property SendStringParametersAsUnicode=false. The driver by default
sends data as 16-bit characters so any Java char is sent correctly.
As long as you are using standard English 8-bit ASCII characters,
you don't need to send as UNICODE.
Let me know if this takes care of the problem.
Joe
|||Hi Joe
setting the property SendStringParametersAsUnicode=false did not change the
performance.
What still is confusing are the Trace Results.
Executing a SQL Statement as prepared statement in a loop just changing the
binded values for 50 times,
is resulting for Java in a trace with 50 statements exec sp_cursoropen.
Doing the same in c# und using an OleDB Connection with the exact same SQL
and value is leading to a different Trace
which contains one statement with "exec sp_prepexec" and 49 statements of
"exec sp_execute".
May i'm doing something wrong ? Or is that really the right behaviour ? It
looks to me that in our java application the PrepareStatement is not really
executed.
bye
chris
|||Seems to be a problem of the JDBC Driver even prepare Insert and Update
statements are being executed as sp_executesql
where as using the jdbc 2005 beta 1 driver they are executed as sp_prepexec
and sp_execute.
Even Select statements which were executed as cursor-open, -fetch and -close
are now executed as sp_prepexec and sp_execute as long as the resultset is
text_forward_only.
Due to the fact that we need scrollable resultsets the former behaviour is
ok
During testing the JDBC 2005 Beta 1 we recognized a bug in this
constellation.The SQL Statement used for opening the cursor is not prepared
any longer although we are using the preparedstatement method. That causes
an immens memory usage until the SQL Server reachs the allowed maximum
memory usage and begins to use the swap file.
|||forgot the log
Both Statements were executed as PreparedStatement with an insensitive
Cursor ResultSet.
JDBC 2000 on SQL Server 2000 SP4
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=4104
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'SELECT "LVORLP".* FROM "LVORLP" WHERE
("LVBNKZ" = @.P1 AND "LVFIRM" = @.P2 AND ("LVABRJ" > @.P3 OR "LVABRJ" = @.P4 AND
"LVABRM" > @.P5 OR "LVABRJ" = @.P6 AND "LVABRM" = @.P7 AND "LVABGR" > @.P8 OR
"LVABRJ" = @.P9 AND "LVABRM" = @.P10 AND "LVABGR" = @.P11 AND "LVSABE" > @.P12
OR "LVABRJ" = @.P13 AND "LVABRM" = @.P14 AND "LVABGR" = @.P15 AND "LVSABE" =
@.P16 AND "LVPRNR" >= @.P17)) ORDER BY "LVABRJ", "LVABRM", "LVABGR", "LVSABE",
"LVPRNR"', @.P2 output, @.P3 output, @.P4 output, N'@.P1 varbinary(8000) ,@.P2
varbinary(8000) ,@.P3 decimal(38,0) ,@.P4 decimal(38,0) ,@.P5 decimal(38,0)
,@.P6 decimal(38,0) ,@.P7 decimal(38,0) ,@.P8 varbinary(8000) ,@.P9
decimal(38,0) ,@.P10 decimal(38,0) ,@.P11 varbinary(8000) ,@.P12
varbinary(8000) ,@.P13 decimal(38,0) ,@.P14 decimal(38,0) ,@.P15
varbinary(8000) ,@.P16 varbinary(8000) ,@.P17 decimal(38,0) ', 0x42,
0x303031, -9999, -9999, -99, -9999, -99, 0x00, -9999, -99, 0x00,
0x000000, -9999, -99, 0x00, 0x000000, -999999
select @.P1, @.P2, @.P3, @.P4
JDBC 2005 Beta 1 on SQL Server 2000 SP4
RPC Starting
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=0
exec sp_cursoropen @.P1 output, 'SELECT "LVORLP".* FROM "LVORLP" WHERE
(("LVBNKZ" > 0x42) OR ("LVBNKZ" = 0x42 AND "LVFIRM" > 0x303031) OR ("LVBNKZ"
= 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" > 2005) OR ("LVBNKZ" = 0x42 AND
"LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" > 9) OR ("LVBNKZ" =
0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" = 9 AND
"LVABGR" > 0x20) OR ("LVBNKZ" = 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" =
2005 AND "LVABRM" = 9 AND "LVABGR" = 0x20 AND "LVSABE" > 0x202020) OR
("LVBNKZ" = 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" =
9 AND "LVABGR" = 0x20 AND "LVSABE" = 0x202020 AND "LVPRNR" >= 0)) ORDER BY
"LVBNKZ", "LVFIRM", "LVABRJ", "LVABRM", "LVABGR", "LVSABE", "LVPRNR"', 8, 1,
@.P2 output
select @.P1, @.P2