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 report parameters in nested IIf

I have the following to diplay the frequecny a report is run for -
based on parameters selected at run time...
=3D"Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
"01/01/1900", Parameters!EndDate.Value,
Parameters!StartDate.Value.AddHours(1)), Parameters!Frequency.Label)
but if I leave the start and end date null I get an error #Error and
the following...
The value expression for the textbox 'textbox1' contains an error:
Object variable or With block variable not set.
However, if I replace the above with the following (instead of printing
out the date parameter value in the inner iif, print out a string
instead)....
=3D"Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
"01/01/1900", "Parameters!EndDate.Value",
"Parameters!StartDate.Value.AddHours(1)"), Parameters!Frequency.Label)
it works fine and will display the frequency if both dates are null.
Any idea why this is?
Also as an aside - is there a better way to check is a date field is
null instead of > "01/01/1900"'
Thanks in advance,
Gear=F3idThis occurs for 2 reasons:
1. an Iif statement always evaluates all parts of an equation.
Therefore, if 1/2 of the statement is invalid, the whole thing throws an
error.
2. You're trying to AddHours(1) to a null value, which throws an error.
I don't know why putting it in "" causes it to work, though.
As for a better option than > "01/01/1900", I recommend >
DateTime.MinValue, as that is the default value for an unassigned DateTime.
Ciao,
Noah
Gearoid wrote:
> I have the following to diplay the frequecny a report is run for -
> based on parameters selected at run time...
> ="Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
> Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
> "01/01/1900", Parameters!EndDate.Value,
> Parameters!StartDate.Value.AddHours(1)), Parameters!Frequency.Label)
> but if I leave the start and end date null I get an error #Error and
> the following...
> The value expression for the textbox 'textbox1' contains an error:
> Object variable or With block variable not set.
> However, if I replace the above with the following (instead of printing
> out the date parameter value in the inner iif, print out a string
> instead)....
> ="Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
> Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
> "01/01/1900", "Parameters!EndDate.Value",
> "Parameters!StartDate.Value.AddHours(1)"), Parameters!Frequency.Label)
> it works fine and will display the frequency if both dates are null.
> Any idea why this is?
> Also as an aside - is there a better way to check is a date field is
> null instead of > "01/01/1900"'
> Thanks in advance,
> Gearóid
>|||Hey Noah,
Thanks for getting back to me on this. I put it aside for a while but
have to get it sorted now.
I reckon you're right about it trying to add 1 hour to a null value and
bombing out. But it seems kinda crazy that it would try to evaluate
all parts of an IIf statement. How would I conditionally add an hour
to a datetime value so if it's not null? I thought that's what an if
statements for?!...

Problem with query

Hi All!
i want to update a column based on another column and using following
quey but its giving me error any help
update hospital1 set hospital1.father_name = temp.father_name
join temp on temp.id = temp.id
and hospital1.father_name <> 'Not Available'
thanx!
Farid
*** Sent via Developersdex http://www.examnotes.net ***update h
set h.father_name = t.father_name
from hospital1 h
join temp t on t.id = h.id
and h.father_name <> 'Not Available'
I assumed you can join temp and hospital1 on id
http://sqlservercode.blogspot.com/|||Ghulam Farid wrote:
> Hi All!
> i want to update a column based on another column and using following
> quey but its giving me error any help
> update hospital1 set hospital1.father_name = temp.father_name
> join temp on temp.id = temp.id
> and hospital1.father_name <> 'Not Available'
> thanx!
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
I'm guessing you want something like this:
UPDATE hospital1
SET father_name =
(SELECT father_name
FROM temp
WHERE id = hospital1.id
AND father_name <> 'Not Available') ;
WHERE ... ?
Note that this statement assumes id is unique in Temp for each hospital
row to be updated. If that assumption is incorrect then please explain
how you want to handle the duplicates (i.e. more than one father_name).
David Portas
SQL Server MVP
--|||Your proprietary answer makes no sense in terms of the SQL language
model. A FROM clause is always suppose effectively materialize a
working table that disappears at the end of the statement. Likewise,
an alias is supposed to act as it materializes a new working table with
the data from the original table expression in it. To be consistent,
this syntax says that you have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).

Problem with parameter selection.

i have a first parameter where user can select either office or hometown selection. based on this selection i have two more paramters in which only one should be populated and the other should be disabled.

i was able to manage to do it, but when i veiw it in the report viewer the problem is its not populating the values for other one which is supposed to be at the same time it says select a value in that combo and report doesn't execute bcoz of this.

any help.

parameter1 choices : office, hometown.

parameter2: will be populated if office is selected

parameter3: will be populated if hometown is selected.

is there a way to disable completely upon selection of the first one.

Thanks

Kishore.

Hi,

for the second parameter if you are using the stored procedure then pass the first parameter value i.e Parameters!Param1.Value,to do this in the dataset of second parameter(ds2) click on Parameters tab,value:Parameters!Param1.Value.This will automatically populate the second parameter.And coming to the Report->Parameters->Param2->Available values From ds2.

Hope this helps

I

|||

Thanks for the reply,

I am able to do that. but the thing thats bothering me is in the report viewer, i cant run the report without selecting all the parameters, in my case here what ever the user selects by home or office.. accordingly the combo box is populated and the other one shouldn't and its doing it but the problem is when i run the rep in viewer, its asking me to select a value for the no populated box. the report wont execute if i leave it like that.

thnks.