SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Historical Reporting on Changing Data
 New Topic  Topic Locked
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Van
Constraint Violating Yak Guru

458 Posts

Posted - 04/29/2008 :  11:50:40  Show Profile
I'm not following one thing. You mentioned that you have an audit table. So if the score goes from 70, to 75, then to 90, then to 85, shouldn't there be a row with a timestamp for each change in your audit table?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/29/2008 :  12:53:49  Show Profile
I think I understand the nature of the problem, and I have a solution that I am sure will work. The solution is...
...have your client contact me and I will write this for them.

e4 d5 xd5 Nf6
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/29/2008 :  13:31:36  Show Profile
Anecdotal evidence:
One of the reasons I don't post as much any more is by the time I prepare a thoughtful response to a new post and refresh the page there are already several responses in front of me. Granted I've gotten slow in my old age but that is still a pretty quick response time.

Be One with the Optimizer
TG
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  13:43:36  Show Profile  Visit m_k_s@hotmail.com's Homepage
quote:
Originally posted by blindman

I think I understand the nature of the problem, and I have a solution that I am sure will work. The solution is...
...have your client contact me and I will write this for them.

e4 d5 xd5 Nf6



That's funny - all the experts here, experts who don't have enough work. I do farm out work, but it seems most people don't speak the same English I do...

<b>I've got a customer who wants reproducible/historical reporting. The problem is that the underlying data changes.</b>

If the first sentence doesn't strike you as problematic, well, I'm not sure well you'd be able to capture specifications.

In any event, the problem has resolved itself. The dba I contracted was able to demonstrate through example that what the client wanted is not feasible and we've talked the client into a more standard approach to historical reporting.

Thanks to all who tried to help.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 04/29/2008 :  13:48:29  Show Profile  Visit tkizer's Homepage
I find it funny that people call us experts here. Who claims to be an expert?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  14:55:54  Show Profile  Visit jsmith8858's Homepage
quote:

<b>I've got a customer who wants reproducible/historical reporting. The problem is that the underlying data changes.</b>

If the first sentence doesn't strike you as problematic, well, I'm not sure well you'd be able to capture specifications.



WTF??? All you need is audit tables and date stamps. What is so hard?

The main thing you've communicated in this thread is your overall failure to work with your client to come up with specifications to handle their needs. You have only vaguely told them that their need for historical reporting is "unreasonable" and "problematic", and given no conclusive and specific reasons as to why. Your posts portray you as someone who lacks the necessary skills or work ethic to interview a client to help them determine what it is they are after, and to ultimately deliver it to them.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  15:29:08  Show Profile  Visit m_k_s@hotmail.com's Homepage
quote:
Originally posted by jsmith8858

quote:

<b>I've got a customer who wants reproducible/historical reporting. The problem is that the underlying data changes.</b>

If the first sentence doesn't strike you as problematic, well, I'm not sure well you'd be able to capture specifications.



WTF??? All you need is audit tables and date stamps. What is so hard?

The main thing you've communicated in this thread is your overall failure to work with your client to come up with specifications to handle their needs. You have only vaguely told them that their need for historical reporting is "unreasonable" and "problematic", and given no conclusive and specific reasons as to why. Your posts portray you as someone who lacks the necessary skills or work ethic to interview a client to help them determine what it is they are after, and to ultimately deliver it to them.

- Jeff
http://weblogs.sqlteam.com/JeffS




Your posts portray you as an insecure douchebag...

The problem is clearly that reproducible history of changing data is not realistic. If you don't understand that you can't reproduce that which changes haphazardly, then perhaps you should go back to college and spend more time smoking dope in metaphysics...

My inability to ineffectively and lazily work with clients has led to the development of a consulting group where all the partners earn over 150k a year with very satisfied customers...

Perhaps you can teach me to be more effective and how to communicate better so I can not have as many contracts to work on. Perhaps you can teach me to spend my time writing marginally useful posts and articles so that I can feel smart and that I'm a helpful member of society. Maybe we could go on a double internet date or something...

and what is this thing - this audiTable? how will having a table of a car manufacturer help me? Does it have to be a European car? I prefer gm - can we make it gmTable instead?

An audiTable with date stamps... Why, I've never heard of such a thing before, you must have gone to a better community college than I did... you may want to patent this notion before it becomes wide spread in industry... ingenius... I wish my CS professors would have taught me stuff like this. How many date stamps do you have? can you count the time you went on a date with your sister? or do only preety girls count?

Thanks for the useful tips!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/29/2008 :  15:32:25  Show Profile  Visit spirit1's Homepage
m_k_s@hotmail.com:
no insults please.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 04/29/2008 :  15:34:42  Show Profile  Visit tkizer's Homepage
m_k_s, please note that Jeff (jsmith8858), Mladen (spirit1), and myself are moderators here. We have the ability to lock your account. Although I don't think it is warranted yet, you are getting closer and closer to it.

If this thread continues on this track, we will lock it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/29/2008 :  15:35:09  Show Profile  Visit spirit1's Homepage
> The problem is clearly that reproducible history of changing data is not realistic

why exactly is that not realistic?
ok you can't reproduce data that has been changed in the past without auditing.
maybe you can if you have backups.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  15:37:20  Show Profile  Visit jsmith8858's Homepage
Wow.

It would be nice if, during all your ranting, you had actually answered my question:

quote:
All you need is audit tables and date stamps. What is so hard?



I will add: it is funny that you call me insecure (where did that come from?) yet you feel the need to tell me how successful you are, and how much money you make ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/29/2008 15:42:41
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/29/2008 :  16:58:32  Show Profile
The OP seems to be mad because he wanted help proving that something is impossible. It may be impractical, too expensive, something he doesn’t want to do, or something he just can’t figure out how to do, but I saw nothing in any of the posts that could prove it was impossible. It is also possible, even likely, that he is doing a terrible job of explaining the problem. He certainly didn’t supply any of the usual items that would be needed to explain the problem in detail: DDL, sample data, expected output, etc.

I think the moderators have been very generous in letting this thread continue. Calling Jeff an “insecure douchebag” and telling him to “spend more time smoking dope” is way outside the bounds of acceptable posting. Besides the immature abuse, the OP doesn’t really seem to want any help in solving the problem, just some reassurance that it is impossible.






CODO ERGO SUM
Go to Top of Page

eilert
Starting Member

3 Posts

Posted - 04/29/2008 :  17:34:06  Show Profile
I know that this is no longer a problem, since your contracted dba demonstrated that it is not possible, but I agree with Michael that there is no evidence posted that this is impossible, rather, there is a whole lot of confusion with what is being requested. Furthermore, if we can solve this issue, it may be useful to others in the future.

With that said, I'm confused. Generally data changes, but from what I've read it is being stated that "The customer makes historical changes".

Lets take this example:

On 1/1 a variable is set to 70
On 2/1 the same variable is changed to 75
On 3/1 the same variable is changed to 80
On 4/1 the same variable is changed to 85

Currently the variable is 85 since it has not been changed since 4/1

If I understand correctly, you have no problem reporting back what the value was on a previous date. For example, you can easily say that on 3/1 the value was 85 and on 2/23 the value was 75.

If this is correct, then I am very confused as to what you mean when you say that "The customer makes historical changes"

To me this is implying that the customer can go in and rather than change what the value is now, they can change what the value was on 2/1. So, continuing the example, the current value is 85 and the value on 2/1 was 75 but is being changed to 95. In this senario the current value is still 85 but the value on 2/1/2008 is now 95!?

The only way I can think of this update being possible is if someone is making these "historical changes" to an audit log (and that is pretty scary).

So my basic question is, when a value is updated, by "historical changes" do you mean that the current value is being updated? or a value from another point in time is being updated and the current value remains the same?
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  18:00:10  Show Profile  Visit m_k_s@hotmail.com's Homepage
So the domain is test scores and the issue is aggregate reporting of values of these changing values.

e.g.

In January, the avg test score for 90 tests completed in January is 78.

In February, the avg test score for 120 tests completed in February is 84. Also in February, some of the tests from January changed score, now the January average is 80 instead of 78.

In March, the avg test score for 70 tests completed in March is 86. Also in March, the test scores from February changed to average 76 and the test scores from January changed to 65.


When the history report is run in January it will show 78 for the January Average.

When the history report is run in February, January now shows 80 and February shows 84.

When the history report is run in March, January shows 65, February shows 76, and March shows 86.

The customer is saying that for the March History report, he wants to see 78 for January, 84 for February, and 86 for March.

Now even though this is not good, the above can be accomplished. The problem is that the time points at which reports are run interspersed throughout the month. So, the way the hired gun killed the conversation with the client was to use above example (with more data points) and said, ok now what is the right "history" value for January, when I run this for you in March?

The two best solutions to the requirement were:
A) have the test table have a score and an initial_score column, which got us close to what he wanted.
B) take snap shots of the report on a daily basis and record the values in a separate table and when asked for a history, to plot these points.

The interesting thing is that the dba understood right away what the problem was when I explained it to him, but I could not convey the problem to the customer (or to the people reading this post)...

So the moral of the story, if you are having problems explaining something to someone, find someone who looks smarter than you explain it...


quote:
Originally posted by eilert

I know that this is no longer a problem, since your contracted dba demonstrated that it is not possible, but I agree with Michael that there is no evidence posted that this is impossible, rather, there is a whole lot of confusion with what is being requested. Furthermore, if we can solve this issue, it may be useful to others in the future.

With that said, I'm confused. Generally data changes, but from what I've read it is being stated that "The customer makes historical changes".

Lets take this example:

On 1/1 a variable is set to 70
On 2/1 the same variable is changed to 75
On 3/1 the same variable is changed to 80
On 4/1 the same variable is changed to 85

Currently the variable is 85 since it has not been changed since 4/1

If I understand correctly, you have no problem reporting back what the value was on a previous date. For example, you can easily say that on 3/1 the value was 85 and on 2/23 the value was 75.

If this is correct, then I am very confused as to what you mean when you say that "The customer makes historical changes"

To me this is implying that the customer can go in and rather than change what the value is now, they can change what the value was on 2/1. So, continuing the example, the current value is 85 and the value on 2/1 was 75 but is being changed to 95. In this senario the current value is still 85 but the value on 2/1/2008 is now 95!?

The only way I can think of this update being possible is if someone is making these "historical changes" to an audit log (and that is pretty scary).

So my basic question is, when a value is updated, by "historical changes" do you mean that the current value is being updated? or a value from another point in time is being updated and the current value remains the same?

Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 04/29/2008 :  18:05:10  Show Profile
insecure douchebag sounds like a title to me



-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 04/29/2008 :  18:06:16  Show Profile  Visit tkizer's Homepage
I wish moderators could make that change, but sadly only Bill, Damian, and Rob can.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  18:29:21  Show Profile  Visit jsmith8858's Homepage
quote:
Originally posted by m_k_s@hotmail.com

So the domain is test scores and the issue is aggregate reporting of values of these changing values.

e.g.

In January, the avg test score for 90 tests completed in January is 78.

In February, the avg test score for 120 tests completed in February is 84. Also in February, some of the tests from January changed score, now the January average is 80 instead of 78.

In March, the avg test score for 70 tests completed in March is 86. Also in March, the test scores from February changed to average 76 and the test scores from January changed to 65.


When the history report is run in January it will show 78 for the January Average.

When the history report is run in February, January now shows 80 and February shows 84.

When the history report is run in March, January shows 65, February shows 76, and March shows 86.

The customer is saying that for the March History report, he wants to see 78 for January, 84 for February, and 86 for March.

Now even though this is not good, the above can be accomplished. The problem is that the time points at which reports are run interspersed throughout the month. So, the way the hired gun killed the conversation with the client was to use above example (with more data points) and said, ok now what is the right "history" value for January, when I run this for you in March?

The two best solutions to the requirement were:
A) have the test table have a score and an initial_score column, which got us close to what he wanted.
B) take snap shots of the report on a daily basis and record the values in a separate table and when asked for a history, to plot these points.

The interesting thing is that the dba understood right away what the problem was when I explained it to him, but I could not convey the problem to the customer (or to the people reading this post)...

So the moral of the story, if you are having problems explaining something to someone, find someone who looks smarter than you explain it...


quote:
Originally posted by eilert

I know that this is no longer a problem, since your contracted dba demonstrated that it is not possible, but I agree with Michael that there is no evidence posted that this is impossible, rather, there is a whole lot of confusion with what is being requested. Furthermore, if we can solve this issue, it may be useful to others in the future.

With that said, I'm confused. Generally data changes, but from what I've read it is being stated that "The customer makes historical changes".

Lets take this example:

On 1/1 a variable is set to 70
On 2/1 the same variable is changed to 75
On 3/1 the same variable is changed to 80
On 4/1 the same variable is changed to 85

Currently the variable is 85 since it has not been changed since 4/1

If I understand correctly, you have no problem reporting back what the value was on a previous date. For example, you can easily say that on 3/1 the value was 85 and on 2/23 the value was 75.

If this is correct, then I am very confused as to what you mean when you say that "The customer makes historical changes"

To me this is implying that the customer can go in and rather than change what the value is now, they can change what the value was on 2/1. So, continuing the example, the current value is 85 and the value on 2/1 was 75 but is being changed to 95. In this senario the current value is still 85 but the value on 2/1/2008 is now 95!?

The only way I can think of this update being possible is if someone is making these "historical changes" to an audit log (and that is pretty scary).

So my basic question is, when a value is updated, by "historical changes" do you mean that the current value is being updated? or a value from another point in time is being updated and the current value remains the same?





Again, this is a trivial thing to solve if you set up your application so that clients don't delete or change data, but rather always add transactions to a table with date stamps. Like how an accounting system works: you always post transactions to a ledger, and that's all you can really do -- add more transactions. If a transaction posted previously was wrong and needs to change, you just reverse it out and then book the new transactions. You have an audit trail and your final totals will be correct depending on the effective dates that you wish to report on.

If you simply follow this model, and use date stamps to keep track of when things were added to your table, it is a very simple database design, a very simple application design, and very simple SQL that you need to write to maintain things and for your reporting.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/29/2008 18:50:58
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/29/2008 :  18:38:52  Show Profile
So in the end, the problem is not that the data is unavailable or has changed, but that the selection criteria for the report are unavailable and/or undefined.

You know what the historical values of the data points are, but you do not know the selection criteria for the prior reports. Even if you had recorded the selection criteria for prior reports, you do not have a definition of which prior report selection criteria should be used for particular data points.

That certainly is a different problem from what was originally explained. I would also want to explore with users the reasons why they want this. It sounds like there is a lot of potential for hidden requirements.








CODO ERGO SUM
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  19:57:23  Show Profile  Visit m_k_s@hotmail.com's Homepage
Yes - you got it on the nose now. The customer did not know what he was asking for and was not listening to the finer details about what a history report could mean given that his data is changing all the time.

Either that or Jeff knows more about the domain that the customer, me, and my $150/hr dba...



quote:
Originally posted by Michael Valentine Jones

So in the end, the problem is not that the data is unavailable or has changed, but that the selection criteria for the report are unavailable and/or undefined.

You know what the historical values of the data points are, but you do not know the selection criteria for the prior reports. Even if you had recorded the selection criteria for prior reports, you do not have a definition of which prior report selection criteria should be used for particular data points.

That certainly is a different problem from what was originally explained. I would also want to explore with users the reasons why they want this. It sounds like there is a lot of potential for hidden requirements.








CODO ERGO SUM

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 04/29/2008 :  20:00:07  Show Profile  Visit tkizer's Homepage
It's obvious that Jeff knows more than you. But it doesn't matter what someone gets paid an hour. There are plenty of people who get paid more than I do that know less than I do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000