Data Warehouse Design Issue

Posted on 2013-09-24
Medium Priority
Last Modified: 2015-04-13

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.
Question by:VoiceOver

Expert Comment

ID: 39517025
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.
LVL 70

Accepted Solution

Scott Pletcher earned 1500 total points
ID: 39518327
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.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question