cursor question

hi

From general point of view I would like to update table 'warehouse' everday from another table 'table dump' and to checkif some of the columns have changed in table 'dump' against old values in table 'warehouse'. If there is some change I need to backup
old values in some kind of history_logs table.

So I begun by writing a stored procedure for this...

DECLARE ticket_dump_cursor CURSOR FOR
SELECT * FROM ticket_dump order by Ticket_ID

OPEN ticket_dump_cursor

FETCH NEXT FROM ticket_dump_cursor

WHILE @.@.FETCH_STATUS = 0
BEGIN
-- insert code later

END
CLOSE ticket_dump_cursor
DEALLOCATE ticket_dump_cursor

GO

Now the question? is there a way how to store one row as some kind of
row datatype (something like in POSTGRE SLQ is record datatype) and then
iterate trough columns and check their value against old values in 'warehouse' table

I don't want to use FETCH NEXT FROM dump INTO @.var1, @.var2
because I need to check about 50 columns..

How to trick this? Desperately waiting for your suggestions..

thanks a lot

misoI don't know how to accomplish this in SP, but if a client app is possible, I would use DAO or better ADO technology, which does enable you to loop through all fields of a record. I can provide you with some example code.|||Hi

thanx for suggestion but I would like to us dynamic SQL if possible...
I am not very familiar with ADO and to tell the truth I would like to avoid it if I can...If it is the only way then please send me examples you mentioned

e-mail michal.holecka@.accenture.com

I was thinking about alterative solution and maybe I could do the job this way:

I would simply update rows in 'warehouse' table and write trigger which would fire after update. This trigger will check if some column has changed and if it has been changed it will log the old value into history table..

The only problem is that inside this trigger I will have to check all 50 columns manualy ... that's not very systematic solution.|||You can archive the entire row if you want through a trigger with the type of action that was performed , that is Insert , Update Or delete|||yeah I can do that but there will be a lot of redundancy in storing the whole row..

imagine that this row has 56 columns exactly and only one column will change.. I will store the whole row. Then another row changes. and so on..

I was thinking about some kind of history logging that works like this..
if one column will change I will find out which one and log only column name and old value into history table not the whole row.

Does it make sense to you?

Can I do it somehow this way?

thank you for your time ..

michal holecka|||Its a choice between space and performance ...

Trying to figure out which column has changed would take about 56 comparisons ...

Hmm ... ask your managers to get a bigger disk :)|||Your original idea was to compare your table with a copy of the day before?! It can be done, but you would have to compare keys to detect inserted and deleted rows. To detect field changes, you would have to join your current and copied table on your key, and to compare field values.

A complete other approach is event-driven to catch every database change. This approach is mostly used in DWH environments to trace db changes.

The question is thus: what do you want to accomplish? :rolleyes:|||Originally posted by mikosan
I was thinking about some kind of history logging that works like this..
if one column will change I will find out which one and log only column name and old value into history table not the whole row.

Does it make sense to you?

Can I do it somehow this way?


You can detect, which columns are updated. See the CREATE TRIGGER syntax:

CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
sql_statement [...n]
}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (column)
[{AND | OR} UPDATE (column)]
[...n]
| IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}

Example:

CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS

-- Check whether columns 2, 3 or 4 has been updated. If any or all of
-- columns 2, 3 or 4 have been changed, create an audit record.
-- The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14
-- To check if all columns 2, 3, and 4 are updated, use = 14 in place of
-- >0 (below).

IF (COLUMNS_UPDATED() & 14) > 0
-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
-- columns 2, 3, and 4 are updated.
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
....|||sorry if I mady myself unclear.

I have some identifier and according this value I can tell whether the row will be updated or appended to dwh table.

For those rows that will be updated I will try backup old values.. problem is that we are talking about 56 columns.

My idea was not to backup the whole row but to have some mechanism like somehow iterate through columns of one row and log to history table following

changed_column_name name
old_value

If I understand it well I am able to do that but only with 56 IF UPDATES...|||Originally posted by DoktorBlue
You can detect, which columns are updated. See the CREATE TRIGGER syntax:

CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
sql_statement [...n]
}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (column)
[{AND | OR} UPDATE (column)]
[...n]
| IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}

Example:

CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS

-- Check whether columns 2, 3 or 4 has been updated. If any or all of
-- columns 2, 3 or 4 have been changed, create an audit record.
-- The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14
-- To check if all columns 2, 3, and 4 are updated, use = 14 in place of
-- >0 (below).

IF (COLUMNS_UPDATED() & 14) > 0
-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
-- columns 2, 3, and 4 are updated.
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
....

This approach will work only till you dont make any changes to your table schema . If for ex. I remove the first column , what will happen :)|||If I use your way
IF (COLUMNS_UPDATED() & 14) > 0
-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
-- columns 2, 3, and 4 are updated.
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
....

Can I do something like
while

If (COLUMNS_UPDATED() &|||Originally posted by Enigma
This approach will work only till you dont make any changes to your table schema . If for ex. I remove the first column , what will happen :)

I assumed a fixed structure, indeed. Mikrosan, correct me if this isn't the case.|||yes it will be static structure it won't change...|||But why still go for the column checks ... is disk space really that expensive ? I would anytime sacrifice disk space for performance|||To be really honest I am just junior programmer and seniors advised/ordered me to do that this way...

But considering your replies I see that I will have to talk to him..

thank you guys you are opening my eyes all the time :)|||What is the size of the table ... no of average inserts,updates, deletes per day ... that would be a good deciding factor ...

Dont go on my words alone ... If the table is updated,deleted, inserted too much then the performance would be affected by the column level approach ... otherwise you can go for DoctorBlue's Method|||Originally posted by mikosan
If I use your way
IF (COLUMNS_UPDATED() & 14) > 0
-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
-- columns 2, 3, and 4 are updated.
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
....

Can I do something like
while

If (COLUMNS_UPDATED() &

I'm not sure about this. Certainly, you can say:

IF (COLUMNS_UPDATED() & 1) = 1 -- 1st column updated
INSERT INTO LOG(Name, Value) VALUES(<1st col name>, OLD.1st value)
ELSE IF (COLUMNS_UPDATED() & 2) = 2 -- 2nd column updated
INSERT INTO LOG(Name, Value) VALUES(<2nd col name>, OLD.2nd value)
ELSE IF (COLUMNS_UPDATED() & 4) = 4 -- 3rd column updated
INSERT INTO LOG(Name, Value) VALUES(<3rd col name>, OLD.3rd value)
ELSE IF ...|||the warehouse table will be updated once a day
dump table will every day have 4000 rows but only 500 will be changed
against yesterday and about 50 will be brand new..

one guy advised me to use dynamic select and exec query..

to be more specific

use while loop to and create dynamic select which compare columns in whs table and dump table...

my senior insists on doing this that way.. so I will try to study this..|||every record will have app. 5 changes trough it's lifetime..|||Originally posted by mikosan
every record will have app. 5 changes trough it's lifetime..

Seems like some home work ....:p|||well, I will study dynamic queries and we will see if seniors like it that way

anyway I would like to thank for cooperation ...

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 insteadof warehouse

is cube a Replacement for data warehouse in sql server 2005?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...house/200512/1
Hello Maryam,
Although AS2005 is very powerful, it will not solve issues like data
integration, complex business logic, or even transforming data into a
usable format, which are common problems for data warehouses.
AS2005 does have some capabilities in solving complex business logic
using data source views, but for data consolidation from different data
sources I would always go for a data warehouse.
AS2005 does go a long way to creating cubes against relational data
sources. But if these data sources are not in a format you desire then
you may want to look at a data warehouse to solve those problems.
Hope it helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Cube insteadof warehouse

is cube a Replacement for data warehouse in sql server 2005?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...ehouse/200512/1Hello Maryam,
Although AS2005 is very powerful, it will not solve issues like data
integration, complex business logic, or even transforming data into a
usable format, which are common problems for data warehouses.
AS2005 does have some capabilities in solving complex business logic
using data source views, but for data consolidation from different data
sources I would always go for a data warehouse.
AS2005 does go a long way to creating cubes against relational data
sources. But if these data sources are not in a format you desire then
you may want to look at a data warehouse to solve those problems.
Hope it helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/