Data Warehouse Design Issue

Hi,

In my (SQL Server 2008 R2) data warehouse I have a fact table with non-additive facts. The business should be able to look at the current values at any point in time.
Is there no way to achieve this except to store every value every day whether there have been changes or not for an account?
The table holds account balances of all accounts and the business should be able to look at any given day and see what the balances are of various accounts.
The data is used (unfotunately) not with Analysis Services so any mdx functions I cannot use.
They will use a flat reporting table that will receive its data from the data warehouse.
My idea now is to store only changes in the data warehouse and store every day balances per account in the reporting table.
This would obviously mean a pretty big reporting table. Is this the only way to go about it?
Thanks for any input.
VoiceOverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ggzfabCommented:
You don't have to store the info, just a query would do.

I solved a similar case by joining the fact table with the date table (Holding all dates needed) without a constraint, thus getting the cartesian product of both tables. By adding a SUM(balance_value) where data <= date from joined date table you get the accurate balance value on that particular day.
0
Scott PletcherSenior DBACommented:
If you're on Enterprise Edition, I'd definitely go ahead and store all the data, since you can use page compression on the table to dramatically reduce the data stored.

You could just store the change days, but the queries against it will be much more difficult to write, use and maintain.  I'd strongly suggest using views to get the correct data, so that you don't have the logic to "explode" missing days in multiple places, just in a single view definition.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.