Finding affected tables

I am attempting to document a sql server 2000 based accounting system.
Is there any way to see what tables a stored procedure affects
without diving into the code?

Regards,

Ty"Tyler Hudson" <tylerh@.allpax.com> wrote in message
news:804fa945.0403020849.fcf8f0b@.posting.google.co m...
> I am attempting to document a sql server 2000 based accounting system.
> Is there any way to see what tables a stored procedure affects
> without diving into the code?
>
> Regards,
> Ty

You can try sp_depends, although it may not be completely accurate,
depending on the order in which objects were created.

Simon

Financial Year

Hi,
one of the important things in accounting systems is using financial
year. whenever the year changes, they change the financial year and
use the new one. some times they need to reference the previous
financial year accounts.
I would like to to know what is the best approach to store these case
in sql server?
I thought of an approach which we store a field for Financial year for
each table which needs it. and when querying we use the default
Financial Year. Well, might it be very time consuming when the size of
the database grows very much?
I mean does it take a lot of time to find the financial year we want
through lots of other financial year information.
Or are there any better methods and solution to do so in SQL Server
2005?
Thank you very much
Ali"Ali" <nikzad.a@.gmail.com> wrote in message
news:1189316098.187434.283920@.57g2000hsv.googlegroups.com...
> Hi,
> one of the important things in accounting systems is using financial
> year. whenever the year changes, they change the financial year and
> use the new one. some times they need to reference the previous
> financial year accounts.
> I would like to to know what is the best approach to store these case
> in sql server?
> I thought of an approach which we store a field for Financial year for
> each table which needs it. and when querying we use the default
> Financial Year. Well, might it be very time consuming when the size of
> the database grows very much?
> I mean does it take a lot of time to find the financial year we want
> through lots of other financial year information.
> Or are there any better methods and solution to do so in SQL Server
> 2005?
> Thank you very much
> Ali
>
Create a Calendar table:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
--
David Portas|||You can use Partitioning for your database for this purpose in case your SQL
Server version is 2005 and edition Enterprise.
Using partitioning, you will be able to save all different years in
different files. It will be more managable and faster. You may keep the old
and rarely used partitions (files of them) to slower disks relatively to the
recent and more used partitions (files of them).
You can find more information about partitioning in the following page:
Understand Partitioning:
http://msdn2.microsoft.com/en-us/library/ms188232.aspx
Ekrem Önsoy
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1189316098.187434.283920@.57g2000hsv.googlegroups.com...
> Hi,
> one of the important things in accounting systems is using financial
> year. whenever the year changes, they change the financial year and
> use the new one. some times they need to reference the previous
> financial year accounts.
> I would like to to know what is the best approach to store these case
> in sql server?
> I thought of an approach which we store a field for Financial year for
> each table which needs it. and when querying we use the default
> Financial Year. Well, might it be very time consuming when the size of
> the database grows very much?
> I mean does it take a lot of time to find the financial year we want
> through lots of other financial year information.
> Or are there any better methods and solution to do so in SQL Server
> 2005?
> Thank you very much
> Ali
>

Financial Accounting Cube

Hi,

I'm designing a cube for analyzing financial data from an accounting application.

Basically, the facts table looks like:

(idAccount, idTime, nValue)

that shows every financial operation for any account, and it's very easy to build a cube for analysing the accounting movements.

But there is another important measure, that's the balance of every account in any time.

A tipical report would be, for a whole year, the sum of movements of any account and the balance at the end of the period.

I think adding a new column to the facts table like:

(idAccount, idTime, nValue, nBalance)

wouldn't work, because any account has a balance in any time, even thought there isn't movements in that time period.

What do you think would be a good design approach?

Why not break it into two (Seems like they are unrelated facts that happen to have common dimensions):

idAccount, idTime, nValue

idAccount, idTime, nBalance

|||

Could work, but with that approach I would have a row in the fact table for every day and for every account, and that doesn't look very efficient.

Imagine a "sales" account, that has a balance value in day 10 of 10,000$, and there are more sales after that day:

(idAccount="sales", idTime=10, nBalance=10000)

(idAccount="sales", idTime=15,nValue=500)

so, (idAccount="sales", idTime=15, nBalance=10500)

That means, the balance value changes after any accounting operation.

And now, what happens if the user makes a report that has a row for every account with two more columns: nBalance and nValue, and chooses only the day 20?

For the "sales" account nValue = 0, that's clear, but, what happens with nBalance?

It should be 10500, becuase the balance didn't change after the day 15.

The problem is that I don't know how to create the nBalance measure in order to get that behaviour.

The only way I can figure out is filling the (idAccount, idTIme, nBalance) table with the balance of every account for every day, and that seems to be a waste of resources, because there're many accounts that have very few changes in their balance over the year.

Any suggestion?

P.S. Another problem is the calculation of aggregations for nBalance. Aggregations for nBalance in the account dimension must be a sum of nBalance for any account. But for time dimension, nBalance is the value of nBalance in the last time of the time dimension.

I can figure out many "real wolrd" problems similar to this. For example, imagine that you have a solution for identifying the stock in a warehouse. There are, at least, two main measures: the ins and outs of the warehouse and the actual stock in a certain value of time.