replace IIF statement

I believe I am running into performance issues by using the IIF for filter out zero values in my fact table. My statement that I am using is the following: IIF(Measures.[Test Scores] > 0, Measures.[Test Scores], NULL). Unfortunately this calculated measure is being referenced quite a bit and I believe this is causing major performance issues once the users start to drilldown into the lower levels of the hierarchy.

I orginally tried placing a filter on a named set I was using, but it was not filtering out the measures with a zero value. The statement I was using was the following: FILTER([Course Score Tests], Measures.[Test Scores] > 0) where [Course Score Tests] is a named set that I am referencing. Unfotunately this was not removing the zero test scores when I started to reference this newly filtered named set in one of my calculated measures to try and bypass using the IIF statement. I went back to using the IIF statement on the calculated measure and that is working, but I am see major performance issues and I am currently unable to drilldown to the lowest level of the hierarchy and the memory on the server is taken major hits.

At this point I am at a loss and I am unsure how the rectify the situation. Any advice would be greatly appreciated. I am using SSAS 2005 w/ SP1.

Is [Test Scores] a calculated measure or a real measure ? If it is a calculated measure - what is the expression for it ? If it is a real measure - can you define it as nullable - then you won't need calculated measure to do the conversion.|||Thanks for your reply. [Test Scores] is a real measure, not a calculated measure. I have actual zero values in the fact table that I want to filter out for a particular calculated measure, but not necessarily permanently. Are you refering to changing the NullProcessing on the measure from Automatic to ZeroOrBlank?|||

In that case, you may consider creating a calculated column in DSV which will convert zeros to NULLs, and creating two different measures - one with zeros and another one with NULLs. It would be an overhead during processing, of course, and the cube will be somewhat bigger, but at least it should solve the performance problem.

REPLACE function doesnt work with null-bytes

Dear Community,

We have a problem with null-Bytes in varchar-Columns, which are not
handled correctly in our application. Therefor we try to filter them
out using the Transact-SQL REPLACE function.

The Problem was, that the REPLACE-function didn't behave the way we
expected.

Following Example demonstrates the behavior:

declare @.txt varchar(512)
declare @.i int
set @.txt = 'hello ' + char(0) + 'world'
print @.txt
set @.i = 1

while @.i <= len(@.txt)
begin
print str(@.i) + substring(@.txt, @.i, 1)
set @.i = @.i + 1
end

print 'Length: ' + str(len(@.txt))
print 'trying to replace null-byte:'
print replace(@.txt, char(0), '*')
print 'replace Letter h'
print replace(@.txt, 'h', char(39))

-- end example

Output:

hello
1h
2e
3l
4l
5o
6
7
8w
9o
10r
11l
12d
Length: 12
trying to replace null-byte:
*
replace Letter h
'ello

The Null-Byte replace destroys the whole string. This behavior occurs
only on some of our
databases. The others work correctly.

Is it possible that it depends on some server setting?

Thanks
Enno(enno@.berlin.cortex-software.de) writes:
> We have a problem with null-Bytes in varchar-Columns, which are not
> handled correctly in our application. Therefor we try to filter them
> out using the Transact-SQL REPLACE function.
> The Problem was, that the REPLACE-function didn't behave the way we
> expected.
>...
> The Null-Byte replace destroys the whole string. This behavior occurs
> only on some of our
> databases. The others work correctly.
> Is it possible that it depends on some server setting?

I've seen this before, but I was a little puzzled when you said that
it worked on some databases.

Playing around, I was lucky to find that it works if you have an SQL
collation. So in your script, change the crucial line to:

print replace(@.txt COLLATE SQL_Latin1_General_CP1_CI_AS, char(0), '*')

In SQL 2005, the string does not get destroyed, but neither does the
NULL get replaced. Since this is inconsistent, I filed bug for this on
http://lab.msdn.microsoft.com/Produ...ackId=FDBK45444
You can vote on it, if you like.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?

-- Mark D Powell --|||Mark D Powell (Mark.Powell@.eds.com) writes:
> Would the database setting of ANSI Nulls have an effect on this
> behavior? In 2000? In 2005?

I haven't tested, but I find that very unlikely.

The effect of the database setting ANSI_NULLS when it's ON is that it forces
the SET option ANSI_NULLS to be in the ON position, even though the process
has it OFF. If the database setting is OFF, it has no effect, but the
setting of the process determines.

And to make it even messier, when you run a stored procedure, what counts is
the setting when the procedure was saved.

As far as I know, ANSI_NULLS has no effect on the interpretation of NUL
characters. ANSI_NULLS determines how things like "IF @.x = NULL" works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the answer.
We have cleaned our data using the sql-Collation.
I think it's not wise to have Data with NUL-bytes in a
varchar-Column, because other Transact-SQL-functions have also
problems.

For example the LIKE operator is also inconsistent.

SELECT 1
WHERE CHAR(0) LIKE '_'

Yields:
<nothing
SELECT 1
WHERE CHAR(65) LIKE '_'

Yields:
1

By using the collation you suggested, the LIKE Operator worked as
expected.

There must be some enviromental COLLATION setting, which is
database-specific, but i didn't find it in exported-scripts from the
enterprise manager.|||(enno@.berlin.cortex-software.de) writes:
> There must be some enviromental COLLATION setting, which is
> database-specific, but i didn't find it in exported-scripts from the
> enterprise manager.

In SQL Server there is first a *server collation* which defines the
collation for the system databases, and the default for new databases.

There is also a database collation, which defines the default collations
for new charcater columns in the database. If memory serves the database
collation also sets the collation for variables. However, temp-table
columns get their default from tempdb. If I remember correctly, table
variables gets their default from tempdb in SQL2000, but the database in SQL
2005.

When you create a table column, you can always explicitly set the collation.

And finally, when you have a character expression, you can always cast
to another collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx