Not a Number Values in the Database

Our application has a table, which is populated via ADO.Net from C# with data originating from a C++ COM call. Today I encountered an entry that is C++ code for an undefined value: -1.#IND stored in the database. However, I could only discover what was stored in the table by Casting the value to a varchar -- simply selecting returned an error.

Is this expected behavior or a bug? It does not seem correct that SQL Server should store a value that cannot be displayed. In essence, either the value should not be allowed in the table because it violated the domain or SQL Server ought to have a way to display it with a Select *.

As fas as our application is concerned, we will be masking these values -- initially by ignoring them in the queries and eventually the loading program will convert to null.

What is the defined datatype for the field?|||

The column in question is a float column.

I would argue that the datatype should not be that important. The server should not allow a value to be stored which cannot be displayed.

|||

IND could be (COULD BE) referencing the unicode index control character. It looks like you had some sort of odd data transformation from the COM-C++-C#-SQL conversion process, especially considering it's a floating point variable which can lose precision through casting, it didn't surprise me to see that that was the data type in question.

Have you considered testing that single row by running it through your conversion process again by itself? Is the original float data for the column and row something... unusual?

|||

Hmm, very interesting. Try casting it to a numeric of some sort (particularly really large precision after the decimal)... That might give the clue. What datatype are you using in your C# application? And what prints out when you select from the column in Management Studio?

I can't imagine there is any value that is illegal for a float, but stranger things have happened.

|||

The specific data in the database was purged, due to some maintenance, so I'll have to see if it returns.

However, I made a simple example:

static void Main(string[] args)

{

double d = Double.NegativeInfinity;

using( SqlConnection conn = new SqlConnection( "server=(local);initial catalog=Northwind;integrated security=true" ) )

{

SqlCommand cmd = new SqlCommand( "Insert Foo(zero, one) Values( Datepart(mi, getdate() ),@.val )", conn );

cmd.Parameters.Add( "@.val", d );

conn.Open();

int rows = cmd.ExecuteNonQuery();

Console.WriteLine( rows );

}

Console.WriteLine( d );

}

with a target table:

Create Table Foo(

zero int not null,

one float null

)

Using any of Double.NegativeInfinity, Double.PositiveInfinity, or Double.NaN gives an odd result. The code succeeds and reports that a record has been added. However, the command Select * from Foo will not return records with these values. The values cannot be displayed by Casting to a numeric type, but output is produced by Casting to a varchar.

Using Select Count(*) from Foo will report the correct number of records.

|||

If you worked with MDX queries (analysis server), it is very common issue.

When you store the INDEFINITE/INFINTE value into your variable, compiler uses the special values to identify the INDEFINITE/INFINTE number/value,

1. 1.#IND (positive indefinite) or -1.#IND (negative indefinite)

2. 1.#INF (positive infinite)or -1.#INF (negative infinite)

Both are used to identify the compiler the variables hold the INDEFINITE/INFINTE, usally when you try to divide by zero it will be throw an error. But here you are forcefully stroring the infinite value. It is the compiler specific data. It can’t trusted across the compilers. So when you execute the code on your .NET framework will work fine. When try to pass this value into SQL Server it doesn’t understand, there is no specification available to identify the indefinite value in database.

Logically you can have nothing in your column (NULL/ZER0), but you are not allowed to store indefinite value in your column. Indefinite values are not comparable, these are imaginary values. But database store the masseurable values.

Solution:

Before passing the value to your database, validate the data, if it is INDEFINITE/INFINTE reset the value to NULL or other identifiable value for your future logics...

|||

I know you are a moderator, but as a general rule the thread creator should close threads. Being a moderator does not automatically make your information infallible.

As a matter of fact, on this issue you did not really add any additional information to the thread other than that you have experienced the issue as well.

I would now contend that this is, in fact, a bug in the SQL Server implementation. Either these values -- NaN, PositiveInfinity, and NegativeInfinity -- should be excluded from the domain of the Float datatype and rejected on Insert; or they are included in the domain and should be handled in a logical and consistent manner -- certainly they should have a display representation and they should have defined behavior in functions.

The current implementation is just a landmine waiting to go off at inopportune times.

|||

I marked the thread as 'answered'. As a 'general rule', we all take responsibitly to close a thread when there doesn't seem to be anymore 'interest' and/or it appears to be adequately 'answered'. And anyone can choose to 're-open' that thread by 'unmarking' the marked 'answer'. It's just a 'housekeeping' matter to help focus limited resources (the time and energy of volunteers) to the active and unanswered threads.

It appeared that Mani provided an adequate explanition for the behavior, and it seemed to me to be doubtful if there could be a 'solution' posted for your question. It appears that there to be a 'anomoly' involved in this situation -concerning a C++ interaction with the datastore, but I haven't been able to locate a declared 'bug' about the issue. (Doens't mean it's not there, just that I haven't devoted enough time to find it if it exists.) Mani's response satisfied the need to provide information to subsequent readers of the thread.

My mistake was in not encouraging you to post a 'potential' bug. I'll correct that now.

Please go to: http://connect.microsoft.com/sqlserver and post your observations and concerns. With everyone's keen interest and help to provide feedback and critique, the SQL Server will only continue to get better.

If you want this thread to stay 'open' to see if additional comments are forthcoming, that is perfectly fine.

As a postscript, let me add that there is often an issue of 'the' answer. We work on the concept of helping the questioner find a way to solve his/her problem. At some point, one has to say, ok, maybe there is a 'better' answer, but what is here now works, so lets move on.

|||

I have found more information on the issue:

First to clarify, the issue is not restricted to C++ interaction. The code I show above is C# and can be used to demonstrate the behavior when NaN or Infinity is stored in the database. Furthermore, I was surprised to find out that the data can be read out with a query from a C# program. My code and example were run against Sql Server 2000.

Looking on Microsoft Connections, I found an interesting item. It appears that MS has chosen to no longer support NaN and Infinity in the database in SQL Server 2005. In fact, a request had been made to restore the 2000 functionality. There is, at least, one organization which is using SQL Server to store engineering data and who has the need to occassionally store Infinities in the database. Evidently they are not performing any ad hoc queries or calculation within the database.

I plan to post a recommendation that MS embrace full support for the IEEE floating point numbers rather than simply disallowing them. It seems like the best, long term, behavior for the database and would be keeping in line with industry trends. Ideally, some form of patch would be made available for SQL 2000 to more gracefully handle NaN or Infinity when it is entered in the database.

|||Yes, this is a behavior change from SQL Server 2000. We now validate float values and Unicode data when you pass them via RPC calls or Bulk Copy APIs. So for float even though values like NaN and Infinity is allowed in the IEEE 754 spec (floating point implementation) we do not allow it now. Upon upgrade to SQL Server 2005 from older versions you need to modify the data in your tables to use them within TSQL. TSQL does not support any way to query these type of data or manipulate it explicitly - older SQL Server versions used to just allow them to be stored but you can only manipulate it on the client-side.|||

Is there a strong reason to not implement full support for the IEEE 754 spec? It specifies the rules for all operations involving the extra values. Some external representation of the values would be needed, but that has been solved for C# already.

It is a little surprising that it is not easier to support them than to prevent them. I assume that much of the Server software is written in a high level language that supports IEEE floats.

With competing products already supporting IEEE floats, the change seem inevitable.

Thanks for your input.

|||

I agree with your assessment. It is really unfortunate that we do not support it yet. Did you vote on the connect bug at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674? I would also encourage anyone reading this thread to vote on this bug. The issue today is that several built-ins in SQL Server (those that work with floating point data) and other facilities cannot handle these values. I will follow-up with the owner of this bug and see if we can do anything about it in SQL Server 2008.

sql

Normalization Question....

Hi,
I have a table of entities. Each of which has a unique ID. For a certain
subset of those entities, a particular set of values can apply to a pair of
entities.
Let's call the entity ID's E1 and E2 and the associated values x, y and z.
E1 and E2 are GUID's and x, y and z are floats.
My temptation is to create a table that has E1 and E2 as the primary key and
have columns for x, y and z. However, since E1 and E2 both represent an ID
column that relate to the same table, I feel I am breaking the first normal
form.
Essentially what needs to happen is that a pair of entity ID's needs to be
related to x, y and z values and I want the table normalized. Any advice
would be appreciated. Thanks in advance.
-PetePete Wittig wrote:
> Hi,
> I have a table of entities. Each of which has a unique ID. For a certain
> subset of those entities, a particular set of values can apply to a pair o
f
> entities.
> Let's call the entity ID's E1 and E2 and the associated values x, y and z.
> E1 and E2 are GUID's and x, y and z are floats.
> My temptation is to create a table that has E1 and E2 as the primary key a
nd
> have columns for x, y and z. However, since E1 and E2 both represent an I
D
> column that relate to the same table, I feel I am breaking the first norma
l
> form.
> Essentially what needs to happen is that a pair of entity ID's needs to be
> related to x, y and z values and I want the table normalized. Any advice
> would be appreciated. Thanks in advance.
> -Pete
It is extremely difficult to give goosd advise on such questions
without the opportunity to analyse your requirements in detail. Here
are some ideas. If you are saying that (x,y,z) uniquely defines a set
of entities then you can implement a cascading foreign key:
CREATE TABLE foo (x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL,
PRIMARY KEY (x,y,z));
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL, FOREIGN KEY (x,y,z)
REFERENCES foo (x,y,z) ON UPDATE CASCADE);
Perhaps more likely though is that your set is defined by some other
attribute(s) you haven't mentioned (foo_key in this example):
CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
*/ INTEGER NOT NULL REFERENCES foo (foo_key));
Your choice of datatypes would make me want to review this design. I've
almost never used FLOAT in tables. Although it certainly has legitimate
uses, most of the time the exact types (INTEGER or DECIMAL for example)
are much more useful. FLOAT probably isn't a good choice for a key
because of its imprecise nature. I have difficulty making sense of your
business requirement if you don't have the additional key I proposed
for the "foo" table but then I don't know your business...
Also, UNIQUEIDENTIFIER, while it may be used as a PK isn't generally
good as the only key of a table. Certainly it shouldn't be so if its
purpose is an artificial surrogate.
David Portas
SQL Server MVP
--|||Hi David,
Thank you for the response. Allow me to clarify: I have a table that
describes my entities (call it Table E). E contains 1...n entries.
CREATE TABLE E (E_key UNIQUEIDENTIFIER PRIMARY KEY, Name nvarchar(100) NOT
NULL);
The other table I was tempted to create would have looked like this:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER, E2 UNIQUEIDENTIFIER, X FLOAT NOT
NULL, Y FLOAT NOT NULL, Z FLOAT NOT NULL,
PRIMARY KEY (E1, E2))
Where E1 and E2 would have been entries from Table E. So certain pairs of
entities (i.e. entry 5 and 2, entry 19 and 20) have parameters X, Y and Z.
X, Y and Z are dependent on the pair of entities.
If I understand you correctly, I think this method would work for me best:
"CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
*/ INTEGER NOT NULL REFERENCES foo (foo_key));"
However, allow me ask a few questions for clarification. In this case,
foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
Thanks again.
BTW: My reasoning for using floats is because I am storing math/scientific
data so I need a high level of precision.
-Pete
"David Portas" wrote:

> Pete Wittig wrote:
> It is extremely difficult to give goosd advise on such questions
> without the opportunity to analyse your requirements in detail. Here
> are some ideas. If you are saying that (x,y,z) uniquely defines a set
> of entities then you can implement a cascading foreign key:
> CREATE TABLE foo (x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL,
> PRIMARY KEY (x,y,z));
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL, FOREIGN KEY (x,y,z)
> REFERENCES foo (x,y,z) ON UPDATE CASCADE);
> Perhaps more likely though is that your set is defined by some other
> attribute(s) you haven't mentioned (foo_key in this example):
> CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));
> Your choice of datatypes would make me want to review this design. I've
> almost never used FLOAT in tables. Although it certainly has legitimate
> uses, most of the time the exact types (INTEGER or DECIMAL for example)
> are much more useful. FLOAT probably isn't a good choice for a key
> because of its imprecise nature. I have difficulty making sense of your
> business requirement if you don't have the additional key I proposed
> for the "foo" table but then I don't know your business...
> Also, UNIQUEIDENTIFIER, while it may be used as a PK isn't generally
> good as the only key of a table. Certainly it shouldn't be so if its
> purpose is an artificial surrogate.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:
> Hi David,
> Thank you for the response. Allow me to clarify: I have a table that
> describes my entities (call it Table E). E contains 1...n entries.
> CREATE TABLE E (E_key UNIQUEIDENTIFIER PRIMARY KEY, Name nvarchar(100) NOT
> NULL);
> The other table I was tempted to create would have looked like this:
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER, E2 UNIQUEIDENTIFIER, X FLOAT NOT
> NULL, Y FLOAT NOT NULL, Z FLOAT NOT NULL,
> PRIMARY KEY (E1, E2))
> Where E1 and E2 would have been entries from Table E. So certain pairs of
> entities (i.e. entry 5 and 2, entry 19 and 20) have parameters X, Y and Z.
> X, Y and Z are dependent on the pair of entities.
> If I understand you correctly, I think this method would work for me best:
> "CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));"
> However, allow me ask a few questions for clarification. In this case,
> foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
> Thanks again.
> BTW: My reasoning for using floats is because I am storing math/scientific
> data so I need a high level of precision.
> -Pete
>
Your choice of keys in the Params table looks very suspect to me. If E1
or E2 can appear multiple times then apparently these are groups, not
pairs. I had undestood that E1 determined (x,y,z) and that E2
determined the SAME (x,yz). If so, your table creates redundancy
because you allow E1 or E2 to be added multiple times with different
values of (x,y,z). Based on your original post I would have guessed:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL)
but now I have my doubts as to whether I understood you.
Design-by-newsgroup is a recipe for these kinds of misunderstandings
unfortunately.

> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));"
> However, allow me ask a few questions for clarification. In this case,
> foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
I doubt it, but you tell me. I was guessing there might be some
meaningful attribute that relates pairs or sets of entities together.
You wouldn't (shouldn't) need to use an artificial key unless (x,y,z)
are unique in foo.
David Portas
SQL Server MVP
--|||Thanks for the reply. Let me try and put this into context. In my
application, I want to look up an entity. That entity has certain attribute
s
which are returned such as its name. Another type of information is its
interaction parameters, how it interacts with other entities. This is the
data that I am trying to store.
So for a entity 1, I would return its name. Then I would want to return all
the ID's of the other entities it interacts with as well as the x, y and z
parameters associated with each particular pair/interaction (so potentially,
entity 1 & entity 2 and their associated x, y and z, entity 1 & entity 5 and
their associated x, y and z...).
Additionally, I will have a tool in which I can enter two entities and
return their x, y and z parameters.
In the application I will have the ID for entity 1, what I need to get is
all entity ID's that have an interaction with entity 1 and the associated x,
y and z parameters for that interaction.
I think you are correct in saying the key in my table Params looks suspect.
I agree. Since the x, y and z parameters would be the same for the pairs
entity1 & entity 2 and entity 2 & entity 1.
Does this help to clarify?
"David Portas" wrote:

> Pete Wittig wrote:
> Your choice of keys in the Params table looks very suspect to me. If E1
> or E2 can appear multiple times then apparently these are groups, not
> pairs. I had undestood that E1 determined (x,y,z) and that E2
> determined the SAME (x,yz). If so, your table creates redundancy
> because you allow E1 or E2 to be added multiple times with different
> values of (x,y,z). Based on your original post I would have guessed:
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
> REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
> (e_key), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL)
> but now I have my doubts as to whether I understood you.
> Design-by-newsgroup is a recipe for these kinds of misunderstandings
> unfortunately.
>
> I doubt it, but you tell me. I was guessing there might be some
> meaningful attribute that relates pairs or sets of entities together.
> You wouldn't (shouldn't) need to use an artificial key unless (x,y,z)
> are unique in foo.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:

> Thanks for the reply. Let me try and put this into context. In my
> application, I want to look up an entity. That entity has certain attribu
tes
> which are returned such as its name. Another type of information is its
> interaction parameters, how it interacts with other entities. This is the
> data that I am trying to store.
> So for a entity 1, I would return its name. Then I would want to return a
ll
> the ID's of the other entities it interacts with as well as the x, y and z
> parameters associated with each particular pair/interaction (so potentiall
y,
> entity 1 & entity 2 and their associated x, y and z, entity 1 & entity 5 a
nd
> their associated x, y and z...).
> Additionally, I will have a tool in which I can enter two entities and
> return their x, y and z parameters.
> In the application I will have the ID for entity 1, what I need to get is
> all entity ID's that have an interaction with entity 1 and the associated
x,
> y and z parameters for that interaction.
> I think you are correct in saying the key in my table Params looks suspect
.
> I agree. Since the x, y and z parameters would be the same for the pairs
> entity1 & entity 2 and entity 2 & entity 1.
> Does this help to clarify?
>
>
If we are only talking about pairs of entities then I think we're
nearly there. Just add the constraint E1<E2 so that you can never get
redundant pairs (A-B and B-A):
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
NOT NULL);
BTW I have left out the constraint names for brevity. You should always
give constraints your own names rather than let the system generate
them. It makes them much easier to maintain.
David Portas
SQL Server MVP
--|||Thanks for the reply.
In regards to this table:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
NOT NULL);
If I read this table correctly, it has only E1 as the primary key. I
believe that the primary key will have to be E1 and E2 since I could have
values like this (I've substituted ints for unique identifiers for
convenience in this example):
E1 E2 x y z
-- -- -- -- --
1 5 0.1 0.2 0.3
1 6 0.4 0.5 0.6
9 1 0.7 0.8 0.9
If I make the addition of including E2 in the primary key, will that effect
the "CHECK (e1 < e2)"?
I still have a question with regards to normalization. Since there is an E1
and E2 column in the Params table, does this mean it violates the 1NF? If
not, could you please explain why?
Thanks again.
"David Portas" wrote:

> Pete Wittig wrote:
>
> If we are only talking about pairs of entities then I think we're
> nearly there. Just add the constraint E1<E2 so that you can never get
> redundant pairs (A-B and B-A):
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
> REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
> (e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
> NOT NULL);
> BTW I have left out the constraint names for brevity. You should always
> give constraints your own names rather than let the system generate
> them. It makes them much easier to maintain.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:
> E1 E2 x y z
> -- -- -- -- --
> 1 5 0.1 0.2 0.3
> 1 6 0.4 0.5 0.6
> 9 1 0.7 0.8 0.9
> If I make the addition of including E2 in the primary key, will that effec
t
> the "CHECK (e1 < e2)"?
Still looks OK to me.

> I still have a question with regards to normalization. Since there is an
E1
> and E2 column in the Params table, does this mean it violates the 1NF? If
> not, could you please explain why?
>
First Normal Form is an elusive concept. Strictly speaking any table
conforms to 1NF if it has a candidate key and doesn't permit nulls. In
practice we look out for attributes that contain more than one domain
of values or have several columns representing the same attribute or
contain data structures encoded in strings. Loosely speaking we say
that these are a violation of 1NF. These are all subjective notions.
Sometimes it seems "obvious" when the spirit of 1NF is being violated.
Sometimes it is more tricky and the judgement will come down to one's
knowledge and experience of what works and what doesn't.
In your case we can say that using a pair of foreign keys in a single
table to represent a many-to-many relationship is a proven design that
appears frequently and that almost every database architect must have
used it. There are alternatives but the major benefit of your design is
that it's very easy to apply the necessary constraints for the business
rules. Your table is in 1NF and I don't see a better design.
David Portas
SQL Server MVP
--|||Thanks very much David. I appreciate the advice.
"David Portas" wrote:

> Pete Wittig wrote:
> Still looks OK to me.
>
> First Normal Form is an elusive concept. Strictly speaking any table
> conforms to 1NF if it has a candidate key and doesn't permit nulls. In
> practice we look out for attributes that contain more than one domain
> of values or have several columns representing the same attribute or
> contain data structures encoded in strings. Loosely speaking we say
> that these are a violation of 1NF. These are all subjective notions.
> Sometimes it seems "obvious" when the spirit of 1NF is being violated.
> Sometimes it is more tricky and the judgement will come down to one's
> knowledge and experience of what works and what doesn't.
> In your case we can say that using a pair of foreign keys in a single
> table to represent a many-to-many relationship is a proven design that
> appears frequently and that almost every database architect must have
> used it. There are alternatives but the major benefit of your design is
> that it's very easy to apply the necessary constraints for the business
> rules. Your table is in 1NF and I don't see a better design.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for your help David. I appreciate it.
"David Portas" wrote:

> Pete Wittig wrote:
> Still looks OK to me.
>
> First Normal Form is an elusive concept. Strictly speaking any table
> conforms to 1NF if it has a candidate key and doesn't permit nulls. In
> practice we look out for attributes that contain more than one domain
> of values or have several columns representing the same attribute or
> contain data structures encoded in strings. Loosely speaking we say
> that these are a violation of 1NF. These are all subjective notions.
> Sometimes it seems "obvious" when the spirit of 1NF is being violated.
> Sometimes it is more tricky and the judgement will come down to one's
> knowledge and experience of what works and what doesn't.
> In your case we can say that using a pair of foreign keys in a single
> table to represent a many-to-many relationship is a proven design that
> appears frequently and that almost every database architect must have
> used it. There are alternatives but the major benefit of your design is
> that it's very easy to apply the necessary constraints for the business
> rules. Your table is in 1NF and I don't see a better design.
> --
> David Portas
> SQL Server MVP
> --
>

Non-queried default values not working

This is driving me crazy. I can't get non-queried default values to
work with non-queried string report parameters. I'm using Reporting
Services 2000. Here's a test case: I have a simple Report Parameter: a
string, called "Color". It has three non-queried label-value pairs:
Red-red, Green-green, Blue-blue. I then try to define a default
non-queried value of green. Run the report, it always says "Select a
value". If I change the label-values to single-digit values, like
this: Red-1, Green-2, Blue-3, and set the default value to 2, then
Green comes up as the selected value (which is what I want). But I
cannot use a string for the value - it never matches. I've also tried
two-digit numbers: Red-11, Green-22, Blue-33, and used 22 as the
default. Comes up with "Select a value".
Can you not use strings as default values?Found the answer myself. Yes, you can use strings as default values,
as you would expect. Everything worked great in the Preview pane of
Visual Studio (the defaults would show properly) but when I put the
report on the report server, the dropdowns would say "Select a value".
To fix it, I had to completely delete the report from the server, and
upload a brand new copy of the RDL file. It did not work to simply use
the "Update" option in the Report Properties web page on the server.
It would update the report (i.e., I could see other changes I made) but
for some reason, the parameters would not work properly until I
completed deleted the report and uploaded it again. Go figure.

None aggregation function

I have created a cube where I simply want to store non-aggregatable values. I have used the none aggregation function setting but can't seem to browse the leaf level and see any values after processing. If I set the aggregation function to sum , max or min, all works as expected. I assume that None should be used when you want to load non-aggreatable data into a cube. I would have expected to be able to see data at the leaf level, is this assumption incorrect? I'm using SQL 2005 SP1.

None aggregation function works, you probably simply are not browsing at the real leaves. It is not that simply to get to real leaves, since you need to position every single attribute included in the measure group to get there. For testing you can use Leaves() function (few more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx), but with just a browser you will have hard time navigating to real leaves.|||

Mosha-

Thanks for the rapid response! You are correct I had a tough time getting to the leaves. Problem was... I used the upper filter area in the VS browser, rather that the filters directly on the top of the grid, and each view gave a differing response. I have put screen shots into a word doc to demonstrate but can't find a way to attach them to this post. At least my question is anwsered but I'm slighly concernerd that I got 2 different results based on the way I filtered the view.

Thanks Again

Chris

Non aggregate case

Hello,
In the same fact table, I have real values and min/max values. The type of value (real, min, max) is a dimension. So, I want to aggregate value only for real value, because for min/max values, all the leaf are filled by my program.
So, it's possible to say "I want the not aggregate value in the database" in MDX query?
Thank you,
Guillaume.

Hi Guillaume,

Can you create a view (or named query, if you're using AS 2005) on the fact table, such that 3 separate measure fields can be created for the real, min and max values? In that case, appropriate aggregation functions of sum(), min(0 and max() can be applied to each measure. Something like:

create view splitfact as

select fact.dim1, fact.dim2, fact.dim3, ..,

sum(case fact.typedim when 'real' then fact.valfield else 0 end) as realval,

sum(case fact.typedim when 'min' then fact.valfield else 0 end) as minval,

sum(case fact.typedim when 'max' then fact.valfield else 0 end) as maxval

from fact

group by fact.dim1, fact.dim2, fact.dim3, ..

|||

If you look at the attribute in the cube editor, you will see that there is an aggregation usage property. Change it from default to none.

_-_-_ Dave

|||Thank you !

Ok, now I created 2 NamedCalculation on my DataSourceView. The first one is for 'real' value with aggregate function = SUM, and for the second NamedCalculation, I put 'min/max' values, with aggregate function = NONE.

But when I browse data, I can't see my data with aggregate function=None. But when I change the aggregate function, I can see my data. It's like if the none aggreagte function doesn't work correctly. Do you know this problem?

No Values in Dropdowns

I'm having a problems with SQL 2005 reporting services. We have deveoped a number of reports that operate fine if the developer runs them but, when a user accesses them, the dropdown fields are empty. Also, when selecting a date from the date picker, the value reverts back to the default set in the report.

Any ideas?

Thanks

Brian

I should add this is in the report viewer webpage. The use can see all forlders and execute the reports. We place data from an "environments database in the header and those fields are showing up fine. It appears that there is a problem with users modifying the report parameters at run time...... Driving me crazy....

Brian

|||Problem solved. My user had ie version 5 sp4. The minimum is v 5.5 sp2. Preferably v 6 sp1.