DIMSFORFACTTBLINDEX

[fabian_bistram2]

Hi,

I have a short question about DIMSFORFACTTBLINDEX.

Normaly BPC has the for the index choosen the following columns: CATEGORY,TIMEID,UNIT,ACCOUNT,CURRENCY.

We have a facttable that has the following columns: CATEGORY,TIMEID,UNIT,ACCOUNT,CURRENCY + GROUP, DATASRC, FLOW, INTERCO.

My question now is does it make sense to change the DIMSFORFACTTBLINDEX to all these columns?

I would think of GROUP,CATEGORY,TIMEID,UNIT,ACCOUNT,CURRENCY,DATASRC,FLOW,INTERCO.

What would be the benefit on doing this change?

Perhaps it help to tell how many members we have per dimension:

CATEGORY: 82 MEMBER

TIMEID: 121 MEMBER

UNIT: 472 MEMBER

ACCOUNT: 1124 MEMBER

CURRENCY: 9 MEMBER

GROUP: 87 MEMBER

DATASRC: 148 MEMBER

FLOW: 178 MEMBER

INTERCO: 270 MEMBER

Of course not all of them are filled wit data in the same way but maybe it helps.

Kind regards,

Fabian

[former_member186498]

Hi Fabian,

UNIT is Entity, isn't it?

Normally, if you don't have tens of millions of records changing this parameter don't give visible changes in performance but if you set badly the fields you can also decrease performance.

"The default fields for the index are:

Category, Time, Entity, Account, RptCurrency (see please Appl. Parameters) so your beginning values are the same with just more fields added at the end. You can try leaving blank (so it works with default index) and see if the performances has visible changes.

I think that other factors, hardware and software can increase more the performances, e.g. reducing or eliminating MDX logic, scheduling optimize, redesigning reports, etc. see please http://scn.sap.com/docs/DOC-4143 it's old but still valid.

Regards

     Roberto

[fabian_bistram2]

Hi Roberto,

thanks a lot for your answer.

We have over 60 millions of records in the fact table. Thats the reason why I started looking into it.

We do not have any MDX and a full optimize run every night and a light optimize every second hour.

I will test a little further.

Kind regards,

Fabian

[former_member186498]

Hi Fabian,

Fabian Bistram wrote:

We have over 60 millions of records in the fact table. Thats the reason why I started looking into it.

if you have only 9 dims it should be not a problem. Which SQL server version are you using?

Normally account and entities (unit?) are always present and account have a good granularity, so if you want you can try to test with account, unit, timeid, category, flow, group (if these dimensions are always present).

Regards

     Roberto

[fabian_bistram2]

Hi Roberto,

of course you are right with entities I mean Unit. We have SQL Server 2008R2 installed.

In general most of the dimensions are present. On INTCO the granularity is extremly poor. One member makes 85% of data. DataSrc has a not good granularity as well 5 members makes 80% of data.

I will give that a try and then we will see what the outcome is.

Thanks a lot for your help.

Kind regards,

Fabian

[former_member186498]

Hi Fabian,

yes you're right with Intco, looking at the statistics on the DB you can choose which dimensions gives the best granularity to build the index.

Regards

     Roberto