Skip to Content
author's profile photo Former Member
Former Member

Size of Fact Index and Fact Table

Hello Expert,

I've one doubt when we would compare size of Fact Index and Fact Table.

In our system , we have implemented one weekly cube where in we usually load daily data into it. This cube is loaded with process chain and has two aggregates created on top of this cube - we have also moved this cube onto BWA for faster reporting.

I check size of Fact Index and Fact Table weekly and found size of fact index increaes 10% week-by-week. Usually , size differ if we would delete data from cube but forget to synch with BWA but in this case we are not deleting any data from cube.

Later I analyzed that process chain by which I upload data into cube is first doing roll up and then it is doing compression so it might be possible that during roll up data get roll up into BWA and later same cube is compressed , thereby reduce actual fact table size but fact index remain same therefore , it may lead to size difference always.

Is my understanding is correct ?. if yes , would it be possible to do compression first then do roll up ?. Have you faced this situation before ?.

Thanks,

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 14, 2013 at 07:14 AM

    This looks like a normal behaviour.

    You can not perform the Rollup after compression as Rollup is required before compressing the data.

    To revalidate the size of the index and the data, try to rebuild the BWA for your cube and then check the results to see if the same appears?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 08, 2013 at 05:09 PM

    Hi ,

    Firstly you cannot do Compression before Roll up.

    Index size on BWA is always going to increase as we are doing roll up on daily basis. It means we are making all the data existing in Info cube available on BWA as well.

    Whenever Query gets executes it would search in the following manner:

    Cache >> BWA >> Aggregates >> Info cube relevant Tables

    Fact Table size is always going to reduce as you are doing compression on daily basis ( E table size going to increase daily basis).

    Hope it clarifies .

    Thanks

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 10, 2013 at 08:27 AM

    Hi,

    BW and BWA both has different storage mechanism (as you are using BWA, surely no HANA 😊).

    As I understand, though you compress BW cube or not, it will not impact size of BWA rolled up data. BWA does it own compression.

    Try one thing - drop all BWA indexes from cube and do a fresh create indexes instead of roll up. And check if the size gets in sync. When you are compressing the records in BW cube, where all keyfigures are zero - that record gets out of the cube. But in BWA i think it gets stay there with a deletion flag and does not get reported. Same deletion flag is set up for request when you do a request base deletion from cube. And these marked data gets deleted once you drop entrie indexes and rebuild. Or please check once with BWA Admin guys, there is one program also to clear such entries. May that also helps.

    Hope this will help in clarifing.

    Regards,

    Anil

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.