Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
qwert
Starting Member
2 Posts |
 Posted - 2013-05-29 : 03:25:20
|
| Completed subject:Any solution for better performance about a cube with 3 distinct counts on the same source DB table on SQL Server 2008 R2?Backgrounds:I have a big table about 180G for size and 4 billion rows on SQL server 2008R2 64bit so far, and growing much very day.So far, we partitioned source DB table by ColumnA, and we have 3 distinct count on ColumnA, ColumnB and ColumnC, then we have 3 measure groups(as some sqlserver&doc required), for example, MG_A, MG_B and MG_C for each distinct count, and we want to partition MG_A, MG_B and MG_C. The final goal is that we can build each measure group partitions incrementally.Goal:Better performance for building cube with these 3 distinct counts on the same source DB table.Question 1:For MG_A, ColumnA is OK, source DB table and MG_A are all partitioned by ColumnA, but how about MG_B and MG_C?If source DB table is partitioned by ColumnA, and MG_B is partitioned by ColumnB(this is recommended by some docs), then these two partitions are not aligned one by one(Alignment is required by some doc), then any impact on performance?Question 2:Any idea for better performance of cube building?Since 3 distinct count is based on the same source DB table, any chance to improve the performance of cube building? otherwise, 3 times work to do just as 3 distinct counts on 3 DB tables.Question 3:If we partition measure group NOT by distinct column, for example, partition MG_B by ColumnA, instead of ColumnB, is this a correct solution? That means, is it mandatory that partition MG_B by ColumnB?Question 4:If we partition measure group by distinct column, any chance for only incremental processing? For example, just build the latest partition? Otherwise, we have to re-build all MGs from the scratch. It's too slow.Because I saw some doc said for distinct count aggregatonfunction, you have to re-build all partitions.I know too much questions here, but one depends on another one.All in all, any suggestions about distinct count and incremental cube building?Thanks. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2013-06-25 : 03:58:42
|
| Its probably best to partition your measure groups tables on a date column, this will make incremental loading a lot easier.Have you tried creating views for the 3 tables that query off the large sql table using clever joins to make the best use of the tables indexing, thereby creating faster loads?Duane.http://ditchiecubeblog.wordpress.com/ |
 |
|
|
|
|
|
|
|
3 distinct counts on the same source DB table?