cancel
Showing results for 
Search instead for 
Did you mean: 

Dimension Table size greater than Fact Table Size in SAP_INFOCUBE_DESIGNS

KamalMehta
Advisor
Advisor
0 Kudos

Hi Experts ,

SAP_INFOCUBE_DESIGNS shows that the size of the Dimension Table Size is greater than that of Fact Table Size .

Its only 1 dimesnsion that is showing this behaviour and it contains Characteristics Sales Document Number , Sales Document Item , which means Info Cube Contains Item Level Information as well.

Analysis :

1. Cannot Mark the Dimension as Line Item because of more than 1 charatceristics in it.

2. Can i mark it as High Cardinality ? Would it have any impact ? Also as per my understanding this would only chnage the index from BITMAP to BTREE and would help only in Non Oracle Based data bases. Ours is an Oracle one so dont think it would help? Please suggest.

3. Going for Remodeling also wont help because if i keep both the Sales Document number and Item Dimension it would add more entries not less.Please suggest.

4. Need to know does this ( higher %ages of  dimesnsions in SAP_INFOCUBE_DESIGN)  this have any impact on the performance of the report.

Note : The Info cube has been accelerated to BWA Environment( Accelerator) so haven't recieve any performace related issue on the same fromm user.

Please suggest what all we can do to reduce the size of the dimension tables so that SAP_INFOCUBE_DESIGN doesn't show higher percentage for the concerned dimension.

Regards

Kamal

Accepted Solutions (0)

Answers (6)

Answers (6)

umashankar_poojar
Active Contributor
0 Kudos

Hi Kamal,

      I do agree with experts and their solutions. If issue exists even after trying all above said, I would like to suggest.

  • Create year based InfoCubes on top of existing(if current cube is the only one contains all years data) and move respective year data into newly created cubes.
  • Create a MultiProvider on these cubes and move all existing queries to this.

By reducing/logically partitioning the existing cube, I'm sure even your dimension table will be reduced.

Thanks,

Umashankar

KamalMehta
Advisor
Advisor
0 Kudos

Thanks all for your replies .

As the affected dimension is Sales Dimension which contains Sales Document Number and Sales Document Item , Sales Document Type so even if i go for remodeling but i can't keep the document number and document item in separate dimensions because it is going to add more entries rather than reducing them.

Therefore , I am not very sure whether this would help or not .

Also can you please clarify the below as well:

Can i mark it as High Cardinality ? Would it have any impact ? Also as per my understanding this would only change the index from BITMAP to BTREE and would help only in Non Oracle Based data bases. Ours is an Oracle one so don't think it would help? Please suggest.

Regards

Kamal


former_member182470
Active Contributor
0 Kudos

Hi,

You are right. High cardinality will decide about the B-tree or Bitmap indexes by based on your database.

I suggest you to keep Sales Document No in a separate dimension and mark it as Line item dimension.

Create separate dimension for Sales Item and mark it as Line Item dimension too. Do not mark cardinality settings for both dimensions.

Keep Sales Document type in any other dimensions. This way, your cube will be optimized well.

Nothing to worry about creating two line item dimensions in a cube. No problem will occur,infact the design will be perfect.

Regards,

Suman

KamalMehta
Advisor
Advisor
0 Kudos

Hi Suman ,

Thanks .

If i keep create Line Item Diemsion for both Document number and Item because as per Info Cube design considerations if we dont keep similar characteristics in to 1 dimension its going to add more number of entries and performance is going to be degraded.

Please check and suggest .

Regards

Kamal

former_member182470
Active Contributor
0 Kudos

Hi,

I got your concern. You are just saying about a standard methodology. Reality is different. We should not keep deep granular chars into a single dimension. Keeping the relevant chars under one dimension is the least priority while designing a Cube.

I have several cubes designed in the same fashion. All my cubes have good ratio of dimension and fact tables in SAP_INFOCUBE_DESIGNS.

So, you can go ahead with my suggestion.

Regards,

Suman

KamalMehta
Advisor
Advisor
0 Kudos

Hi Suman ,

Thanks.

I would check and update later .

I think i  have to delete and reload all the data while going for remodeling followed by accelerating it to BWA Environment which in turn would account for lot of effort in terms of time and money which needs to be checked first .

Thanks all for your help.

Regards

Kamal

former_member182346
Active Contributor
0 Kudos

Hi,

Keeping the similar infoobjects in same dimension is valid point but as this should not affect the design of cube.

Say you have 100 Sales Order and each Sales Order has 10 line item, so it you keep both of them in single dim. then there will be 100 x 10 = 1000 recs.against keeping separate like each will have 100 and 10 respectively.

Now making it Line item dimension means the value being directly posted on fact table of cube so that performance can be increased.

Suman,

I still not convinced, will it be beneficial to keep line item dimension for sales item as well since the number of records for item is not exponential.

Thank-You.

Regards,

VB

former_member182470
Active Contributor
0 Kudos

Hi Vinod,

Each sales document no. can contain several sales items, means sales items char is a very granular char. So we have to keep it in a line item dimension. My suggestion will work 100%. I have similar scenarios in my prod system.

Regards,

Suman

former_member182346
Active Contributor
0 Kudos

Hi Suman,

Thanks for reply.

But in actual scenario, its not like sales document whose number can increase day by day. A single Sales Doc can have say 100 line item (somebody can create say 500 items) so the number of sales item is limited vs the sales doc.

We do keep sales doc as line item dim and keep value directly stored in fact table of cube but does the line item values really we need to keep as well, still question in mine mind.

I have no doubt about your suggestion Sir, it will work 100% sure .

Thank-You.

Regards,

VB

RamanKorrapati
Active Contributor
0 Kudos

Hi kamal,

If users are facing any performance issue while retrieving data from this info cube, you may need to think about remodeling your cube design. if you have chance to create one more custom dimension, create new dim and add doc num and remove doc num from old dim.

even if no problem at reporting side, you may face this  problem in future. because going on days data volume may increase.

Check with concern person and suggest about remodeling then go forward accordingly.

Thanks

former_member182346
Active Contributor
0 Kudos

You have to make the sales doc as line item dimension by removing the line item object from that dimension and try to accommodate item into some another dimension or create new for it.

if Dimension having only item in it, need not to be created as line item dimensions as number of possible item will not be huge.

So in my opinion, its good to remodel by making sales doc as line item dimension.

Thank-You.

Regards,

VB

former_member202718
Active Contributor
0 Kudos

Hi Kamal,

If it realy not affecting the Query Performance/Data load Performance then you can park it for time being.

The only other option is to ReModel it.

Rgds

SVU123

former_member209032
Active Contributor
0 Kudos

Hi,

  Redesigning or remodelling are the only options available. if u have all the data in the base DSO then drop the data in the cube re-design the dimension and reload it. Anyway since this cube has been pushed to BWA there will not be any perrformance issue.

Regards,

Raghavendra