June 27, 2012 at 11:20 am
I have a cube with a single very large fact table. We add data to the table on a daily basis. About 8 months ago we redesigned our database that contains the fact table. We left the historical records in place in the original database, and added a new database to house the data going forward. The new database table has the same granularity as the old, and has a similar, but not identical, structure. I'm only now getting around to modifying the cube to accomodate the new data source (it hasn't been updated since the database was redesigned). When it's done, I'd like the cube to have the same measures and dimensions it has always had. The source data will just be split between two different databases based on Time.
I have no experience with designing a cube with multiple fact tables, and I'm wondering if this might be a good case for going down that road. I'd welcome any advice.
June 28, 2012 at 12:39 am
Multiple fact tables but with (almost) identical structure and type of data is confusing for your end users. I would merge them together in the data source view and use partitions on the measure group. One partition for the older data, one for the new data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply