BW and In-Memory Storage

oday I’m going to talk to you a bit about how data is stored in BW and why proper planning for storage is important.  Let’s say for example, you have a large project coming down the pipeline and are unsure of how this new data being introduced into your production landscape will affect the BW environment.   You probably have a mature SAP installation at your company, and chances are you are using a Business Warehouse Accelerator (BWA) appliance or HANA (don’t forget to factor these in).  It’s essential to plan a strategy with the database administrators as they are directly impacted by each and every new project.  New projects mean more data being added, which means more space required within the database.
The 10% rule – BWA Sizing:
An easy way to ballpark how much memory is required to index BW data onto your BWA is to follow the 10% rule.  That means for every Gigabyte of BW data, around 100 MB will be needed for BWA storage.  Thankfully, the TREX engine that BWA runs is capable of efficiently compressing and storing of data in memory at 1/10^th the size of what BW stores it at.  This allows you to fit more data in BWA without having to worry too much about space constraints.
Database storage vs in-memory storage
Data can be stored cheaply on a disk based database versus an in-memory database.  Each type has their pros and cons.  Disk based storage is cheap, but the catch is slow performance.  In-Memory is fast, but extremely expensive.  That’s why it’s important for companies to properly plan for and balance out their data distribution to only index into memory the best candidates.
BWA and the 50% rule
BWA is made up of a series of blades.  Let’s use HP’s 36GB blades for examples sake.  If your appliance has 14 blades, you would have 504 GB of space for data storage, right? Wrong.  BWA has its own 50% rule.
Per SAP’s best practices, only half of each blade can be filled with data meaning only 18GB of each 36GB blade is usable for indexing.  18GB*14 blades = 252GB.  The other 18GB is used for processing and computing.  As you creep over the 50% index storage number, expect to see performance degrading exponentially.
Our BWA is full, now what?
RAM within BWA is very, very expensive.  Unless everything is being used equally within the BWA, I would recommend performing an As-Is assessment of all indexed InfoCubes and remove the least utilized InfoCubes to free up space for more popular cubes.  RSDDBIASTATUSE is a handy BW table that stores the execution history of queries against the BWA by cube.  Take a look at query execution history to better identify the slackers.  You will be surprised by how much space you could easily regain by removing a few hogs.
How much space is a cube taking up on BWA memory?
Transaction TREXADMIN allows you to view exactly how much space a table is taking up on BWA.  Focus solely on the F table which consists of both the E and F tables from the BW side.  There is no E table on BWA, just F.
Firstly, we type in an InfoCube we know is indexed in BWA.  In this example I’m using cube 0PCA_C01.  Navigate to the Index Admin tab and type *0PCA_C01* in order to pull all relevant tables for this cube.  Now focus on the F table and the Memory Size column.  This cube is taking up 211,292 KB or 0.2 GB of BWA’s total memory.

How much space is a cube taking on BW disk?
Transaction DB02 allows you to view size at a database level.  Go into BW Analysis and access both the E and F cube object areas.  Below you can see each row from each area.  If you combine both of these, you will have around 3.5GB of data.

As you can see the compression ratio can change drastically from cube to cube.  3.5GB of data on BW for this cube only takes up 0.2 GB on BWA!  Feel free to leave comments below and share with your friends and colleagues.