Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

View Backup's

Go down

View Backup's  Empty View Backup's

Post  rajwin.kau 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

Back to top Go down

View Backup's  Empty Re: View Backup's

Post  hkandpal 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

Back to top Go down

View Backup's  Empty Re: View Backup's

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

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

http://aginity.com

Back to top Go down

View Backup's  Empty View Backup's

Post  zoom 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

Back to top Go down

View Backup's  Empty Re: View Backup's

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

View Backup's  Empty Re: View Backup's

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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