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.