View Backup's

I have three dimension views and one fact view on top of it as below :
Broker dealer
date
product


broker dealer
broker delaer id
broker dealer name
broker dealer status


Product
product id
product name

date
date id
.......
month
day of year



fact

broker dealer id
product id
product amount
product quantity
total amount

what i need to do is store history for these views on daily basis .



i m thinking my table structure to persist history of these views will be :

broker dealer

load date--primary key
broker delaer id
broker dealer name
broker dealer status


Product
load date --primary key
product id
product name


fact
load date ---primary key
broker dealer id
product id
product amount
product quantity
total amount


please provide your input if there is any better way to do this .

Thanks

Hi,

one thing you need to looks it to the space that those backups will take, if it is large you can have an incremental load where if something has changed/ newly added then you can insert into the history table.

thanks

I guess I don't understand the issue. Isn't the whole point of a type 2 is to record a historical perspective of a dimension?

You should make your dims as SCD type 2 to keep history in dims. In your dims you need start date and end date. For example on product dim, lookup a product id where end date is null (that means it is the most current row in the dim) and compare that row values to incoming values from the source system, if values change, then end that product row with time stamp as end date and open a new row with new values from the source system. This is the design one use to keep history in DW. Please search for "SCD type 2" in Kimball tips and technique for more information.

I also don't understand the issue - especially when you say you have Fact and Dimension views.
Unless you are doing something clever with in-memory technology, you can't build a dimensional model using views. Unless the underlying tables that the views are based on follow a dimensional structure then you wont see the query performance benefits that are the (main) reason for building and populating a DM rather than just querying your 3NF transactional model