How does the In-Memory DB work, and will it make my reports run faster

Yellowfin ships with an In-Memory DB? (also known as the Columnar DB) that can be used for caching views.Each view in which you want to cache must be configured manually.

Caching your entire view though may not actually improve performance, and can actually increase the original size, making it slower. So it's important to understand how views are cached in order to decide if this will be beneficial.
NOTE:It is also possible to cache views into another writable data source (e.g. your own SQL , PostrgreSQL DB)

So how does it work?

The query for an entire view is run on the source database, and then all of the data is dumped into a flat table in a target database. Essentially the view data has been completely denormalised and moved to a new database (depending on the data, this database may actually be bigger in size). Yellowfin's Inmem-DB can compress the data, and if in particular formats (with the values repeating often, etc) it performs extremely well, so this flattened table can actually be smaller than your orignal view. This means that if a column contained the word "Apple" 100 consecutive times the word itself would only be stored once along with the fact that it was reoccurring in the next 99 rows.
Strings of a certain length are also stored in a lookup table, so when they appear in the data they can be replaced by their index within the table. This can result in very good compression when long strings occur repeatedly.

If the view is flattened, how does this help?

Since retrieving data from a denormalised database (flat table) is generally faster, this helps speed up the report generation.
Reports which are run on this view will now reference the flat table.

How much space should I assume the cached view will take?


Generally we'd estimate the amount of memory required to store the view as being similar to the size on disk of the tables used in the view - although the denormalisation required for caching can cause this to increase.
You could also cache the view into your own database e.g. SQL Server, which means it will actually use hard disk space and not memory allocated to Tomcat, though this may not improve YF performance much.

After understanding the above, you should now have an idea if this something that will actually improve perfomance.
A good example on how the IMDB can help performance is when you want to specify lookup fields which are dimensionIDs to help the joins since dimension keys are mostly integers. (see example link below)

If you have any questions or concerns, please email

support@yellowfin.com.au

Is article helpful?