Confusion: "Manual", "Incremental", "Update"

Hi,
I'm using SQL Server 2005. What's difference between mechanisms of "Manual"
update and "Incremental" population? I also need some clarification about
"Update" type of population.
Thanks in advance,
Leila
There are three population types, full, incremental and change tracking.
Change Tracking can be continuous or on demand (Manual). When using the
Change Tracking - manual option you must run a ALTER FULLTEXT INDEX ON
TableName START UPDATE to process the tracked changes.
No matter what population method you try a full population will always be
done first. If you have a timestamp column on your table and have already
done an full population, an incremental population will be done.
Change tracking will track changes which occur to the columns you are
full-text indexing. They will be indexed near real time, or at scheduled
intervals using the alter command mentioned above.
A Full population will index every row on your table whenever it is run. An
incremental population will extract each row from your table and detect if
it has changed (it can't detect if the change occured to a column which you
are full-text indexing or not), and it will then compare it with its list to
see what has been deleted and then update the catalog with the
updated/deletions. Incremental populations take almost as long as
full-populations. Use incremental populations when small amounts of data
changes at discrete times. By small I mean around 40-80% or so. Use Full
when more than 90% occurs at any one time. Use change tracking everywhere
else. Your numbers may vary.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23fDNVdwRHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm using SQL Server 2005. What's difference between mechanisms of
> "Manual" update and "Incremental" population? I also need some
> clarification about "Update" type of population.
> Thanks in advance,
> Leila
>
|||Thanks indeed Hilary!
1) Can I conclude that: When we use change tracking (manual), we must use
ALTER FULLTEXT INDEX by considering whether we have timestamp column or not.
If we have, then we must use START INCREMENTAL, unless START UPDTAE?
2) How the changes are tracked when we don't have timestamp column?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYvp08wRHHA.4632@.TK2MSFTNGP04.phx.gbl...
> There are three population types, full, incremental and change tracking.
> Change Tracking can be continuous or on demand (Manual). When using the
> Change Tracking - manual option you must run a ALTER FULLTEXT INDEX ON
> TableName START UPDATE to process the tracked changes.
> No matter what population method you try a full population will always be
> done first. If you have a timestamp column on your table and have already
> done an full population, an incremental population will be done.
> Change tracking will track changes which occur to the columns you are
> full-text indexing. They will be indexed near real time, or at scheduled
> intervals using the alter command mentioned above.
> A Full population will index every row on your table whenever it is run.
> An incremental population will extract each row from your table and detect
> if it has changed (it can't detect if the change occured to a column which
> you are full-text indexing or not), and it will then compare it with its
> list to see what has been deleted and then update the catalog with the
> updated/deletions. Incremental populations take almost as long as
> full-populations. Use incremental populations when small amounts of data
> changes at discrete times. By small I mean around 40-80% or so. Use Full
> when more than 90% occurs at any one time. Use change tracking everywhere
> else. Your numbers may vary.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23fDNVdwRHHA.1200@.TK2MSFTNGP02.phx.gbl...
>

Confusion with SQL Server database data types

I trust you'll bear with an SQL Server newbie with what may seem a rather inane request. I am designing a web app in Web Designer 2005 Express with SQL Server Express. Unfortunately, I'm finding a little confusing with some of the data types when designing tables. I have tried to find information on the various Microsoft sites (general site, MSDN, here) and while I found one document that had a table comparing data types in different implementations of SQL, it wasn't at all helpful. Most of my confusion is with the various string and char types; the numeric types seem pretty straight forward for the most part. However, it might be helpful to know the difference between money and smallmoney/datetime and smalldatetime, particularly space/size information and formatting options (unless the latter is up to the interface). It would also be helpful to know which string/char types correspond to any counterparts they might have in, for instance, Access (with which I am already quite exprienced). Or any particular quirks or idiosyncracies they might have. I don't expect anyone to write a full tutorial, but if someone could point me in the direction of a good online doc, it would be most appreciated. You might well ask, why not use Access databases? I would answer...I like to learn new stuff!

Thanks much.

Hi Eyetech... I agree, there seem to be lots of data types to chose from. Sorry, I cant do a case study on them all for you as I too am a relative newbie... I can share one experience though... I found a key difference between the nchar and vchar types. It seems the nchar forces the lenth of the data stored to be whatever length the field is declared as - padding with spaces when needed. I found all those padding spaces to be a real pain in my code... I've since moved away from nchar in favor of vchar. I suppose there may be some performance issues there, but so far the code's a lot more friendly. -- Curt

|||

curtisdehaven:

I found a key difference between the nchar and vchar types. It seems the nchar forces the lenth of the data stored to be whatever length the field is declared as - padding with spaces when needed. I found all those padding spaces to be a real pain in my code... I've since moved away from nchar in favor of vchar.

Thanks Curtis. Since my first post, I did some Googleing and found some very helpful information on a third-party website. It includes a table with the actual numeric ranges for numeric types (eg: int: integer data from -2^31 through 2^31-1 stored in 4 bytes). It also explains something that may be the cause of your woes in using nchar type. nchar is actually for storing unicode char data which uses 2 bytes instead of 1 for each character. char supports the 1-byte ASCII characters padded to fixed lengths while varchar is the variable length type. It is recommended that if you don't actually have to store unicode, avoid using the 'n' types and stick to char/varchar. It will certainly take up less space in your database in the long run since nchar and nvarchar will always take up double the space of their char/varchar counterparts.

Being new here, I'm not sure if it is appropriate to post links to outside sources. If someone can confirm that it is ok, I'll gladly add the link for others to refer to.

|||

Hi eyetech,

Yes, you can post links to outside resource. Thank you for sharing your knowlege with all the people here.

|||

The .NET Char is the nineth integer and Unicode by default so when you are using database Char/Varchar you are using bytes until 255, the reason Membership data types are Nvarchar instead of Varchars. The SQL Server team have prepared a comprehensive chart of the three type your application uses SQL Server, ADO.NET and FCL(framework class library). Hope this helps

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

SQL Server data type

CLR data type (SQL Server)

CLR data type (.NET Framework)

varbinary

SqlBytes, SqlBinary

Byte[]

binary

SqlBytes, SqlBinary

Byte[]

varbinary(1), binary(1)

SqlBytes, SqlBinary

byte, Byte[]

image

None

None

varchar

None

None

char

None

None

nvarchar(1), nchar(1)

SqlChars, SqlString

Char, String, Char[]

nvarchar

SqlChars, SqlString

SQLChars is a better match for data transfer and access, andSQLString is a better match for performing String operations.

String, Char[]

nchar

SqlChars, SqlString

String, Char[]

text

None

None

ntext

None

None

uniqueidentifier

SqlGuid

Guid

rowversion

None

Byte[]

bit

SqlBoolean

Boolean

tinyint

SqlByte

Byte

smallint

SqlInt16

Int16

int

SqlInt32

Int32

bigint

SqlInt64

Int64

smallmoney

SqlMoney

Decimal

money

SqlMoney

Decimal

numeric

SqlDecimal

Decimal

decimal

SqlDecimal

Decimal

real

SqlSingle

Single

float

SqlDouble

Double

smalldatetime

SqlDateTime

DateTime

datetime

SqlDateTime

DateTime

sql_variant

None

Object

User-defined type(UDT)

None

Same class that is bound to the user-defined type in the same assembly or a dependent assembly.

table

None

None

cursor

None

None

timestamp

None

None

xml

SqlXml

None

Confusion with date attributes and member name / value

Hi,

I'm just wondering about how the best approach is to create a date member... The member name has to be a string, so there seams to be no way that a client application can decide how to display a date (by it's regional settings). You have to do the formatting in the DSV. You can use some formatting with the member value but this isn't used by client applications by default (is there any application using it out there, yet?).

So what's the best approach here?

Thanks,

Hi Thomas,

Could Translations help you (i.e defining different captions for a given date, by locale)?

|||

Deepak,

yes that might be a solution... However this might be a problem with other datatypes as well... I just think about an attribute "Size" which might be formatted in differently in different regions (with a comma or point as decimal seperator, ...). So everything has to be done with translations? Not a very good approach...

Thanks anyway...

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

It's per processor regardless of the number of cores. See the link below.

Doug

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

On the bottom of page two: Microsoft has been driving thought leadership in this area by charging the same amount per processor, regardless of how many cores are in the processor.

|||

Hi Doug, thanks for the info.

You've actually answered another question that I was going to ask in my original post (but forgot to include).

One thing that's missing is any information regarding SQL Server 2005 Standard's limit of 4 CPUs and whether this limit refers to logical or physical CPUs. I'm now happy with the licensing aspect, they couldn't have made it any clearer, however I don't want to purchase four dual-core CPUs if only four of the eight cores will actually be used by SQL Server.

I have been forwarded the following link that clearly describes SQL Express' behaviour when presented with a single dual-core and/or hyperthreaded CPU, however there seems to be a lack of information as to whether the same rules apply to the Standard and Workgroup editions of SQL Server.

http://support.microsoft.com/kb/914278/en-us

Thanks again, Chris.

|||

From emperical evidence, it looks like Standard is restricting on logical processors.

We have a production config with SQL2005-Standard-SP1 on Dell 6850 (quad-socket/dual-core/hyper-threaded [tulsa]) ..... Taskmgr only shows 4 logical cpus busy at any one time.

Also have SQL2005-Standard-SP1 on Dell 2950 (dual-socket/dual-core [woodcrest]) .... Taskmgr shows all 4 cores busy.

Nice confusing tactics ... license on sockets .... tether performance on logicals.

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

It's per processor regardless of the number of cores. See the link below.

Doug

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

On the bottom of page two: Microsoft has been driving thought leadership in this area by charging the same amount per processor, regardless of how many cores are in the processor.

|||

Hi Doug, thanks for the info.

You've actually answered another question that I was going to ask in my original post (but forgot to include).

One thing that's missing is any information regarding SQL Server 2005 Standard's limit of 4 CPUs and whether this limit refers to logical or physical CPUs. I'm now happy with the licensing aspect, they couldn't have made it any clearer, however I don't want to purchase four dual-core CPUs if only four of the eight cores will actually be used by SQL Server.

I have been forwarded the following link that clearly describes SQL Express' behaviour when presented with a single dual-core and/or hyperthreaded CPU, however there seems to be a lack of information as to whether the same rules apply to the Standard and Workgroup editions of SQL Server.

http://support.microsoft.com/kb/914278/en-us

Thanks again, Chris.

|||

From emperical evidence, it looks like Standard is restricting on logical processors.

We have a production config with SQL2005-Standard-SP1 on Dell 6850 (quad-socket/dual-core/hyper-threaded [tulsa]) ..... Taskmgr only shows 4 logical cpus busy at any one time.

Also have SQL2005-Standard-SP1 on Dell 2950 (dual-socket/dual-core [woodcrest]) .... Taskmgr shows all 4 cores busy.

Nice confusing tactics ... license on sockets .... tether performance on logicals.

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

Hi,

all is physical CPUs, as well as licencing and limitations.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks for the reply.

Just to clarify in my own mind - are you saying that an instance of SQL Server 2005 Standard will be able to fully utilise four dual-core CPUs (i.e. all eight logical CPUs)?

Thanks again, Chris.

|||Well, interesting thing, I would assume you could use them as SQL Server starts a separate scheduler for the single core:

http://support.microsoft.com/kb/914278/en-us

Don′t thing that this differs much from the full editions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

SQL Server 2005 only counts physical CPU's for licensing purposes, whether they are hyper-threaded, dual-core, or quad-core. SQL Server 2005 will use all of the logical CPU's that are present, based on the physical CPU count limitation.

You should be careful about getting Standard Edition instead of Enterprise Edition. Lots of important features, such as online index rebuild, online restore and fast recovery are only in Enterprise Edition.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Confusion over SQL 2005 Standard CPU Support

I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).

Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?

Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?

Thanks, Chris.

Hi,

all is physical CPUs, as well as licencing and limitations.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks for the reply.

Just to clarify in my own mind - are you saying that an instance of SQL Server 2005 Standard will be able to fully utilise four dual-core CPUs (i.e. all eight logical CPUs)?

Thanks again, Chris.

|||Well, interesting thing, I would assume you could use them as SQL Server starts a separate scheduler for the single core:

http://support.microsoft.com/kb/914278/en-us

Don′t thing that this differs much from the full editions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

SQL Server 2005 only counts physical CPU's for licensing purposes, whether they are hyper-threaded, dual-core, or quad-core. SQL Server 2005 will use all of the logical CPU's that are present, based on the physical CPU count limitation.

You should be careful about getting Standard Edition instead of Enterprise Edition. Lots of important features, such as online index rebuild, online restore and fast recovery are only in Enterprise Edition.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Confusion over SP versions

Hi,
I have downloaded and run the file sql2ksp3.exe for
SQL Server 2000.
However, when I run select @.@.version in SQL Analyser it
returns the details for version 2.
ie,
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
2001 13:23:50
Is this OK? Has the SP deployed correctly?
regards
john
Hi,
After the SP3 upgrade the build number should be Microsoft SQL Server
2000 - 8.00.760.
You could check this in SQL 2000 using the below command in query analyzer:-
select serverproperty('ProductLevel')
Can you down load the SP3a from below site and try applying it. After the
upgrade execute the above statement
to get service pack level.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Thanks
Hari
MCDBA
"John Mullen" <anonymous@.discussions.microsoft.com> wrote in message
news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
> Hi,
> I have downloaded and run the file sql2ksp3.exe for
> SQL Server 2000.
> However, when I run select @.@.version in SQL Analyser it
> returns the details for version 2.
> ie,
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
> 2001 13:23:50
> Is this OK? Has the SP deployed correctly?
> regards
> john
>
|||IIRC, if you run the sp3.exe file, it just extracts the contents into a
folder. You need to go to that folder and run setup.exe ...
http://www.aspfaq.com/
(Reverse address to reply.)
"John Mullen" <anonymous@.discussions.microsoft.com> wrote in message
news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
> Hi,
> I have downloaded and run the file sql2ksp3.exe for
> SQL Server 2000.
> However, when I run select @.@.version in SQL Analyser it
> returns the details for version 2.
> ie,
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
> 2001 13:23:50
> Is this OK? Has the SP deployed correctly?
> regards
> john
>
|||Thanks Aaron,
sometimes the most obvious things are the most elusive
regards
john

>--Original Message--
>IIRC, if you run the sp3.exe file, it just extracts the
contents into a
>folder. You need to go to that folder and run
setup.exe ...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John Mullen" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
19
>
>.
>

Confusion over SP versions

Hi,
I have downloaded and run the file sql2ksp3.exe for
SQL Server 2000.
However, when I run select @.@.version in SQL Analyser it
returns the details for version 2.
ie,
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
2001 13:23:50
Is this OK? Has the SP deployed correctly?
regards
johnOn my version its saying Microsoft SQL Server 2000 -
8.00.760 (Intel X86) Dec 17 2002 14:22:05.
So my version seems to be ahead of yours.
Peter
>--Original Message--
>Hi,
>I have downloaded and run the file sql2ksp3.exe for
>SQL Server 2000.
>However, when I run select @.@.version in SQL Analyser it
>returns the details for version 2.
>ie,
>Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov
19
>2001 13:23:50
>Is this OK? Has the SP deployed correctly?
>regards
>john
>.
>|||Hi,
After the SP3 upgrade the build number should be Microsoft SQL Server
2000 - 8.00.760.
You could check this in SQL 2000 using the below command in query analyzer:-
select serverproperty('ProductLevel')
Can you down load the SP3a from below site and try applying it. After the
upgrade execute the above statement
to get service pack level.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Thanks
Hari
MCDBA
"John Mullen" <anonymous@.discussions.microsoft.com> wrote in message
news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
> Hi,
> I have downloaded and run the file sql2ksp3.exe for
> SQL Server 2000.
> However, when I run select @.@.version in SQL Analyser it
> returns the details for version 2.
> ie,
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
> 2001 13:23:50
> Is this OK? Has the SP deployed correctly?
> regards
> john
>|||IIRC, if you run the sp3.exe file, it just extracts the contents into a
folder. You need to go to that folder and run setup.exe ...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Mullen" <anonymous@.discussions.microsoft.com> wrote in message
news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
> Hi,
> I have downloaded and run the file sql2ksp3.exe for
> SQL Server 2000.
> However, when I run select @.@.version in SQL Analyser it
> returns the details for version 2.
> ie,
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
> 2001 13:23:50
> Is this OK? Has the SP deployed correctly?
> regards
> john
>|||Thanks Aaron,
sometimes the most obvious things are the most elusive
regards
john
>--Original Message--
>IIRC, if you run the sp3.exe file, it just extracts the
contents into a
>folder. You need to go to that folder and run
setup.exe ...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John Mullen" <anonymous@.discussions.microsoft.com> wrote
in message
>news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
>> Hi,
>> I have downloaded and run the file sql2ksp3.exe for
>> SQL Server 2000.
>> However, when I run select @.@.version in SQL Analyser it
>> returns the details for version 2.
>> ie,
>> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov
19
>> 2001 13:23:50
>> Is this OK? Has the SP deployed correctly?
>> regards
>> john
>
>.
>

Confusion over SP versions

Hi,
I have downloaded and run the file sql2ksp3.exe for
SQL Server 2000.
However, when I run select @.@.version in SQL Analyser it
returns the details for version 2.
ie,
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
2001 13:23:50
Is this OK? Has the SP deployed correctly?
regards
johnHi,
After the SP3 upgrade the build number should be Microsoft SQL Server
2000 - 8.00.760.
You could check this in SQL 2000 using the below command in query analyzer:-
select serverproperty('ProductLevel')
Can you down load the SP3a from below site and try applying it. After the
upgrade execute the above statement
to get service pack level.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Thanks
Hari
MCDBA
"John Mullen" <anonymous@.discussions.microsoft.com> wrote in message
news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
> Hi,
> I have downloaded and run the file sql2ksp3.exe for
> SQL Server 2000.
> However, when I run select @.@.version in SQL Analyser it
> returns the details for version 2.
> ie,
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
> 2001 13:23:50
> Is this OK? Has the SP deployed correctly?
> regards
> john
>|||IIRC, if you run the sp3.exe file, it just extracts the contents into a
folder. You need to go to that folder and run setup.exe ...
http://www.aspfaq.com/
(Reverse address to reply.)
"John Mullen" <anonymous@.discussions.microsoft.com> wrote in message
news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
> Hi,
> I have downloaded and run the file sql2ksp3.exe for
> SQL Server 2000.
> However, when I run select @.@.version in SQL Analyser it
> returns the details for version 2.
> ie,
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19
> 2001 13:23:50
> Is this OK? Has the SP deployed correctly?
> regards
> john
>|||Thanks Aaron,
sometimes the most obvious things are the most elusive
regards
john

>--Original Message--
>IIRC, if you run the sp3.exe file, it just extracts the
contents into a
>folder. You need to go to that folder and run
setup.exe ...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John Mullen" <anonymous@.discussions.microsoft.com> wrote
in message
>news:c4ea01c47a35$714a3300$a501280a@.phx.gbl...
19[vbcol=seagreen]
>
>.
>

Confusion over datasets, lists, first(

I'm trying to build a report in VS2005. The main dataset (DSCases) for
the report is just "Select * from LawCases where Docket = ?"
It should only return one case. So I dragged a few fields onto the
report body, and VS kept making the expression First(Fields!...)
instead of just Fields!. I don't know why but I don't know much about
SSRS so that isn't too surprising.
I used a list with that dataset as the DataSetName and then I could
get rid of the Firsts. Fine.
However I need some other tables mixed in, so I created another
dataset DSAttorneys (Select * from LawCasesAttorney where Docket = ?),
which should get me the various attorneys who worked on the case. Then
I added a list inside the main list, set the DataSetName to the
DSAttorneys, and VS insists on using First again. And, indeed, I do
get the first attorney, but I need them all. It won't compile without
the First though.
I hoped that the inside list would understand that it would repeat
those fields for each record. I looked at filters and grouping but
can't get it to work as I expect.
Do I need to use subreports instead of a list?As you saw you can't just put a field on the layout surface. It has to be
bound to a list or a table. If you know there is only going to be one record
returned then drag and drop the fields and use the First aggregate. Then
drag the list over (or the table object, whichever works best). Otherwise
use a subreport.
What is happening is RS does not know it is a single row, how can it. So for
a list within a list it would need to be joining the data (like a
master-detail). For instance a list of orders and a list of items for each
order. When you do a 1 to many or a 1 to 1 you need to use subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:0i3pr39o6so20jdih2k8p1lrbpsep6mt37@.4ax.com...
> I'm trying to build a report in VS2005. The main dataset (DSCases) for
> the report is just "Select * from LawCases where Docket = ?"
> It should only return one case. So I dragged a few fields onto the
> report body, and VS kept making the expression First(Fields!...)
> instead of just Fields!. I don't know why but I don't know much about
> SSRS so that isn't too surprising.
> I used a list with that dataset as the DataSetName and then I could
> get rid of the Firsts. Fine.
> However I need some other tables mixed in, so I created another
> dataset DSAttorneys (Select * from LawCasesAttorney where Docket = ?),
> which should get me the various attorneys who worked on the case. Then
> I added a list inside the main list, set the DataSetName to the
> DSAttorneys, and VS insists on using First again. And, indeed, I do
> get the first attorney, but I need them all. It won't compile without
> the First though.
> I hoped that the inside list would understand that it would repeat
> those fields for each record. I looked at filters and grouping but
> can't get it to work as I expect.
> Do I need to use subreports instead of a list?|||On Wed, 20 Feb 2008 15:06:49 -0600, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>As you saw you can't just put a field on the layout surface. It has to be
>bound to a list or a table. If you know there is only going to be one record
>returned then drag and drop the fields and use the First aggregate. Then
>drag the list over (or the table object, whichever works best). Otherwise
>use a subreport.
>What is happening is RS does not know it is a single row, how can it. So for
>a list within a list it would need to be joining the data (like a
>master-detail). For instance a list of orders and a list of items for each
>order. When you do a 1 to many or a 1 to 1 you need to use subreports.
Thanks. There will be quite a few different detail records for each
master (at least four other tables) so I can't even think of how to
join them all. Subreports certainly seems the way to go. I'll give it
a shot.

Confusion over @@ROWCOUNT test

I have a pair of services.

One is an initiator of a conversation, another is a target. I have a pair of message types, one sent by the initiator one sent by the target.

I have a pair of queues corresponding to the above services. Both queues have activation stored procedures associate with them.

The intiiator sends its mesage to the queue that the target service is defined on.

The activation procedure successfully retrieves the message, does some work and then sends a reply message on the same conversation to the initiating service.

At this point behavior seems to become strange.

Some of the time the send from the target service produces the "zero length message" error and indeed at times the message body is empty - while at others (no code changes just another trial send) it is not.

All of the time the RECEIVE on the Initiator's queue executed by the Activated Procedure (in response to the target sending a reply) gives a @.@.ROWCOUNT = 0 (i.e. even when there are no errors generated)

However in this case, the message_body field contains the (target sent, reply) message

The Target RECEIVE and its SEND reply are both executed within the same transaction and the SEND is performed by a stored procedure call passing the conversation handle (inter alia) as a parameter.

The Initiator's queue (with RETENTION = ON) then shows 2 messages in it, one is the reply message (with a status of 0) and the other is the original message (sent by the initiator with status of 3)

All services and queues and Activation procedures are in the same database. Dialog encryption is off.

The intial SEND is done by a simple driver stored procedure in the same database

The security context of the Activation procedures for the 2 queues is - as far as I can tell - the same.

Objects were all created in the dbo security context.

The "work" involves updates to tables in the local database.

No doubt, I have done something stupid somewhere and am incapable of recognising it, but (apart from the work performed by the target procedure) what I have done appears to be no more than the above, using Service Broker SQL copied from examples, with my identifiers substituted for queue names, etc.

Any thoughts anyone?

rrkk wrote:

Some of the time the send from the target service produces the "zero length message" error and indeed at times the message body is empty - while at others (no code changes just another trial send) it is not.

I'm not familiar with the "zero length message" error. Can you post the actual SQL error message?

rrkk wrote:

The Initiator's queue (with RETENTION = ON) then shows 2 messages in it, one is the reply message (with a status of 0) and the other is the original message (sent by the initiator with status of 3)

Status = 0 indicates a message that was RECEIVEd from the queue (was already returned in a RECEIVE resultset). In your send script, are you trying to RECEIVE the reply as well? This RECEIVE will conflict with the activated procedure's RECEIVE.

HTH,
~ Remus

|||

Thanks for the reply.

The RECEIVE of the reply messsage takes place in a different stored procedure from the one that executes the SEND. It is however, of course, associated with the same service (as its Activation Procedure) that is used in the sending stored procedure's FROM SERVICE clause in the BEGIN DIALOG statement.

To my - uninitiated eye - it looks almost like a timing or threading issue. After all, all of this activity is taking place inside the same database: one stored proc sends a message (on one queue), another is activated to receive, it then sends a reply (on another queue) which causes another store procedure to be activated to consume the reply.

This of course, is a deliberately "simple" set up to facilitate learning of how Service Broker works. In a production environement there would be external components also.

The text of the message is:

The message body may not be NULL. A zero-length UNICODE or binary string is allowed

.... which from my reading is a designed behaviour in the case of a response (Target) message that is in fact empty.

My puzzle is that the appearance of this error seems intermittent. The only candidate cause for an empty messsage is a string concatenation operation on a variable that is populated from data RECEIVED. If this were to be NULL, etc, this would explain it.

However, the SEND/RECEIVE code does not change from trial to trial. Extra "PRINTs" are added, @.@.ROWCOUNT tests are replaced by "IF @.message_body IS NULL" (in the procedure that recieves the reply message) tests - but processing logic does not change at any of the endpoints.

|||

Just a shot in the dark, but are you RECEIVEing the message_body into a @.variable and then SENDing back a response of the same @.variable w/o actually checking the message_type_name received? My guess is that you are treating all message types the same way, incuding the EndDialog message, that is part of every dialog and has a NULL message body.

Posting the actual stored procs you have might help.

HTH,
~ Remus

|||

Remus,

thank you very much for your replies. It will probably turn out just to be "noise" born of ignorance, so I very mcuh appreciate your help.

When I show up at work on Monday I will do so

Cheers,

Roger

|||

Remus,

Here is the code that is running (sic!).

Please bear in mind that it is pretty much still in the experimental stage with no thought yet being given to optimisation or robustness, etc. - but simply to get a sound grasp of how things work.

Thanks

Roger

=====>>>

-- *********** Service Broker Objects
alter QUEUE dbo.SurveySetQueue WITH
STATUS = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ShredSurveySet,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo'
)
ALTER QUEUE dbo.ParseRequestsQueue
WITH
STATUS = ON,
RETENTION = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = UpdateSetParseState,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo'
)

CREATE MESSAGE TYPE SurveySetMessage VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE ParseOutcomeMessage VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT SurveySetContract (
SurveySetMessage SENT BY INITIATOR,
ParseOutcomeMessage SENT BY TARGET
);

CREATE SERVICE SurveySetService ON QUEUE SurveySetQueue (SurveySetContract);
GO

CREATE SERVICE ParseRequestsService ON QUEUE ParseRequestsQueue (SurveySetContract);
GO

-- ***********

-- ******** STORED PROCS

-- ************* Initial sending procedure:

ALTER PROCEDURE SendParseRequestMessage(@.SetId int)
AS
BEGIN
DECLARE @.message XML ;

BEGIN TRY
BEGIN TRANSACTION ;

UPDATE CustomerSetMap SET State = 1 WHERE SetId = @.SetId -- AND State = 0;

SET @.message = N'<SetId>' + CAST(@.SetId AS nvarchar(10)) + N'</SetId>';

-- Declare a variable to hold the conversation
-- handle.
DECLARE @.conversationHandle UNIQUEIDENTIFIER ;

-- Begin the dialog.
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE ParseRequestsService
TO SERVICE 'SurveySetService'
ON CONTRACT SurveySetContract
WITH ENCRYPTION = OFF;

-- Send the message on the dialog.
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE SurveySetMessage
(@.message) ;

COMMIT TRANSACTION ;

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
exec LogError
END CATCH

END

- Procedeure activates as the result of the initial SEND
ALTER PROCEDURE ShredSurveySet
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.message_body XML,
@.message_type_name NVARCHAR(256),
@.dialog UNIQUEIDENTIFIER,
@.ErrorMessage nvarchar(max);

DECLARE @.SetId int,
@.SurveyXml xml,
@.SurveyId uniqueidentifier,
@.conversation_group_id uniqueidentifier,
@.CursorDefined bit;

-- One at a time for development purposes
BEGIN TRY
BEGIN TRANSACTION ;

SET @.CursorDefined = 0;
SET @.SurveyXml = null;

-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@.message_type_name=message_type_name, --the type of message received
@.message_body=
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [dbo].SurveySetQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away

IF (@.@.ROWCOUNT = 0) --@.SurveyXml is null --(
BEGIN
print 'no rows'
print cast(@.dialog as nvarchar(50));

-- For development purposes do not try any
-- poison message handling due to consecutive rollbacks
COMMIT TRANSACTION ;
RETURN ;
END ;

-- Check to see if the message is an end dialog message.
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@.dialog as nvarchar(40)) ;
END CONVERSATION @.dialog ;
END ;

-- Error?
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
SET @.ErrorMessage = CAST(@.message_body as nvarchar(max));
RAISERROR( @.ErrorMessage, 1, 1);
RETURN;
END

-- Extract the data element information using XQuery.

SET @.SetId = @.message_body.value('(/SetId)[1]', 'int');

--select @.SetId;

-- ********* This is the DB work section

DECLARE Survey_Cursor Cursor LOCAL FOR SELECT top 5 SurveyId FROM [WSS Surveys] WHERE SetId = @.SetId ORDER BY Ordinal;
SET @.CursorDefined = 1;

OPEN Survey_Cursor;

FETCH NEXT FROM Survey_Cursor INTO @.SurveyId;

-- Shred XML into a table set
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC MakeXMLSurveyTempTables1 @.SurveyId;
FETCH NEXT FROM Survey_Cursor INTO @.SurveyId;
END

CLOSE Survey_Cursor;
DEALLOCATE Survey_Cursor;
SET @.CursorDefined = 0;
-- ******************

-- ******** The Reply
-- Send a message back to the originating service
-- indicating success
EXEC SendParseUpdateMessage @.dialog, @.SetId, 2;

COMMIT TRANSACTION ;

END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;

-- Send a message back to the originating service
-- indicating error
EXEC SendParseUpdateMessage @.dialog, @.SetId, 9;

-- Deaalocate cursor if necessary
IF @.CursorDefined = 1
DEALLOCATE Survey_Cursor;

-- Write to error table
EXEC LogError;
END CATCH;
--END;
END

-- Procedure that sends the reply
ALTER PROCEDURE dbo.SendParseUpdateMessage(@.conversationHandle UNIQUEIDENTIFIER, @.SetId int, @.State smallint)
AS
BEGIN
DECLARE @.message XML ;

BEGIN TRY

SET @.message = N'<UpdateState><SetId>' + CAST(@.SetId AS nvarchar(10)) + N'</SetId><State>' +
CAST(@.State AS nvarchar(4)) + N'</State></UpdateState>';

-- Send the message on the dialog.
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE ParseOutcomeMessage
(@.message) ;

-- print 'message sent' + cast(@.message as nvarchar(max));

END TRY
BEGIN CATCH

EXEC LogError
END CATCH

END

-- This is the Activation stored proc for processing the reply
ALTER PROCEDURE dbo.UpdateSetParseState
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.message_body XML,
@.message_type_name NVARCHAR(256),
@.dialog UNIQUEIDENTIFIER,
@.ErrorMessage nvarchar(max);

DECLARE @.SetId int,
@.State smallint;

-- One at a time for development purposes
BEGIN TRY
BEGIN TRANSACTION ;

SET @.message_body = null;

-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@.message_type_name=message_type_name, --the type of message received
@.message_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM ParseRequestsQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away

-- ***** So far always 0
print 'rowcount = ' + cast(@.@.ROWCOUNT as nvarchar(10));

-- *******************************************
-- *******************************************
--
-- THIS IS WHERE I AM PUZZLED
--
-- *******************************************
-- *******************************************

--IF (@.@.ROWCOUNT = 0)
IF @.message_body IS NULL
BEGIN

-- NO poison handling due to repeated rollbacks
COMMIT TRANSACTION;
Print ' no rows: parse update'
RETURN;

END ;

-- Check to see if the message is an end dialog message.

IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@.dialog as nvarchar(40)) ;
END CONVERSATION @.dialog;
RETURN
END

-- Error?
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
SET @.ErrorMessage = CAST(@.message_body as nvarchar(max));
RAISERROR( @.ErrorMessage, 1, 1);
RETURN;
END

-- *** Proceed ...

-- *** This will run successfully despite @.@.ROWCOUNT = 0

-- Extract the event information using XQuery.
SET @.SetId = @.message_body.value('(/UpdateState/SetId)[1]', 'int');
SET @.State = @.message_body.value('(/UpdateState/State)[1]', 'smallint');

UPDATE CustomerSetMap SET State = @.State WHERE SetId = @.SetId AND (State = 1 or State = 9);

-- END CONVERSATION @.dialog;

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;

EXEC LogError
END CATCH

END

|||

Hello Roger,

If the ShredSurveySet throws an exception which leaves the transaction commitable, the XACT_STATE() will be 1. Your CATCH block will not commit this transaction and as such the procedure will exit with an unbalanced BEGIN TRY/COMMIT pair, resulting in a nastygram in the ERRORLOG:

2006-11-06 14:14:32.69 spid52s The activated proc [dbo].[ShredSurveySet] running on queue testNG.dbo.SurveySetQueue output the following: 'Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.'

Do you have any such messages in your ERROLOG? BTW, an example of a situation that throw an exception that leaves the pending transaction context commitable is trying to SEND a NULL message body...

As about the sender's procedure never getting a @.@.ROWCOUNT, it seems to me you're never activated because of a incomming message. There are cases when activation will start the procedure even though there are no messages to receive and it seems your hitting such case.

My recommendation would be to test the procedures first manually, w/o activation. Send a message, then run the target's procedure by calling manualy EXEC, verify the response was sent (SELLECT ... FROM ParseRequestsQueue) and then run the sender's procedure manually. Only after you're satisfied with this results turn on activation.

HTH,
~ Remus

|||

I hit exactly this issue because I expected the activation procedure only to be activated if there were messages to consume. What I found that was that activation occurs when the message is received, my sp processed the message on the queue and completed. Activation the fire up another SP almost as a catch all, I wasn't verifying that a message was actually received from the queue so my logic fired twice.

So the morale of the story is, always check the @.@.rowcount after receiving a message

Confusion over @@ROWCOUNT test

I have a pair of services.

One is an initiator of a conversation, another is a target. I have a pair of message types, one sent by the initiator one sent by the target.

I have a pair of queues corresponding to the above services. Both queues have activation stored procedures associate with them.

The intiiator sends its mesage to the queue that the target service is defined on.

The activation procedure successfully retrieves the message, does some work and then sends a reply message on the same conversation to the initiating service.

At this point behavior seems to become strange.

Some of the time the send from the target service produces the "zero length message" error and indeed at times the message body is empty - while at others (no code changes just another trial send) it is not.

All of the time the RECEIVE on the Initiator's queue executed by the Activated Procedure (in response to the target sending a reply) gives a @.@.ROWCOUNT = 0 (i.e. even when there are no errors generated)

However in this case, the message_body field contains the (target sent, reply) message

The Target RECEIVE and its SEND reply are both executed within the same transaction and the SEND is performed by a stored procedure call passing the conversation handle (inter alia) as a parameter.

The Initiator's queue (with RETENTION = ON) then shows 2 messages in it, one is the reply message (with a status of 0) and the other is the original message (sent by the initiator with status of 3)

All services and queues and Activation procedures are in the same database. Dialog encryption is off.

The intial SEND is done by a simple driver stored procedure in the same database

The security context of the Activation procedures for the 2 queues is - as far as I can tell - the same.

Objects were all created in the dbo security context.

The "work" involves updates to tables in the local database.

No doubt, I have done something stupid somewhere and am incapable of recognising it, but (apart from the work performed by the target procedure) what I have done appears to be no more than the above, using Service Broker SQL copied from examples, with my identifiers substituted for queue names, etc.

Any thoughts anyone?

rrkk wrote:

Some of the time the send from the target service produces the "zero length message" error and indeed at times the message body is empty - while at others (no code changes just another trial send) it is not.

I'm not familiar with the "zero length message" error. Can you post the actual SQL error message?

rrkk wrote:

The Initiator's queue (with RETENTION = ON) then shows 2 messages in it, one is the reply message (with a status of 0) and the other is the original message (sent by the initiator with status of 3)

Status = 0 indicates a message that was RECEIVEd from the queue (was already returned in a RECEIVE resultset). In your send script, are you trying to RECEIVE the reply as well? This RECEIVE will conflict with the activated procedure's RECEIVE.

HTH,
~ Remus

|||

Thanks for the reply.

The RECEIVE of the reply messsage takes place in a different stored procedure from the one that executes the SEND. It is however, of course, associated with the same service (as its Activation Procedure) that is used in the sending stored procedure's FROM SERVICE clause in the BEGIN DIALOG statement.

To my - uninitiated eye - it looks almost like a timing or threading issue. After all, all of this activity is taking place inside the same database: one stored proc sends a message (on one queue), another is activated to receive, it then sends a reply (on another queue) which causes another store procedure to be activated to consume the reply.

This of course, is a deliberately "simple" set up to facilitate learning of how Service Broker works. In a production environement there would be external components also.

The text of the message is:

The message body may not be NULL. A zero-length UNICODE or binary string is allowed

.... which from my reading is a designed behaviour in the case of a response (Target) message that is in fact empty.

My puzzle is that the appearance of this error seems intermittent. The only candidate cause for an empty messsage is a string concatenation operation on a variable that is populated from data RECEIVED. If this were to be NULL, etc, this would explain it.

However, the SEND/RECEIVE code does not change from trial to trial. Extra "PRINTs" are added, @.@.ROWCOUNT tests are replaced by "IF @.message_body IS NULL" (in the procedure that recieves the reply message) tests - but processing logic does not change at any of the endpoints.

|||

Just a shot in the dark, but are you RECEIVEing the message_body into a @.variable and then SENDing back a response of the same @.variable w/o actually checking the message_type_name received? My guess is that you are treating all message types the same way, incuding the EndDialog message, that is part of every dialog and has a NULL message body.

Posting the actual stored procs you have might help.

HTH,
~ Remus

|||

Remus,

thank you very much for your replies. It will probably turn out just to be "noise" born of ignorance, so I very mcuh appreciate your help.

When I show up at work on Monday I will do so

Cheers,

Roger

|||

Remus,

Here is the code that is running (sic!).

Please bear in mind that it is pretty much still in the experimental stage with no thought yet being given to optimisation or robustness, etc. - but simply to get a sound grasp of how things work.

Thanks

Roger

=====>>>

-- *********** Service Broker Objects
alter QUEUE dbo.SurveySetQueue WITH
STATUS = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ShredSurveySet,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo'
)
ALTER QUEUE dbo.ParseRequestsQueue
WITH
STATUS = ON,
RETENTION = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = UpdateSetParseState,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo'
)

CREATE MESSAGE TYPE SurveySetMessage VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE ParseOutcomeMessage VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT SurveySetContract (
SurveySetMessage SENT BY INITIATOR,
ParseOutcomeMessage SENT BY TARGET
);

CREATE SERVICE SurveySetService ON QUEUE SurveySetQueue (SurveySetContract);
GO

CREATE SERVICE ParseRequestsService ON QUEUE ParseRequestsQueue (SurveySetContract);
GO

-- ***********

-- ******** STORED PROCS

-- ************* Initial sending procedure:

ALTER PROCEDURE SendParseRequestMessage(@.SetId int)
AS
BEGIN
DECLARE @.message XML ;

BEGIN TRY
BEGIN TRANSACTION ;

UPDATE CustomerSetMap SET State = 1 WHERE SetId = @.SetId -- AND State = 0;

SET @.message = N'<SetId>' + CAST(@.SetId AS nvarchar(10)) + N'</SetId>';

-- Declare a variable to hold the conversation
-- handle.
DECLARE @.conversationHandle UNIQUEIDENTIFIER ;

-- Begin the dialog.
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE ParseRequestsService
TO SERVICE 'SurveySetService'
ON CONTRACT SurveySetContract
WITH ENCRYPTION = OFF;

-- Send the message on the dialog.
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE SurveySetMessage
(@.message) ;

COMMIT TRANSACTION ;

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
exec LogError
END CATCH

END

- Procedeure activates as the result of the initial SEND
ALTER PROCEDURE ShredSurveySet
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.message_body XML,
@.message_type_name NVARCHAR(256),
@.dialog UNIQUEIDENTIFIER,
@.ErrorMessage nvarchar(max);

DECLARE @.SetId int,
@.SurveyXml xml,
@.SurveyId uniqueidentifier,
@.conversation_group_id uniqueidentifier,
@.CursorDefined bit;

-- One at a time for development purposes
BEGIN TRY
BEGIN TRANSACTION ;

SET @.CursorDefined = 0;
SET @.SurveyXml = null;

-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@.message_type_name=message_type_name, --the type of message received
@.message_body=
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [dbo].SurveySetQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away

IF (@.@.ROWCOUNT = 0) --@.SurveyXml is null --(
BEGIN
print 'no rows'
print cast(@.dialog as nvarchar(50));

-- For development purposes do not try any
-- poison message handling due to consecutive rollbacks
COMMIT TRANSACTION ;
RETURN ;
END ;

-- Check to see if the message is an end dialog message.
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@.dialog as nvarchar(40)) ;
END CONVERSATION @.dialog ;
END ;

-- Error?
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
SET @.ErrorMessage = CAST(@.message_body as nvarchar(max));
RAISERROR( @.ErrorMessage, 1, 1);
RETURN;
END

-- Extract the data element information using XQuery.

SET @.SetId = @.message_body.value('(/SetId)[1]', 'int');

--select @.SetId;

-- ********* This is the DB work section

DECLARE Survey_Cursor Cursor LOCAL FOR SELECT top 5 SurveyId FROM [WSS Surveys] WHERE SetId = @.SetId ORDER BY Ordinal;
SET @.CursorDefined = 1;

OPEN Survey_Cursor;

FETCH NEXT FROM Survey_Cursor INTO @.SurveyId;

-- Shred XML into a table set
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC MakeXMLSurveyTempTables1 @.SurveyId;
FETCH NEXT FROM Survey_Cursor INTO @.SurveyId;
END

CLOSE Survey_Cursor;
DEALLOCATE Survey_Cursor;
SET @.CursorDefined = 0;
-- ******************

-- ******** The Reply
-- Send a message back to the originating service
-- indicating success
EXEC SendParseUpdateMessage @.dialog, @.SetId, 2;

COMMIT TRANSACTION ;

END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;

-- Send a message back to the originating service
-- indicating error
EXEC SendParseUpdateMessage @.dialog, @.SetId, 9;

-- Deaalocate cursor if necessary
IF @.CursorDefined = 1
DEALLOCATE Survey_Cursor;

-- Write to error table
EXEC LogError;
END CATCH;
--END;
END

-- Procedure that sends the reply
ALTER PROCEDURE dbo.SendParseUpdateMessage(@.conversationHandle UNIQUEIDENTIFIER, @.SetId int, @.State smallint)
AS
BEGIN
DECLARE @.message XML ;

BEGIN TRY

SET @.message = N'<UpdateState><SetId>' + CAST(@.SetId AS nvarchar(10)) + N'</SetId><State>' +
CAST(@.State AS nvarchar(4)) + N'</State></UpdateState>';

-- Send the message on the dialog.
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE ParseOutcomeMessage
(@.message) ;

-- print 'message sent' + cast(@.message as nvarchar(max));

END TRY
BEGIN CATCH

EXEC LogError
END CATCH

END

-- This is the Activation stored proc for processing the reply
ALTER PROCEDURE dbo.UpdateSetParseState
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.message_body XML,
@.message_type_name NVARCHAR(256),
@.dialog UNIQUEIDENTIFIER,
@.ErrorMessage nvarchar(max);

DECLARE @.SetId int,
@.State smallint;

-- One at a time for development purposes
BEGIN TRY
BEGIN TRANSACTION ;

SET @.message_body = null;

-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@.message_type_name=message_type_name, --the type of message received
@.message_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM ParseRequestsQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away

-- ***** So far always 0
print 'rowcount = ' + cast(@.@.ROWCOUNT as nvarchar(10));

-- *******************************************
-- *******************************************
--
-- THIS IS WHERE I AM PUZZLED
--
-- *******************************************
-- *******************************************

--IF (@.@.ROWCOUNT = 0)
IF @.message_body IS NULL
BEGIN

-- NO poison handling due to repeated rollbacks
COMMIT TRANSACTION;
Print ' no rows: parse update'
RETURN;

END ;

-- Check to see if the message is an end dialog message.

IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@.dialog as nvarchar(40)) ;
END CONVERSATION @.dialog;
RETURN
END

-- Error?
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
SET @.ErrorMessage = CAST(@.message_body as nvarchar(max));
RAISERROR( @.ErrorMessage, 1, 1);
RETURN;
END

-- *** Proceed ...

-- *** This will run successfully despite @.@.ROWCOUNT = 0

-- Extract the event information using XQuery.
SET @.SetId = @.message_body.value('(/UpdateState/SetId)[1]', 'int');
SET @.State = @.message_body.value('(/UpdateState/State)[1]', 'smallint');

UPDATE CustomerSetMap SET State = @.State WHERE SetId = @.SetId AND (State = 1 or State = 9);

-- END CONVERSATION @.dialog;

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;

EXEC LogError
END CATCH

END

|||

Hello Roger,

If the ShredSurveySet throws an exception which leaves the transaction commitable, the XACT_STATE() will be 1. Your CATCH block will not commit this transaction and as such the procedure will exit with an unbalanced BEGIN TRY/COMMIT pair, resulting in a nastygram in the ERRORLOG:

2006-11-06 14:14:32.69 spid52s The activated proc [dbo].[ShredSurveySet] running on queue testNG.dbo.SurveySetQueue output the following: 'Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.'

Do you have any such messages in your ERROLOG? BTW, an example of a situation that throw an exception that leaves the pending transaction context commitable is trying to SEND a NULL message body...

As about the sender's procedure never getting a @.@.ROWCOUNT, it seems to me you're never activated because of a incomming message. There are cases when activation will start the procedure even though there are no messages to receive and it seems your hitting such case.

My recommendation would be to test the procedures first manually, w/o activation. Send a message, then run the target's procedure by calling manualy EXEC, verify the response was sent (SELLECT ... FROM ParseRequestsQueue) and then run the sender's procedure manually. Only after you're satisfied with this results turn on activation.

HTH,
~ Remus

|||

I hit exactly this issue because I expected the activation procedure only to be activated if there were messages to consume. What I found that was that activation occurs when the message is received, my sp processed the message on the queue and completed. Activation the fire up another SP almost as a catch all, I wasn't verifying that a message was actually received from the queue so my logic fired twice.

So the morale of the story is, always check the @.@.rowcount after receiving a message

Confusion over "ANY" keyword

I am studying for the MSCE/MCDBA exam 70-229. In the book I am using
("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000
Database Design and Implementation Exam 70-229, Second Edition") I am
looking at the section on the ANY/ALL keyword.

<QUOTE>
USE Pubs
SELECT Title
FROM Titles
WHERE Advance > ANY
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
AND Pub_name = 'Algodata Infosystems')

This statement finds the titles that received an advance larger than
the minimum advance amount paid by Algodata Infosystems (which, in this
case, is $5,000). The WHERE clause in the outer SELECT statement
contains a subquery that uses a join to retrieve advance amounts for
Algodata Infosystems. The minimum advance
amount is then used to determine which titles to retrieve from the
Titles table.
</QUOTE
I don't understand why this references the "minimum advance". If you
run the subquery on its own, it returns the following values:

5000.0000
5000.0000
5000.0000
7000.0000
8000.0000
NULL

>From my limited understanding, the "ANY" keyword applies to at least
one value, but which one? How is this determined?

Any help gratefully received.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukThe value to the left of the comparison operator (Advance) is compared
to each of the values returned by the subquery. If it matches at least
one of those values then the result is True. Think of it as a series of
comparions linked by OR. For example your query could be expanded to
the following, which is logically equivalent:

SELECT Title
FROM Titles
WHERE
(advance > 5000
OR advance > 5000
OR advance > 5000
OR advance > 7000
OR advance > 8000
OR advance > NULL)

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> The value to the left of the comparison operator (Advance) is
compared
> to each of the values returned by the subquery. If it matches at
least
> one of those values then the result is True. Think of it as a series
of
> comparions linked by OR. For example your query could be expanded to
> the following, which is logically equivalent:
> SELECT Title
> FROM Titles
> WHERE
> (advance > 5000
> OR advance > 5000
> OR advance > 5000
> OR advance > 7000
> OR advance > 8000
> OR advance > NULL)

Thanks, David, that's extremely helpful. Am I right in thinking that
if I substitute the "ALL" keyword for the "ANY" keyword in the original
query, the expansion as above would be:

SELECT Title
FROM Titles
WHERE
(advance > 5000
AND advance > 5000
AND advance > 5000
AND advance > 7000
AND advance > 8000
AND advance > NULL)

If so, I'm back with the programme.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk|||That's correct. I should also say that there is a subtle catch that if
the subquery is empty then ALL always returns True whereas the ANY
returns False. Try:

SELECT Title
FROM Titles
WHERE Advance > ANY
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
AND 1=0)

SELECT Title
FROM Titles
WHERE Advance > ALL
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
AND 1=0)

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> That's correct. I should also say that there is a subtle catch that
if
> the subquery is empty then ALL always returns True whereas the ANY
> returns False. Try:
> SELECT Title
> FROM Titles
> WHERE Advance > ANY
> (
> SELECT Advance
> FROM Publishers INNER JOIN Titles
> ON Titles.Pub_id = Publishers.Pub_id
> AND 1=0)
> SELECT Title
> FROM Titles
> WHERE Advance > ALL
> (
> SELECT Advance
> FROM Publishers INNER JOIN Titles
> ON Titles.Pub_id = Publishers.Pub_id
> AND 1=0)

Interesting, but I've been using SQL without either ANY or ALL for
about ten years, so I guess I'll carry on without. However, the book
I'm studying has alerted me to CUBE and ROLLUP which I can see some
serious uses for.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

Confusion on Time Intelligence

Hi, all experts here,

Thank you for your kind attention.

Just really confused about Time Intelligence. Would please any experts here shed me any light on Time Intelligence? What are the main concern or benefits of creating Time Intelligence? Any disavantages of using it?

Thank you very much in advance for your kind advices and help for that and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello Helen!

Here is a link to Moshas Blog about Time Intelligence: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

The are also additional links to more information in that Blog entry.

And Chris Webb have written a few posts on this subject here: http://cwebbbi.spaces.live.com/

HTH

Thomas Ivarsson

Confusion on Cube Design

Hello everyone. I'm having a bit of trouble getting my head around designing a new cube and I'm hoping this forum can help. I have a database which contains the following simplified structure:

tbl_Panelist:

panelist_id

tbl_Question:

question_id

tbl_Answer:

answer_id

tbl_Result_Set:

panelist_id

question_id

answer_id

I'm trying to design a cube which will allow analyzing of the counts of how many panelists answered each question by each answer. For example if Q1 has possible answers of A,B,C and Q2 has possible answers of X,Y, I'd like to be able to browse the cube and see

X Y

A 10 5

B 3, 2

indicating that 10 people answered Q1 with A and Q2 with X. My current thinking has been to create a view for each question and create a dimension off of that. Is this the correct method in a case like this? Any help would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Hello Alex,

I was able to create an AS solution for your case simply by using the cube wizard. Basically you create 3 dimensions - Panelists, Questions, Answers bound to the respective tables and tell the cube wizard that your fact table is ResultSet, while including the existing dimensions. The resulting cube will provide additional analysis by Panelists, if needed.

If you write me at this address: andrewgaATnetzeroDOTcom i will send you a zip file with the AS solution, which you can deploy onto your server.

Andrew

|||

Thank you for responding. That is exactly what I attempted initially. However, I could not have different Questions act as different dimensions through that technique. I should say that at this point, due to the views, I am not having problems creating the dimensions. My current problem lies with attempting to create measures that get "hit" by every dimension.

To make this even more complicated, a single person can answer some of the questions with multiple answers. Thus, I need to have two different measures, one for a raw count of the answers, and the other with a distinct count of each panelist's answers.

Finally, I should also mention that I am using SQL Server 2005 and SSAS 2005 to do this project. Thanks again for any help that could be provided.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

> distinct count of each panelist's answers.

Could you please clarify? When you browse this measure and panelists dimension, do you want to see how many *questions* each panelist answered with at least one answer? Also, do you need it as a measure, which should also behave nice when user browses not panelists dimension, or all you need is some MDX query, which would fetch this information (calc measure within one MDX query)?

I am asking because in my test i created a Count measure bound to the row of the fact table. When i browse the panelists dimension and that measure i do get the numbers of answers provided by each panelist. I suppose you gave it "raw answers" name.

Andrew

|||

I'm not exactly sure of the correct terminology here, so perhaps a continuation of my first example will explain what I'm looking for. Using the same table structure mentioned previously, suppose three panelists, p1, p2, and p3. We'll also suppose two questions, q1 and q2. Finally, q1 has possible answers A-E and q2 has possible answers W-Z. Here are the responses of each:

P1 A, C, D, W, Y, Z

P2 A, B, W, Z

P3 A, G, X, Z

Using this data, I would like to be able to build a pivottable where I can have both Q1 and Q2 as seperate dimensions. Keep in mind, in my actual project, there are closer to 40 questions, rather than just 2. For now I'd just like to focus on one measure as there are other factors involved in how the questions are answered. I would expect the results of this pivottable to looks something like:

W X Y Z

A 2 1 1 3

B 1 0 0 1

C 1 0 1 1

D 1 0 1 1

E 0 0 0 0

The trouble I seem to be running into is that when I try and examine dimesions with a granularity based on the answerID, I am getting no "hits". When there is a one to one relationship between the answer and the panelist, I am able to have a dimension with a granularity of the panelistID. In this case I get the results exactly as expected. I can even examine a granularity of panelist against a granularity of answer and get the result I expect. It's the answer to answer where I am currently having problems. Thanks again for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Just to move this along a bit more, the crux of the problem that I am having is that for questions (dimensions) that allow multiple answers by the same person I cannot browse those two dimensions in the pivottable. I have now changed their granularity to be a collection of the answerID and the panelistID. While this seems to be a step closer, I still cannot get "hits" off of them. If this were a SQL query, I would just do an intersect on the results of selects of the panelist per question query. Once again, any help would be greatly appreciated. I'd be happy to further clarify what I've done if any of this is unclear. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

I have just sent you my version of the solution but that one was based on the first post.

Your second post needs clarifications. In your matrix of the results, which you would like to obtain, let's discuss the left-top cell.

W

A 2

From your description, A and W are the instances of answers. Basically you have put the same dimension "Answers" on columns and rows axes. This can't be.

Anyway, how the value of 2 was computed? From your description the only correlation i can find is "the number of panelists, who gave both answers".

|||

Hello Andrew. Thank you for taking the time to help me. I will attempt to clear this up a bit. A and W are exactly what I need to create a pivottable on as they represent the results of two different questions. I have attempted to overcome this through the use of views containing the results of each question. So, to continue with the example above, I have in my database and data source view:

View_Q1 - panelist_ID, question_ID, answer_ID where question_ID = Q1

View_Q2 - panelist_ID, question_ID, answer_ID where question_ID = Q2

Keep in mind I'm simplifying this as of course you'd want the friendly names and some other information in addition to the ID's for actually building the dimensions. I have then created a dimension from each view table and used my master results table as the Measures table.

This all works fine when comparing questions with a 1-1 (one to one) panelist to answer relationship and even a single 1-M (one to many) question compared to a 1-1 table. My problem comes from comparing two 1-M questions. I've set the granularity in these cases to be the collection of answer and panelist. Perhaps using views is completely the wrong approach, but it certainly seems like I'm pretty close. The example above is of two 1-M questions.

Finally, to answer your second question, 2 is the count of panelists who answered both A and W. In this example that would actually be panelists P1 and P2. I'm only concerned with the count of panelists, and not with actually identifiying the panelists in any way. Does that explain what I am trying to accomplish or is there still something else that I can clear up? Once again, thank you for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

It might take some time till i will be able to think how to resolve your problem by means of OLAP, but having read your goal i wanted to ask some questions if you were really determined to solve it with OLAP.

The model of the cube should be good enough to resolve not just one report. Although it is also OK to build a cube for the sake of one report, it does not seem people do that.

In your case, what would be the value of your measure for the tuple (AnswersDim1.All, AnswersDim2.All)? Is it the number of panelists answered all questions?

What would be the values for the tuple (AnswersDim1.A, AnswersDim2.All) and (AnswersDim1.All, AnswersDim2.A)?

Suppose you make both dimensions non-aggregatable. You would not need to think about the 2 questions above, but would need to think about the default members for the 2 dimensions. Are you planning to run MDX queries like:

select [measures].yourmeasure on 0,

AnswersDim1.members on 1

from Cube

Notice, that this query would need to fetch tuples like (AnswersDim1.currentmember, AnswersDim2.defaultmember).

Being not advanced in OLAP modeling personally, if my goal was to produce just that matrix report you described, i would have been done in one day by writing a C++ or C# program, which would open forward-only row-set for a view joining your tables and building in-memory structures based on STL (C++) or Collections.Generics (C#), calculating your goal, writing the result as a SQL table and binding the report to that already calculated table.

Of course, i would think more if the number of panelists were huge. I suppose the number of questions and possible answers is not big, because making the report the way you described would make it unusable.

Andrew

|||

Unfortuantely, I fear that I must use OLAP for this project. The business requirements are that analysists must be able to view the responses to any combination of questions in a pivottable in Excel. To give you an idea of exactly how much data we're talking about here, there are currently about 60 questions with about 2-15 answers each. Currently there are a few thousand panelists, but this is expected to eventually reach into the hundreds of thousands. The database structure was designed to allow for this expected increase in data and for the easy ability to change questions and answers as time goes on.

You are correct in noticing that my aggregations are fairly meaningless in this project. There are some questions which have groupings of answers, but for the most part, each question only has one level in its heirarchy. Perhaps I can turn off aggregations in these cases if that will help.

To answer your questions specifically, Q1Dim.All,Q2Dim.All (I assume that's what you meant) would be 15 which represents the total number of responses to Q1 + the total number of responses to Q2. Had these both been 1-1 questions, that total would represent the union of panelists who answered each of the questions. Q1Dim.A, Q2Dim.All would be7 and Q2Dim.A, Q1Dim.All would be 5.

As I said at the outset, this is my first project using Analysis Server so it would not be surprising if I am going about this the wrong way or if I have missed some fundamental underlying concept. I was not expecting to have to write any MDX queries in this phase. What role does the defaultmember property play here?

Finally, as best as I can tell the crux of my problem may rest on being able to create a measure which will find results within a dimension using the collection of answerid and panelistid but find results between dimensions just using the panelistid. Does that sound correct to you based on your understanding of what I am trying to accomplish? Again, thanks for your time and interest on my behalf.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

I think I've moved forward a bit on this problem. As I'm not an expert on MDX, I've written this in SQL code. Hopefully someone out there can help me write an MDX based measure which will be the equivalent:

with a1 as (select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 40 intersect select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 491)

select count(distpanelistid) from a1

In this case I'm specifically getting the count for answers 40 and 491. I need the MDX to be of whatever answers I am currently browsing in the pivottable. Any MDX experts out there know this one?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Yet another person raised similar task (topic: Need help designing the Cube). I did it in MSAccess, while i see that you made it with SQL. I also provided some thoughts why i failed to do it with OLAP.|||

Ok, after much effort and with some amazing help from Matt Burr with the Analysis Server group at Microsoft, I have a solution to this problem. I am going to quote from the wrap up email provided by Matt. If anyone has any questions or would like a copy of the sample cube and db which was created for this, I would be happy to provide them. Thanks to Andrew Garbuzov for his help on this issue as well. From Matt Burr:

Resolution

In the end, the solution involved creating views against your source “fact” table to represent individual query dimensions. Then, we created the different query dimensions from these views. We also created a Panelist dimension that served two purposes: (1) it contains the data that we actually will count and (2) it serves to help join together the various question dimensions so that navigating one dimension effectively navigates the other dimensions.

Next, we created measures based on each of the question dimensions; these simply counted the unique number of participants that provided any given answer to a question, but their primary value is that they serve as a sort of “hook” that we can use to relate the various question dimensions back to the Panelist dimension, and from there out to the other question dimensions.

We then used the Dimension Usage tab to ensure that the Panelist dimension related to the measures that we had created from the question dimensions, and thus related to the question dimensions (so that navigating or “filtering” a given question dimension subsequently navigated/filtered the Panelist dimension), and we also created many-to-many relationships from each of the question dimensions to each of the other question dimensions (their relationship with the Panelist dimension facilitated this), so that navigating/filtering any one of the question dimensions consequently navigated/filtered all of the other question dimensions, based on a shared set of panelists that provided certain answers to both of the questions.

Finally, we created a calculated member that counts the distinct number of participants that exist in the Panelist dimension, which will have been navigated/filtered by your choice of question dimensions. This calculated member tells you what you wished to know: the number of panelists that answered x for one question AND y for another.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Confusion on Cube Design

Hello everyone. I'm having a bit of trouble getting my head around designing a new cube and I'm hoping this forum can help. I have a database which contains the following simplified structure:

tbl_Panelist:

panelist_id

tbl_Question:

question_id

tbl_Answer:

answer_id

tbl_Result_Set:

panelist_id

question_id

answer_id

I'm trying to design a cube which will allow analyzing of the counts of how many panelists answered each question by each answer. For example if Q1 has possible answers of A,B,C and Q2 has possible answers of X,Y, I'd like to be able to browse the cube and see

X Y

A 10 5

B 3, 2

indicating that 10 people answered Q1 with A and Q2 with X. My current thinking has been to create a view for each question and create a dimension off of that. Is this the correct method in a case like this? Any help would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Hello Alex,

I was able to create an AS solution for your case simply by using the cube wizard. Basically you create 3 dimensions - Panelists, Questions, Answers bound to the respective tables and tell the cube wizard that your fact table is ResultSet, while including the existing dimensions. The resulting cube will provide additional analysis by Panelists, if needed.

If you write me at this address: andrewgaATnetzeroDOTcom i will send you a zip file with the AS solution, which you can deploy onto your server.

Andrew

|||

Thank you for responding. That is exactly what I attempted initially. However, I could not have different Questions act as different dimensions through that technique. I should say that at this point, due to the views, I am not having problems creating the dimensions. My current problem lies with attempting to create measures that get "hit" by every dimension.

To make this even more complicated, a single person can answer some of the questions with multiple answers. Thus, I need to have two different measures, one for a raw count of the answers, and the other with a distinct count of each panelist's answers.

Finally, I should also mention that I am using SQL Server 2005 and SSAS 2005 to do this project. Thanks again for any help that could be provided.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

> distinct count of each panelist's answers.

Could you please clarify? When you browse this measure and panelists dimension, do you want to see how many *questions* each panelist answered with at least one answer? Also, do you need it as a measure, which should also behave nice when user browses not panelists dimension, or all you need is some MDX query, which would fetch this information (calc measure within one MDX query)?

I am asking because in my test i created a Count measure bound to the row of the fact table. When i browse the panelists dimension and that measure i do get the numbers of answers provided by each panelist. I suppose you gave it "raw answers" name.

Andrew

|||

I'm not exactly sure of the correct terminology here, so perhaps a continuation of my first example will explain what I'm looking for. Using the same table structure mentioned previously, suppose three panelists, p1, p2, and p3. We'll also suppose two questions, q1 and q2. Finally, q1 has possible answers A-E and q2 has possible answers W-Z. Here are the responses of each:

P1 A, C, D, W, Y, Z

P2 A, B, W, Z

P3 A, G, X, Z

Using this data, I would like to be able to build a pivottable where I can have both Q1 and Q2 as seperate dimensions. Keep in mind, in my actual project, there are closer to 40 questions, rather than just 2. For now I'd just like to focus on one measure as there are other factors involved in how the questions are answered. I would expect the results of this pivottable to looks something like:

W X Y Z

A 2 1 1 3

B 1 0 0 1

C 1 0 1 1

D 1 0 1 1

E 0 0 0 0

The trouble I seem to be running into is that when I try and examine dimesions with a granularity based on the answerID, I am getting no "hits". When there is a one to one relationship between the answer and the panelist, I am able to have a dimension with a granularity of the panelistID. In this case I get the results exactly as expected. I can even examine a granularity of panelist against a granularity of answer and get the result I expect. It's the answer to answer where I am currently having problems. Thanks again for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Just to move this along a bit more, the crux of the problem that I am having is that for questions (dimensions) that allow multiple answers by the same person I cannot browse those two dimensions in the pivottable. I have now changed their granularity to be a collection of the answerID and the panelistID. While this seems to be a step closer, I still cannot get "hits" off of them. If this were a SQL query, I would just do an intersect on the results of selects of the panelist per question query. Once again, any help would be greatly appreciated. I'd be happy to further clarify what I've done if any of this is unclear. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

I have just sent you my version of the solution but that one was based on the first post.

Your second post needs clarifications. In your matrix of the results, which you would like to obtain, let's discuss the left-top cell.

W

A 2

From your description, A and W are the instances of answers. Basically you have put the same dimension "Answers" on columns and rows axes. This can't be.

Anyway, how the value of 2 was computed? From your description the only correlation i can find is "the number of panelists, who gave both answers".

|||

Hello Andrew. Thank you for taking the time to help me. I will attempt to clear this up a bit. A and W are exactly what I need to create a pivottable on as they represent the results of two different questions. I have attempted to overcome this through the use of views containing the results of each question. So, to continue with the example above, I have in my database and data source view:

View_Q1 - panelist_ID, question_ID, answer_ID where question_ID = Q1

View_Q2 - panelist_ID, question_ID, answer_ID where question_ID = Q2

Keep in mind I'm simplifying this as of course you'd want the friendly names and some other information in addition to the ID's for actually building the dimensions. I have then created a dimension from each view table and used my master results table as the Measures table.

This all works fine when comparing questions with a 1-1 (one to one) panelist to answer relationship and even a single 1-M (one to many) question compared to a 1-1 table. My problem comes from comparing two 1-M questions. I've set the granularity in these cases to be the collection of answer and panelist. Perhaps using views is completely the wrong approach, but it certainly seems like I'm pretty close. The example above is of two 1-M questions.

Finally, to answer your second question, 2 is the count of panelists who answered both A and W. In this example that would actually be panelists P1 and P2. I'm only concerned with the count of panelists, and not with actually identifiying the panelists in any way. Does that explain what I am trying to accomplish or is there still something else that I can clear up? Once again, thank you for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

It might take some time till i will be able to think how to resolve your problem by means of OLAP, but having read your goal i wanted to ask some questions if you were really determined to solve it with OLAP.

The model of the cube should be good enough to resolve not just one report. Although it is also OK to build a cube for the sake of one report, it does not seem people do that.

In your case, what would be the value of your measure for the tuple (AnswersDim1.All, AnswersDim2.All)? Is it the number of panelists answered all questions?

What would be the values for the tuple (AnswersDim1.A, AnswersDim2.All) and (AnswersDim1.All, AnswersDim2.A)?

Suppose you make both dimensions non-aggregatable. You would not need to think about the 2 questions above, but would need to think about the default members for the 2 dimensions. Are you planning to run MDX queries like:

select [measures].yourmeasure on 0,

AnswersDim1.members on 1

from Cube

Notice, that this query would need to fetch tuples like (AnswersDim1.currentmember, AnswersDim2.defaultmember).

Being not advanced in OLAP modeling personally, if my goal was to produce just that matrix report you described, i would have been done in one day by writing a C++ or C# program, which would open forward-only row-set for a view joining your tables and building in-memory structures based on STL (C++) or Collections.Generics (C#), calculating your goal, writing the result as a SQL table and binding the report to that already calculated table.

Of course, i would think more if the number of panelists were huge. I suppose the number of questions and possible answers is not big, because making the report the way you described would make it unusable.

Andrew

|||

Unfortuantely, I fear that I must use OLAP for this project. The business requirements are that analysists must be able to view the responses to any combination of questions in a pivottable in Excel. To give you an idea of exactly how much data we're talking about here, there are currently about 60 questions with about 2-15 answers each. Currently there are a few thousand panelists, but this is expected to eventually reach into the hundreds of thousands. The database structure was designed to allow for this expected increase in data and for the easy ability to change questions and answers as time goes on.

You are correct in noticing that my aggregations are fairly meaningless in this project. There are some questions which have groupings of answers, but for the most part, each question only has one level in its heirarchy. Perhaps I can turn off aggregations in these cases if that will help.

To answer your questions specifically, Q1Dim.All,Q2Dim.All (I assume that's what you meant) would be 15 which represents the total number of responses to Q1 + the total number of responses to Q2. Had these both been 1-1 questions, that total would represent the union of panelists who answered each of the questions. Q1Dim.A, Q2Dim.All would be7 and Q2Dim.A, Q1Dim.All would be 5.

As I said at the outset, this is my first project using Analysis Server so it would not be surprising if I am going about this the wrong way or if I have missed some fundamental underlying concept. I was not expecting to have to write any MDX queries in this phase. What role does the defaultmember property play here?

Finally, as best as I can tell the crux of my problem may rest on being able to create a measure which will find results within a dimension using the collection of answerid and panelistid but find results between dimensions just using the panelistid. Does that sound correct to you based on your understanding of what I am trying to accomplish? Again, thanks for your time and interest on my behalf.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

I think I've moved forward a bit on this problem. As I'm not an expert on MDX, I've written this in SQL code. Hopefully someone out there can help me write an MDX based measure which will be the equivalent:

with a1 as (select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 40 intersect select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 491)

select count(distpanelistid) from a1

In this case I'm specifically getting the count for answers 40 and 491. I need the MDX to be of whatever answers I am currently browsing in the pivottable. Any MDX experts out there know this one?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Yet another person raised similar task (topic: Need help designing the Cube). I did it in MSAccess, while i see that you made it with SQL. I also provided some thoughts why i failed to do it with OLAP.|||

Ok, after much effort and with some amazing help from Matt Burr with the Analysis Server group at Microsoft, I have a solution to this problem. I am going to quote from the wrap up email provided by Matt. If anyone has any questions or would like a copy of the sample cube and db which was created for this, I would be happy to provide them. Thanks to Andrew Garbuzov for his help on this issue as well. From Matt Burr:

Resolution

In the end, the solution involved creating views against your source “fact” table to represent individual query dimensions. Then, we created the different query dimensions from these views. We also created a Panelist dimension that served two purposes: (1) it contains the data that we actually will count and (2) it serves to help join together the various question dimensions so that navigating one dimension effectively navigates the other dimensions.

Next, we created measures based on each of the question dimensions; these simply counted the unique number of participants that provided any given answer to a question, but their primary value is that they serve as a sort of “hook” that we can use to relate the various question dimensions back to the Panelist dimension, and from there out to the other question dimensions.

We then used the Dimension Usage tab to ensure that the Panelist dimension related to the measures that we had created from the question dimensions, and thus related to the question dimensions (so that navigating or “filtering” a given question dimension subsequently navigated/filtered the Panelist dimension), and we also created many-to-many relationships from each of the question dimensions to each of the other question dimensions (their relationship with the Panelist dimension facilitated this), so that navigating/filtering any one of the question dimensions consequently navigated/filtered all of the other question dimensions, based on a shared set of panelists that provided certain answers to both of the questions.

Finally, we created a calculated member that counts the distinct number of participants that exist in the Panelist dimension, which will have been navigated/filtered by your choice of question dimensions. This calculated member tells you what you wished to know: the number of panelists that answered x for one question AND y for another.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com