Talis Developer Network - reservation popularity - Comments

Ah.. makes sense now. The

markhughes — Thu, 8 May 2008 10:44:38 +0100

Ah.. makes sense now.

The way I understood it from the TDN article was that rowcount didn't work at all on prism, that's why I was trying to find various workarounds for the 'top x' type of query.

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

rowcount and prepared searches

Brian Crampton — Thu, 8 May 2008 08:48:36 +0100

There are two types of prepared searches - Offline and Online.

In Offline prepared searches the results are extracted into a table from a script that is run once and so rowcount can be used.

The Online prepared searches are run 'live' when accessed by a user and those have issues with using rowcount.

Some queries are better suited to the Offline approach because of the performance implications of the query. A top x type of query is likely to be one where the performance would not be suitable for an on-line prepared search even given the rowcount issue.

Thanks

Brian Crampton
Developer, Talis

Set rowcount in prepared search

markhughes — Wed, 7 May 2008 16:21:52 +0100

Brian,

I see from a few forum posts that 'set rowcount' seems to work in a prism prepared search, but the TDN article says it doesn't. Is there a problem using it in prism?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

reservation popularity

Brian Crampton — Thu, 17 Apr 2008 18:20:10 +0100

The problem is that the SS_reservations table does not exist and this command will not create that table for you.

if you change the SQL to

SET ROWCOUNT 50
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
into #SS_reservations
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC

It will create a temporary table called #SS_reservations (The hash indicates a temporary table.)

By temporary I mean that within an isql session that table is available to be used so

select * from #SS_reservations

will work until you exit from isql and it won't exist for anyone else of for you if you start another isql session.

A temporary table is held in temp_db database so you need to be careful that you don't fill it or the logs up.

The SQL above is unlikely to do that but care needs to be taken.

What you need to do is create the SS_reservations table in prod_talis first.

e.g.
create table SS_reservations (
WORK_ID ID NOT NULL,
FIGURES smallint NULL)
go
grant all on SS_reservations to public
go

The smallint is upto 32,768 so should be big enough. Use INT (2^31) if not.

The trouble is that I now think with more consideration that with the SQL you have you may have to change the options on prod_talis to allow bulk data load but this would cause problems with transaction logging so would have to be done carefully e.g. the options changed, the script run, the options changed back and then a full dump done to allow transaction logging to start.

There are talis scripts that require these changes but this may be more than you want to do.

A different syntax will still log the transactions (the SS_reservations table still needs to be created) so allow the insert to work.

insert into SS_reservations (WORK_ID,FIGURES)
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC
go

Note: you would need to remove any data from the SS_reservations table first.

As I said before all this type of changes to the db need to be done with great care as they are not covered by Support and done at your own risk.

You don't want to fill your transaction logs or tempdb and affect anything the whole system.

Thanks

Brian Crampton
Developer, Talis

Code

markhughes — Thu, 17 Apr 2008 15:09:37 +0100

Thanks Brian,

I was trying your code:

SET ROWCOUNT 50
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
into SS_reservations
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

inserting data into a table

Brian Crampton — Wed, 16 Apr 2008 15:55:17 +0100

You shouldn't need to grant permissions but could you let me have the SQL statement/s you were using and I'll have a look.

Thanks

Brian Crampton
Developer, Talis

Brian, I was tinkering with

markhughes — Wed, 16 Apr 2008 15:39:40 +0100

Brian,

I was tinkering with this on the MIS server, but gave up when it said

"You can't run SELECT INTO in this database. Please check with the Database
Owner."

Out of curiosity... how would I get round this? How would I grant permissions to 'select into' a table?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

Thats's great Brian. We

markhughes — Thu, 10 Apr 2008 11:17:13 +0100

Thats's great Brian. We have just ordered the new BusinessObjectsXI, so I may go down that route. I'll probably be in touch for advice when things are up and running.

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

prism reservation popularity

Brian Crampton — Wed, 9 Apr 2008 16:10:24 +0100

If you were going to go down the route of a new table to hold the top 50 which is then accessed via the prepared searches then I would be inclined to do the whole process all in one go rather than having to manipulate text files.

The example below assumes that a table has been created in prod_talis called SS_reservations with the right shape e.g. attributes WORK_ID, FIGURES of the right datatype.

This could be set up in the cron to run from a script and then the prepared search could be pointed at the SS_reservations table.

I'm not sure of all the implications so I would advise any testing on the MIS server first.

Also there is the usual caveat that any changes to the prod_talis database on the live server should be done with great care in case of damaging the database for Alto and that you would be responsible.

Another option, depending on how you wanted to view the data might be to use Desktop Intellligence to generate the report. This report can be saved in html format that could be saved to a location on a web server and then accessed from a URL.

In the latest version (XI) the document can be scheduled to run and write the output in html format to a disk location automatically.

There are some limitations particularly in terms of the look but it might be useful. I can go into more details if required.

Thanks

Brian Crampton
Developer, Talis

Rowcount in prepared search

markhughes — Fri, 4 Apr 2008 15:10:44 +0100

Brian,

If I wanted to do something like 'the top 50 whatever', as there is no rowcount facility in prism, would the following be possible???

Say I did a Prepared search, say as an example for reservations....listing ALL reservations. This would create a table like SS_reservations in the database.

Would it then be possible to then write an external script (like the one you did above) that would produce the data in a text file.

Then could I take this textfile data, extract the 'top 50 rows, clear the existing data from the SS_reservations table and then pop the new data in there - Via a cron job? So there would be no 'manual' inputting of data into a secondary script...

Would this be possible?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

Prism Prepared Searches

Russell Hall — Thu, 27 Sep 2007 16:09:37 +0100

Hi Brian,
Its the bit about running the script outside of prism, then manually entering the results in another script.
I recognise the words but at this point of my Talis experience the meaning escapes me.
I really think I will pass on this one, thanks anyway.

Russell

Prism Prepared searches

Brian Crampton — Wed, 26 Sep 2007 12:10:02 +0100

With which bit of the process was there a problem?

By the way the following version of the SQL in the article is better formed. It does not use a subquery.

SET ROWCOUNT 20 SELECT RL.TARGET_ID, COUNT(RL.RESERVATION_ID) FROM RESERVED_LINK RL, RESERVATION R WHERE RL.TYPE = 1 AND RL.RESERVATION_ID = R.RESERVATION_ID AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE()) GROUP BY RL.TARGET_ID ORDER BY COUNT(RL.RESERVATION_ID) DESC

Thanks

Brian Crampton
Developer, Talis

Prism Prepared searches

Russell Hall — Wed, 26 Sep 2007 11:37:24 +0100

Thanks Brian,

I have that document. Sadly your simple work-around isn't simple enough. If at first you don't succeed, give up. Russell

Prism Prepared searches

Brian Crampton — Wed, 26 Sep 2007 08:57:42 +0100

There is an article on prepared searches on the TDN at http://www.talis.com/tdn/talis_prism_articles

This discusses a workaround for not being able to use the set rowcount command directly in the prepared search SQL statement.

Thanks

Brian Crampton
Developer, Talis

Reservation Popularity

Russell Hall — Tue, 25 Sep 2007 16:18:36 +0100

Hello everyone

We have just got around to adding some prepared searches on our prism catalogue. I'm interested in adding the top 20 reservations (as above) but have found elsewhere on the forums a similar SQL with a note mentioning that the "setrowcount 20" command is not recognised in prepared searches. I can't find the reference now. Is this right, or will this SQL produce a list of the 20 most requested items in Prism???? any help will be most appreciated. Regards, Russell

reservation popularity

Tim Hodson — Fri, 16 Dec 2005 16:26:29 +0000