Correcting old data

Hi,
What's the best way to handle data reloads when you have implemented Type 2
slowly changing dimension?
For example, you might have an Account dimension, with a status of open or
closed. Someone accidentally closes the account, which we then extract and
update, creating a new record. They then realise the mistake and re-open the
account. The next extract would be create a third record, with the original
value.
On top of this, fact data may have been loaded against the second (invalid)
record.
If the source system doesn't keep a history of these changes, then we can't
redo the extract because it will contain a mix of valid and invalid changes.
Thanks,
Wreck.Hello Wreck,
Have you considered type 1 for dimensions that are affected by this
issue? This way the transactions are not effected. You can have a
mixture of type 1 and 2 in your stars.
Check out:
http://bi-on-sql-server.blogspot.co...r.blogspot.com/|||Hi Wreck,
you are entering 'religious territory' ground.....
There are two religions here:
1. Go back and correct data 'known to be incorrect'.
2. Retain data known to be incorrect and enter new transactions which
update the DW such that it now represents the data 'known' to be
correct. (Which is really just 'thought to be correct now'.)
I come down in the camp of retain the incorrect data and enter new
transactions such the data is now set to the 'thought to be correct
value'. In your case, the account would be 're-opened' and it would
occur as 're-opened'.
Why?
Because going back and updating data is a very slippery slope. Where
does it end? And if decisions were made against data that was presented
at a certain point in time and then the underlying data is 'corrected'
and people come along to review those decisions and the data says
'these decisions are not well founded on the data' what does one do?
One must remember that data is never, ever 'true' or 'correct' it is
only ever 'thought to be correct/true' which means that data that we
have realised is incorrect and we now believe to be true has the same
likelyhood of being incorrect as the data we just changed....
Whereas, if we retain the fact that we found data to be incorrect and
we enter transactions to place new transactions to correct that data we
can see the information on which decisions were taken at the time. We
can also see how often data is being corrected....perhaps there are
underlying issues for a high rate of data corrections occurring such as
lack of proper training in the call centers taking calls and entering
data? It happens...
In some cases it is a legal requirement not to correct historical data.
For example, in most countries it is illegal to update the financial
statements of a company after the close of the annual accounts...yet
clearly there have been some very 'incorrect' entries in the financials
of some very large companies over the last few years.
Best Regards
Peter Nolan
www.peternolan.com|||Thanks for your feedback guys.
Myles, the system currently has Type I changes across the board, and
naturally doesn't track history very well. I want to implement Type II, but
the data in the source systems has its issues, hence my concerns.
There are steps being taken to improve the data quality in the source system
s.
Peter, I'm inclined to agree with you that we don't correct anything,
especially once it's been reported on. However, the business is still fairly
new to the whole warehouse concept - prior to this, it was the usual story o
f
lots of Access and Excel apps extracting and manipulating data for reporting
.
When these data errors are found, they don't really understand why it's so
difficult to make a correction.
Thanks,
Wreck.
"Peter Nolan" wrote:

> Hi Wreck,
> you are entering 'religious territory' ground.....
> There are two religions here:
> 1. Go back and correct data 'known to be incorrect'.
> 2. Retain data known to be incorrect and enter new transactions which
> update the DW such that it now represents the data 'known' to be
> correct. (Which is really just 'thought to be correct now'.)
> I come down in the camp of retain the incorrect data and enter new
> transactions such the data is now set to the 'thought to be correct
> value'. In your case, the account would be 're-opened' and it would
> occur as 're-opened'.
> Why?
> Because going back and updating data is a very slippery slope. Where
> does it end? And if decisions were made against data that was presented
> at a certain point in time and then the underlying data is 'corrected'
> and people come along to review those decisions and the data says
> 'these decisions are not well founded on the data' what does one do?
> One must remember that data is never, ever 'true' or 'correct' it is
> only ever 'thought to be correct/true' which means that data that we
> have realised is incorrect and we now believe to be true has the same
> likelyhood of being incorrect as the data we just changed....
> Whereas, if we retain the fact that we found data to be incorrect and
> we enter transactions to place new transactions to correct that data we
> can see the information on which decisions were taken at the time. We
> can also see how often data is being corrected....perhaps there are
> underlying issues for a high rate of data corrections occurring such as
> lack of proper training in the call centers taking calls and entering
> data? It happens...
> In some cases it is a legal requirement not to correct historical data.
> For example, in most countries it is illegal to update the financial
> statements of a company after the close of the annual accounts...yet
> clearly there have been some very 'incorrect' entries in the financials
> of some very large companies over the last few years.
> Best Regards
> Peter Nolan
> www.peternolan.com
>|||Hi Wreck,
I have been doing DWing for 15 years...and I have seen a great many
cases of significant destruction of value within a company on the basis
that 'the business users do not understand'.......and I have seen
even more because 'the IT people do not understand'.!!!... ;-)
Indeed, it seems that those who 'do not understand' seem to think the
onus is on those who 'do understand' to explain to them what it is that
is not understood to persuade them not to destroy value in their
company. Those who 'do not understand' do not seem to think the onus
is on them to make an effort to understand to assist them create value
in their company...and so value is commonly destroyed....it would be
funnier if it was not so common...(LOL)!!
You are lucky in that today there are tons of materials available on
reasonable practices for DW development.
However, what is published is never the 'leading edge' and is rarely
'best practice'.
'Leading edge' and 'Best Practice' are retained within companies for
competitive advantage...;-)
Best Regards
Peter Nolan
www.peternolan.com

Correcting old data

Hi,
What's the best way to handle data reloads when you have implemented Type 2
slowly changing dimension?
For example, you might have an Account dimension, with a status of open or
closed. Someone accidentally closes the account, which we then extract and
update, creating a new record. They then realise the mistake and re-open the
account. The next extract would be create a third record, with the original
value.
On top of this, fact data may have been loaded against the second (invalid)
record.
If the source system doesn't keep a history of these changes, then we can't
redo the extract because it will contain a mix of valid and invalid changes.
Thanks,
Wreck.
Hello Wreck,
Have you considered type 1 for dimensions that are affected by this
issue? This way the transactions are not effected. You can have a
mixture of type 1 and 2 in your stars.
Check out:
http://bi-on-sql-server.blogspot.com...-changing.html
I wrote a blog on type 2 and type 1.
Type 2 only works at the point of time for the extracts as you
mentioned. One way of resolving this problem with dirty changes is to
take more frequent extracts to capture more of the changes.
Another option is to remove dirty changes from the dimension if there
are no corresponding fact transactions.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi Wreck,
you are entering 'religious territory' ground.....
There are two religions here:
1. Go back and correct data 'known to be incorrect'.
2. Retain data known to be incorrect and enter new transactions which
update the DW such that it now represents the data 'known' to be
correct. (Which is really just 'thought to be correct now'.)
I come down in the camp of retain the incorrect data and enter new
transactions such the data is now set to the 'thought to be correct
value'. In your case, the account would be 're-opened' and it would
occur as 're-opened'.
Why?
Because going back and updating data is a very slippery slope. Where
does it end? And if decisions were made against data that was presented
at a certain point in time and then the underlying data is 'corrected'
and people come along to review those decisions and the data says
'these decisions are not well founded on the data' what does one do?
One must remember that data is never, ever 'true' or 'correct' it is
only ever 'thought to be correct/true' which means that data that we
have realised is incorrect and we now believe to be true has the same
likelyhood of being incorrect as the data we just changed....
Whereas, if we retain the fact that we found data to be incorrect and
we enter transactions to place new transactions to correct that data we
can see the information on which decisions were taken at the time. We
can also see how often data is being corrected....perhaps there are
underlying issues for a high rate of data corrections occurring such as
lack of proper training in the call centers taking calls and entering
data? It happens...
In some cases it is a legal requirement not to correct historical data.
For example, in most countries it is illegal to update the financial
statements of a company after the close of the annual accounts...yet
clearly there have been some very 'incorrect' entries in the financials
of some very large companies over the last few years.
Best Regards
Peter Nolan
www.peternolan.com
|||Thanks for your feedback guys.
Myles, the system currently has Type I changes across the board, and
naturally doesn't track history very well. I want to implement Type II, but
the data in the source systems has its issues, hence my concerns.
There are steps being taken to improve the data quality in the source systems.
Peter, I'm inclined to agree with you that we don't correct anything,
especially once it's been reported on. However, the business is still fairly
new to the whole warehouse concept - prior to this, it was the usual story of
lots of Access and Excel apps extracting and manipulating data for reporting.
When these data errors are found, they don't really understand why it's so
difficult to make a correction.
Thanks,
Wreck.
"Peter Nolan" wrote:

> Hi Wreck,
> you are entering 'religious territory' ground.....
> There are two religions here:
> 1. Go back and correct data 'known to be incorrect'.
> 2. Retain data known to be incorrect and enter new transactions which
> update the DW such that it now represents the data 'known' to be
> correct. (Which is really just 'thought to be correct now'.)
> I come down in the camp of retain the incorrect data and enter new
> transactions such the data is now set to the 'thought to be correct
> value'. In your case, the account would be 're-opened' and it would
> occur as 're-opened'.
> Why?
> Because going back and updating data is a very slippery slope. Where
> does it end? And if decisions were made against data that was presented
> at a certain point in time and then the underlying data is 'corrected'
> and people come along to review those decisions and the data says
> 'these decisions are not well founded on the data' what does one do?
> One must remember that data is never, ever 'true' or 'correct' it is
> only ever 'thought to be correct/true' which means that data that we
> have realised is incorrect and we now believe to be true has the same
> likelyhood of being incorrect as the data we just changed....
> Whereas, if we retain the fact that we found data to be incorrect and
> we enter transactions to place new transactions to correct that data we
> can see the information on which decisions were taken at the time. We
> can also see how often data is being corrected....perhaps there are
> underlying issues for a high rate of data corrections occurring such as
> lack of proper training in the call centers taking calls and entering
> data? It happens...
> In some cases it is a legal requirement not to correct historical data.
> For example, in most countries it is illegal to update the financial
> statements of a company after the close of the annual accounts...yet
> clearly there have been some very 'incorrect' entries in the financials
> of some very large companies over the last few years.
> Best Regards
> Peter Nolan
> www.peternolan.com
>
|||Hi Wreck,
I have been doing DWing for 15 years...and I have seen a great many
cases of significant destruction of value within a company on the basis
that 'the business users do not understand'.......and I have seen
even more because 'the IT people do not understand'.!!!... ;-)
Indeed, it seems that those who 'do not understand' seem to think the
onus is on those who 'do understand' to explain to them what it is that
is not understood to persuade them not to destroy value in their
company. Those who 'do not understand' do not seem to think the onus
is on them to make an effort to understand to assist them create value
in their company...and so value is commonly destroyed....it would be
funnier if it was not so common...(LOL)!!
You are lucky in that today there are tons of materials available on
reasonable practices for DW development.
However, what is published is never the 'leading edge' and is rarely
'best practice'.
'Leading edge' and 'Best Practice' are retained within companies for
competitive advantage...;-)
Best Regards
Peter Nolan
www.peternolan.com

Correct invalid SID

I migrated my SQL 2000 secuity from one NT Domain to another. In the process of changing the login names in the master..sysxlogins table, the SID did not get updated.

Is there an easy way to correct the SID entry without dropping and recreating each user?

Do you mean that you have manually changed the login names in sysxlogins and you wish to update the SID entries as well? Can you explain what you meant by "the process of changing the login names in the master..sysxlogins table"?

Thanks
Laurentiu

|||

I ran the following in the master db:

UPDATE sysxlogins

Set [name] = 'NEWDOMAIN\' + substring([name], patindex('%\%', [name])+1, 200)

WHERE [name] like 'OLDDOMAIN\%'

It changed all the login names to point to the new domain. The problem was the SIDs changed (which I didn't think about) in the new domain. Users can get in, but when we try to use the function suser_sid(), the correct network sid is being returned and we cant compare it to the one in sysxlogins or sysusers because it doesn't match.

|||

If you move from domain A to domain B, the Windows logins from domain A are normally invalidated. An exception to this would be if domain B was trusted by domain A, then you could still use the A logins even though the server runs in domain B.

This kind of domain change is not a supported operation. There is no supported solution for fixing this. It's not only the logins that you would need to fix, but all the database users as well. If you plan to change the domain for your server often, then you should use only SQL authentication.

Thanks
Laurentiu

sql

Copying the database

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
UdayIf you have Enterprise Manager, right-click on the database, All Tasks -> Export Data.

The source should already be selected, Click next.

Set up the destination to a SQL Server that you have control over and create a <new> database. Type in a name, click OK, then click Next.

Select Copy objects and data, click Next.

Select run immediately, click Next. Then you'll need to click OK somewhere in there.|||Hi,

I did that but I am not able to copy the stored procedures and views, when I try to generate query it gives me

/****** Encrypted object is not transferable, and script can not be generated. ******/

How can I copy this stored procedures and views.

Thanks in advance,
Uday.

Copying records

How would I copy x amount of records, at the same time changing one of the
fields?
I can't copy the records as is, because of unique constraints, and I can't
leave the field that needs to be changed blank, also because nulls are not
allowed. So how would I do it in one swoop?Use a staging table.
Copy the rows from TableA to a staging table. Change the data that
needs changing and then copy the rows in to Table B
Barry|||Not sure about doing in one swoop but you could use a staging table.
Copy the rows from TableA to a staging table. Change the data that
needs changing and then copy the rows in to Table B
Barry|||what does it need to be changed to? and from? and what data type is it?
x amount of records? do you mean e.g., "copy 50 rows from a to b" or
"copy however many rows match my criteria from a to b"?
Please post DDL, including constraints etc., sample data and desired
results. Otherwise, all answers can be considered nothing more than guesses.
dew wrote:
> How would I copy x amount of records, at the same time changing one of the
> fields?
> I can't copy the records as is, because of unique constraints, and I can't
> leave the field that needs to be changed blank, also because nulls are not
> allowed. So how would I do it in one swoop?
>|||Ah. That works great, thanks!
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1139592215.572339.195290@.g47g2000cwa.googlegroups.com...
> Use a staging table.
> Copy the rows from TableA to a staging table. Change the data that
> needs changing and then copy the rows in to Table B
> Barry
>sql