Physical Implementation of Data Marts Using Conformed Dimensions
2 posters
Page 1 of 1
Physical Implementation of Data Marts Using Conformed Dimensions
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?
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
Re: Physical Implementation of Data Marts Using Conformed Dimensions
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?
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?
Re: Physical Implementation of Data Marts Using Conformed Dimensions
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
Re: Physical Implementation of Data Marts Using Conformed Dimensions
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.
Similar topics
» Data Marts, Conformed dimensions and Data Warehouse
» Conformed Dimensions Schemas and Data Marts in 1 DB w/ diff. SLAs - advice requested
» Conformed Dimension Processing Multiple Data Marts
» Enterprise Date Warehouse/Bus Architecture Physical Implementation
» Data Marts Built Upon Data Marts
» Conformed Dimensions Schemas and Data Marts in 1 DB w/ diff. SLAs - advice requested
» Conformed Dimension Processing Multiple Data Marts
» Enterprise Date Warehouse/Bus Architecture Physical Implementation
» Data Marts Built Upon Data Marts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum