cursor usage

hi guys

i have a table that contains a tremendous amount of row. i have written a stored procedure that takes a summary of that information and updates it's master table as well as another table. the problem is it takes very long to do. is what i am doing correct or is there a better way. here is the source

CREATE PROCEDURE update_cvrbatches
AS

declare @.code varchar(25),@.type varchar(3),@.batchno varchar(10),@.batchqty Float,@.issued float,@.returned float,@.transfered float,@.itemcount float,@.totestcost float,@.totactcost float,@.reserved float,@.warehouse varchar(3)

Update cvrwarehouse set BoughtQty =0, IssuedQty = 0, ReservedQty =0 ,Returned = 0 ,Transfered = 0

DECLARE getbatches CURSOR
for
select Code,Type,BatchNo,sum(BoughtQty) as BoughtQty,sum(IssuedQty) as IssuedQty,sum(ReservedQty) as ReservedQty,sum(ReturnedQty) as ReturnedQty,sum(TransferQty) as TransferQty,count(Barcode) as ItemCount,
sum(EstCost) as EstCost,sum(ActCost) as ActCost,Warehouse
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
OPEN getbatches

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost ,@.warehouse
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doen iets hier

update cvrbatchctrl set BatchQty = @.batchqty, Issued = @.issued, Reserved = @.reserved ,Returned = @.returned ,Transfered = @.transfered, ItemCount = @.itemcount,TotalEstCost = @.totestcost,TotalActCost = @.totactcost
where Code = @.code and Type = @.type and Warehouse = @.warehouse and BatchNo = @.batchno
update cvrwarehouse set BoughtQty =BoughtQty + @.batchqty, IssuedQty = IssuedQty + @.issued, ReservedQty = ReservedQty + @.reserved ,Returned = Returned + @.returned ,Transfered = Transfered + @.transfered
where Code = @.code and Type = @.type and Warehouse = @.warehouse

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost,@.warehouse

END
CLOSE getbatches
DEALLOCATE getbatches

is there a better way?

Hi,

better use setbased solutions rather than cursors, one example would be (*untested*) the below one: (put in a more human readble format)

update cvrbatchctrl
set BatchQty = SUbQuery.BoughtQty,
Issued = SUbQuery.IssuedQty,
Reserved = SUbQuery.ReservedQty,
Returned = SUbQuery.ReservedQty,
Transfered = SUbQuery.TransferQty,
ItemCount = SUbQuery.ItemCount,
TotalEstCost = SUbQuery.EstCost,
TotalActCost = SUbQuery.ActCost
FROM cvrbatchctrl cvr
INNER JOIN
(
select Code,
Type,
BatchNo,
Warehouse,
sum(BoughtQty) as BoughtQty,
sum(IssuedQty) as IssuedQty,
sum(ReservedQty) as ReservedQty,
sum(ReturnedQty) as ReturnedQty,
sum(TransferQty) as TransferQty,
count(Barcode) as ItemCount,
sum(EstCost) as EstCost,
sum(ActCost) as ActCost
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
) SUbQuery
ON
cvr.Code = SUbQuery.Code and
cvr.Type = SUbQuery.Type and
cvr.Warehouse = SUbQuery.Warehouse and
cvr.BatchNo = SUbQuery.batchno

--Second one, taking the value from the first update (make surethat your condition is complete in the below script)

update cvrwarehouse
set BoughtQty = cvrhouse.BoughtQty + @.batchqty,
IssuedQty = cvrhouse.IssuedQty + @.issued,
ReservedQty = cvrhouse.ReservedQty + @.reserved ,
Returned = cvrhouse.Returned + @.returned ,
Transfered = cvrhouse.Transfered + @.transfered
FROM cvrwarehouse cvrhouse
INNER JOIN cvrbatchctrl cvrbatch
ON
cvrhouse.Code = cvrbatchctrl.Code AND
cvrhouse.Type = cvrbatchctrl.Type
cvrhouse.Warehouse = cvrbatchctrl.Warehouse

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you.

haven't tried it yet but it sure makes for an interesting solution.

let you know as soon as i do.

again thanks

|||

i just tested it

works really well and the speed is a lot better

thanks again

Current Month & Year query?

I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='.

If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))
or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?

1 SELECT CONVERT(char(10), User_Date, 101)AS userdate,COUNT(*)AS CNT,User_Name
2 FROM Users
3 WHERE (User_Name ='Joe Bob')AND (MONTH(User_Date) =MONTH(DATEADD(mm, - 1,GETDATE())))AND
4 case MONTH(User_Date)when 1then
5 (YEAR(User_Date) =YEAR(DATEADD(yy, - 1,GETDATE())))
6 else (YEAR(User_Date) =YEAR(GETDATE()))
7 END
8 GROUP BYCONVERT(char(10), User_Date, 101),User_Name
9 ORDER BYCONVERT(char(10), User_Date, 101),User_Name

Try this:

ANDYEAR(User_Date)=CASEMONTH(User_Date) WHEN 1 THEN YEAR(DATEADD(yy,- 1,GETDATE())) ELSE(YEAR(GETDATE())) END

|||

stsong,

You have your case statement in the where clause but it is meant to be in the select clause.

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

Move the case statement to your select clause and use variables.

SELECTCONVERT(char(10), User_Date, 101)AS userdate,COUNT(*)AS CNT,User_Name,'User_Year' =caseMONTH(User_Date)when 1then YEAR(DATEADD(yy, - 1,GETDATE())))else YEAR(GETDATE()))FROM UsersWHERE (User_Name ='Joe Bob')AND (MONTH(User_Date) =MONTH(DATEADD(mm, - 1,GETDATE())))ANDENDGROUP BYCONVERT(char(10), User_Date, 101),User_NameORDER BYCONVERT(char(10), User_Date, 101),User_Name

Try that.

|||

do this in your second conditional instead and see if this works for you

YEAR('2007-01-01') = case MONTH('2007-01-01') when 1 then YEAR(DATEADD(yy, - 1, GETDATE())) else YEAR(GETDATE()) END

|||

Hi,

Syntax of you case statement is completely wrong I think in your where statement try this

WHERE (User_Name ='Joe Bob')

AND

(MONTH(User_Date) =MONTH(DATEADD(mm, - 1,GETDATE())))

AND

YEAR(User_Date) =case

when MONTH(User_Date) = 1then

YEAR(DATEADD(yy, - 1,GETDATE()))

else

YEAR(GETDATE())

END

I hope what it will help you a little.

I would recomend to do select from select in this case which will speed up your query a lot saving time on converting user date 3 times (output,group and order in order you can use new field name and it should work)

Thanks

JPazgier

|||

Hi jpazgier,

Yes you are right, my case statement is wrong, I changed the "when MONTH(User_Date) = 12then" instead of = 1 since the where statement is getting last month's data. Thank you everyone for your help!

Cube usage by users

Hi,

What would be the correct way to generate some kind of log or report that will tell me which user used which cube in which database along with the number of times the user connected to each cube each day? The number of queries sent by each user to each cube by day would be ok. I am not interested by the query content for this exercise. I just want Userid, DB name, Cube name and Hits.

Then I can take it from there and create a report for our security/sox guy with User name, Cube name, Week of year and number of hits by week, meaning if the users send 36 queries to Cube A on Monday and 2 queries on Friday, it counts for 2 cube accesses for that week.

This assume that I use only TCP/IP access but if the same mechanism would work for HTTP access that would be fine. I try not to use HTTP but I may be forced to do it.

Regardless of network transport, the only logins allowed are though Windows Domain Global Group Membership.

Seems simple but I do not find where to start.

Thanks,

Philippe

Hello,

I have found a solution to this issue.

In the Server property box, you will enable query loging to a table and set the interval to 1 instead of 10.

Stop and Start the AS Server

Create some query or cube to access your log data, you are done.

I cannot really provide a query example since it depends on your situation. Mine is linked to a Calendar and to a Users tables. I convert the query time to keep only the day and the username to keep only the userid, then I build some Cube and/or Report that joins all that stuff and do some DistinctCount on the daily access by Cube by User and voila. I keep my infosec guy happy and I know what are the cubes I can retire for cause of no use.

You know, sometimes they ask you to build a cube real quick, they use it once and then they forget about it (and do not tell you). So I am not maintaining useless cubes :-)

Regards,

Philippe

Cube showing slightly different data than in data source...?

Hello,
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I am making a basic assumption here about the measure Hit_ID which is also
part of your key --> that you are COUNTING hits.
Try Count Distinct within Analysis Services instead of teh default Count. If
that does not work, I suggest introducing a measure within your fact table
called count_hits or something which will have a value of 1 (one) for every
unique combination of your keys. Then use this with the Sum aggregate
function to define your measure within the cube.
Let us know if it worked.
Thanks and hope this helps.
Rangarajan Suresh
www.picarossolutions.com
"Neile" wrote:

> Hello,
> I am very perplexed with this problem I'm having. I work for a website-based
> company and my job is to create a data warehouse based on usage of the site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse database.
> I then use Reporting Services and MDX to create reports based on this usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all hits
> are originally recorded) with hits in the final Warehouse database to see if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of the
> companies have a higher hit count, i.e. show more hits, than actually exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!

Cube showing slightly different data than in data source...?

Hello,
I am very perplexed with this problem I'm having. I work for a website-based
company and my job is to create a data warehouse based on usage of the site.
We have a Central Logging Database that logs every single hit to the site,
then i have a stored procedure that picks up these hits, does a bit of data
scrubbing and transformation, and puts them into a Warehouse database. i
then have an Analysis Services cube that points at this warehouse database.
I then use Reporting Services and MDX to create reports based on this usage
info.
Scenario: the total hits in the database can be further grouped into
companies, so reports can be run to show a company's usage of the site.
Companies are then further divided into Users belonging to a company. I.e.
reports are generally run to show a company's usage, broken down into
individual users within that company.
In order to check that my warehouse-load stored procedure works properly I
compared the amount of hits in the Central Logging Database (where all hits
are originally recorded) with hits in the final Warehouse database to see if
they balance. Everything looks fine and hunky-dory.
My problem is this: When I browse the cube in analysis manager, some of the
companies have a higher hit count, i.e. show more hits, than actually exist
in any of the original databases! In the cube one company is even showing
1,000 hits for a user who does not have ANY hits in any of the original
databases!!!
the total amount of hits shown in the cube is equal to the original
databases, so some how it balances. but i don't understand where all this
extra\incorrect data is coming from.
My cube consists of a very simple Star-schema design. one main table called
Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
consists of the following columns:
Hit_ID (int)
DatabaseSource (int)
UserAccountID (int)
BusinessEntityID (int)
LogDateID (int)
One hit is defined by a Hit_ID and DatabaseSource (these two form a
composite primary key).
My Measure in the cube is the Hit_ID.
Does anyone know what i could possibly be doing wrong? I would be very
grateful for any bit of help anyone could provide.
Thanks in advance!
I presume your measure is a Count of HitID to have the number of hit by
users...
How many rows are loaded when you process the cube? (normally this number =
number rows in the database)
There is any join between the Hit table and the others? (joins created by AS
to load the cube; see the query executed by AS)
does the user appear twice in the database?
if your users are under the company and if 1 user can appear in more then 1
company, then you can count twice (or more) the same hit.
"Neile" <Neile@.discussions.microsoft.com> a crit dans le message de news:
E05E7B92-22C7-4811-9DDF-B1C9C25EEBD0@.microsoft.com...
> Hello,
> I am very perplexed with this problem I'm having. I work for a
> website-based
> company and my job is to create a data warehouse based on usage of the
> site.
> We have a Central Logging Database that logs every single hit to the site,
> then i have a stored procedure that picks up these hits, does a bit of
> data
> scrubbing and transformation, and puts them into a Warehouse database. i
> then have an Analysis Services cube that points at this warehouse
> database.
> I then use Reporting Services and MDX to create reports based on this
> usage
> info.
> Scenario: the total hits in the database can be further grouped into
> companies, so reports can be run to show a company's usage of the site.
> Companies are then further divided into Users belonging to a company. I.e.
> reports are generally run to show a company's usage, broken down into
> individual users within that company.
> In order to check that my warehouse-load stored procedure works properly I
> compared the amount of hits in the Central Logging Database (where all
> hits
> are originally recorded) with hits in the final Warehouse database to see
> if
> they balance. Everything looks fine and hunky-dory.
> My problem is this: When I browse the cube in analysis manager, some of
> the
> companies have a higher hit count, i.e. show more hits, than actually
> exist
> in any of the original databases! In the cube one company is even showing
> 1,000 hits for a user who does not have ANY hits in any of the original
> databases!!!
> the total amount of hits shown in the cube is equal to the original
> databases, so some how it balances. but i don't understand where all this
> extra\incorrect data is coming from.
> My cube consists of a very simple Star-schema design. one main table
> called
> Hits, and UserAccount, BusinessEntity, and Date dimensions. the Hits table
> consists of the following columns:
> Hit_ID (int)
> DatabaseSource (int)
> UserAccountID (int)
> BusinessEntityID (int)
> LogDateID (int)
> One hit is defined by a Hit_ID and DatabaseSource (these two form a
> composite primary key).
> My Measure in the cube is the Hit_ID.
> Does anyone know what i could possibly be doing wrong? I would be very
> grateful for any bit of help anyone could provide.
> Thanks in advance!
>

Cube Navigation

Hi,

I have a small problem which I hope that someone can help me with.

I have got a fact table looking at website usage and this fact table is related to multiple dimensions. What I want to do Is to look for a particular page that a user has accessed and go 2 steps backwards using the time dimension to see what page was accesed at that time. I do not want to expand the whole page dimension and sort it by time, instead I want to display the name of the page that was accessed in relation to that specific event.

Does all of this make sense? I have been trying to create a recursive solution in MDX without much success.

I am thankful for any guidance that anyone can give me!

Regards

Stefan

Hello Stefan. What you are looking for is probably in one of the data mining algorithms that are part of SSAS2005. It can be the "MS Sequence Clustring". Post your question in the DM newsgroup.

HTH

Thomas Ivarsson

|||Hi Thomas,

thank you four your answer. But I am still wondering if there is no way of designing a recursive MDX solution for this problem.

What I have done so far is created a subcube with the users who have registered on the site.

What I want to is basically this:

Start at one point in time and move backwards until I find a certain page (congratulations you have registered) and then move two steps back in time and write out what page they looked at then. The result should have username on rows and page name on the columns

I want to design that as a measure so that I can reuse it for every user within that subcube.

My time dimension consists of hour - minute - seconds and my time hiearchy is divided in the same way. I am almost there, but I still have problems writing out the name of the actual page dimension when I find that point in time.

I hope that this make it a little bit clearer what I am trying to achieve.

Thank you for taking your time to help me!

Regards

Stefan

CTRL-Z usage in SSRS

I have opened a connect issue for the CTRL Z handling in SSRS.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299431

Please vote on it so it get's fixed in 2008, I am tired of losing random changes because I hit CTRL-Z.

Thanks

BobP

I voted. Thanks Bob! |||

You got my vote!

|||I lost a dataset once after trying to undo a change in a single script line.

j00 got my vote.