Skip to Content

Over the past couple years I have been involved in many sizing activities for BW on HANA. I have collected some valuable lessons learned. This should help you understand better what common mistakes to avoid and how the sizing works in more detail.

Note: This blog is specifically for sizing existing BW systems. For sizing brand new installations, we recommend using SAP QuickSizer (http://service.sap.com/quicksizer). Also if you are planning to run multiple applications on a single SAP HANA database (in addition to BW), you have to size these applications independently and added them together.

The following is based on an actual customer case. The customer says their BW system is 6.1 TB. Quick, takes a guess of how big the SAP HANA appliance needs to be…. Well, I hope you gave the proper “consulting answer” that you need more details. But let’s see if you would have taken all of the following into consideration.

1) Do not count log and free space

In this case we were dealing with BW running on a Microsoft SQL server database. The 6.1 TB was actually the total size of the data and log files. Ha! Beginner mistake. Clearly only data files excluding the free space are relevant for HANA sizing (=allocated space). I’m sure everyone got that right.

Slide2.PNG

2) Do not count indexes

Next we look at the allocated space. Much of it is taken up by database indexes and some database-specific administration data. We are really interested in the pure database tables only. In our case study, over half of the allocated space was indexes! HANA persists data differently, so we disregard all indexes.

Slide3.PNG

3) Do not count aggregates

BW on HANA does not need aggregates. All related menu items have been removed and aggregate functionality is disabled since SAP HANA can aggregate billions of records on the fly.

4) Do not calculate with size of compressed data

We are down significantly from the original 6.1 TB but here’s a surprise… The MS-SQL database was compressed already. We need the uncompressed data into consideration. However, we need to do it right. Don’t rely on any compression factors provided by the database. It’s hard to understand how these were calculated. They are irrelevant anyway since SAP HANA compresses differently.

Here’s an example, you have a table with char(100) field and 1 million records. Some might say that’s 100 million bytes of uncompressed data. But this again is not enough information to determine the size of the table in HANA. It matters what data is actually stored in the table! If a record contains “ABC”, we need 3 bytes and not 100. Ergo, what you really need to do is look at all the data in the table.

Reading the complete database would be rather time consuming so we take some shortcuts: We sample only a small subset of the records and extrapolate the results. And we can exclude reading fields that have a fixed size like INT4. In the example, we select a sample of 100,000 records and determine an average field length of 10. This gives us an uncompressed, raw data size of 10 million bytes.

Your system is not on Unicode yet? Typically, there’s not much textual information stored in BW (this will certainly change over the coming years) so you need to apply an uplift of about 5% (based on experience).

If we follow this process for all tables, we get about 3.2 TB of uncompressed data that will need to fit into our SAP HANA database. Grouped into the usual BW categories, it looks like this:

Slide4.PNG

5) Do not apply the same compression factor to all tables

Sure, HANA does some incredible compression of data but it’s not the same for each type of table. For example, InfoCube fact tables contain integer values only (pointers to dimensions or master data) and we usually find only few unique values per column. These will therefore compress very well. We assume factor 7 based on experience with other customer systems.

For master data tables, the opposite is true. Mostly all values in a column are different (not so much for attributes of master data of course). Overall, we will get little compression (1-2x).

6) Do not assume the complete table must be in memory

We also have to take into consideration that many tables are partitioned by default in BW on HANA and only the used partitions will be loaded into memory. Best example are PSA tables which are partitioned by request id. BW usually works only with the most recent requests and therefore much of the PSA table will not be loaded. This is the default behavior for PSA tables and write-optimized DataStore Objects.

Optionally, we can apply the “non-active” data logic to other InfoProviders as well (if you configure BW accordingly).

The result in our case study: We end up with approximately 925 GB of memory once the data is compressed inside HANA.

Slide5.PNG

7) Do not forget room for server processes, caches, and processing of data

The database software needs to run in memory of course. For SAP HANA we assume about 50 GB of memory are required for these server processes and related caches (per node).

What’s more essential is to leave amble room for dynamically memory allocation for processing your business data. There are two main cases: merging new data into a column store table (so called “delta merge”) and processing end-user queries. We don’t want to run short on memory for these.

In general we double the size of the business data to include enough room for processing. (Note: We don’t double for tables marked as “non-active”).

8) Do not assume the total amount of memory is the same for different size of server nodes

Finally, we need to distribute the data to server nodes. Today we have nodes with 512 GB or 1 TB available. First, we need to take into consideration that only certain tables will be located on the master node. This includes all tables of the row store plus some other administration tables located in the column store (another mistake is thinking that the master node is only for the row store).

All business data however is distributed across the available slave nodes. Since the master node is not used for business data and we need some amount of memory for server processes and cache per slave node, you can end up with different total amount of required hardware.

Slide6.PNG

Slide7.PNG

In our case study, the result was either 5x 512 GB nodes or 3x 1 TB nodes.

9) Do not forget to size themaster node properly

As mentioned above, the master node will contain certain tables as a default configuration. Therefore you have to make sure that these tables will fit into the given size of the master node… including future growth of these tables. If you follow best practices for housekeeping in BW, this is not a problem at all. However, if you are a bit lax on housekeeping, you might run into the restriction of the master node.

For a one time clean up and frequent housekeeping for row store tables, please see SAP Note 706478.

The most common areas for BW customers are:

Additional details on sizing the master node, see SAP Note 1855041.

10) Do not ignore means for system size reduction

So far we assumed that the complete system is migrated as is. However, there are things to consider that will reduce the over size of your system significantly:

  • Removal of complete data layers, for example reporting off DSO instead of an InfoCube with the same or just aggregated data
  • More aggressive housekeeping of PSA, change log, and DTP error stacks
  • Usage of near-line storage solution (NLS)
  • Revisit data retention strategy and archiving

For more details see “Optimization of Data Retention“.

Finally…

Now you know how to avoid these pitfalls. Did you get them all? Of course it would be extremely cumbersome and time consuming to calculate all of this by hand. But here’s the best news: We have a program available that fully automates the complete sizing calculation!

For the one and only way to size a BW system properly for SAP HANA, please see SAP Note 2296290 and its attachments.

To report this post you need to login first.

24 Comments

You must be Logged on to comment or reply to a post.

  1. Phil Gleadhill

    Hi Marc,

    Thanks for sharing. One comment. It might be worth ensuring your very last very important sentence is highlighted and put in Bold Font! 🙂

    Namely:

    “For the one and only way to size a BW system properly for SAP HANA, please see SAP Note 1736976 and its attachments.”

    We have just run that BW HANA sizing report in our Production BW and it has produced some interesting results. If it can be believed, it is a very useful tool.

    Regards, Phil G.

    (0) 
    1. Phil Gleadhill

      Hi Mikhail,

      Thanks for the comment. Just to clarify, could you please comment on this? Below is the summary section from the SAP report /SDF/HANA_BW_SIZING:

      |SUMMARY                                                                       |

      |=======                                                                       |

      |                                                                              |

      |                                                                              |

      | ABAP Size Row Store:             45.0  GB.  No. of tables:     1870          |

      |                                                                              |

      | ABAP Size Column Store:         169.1  GB.  No. of tables:    37409          |

      |  Thereof:                                                                    |

      |          InfoCubes        6.4 GB                                312          |

      |           Std. DSO       46.5 GB                                494          |

      |        Change logs       11.9 GB                                206          |

      |            w/o DSO       42.9 GB                                 95          |

      |                PSA       30.5 GB                                976          |

      |        Master Data        4.0 GB                               8207          |

      |         Aggregates: (not counted)                                21          |

      |                                                                              |

      | TOTAL:                                                        39300          |

      |                                                                              |

      |                                                                              |

      |MINIMUM SIZING RECOMMENDATION – CURRENT                                       |

      |=======================================                                       |

      |                                                                              |

      |                              Phys. memory per node:     512 GB     1024 GB   |

      |——————————————————————————|

      | Memory Requirement (Minimum Total):                     205 GB  |   205 GB   |

      | Disk Space Requirement – data (Minimum Total):          205 GB  |   205 GB   |

      | Disk Space Requirement – logs (Minimum Total):          205 GB  |   205 GB   |

      | Number of Nodes incl. master  (Minimum Total):            1     |     1      |

      |——————————————————————————|

      Does your comment mean that using the above example, we should allow 5-10 times the 46.5 GB for Std. DSO on disk when sizing the required RAM for HANA?

      If so, that doesn’t match up with the overall sizing recommendation for HANA RAM in the report, which is only 205GB. Or have I misunderstood?

      Cheers & Thanks, Phil G.

      (0) 
      1. Mikhail Budilov

        HI Phil.

        Peak using RAM – Take your most biggest DSO and make 5 (10) * DSO on storage size for activation.

        Be very carefully with sizing. Involving your hardaware partners, service partners and SAP.

        In your case sizing (as example) + some reserve (for organic growse by example)

        1. Master node – 512GB RAM (but limit memory for HANA – 4 * 64GB)

        2. Worker1 – 512GB RAM (but limit memory for HANA – 4 * 64GB)

        3. Worker2 – 512GB RAM (but limit memory for HANA – 4 * 64GB)

        4. Stand by – 512GB RAM

        Total licensed memory in this case will be: 12 * 64GB = 768GB.

        (0) 
        1. Phil Gleadhill

          Hi Mikhail,

          Thanks for the reply.

          Boy, looks like our 205GB minimum RAM just got blown out big time! Makes sense though when you include worker nodes and a standby.

          We are still making our hardware selection for BW on HANA, so this information all helps.

          I am a bit confused about your comment on limiting the HANA memory per node to 4x64GB though. That is not what is mentioned as far as I can understand it in the sizing instructions attached to note 1736976?

          Thanks. As you say, we will be working with the selected hardware partner, HANA consulting partner and SAP to finalise this, and conducting a trial first.

          Cheers, Phil G.

          (0) 
          1. Mikhail Budilov

            Memory Requirement (Minimum Total):                     205 GB

            😉

            > I am a bit confused about your comment on limiting the HANA memory per node to 4x64GB though.

            >> Be very carefully with sizing. Involving your hardaware partners, service partners and SAP.

            Don’t be hesistate to ask them questions.

            (0) 
  2. Ashok Babu Kumili

    Hello Marc Bernard,

    Amazing blog.. This is a brilliant way to go. Great read… Indeed, Very helpful. It is very helpful for articulating the possibilities and building solutions….Very nicely written article I’ve learnt the key message . . . …

    Regards-Ashok

    (0) 
  3. Karl Mailman

    Hi Marc,

    Very much liked the post.  You have included some straightforward and easy to follow steps.  Nicely done.

    I have a question about reading the output of the BW sizing report vs. the ones created for Suite on HANA.  The Suite on HANA reports include a calculation for Work Space memory size which is roughly equivalent to the Column Store value, and seems to comprehend the 50/50 allocation ‘guideline’ for data vs. work area memory.  However, I do not see any similar Work Space value on the sizing report for BW.  Is this an oversight?  Should the 50/50 guideline still be applied to BW as an amount beyond the calculated value?  Where does the additional memory mentioned in Step 7 above get reflected in the sizing?

    Thanks,

    Karl

    (0) 
    1. Marc Bernard Post author

      Hi Karl,

      the work area is included in the total already. You can see it in the “Sizing Details” section as the difference between the “data” values and the “total” values. So whatever number of nodes you get as an output is really what you need (as a minimum).

      Best,

      Marc

      Product Management SAP EDW (BW/HANA)

      (0) 

Leave a Reply