My warehouse data is split across 3 databases on the same server. While pulling into a cube, I am essentially doing this:
EDIT: Changed to UNION ALL based on suggestions
SELECT * FROM DB1.dbo.Fact_Pres
UNION ALL
SELECT * FROM DB2.dbo.Fact_Pres
UNION ALL
SELECT * FROM DB3.dbo.Fact_Pres
Should I actually consolidate the data into ONE table? Will that make my processing any faster?
One thing that might help. UNION looks for, and eliminates, duplicate rows. A bit like SELECT DISTINCT. If you just want all the rows, use UNION ALL which will cut out this step and that should make it faster.
I doubt whether consolidating into one table would make much difference, though, if the databases are all on the same server.
Yes it will definitely improve the cube processing perforamnce if the whole data is in a single table. you can test the exact time difference by just processing once with teh query, and dumping the records and poiniting it as the cube data source.
In this case you will get three table scans, but if you add a where clause you could benefit from index seek (clustered), and if you do you may gain more performance from three tables, but that depends on the index depth, index selectivity, statistics etc. And remember, it is easier to rebuild three small indexes rather than one large.
Also keep in mind that "Auto update stats" for an index will execute when 20% of the data in the table is changed. 20% of 100 000 000 records is 20 000 000, but 20% of 33 000 000 is only 6 600 000 records. ( a little simplified )
Some of my large table doesn't get the statistics auto updated because i never pass the 20% limit, and the performance suffer from not having a correct statistics.
I know this answer is not exactly what you are looking for, but the lesson learned is that smaller tables are easier to maintain.
If you decide to merge them into one table, I recommend you to look at the new partitioning features in SQL server 2008. By using a partitioned table you could benefit from multiple threads to read data from the table, especially if you can place your partitions onto separate disks.
