View Backup's

View previous topic View next topic Go down

View Backup's

Post  rajwin.kau on Fri Oct 09, 2015 7:10 pm

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

rajwin.kau

Posts : 4
Join date : 2015-08-25

View user profile

Back to top Go down

Re: View Backup's

Post  hkandpal on Mon Oct 12, 2015 9:57 am

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


hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: View Backup's

Post  ngalemmo on Mon Oct 12, 2015 1:46 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View Backup's

Post  zoom on Tue Oct 13, 2015 7:43 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: View Backup's

Post  nick_white on Wed Oct 14, 2015 10:59 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: View Backup's

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum