Physical Implementation of Data Marts Using Conformed Dimensions

View previous topic View next topic Go down

Physical Implementation of Data Marts Using Conformed Dimensions

Post  Skeeter on Thu Aug 22, 2013 3:18 pm

I'm working on my first major data warehousing project and I wanted to see if anyone could help me with a basic architecture question.  

I have been working on creating a new data warehouse and have started with a data mart related to Sales.  Ultimately, we will probably end up with many more data marts as we address each business process.  After having designed the dimensions and fact tables, I am starting to look at how I should translate my design into a physical architecture.  I was thinking that the correct way to handle this would be to create 3 databases on my server.  The first would be a database for staging/ETL, the second would hold copies of all of the master dimensions (Customer, Date, Product, Branch, etc.), and the last would be for the data mart related to the business process that I have modeled.  I would load the data mart database with physical tables for the facts and create views in the data mart database that look back into the master dimension database to get the relevant information for each dimension.  The only other way I could think to do this would be to copy all of the dimensional information into the data mart, but that seems like it would be a needless duplication of data that would leave room for synchronization issues. Is either of these the correct strategy?  If not, could someone help me understand what the best practices around physical design and architecture would be?

Skeeter

Posts : 2
Join date : 2013-08-22

View user profile

Back to top Go down

Re: Physical Implementation of Data Marts Using Conformed Dimensions

Post  ngalemmo on Thu Aug 22, 2013 3:47 pm

There is no reason to copy dimension tables. A view or synonym should suffice.

The other option is simply put fact and dimensions in the same database and leave it at that. When everything is sitting on the same server, it's not clear that splitting dimensions and facts across databases provides any real advantage. Are there reasons for the split?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Physical Implementation of Data Marts Using Conformed Dimensions

Post  Skeeter on Thu Aug 22, 2013 3:53 pm

Thanks for the quick reply.  I was thinking that it would be easier to maintain each of the data marts if they were on seperate databases (backups, restores, etc.)

Skeeter

Posts : 2
Join date : 2013-08-22

View user profile

Back to top Go down

Re: Physical Implementation of Data Marts Using Conformed Dimensions

Post  ngalemmo on Thu Aug 22, 2013 4:43 pm

How much data are you talking about? If you back up facts separate from dimensions you run the risk of losing referential integrity if a restore is not done properly. It becomes a manual control effort rather than letting the database do what it does best.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Physical Implementation of Data Marts Using Conformed Dimensions

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