Performance question - Separating fast changing attributes into separate dimensions

hi,

i'm trying speed ad hoc cube browsing in excel 2007. have periodic snapshot fact table with mainly last child measures. fact table has about 1.8 million rows. main dimension holds account information, and has 40,000 rows. capture change scd type 2 approach.

to speed ad hoc cube browsing in excel, should separate fast changing attributes in dimension table, provided they're not queried, separate dimension table? have 6000 distinct accounts, , the 'status' attribute forcing new row.

this is how a chopped down version of dimaccount table looks currently, showing 'loantype' attribute, doesn't change, , 'status' attribute, changes frequently:

dimaccountkey      accountreference      status                               loantype            startdate            enddate

123                       1234567                   payments utd                    regulated           20111010          20111131

124                       1234567                   initial arrears stage           regulated           20111201          20120222

125                       1234567                   second arrears stage        regulated           20120223          20790606

my thinking if put fast changing status attribute own dimension table, should reduce size of dimaccount table (i may be able get close 6000 rows for 6000 accounts). should performance.

thanks in advance,

adam

hi adam,

i this:

create 2 partitions start of with. 1 currentdate , other before current(today's) date. (make sure set "slice" properties both partitions).

create aggregations on both partitions using wizard giving 30% performance gain create the initial draft. (check query perfomance after this, if you're satisfied can stop @ step. )if not ...

enable query log , run sort of possible queries might think used tune cube using usage based optimization(ubo)

download bids helper codeplex if haven't yet. 1 of great thing tool is, allows add aggregations query log table w/o dropping existing aggregations created first aggregation wizard.

you have decent idea on how users query cube. based on : review these aggregations query log , see if can adjust attribute levels described before ("day->month->year") impact query pattern. creating affective aggregations sort of art have understand business , query pattern of users. won't @ first, you'll come of set of aggregations that'll satisfy user queries.

hope give ideas.

rok

---

please remember mark answered if post helped resolve issue.

SQL Server  >  SQL Server Analysis Services