October 5, 2007 at 11:21 am
I currently have a dimensional database which contains data for specific part of the company. Now we are developing a second one and need to be able to integrate them using shared dimensions.
I see 2 basic options here.
1. Have multple databases with a separate dimension database that acts as the master and keeps all the dimensions accross all databases in sync. or
2. store all fact and dimesion data in a single database using schemas for organization / security and using multiple file groups to keep the data separate at the physical level.
There will be multiple SSIS jobs feeding the database.
I feel option 2 would be a better plan, any thoughts on this? - my main concern is backup and recovery.
Thanks,
October 8, 2007 at 6:58 am
Yeah, I too feel that the second options is better.
October 9, 2007 at 4:34 pm
Allot would depend on your security standards, data volume, input sources, reporting or other usage of the database(s).
Do you want more of a data warehouse, or data marts.
Do you have security restrictions on dimensional data? Fact data?
Will your dimensions be loaded/updated from two or more separate systems?
Do you have conformed dimensions?
October 10, 2007 at 10:25 am
Number 2 is the best option to do.
October 10, 2007 at 1:34 pm
We want a data warehouse for all data that could in any way be reported on together.
I work for an airline and flight data should be able to be queried with parts inventory or finance, etc...
We are working to have a set of conformed dimensions.
We currently don't have much for security restrictions but that will change as we add the financials, for both fact and dimensional data.
Dimensions will be loaded from multiple sources.
My major concern is restoring a portion of the data warehouse. The only solution I see is to restore the entire warehouse to a separate database then manually restore the needed data.
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply