Daily/weekly/monthly data mart views

Hi,

We have a data warehouse consisting of six fact tables and a series of conformed dimensions shared by all the fact tables. The fact tables are transaction grain -- they represent a point-in-time snapshot. We have reports that run on weekly and monthly schedules, and these sometimes need to be re-run or drilled into at any time. In other words, on any given day, users may want to query the facts and dimensions as they existed after the last weekly build, and/or after the last monthly build.

We originally took a simple approach to this, creating separate "weekly" and "monthly" presentation databases. We simply run the ETL process once a week and once a month in the build environment, and transfer the resulting data to the appropriate presentation database. We're not storing historic records in any of these tables; everything is simply truncated and repopulated fully with each ETL run. Obviously this is costly in terms of space and ETL time. We will soon have daily reports coming, so I'd like to find a solution that allows us to meet the reporting needs, without maintaining multiple copies of all these tables in multiple databases.

How is this typically done? I'm familiar with Type2 SCD's with "row effective date" and "row end date" columns, and I've read a bit about slowly changing facts. Is that the best way to implement something like this? If I process daily and load the data into a single set of dimension and fact tables, could I still effectively recreate daily/weekly/monthly views of the data? Perhaps with separate sets of end user views returning the daily/weekly/monthly views? Or maybe with a "reference date" sent by queries?

I'd love to find out how others have tackled this situation. Any advice or ideas are appreciated! TIA...

I'm a little confused by this statement, "The fact tables are transaction grain -- they represent a point-in-time snapshot." Are you fact tables transaction fact tables or snapshot fact tables?

If your fact tables are transaction fact tables then they are already additive by date. Using the date dimension, you can sum by day, week, month, quarter, etc.

If your fact tables are weekly snapshot fact tables, then you have a complete picture of the business within each snapshot already. If you want to know how the business looked at the end of the month, you just use your last weekly snapshot for that month.

I think your peformance problems might be arising out of here, "We're not storing historic records in any of these tables; everything is simply truncated and repopulated fully with each ETL run." If you're building snapshots, you shouldn't touch any previous snapshot. Everything should be straight inserts.

BoxesAndLines wrote:I'm a little confused by this statement, "The fact tables are transaction grain -- they represent a point-in-time snapshot." Are you fact tables transaction fact tables or snapshot fact tables?

Thanks much for the reply! And sorry about the confusion - I probably shouldn't have used the term "snapshot". I was poorly paraphrasing Kimball, so I'll quote him instead - these are transaction fact tables, representing "an instantaneous measurement at a specific point in space and time".

But your point is well taken, and I'm wondering if we actually need to reconsider the fact table design here. I assumed the idea of using metadata to recreate views of data as of a given "reference date" was a fairly common requirement. Is that not so common? Has anyone addressed that need? If this isn't done regularly, we're probably on the wrong track here. I'll talk to the original data architect about the design and requirements. It may be that we're mixing grains in our fact table, maybe we have a blend of transaction facts and periodic snapshots or something like that. If we had separate daily/weekly/monthly fact tables, this wouldn't be a problem.

It seems like you should be able to create daily, weekly, monthly, views with views since all you are doing is summing by date. I would also make sure your date dimension is working correctly.