Unused dimension members in fact table produce large cubes? RRS feed

  • Question

  • Hello all,


    I was wondering if olap or local cube file generated from a (m)olap cube can be trimmer down in size. It looks like cube files (olap or offline) are always at least 30mb (in our cases)


    We have established a test situation with the following specs:

    32 bits Sql Server 2005 Enterprise edition (sp2)

    Fact table - 600 rows, 100KB in total size in sql server 2005

    5 dimensions - less than 600 members in every dimension (total dimension data 20,5 MB)

    5 measures - decimals(12,3)

    5 calculated measures - ytd and actuals versus last years



    Observed behaviour:

    Unprocessed cube size: 160kb

    Processed cube size 30mb

    Local cube files sliced on sales company  dimension (4 members in total thus 4 cubes are created) 30mb per slice. (For each sales company even when there is no data present for a sales company)


    As you can see the cube is 30MB in size regardless of any aggregations being made. Full aggregations (50 in total) or no aggregations the cube size never diminishes. This also applies to all slices and local cube files.


    I cannot get around the felling that there is a lot of wasted space involved here. Especially when taking in account the 600 fact lines. (Even with 70.000 fact lines the cube size is always 30mb)


    We have used different generation tools (even cubeslicer) and all produced the exact same output files.

    The real problem behind this is that the sales companies want their data in the smallest possible form for near real time synchronisation. (Some utilize 65k6 modems and 30mb for 600 lines is not acceptable to them which I can understand)


    Thank you all for input and if you can verify this behaviour using a small fact table for example please let me know.


    (We noticed that reducing the dimensions members using a filter in the design view will reduce the cube files dramatically. Our largest dimension date was 18mb and actually has 30 references to the test data. Using the filter reduced the cube file to 2,5 MB)

    Wednesday, April 25, 2007 8:47 AM