Although in-memory database structures have been around for a while, they are getting more traction in the Microsoft Dynamics AX world thanks to the efficiency they achieve over traditional query-based engines. They offer the ability to take snapshots of table data for use in a highly efficient BI execution framework. In-memory reporting is changing the way Microsoft approaches reporting Dynamics AX, and customers need to develop an understanding of this evolving technology and its implications for the future.

What is an in-memory database?

Whether it's Microsoft's tabular engine or another BI in-memory engine, the model uses a column-store database. A column store database is a data structure that resides in-memory rather than on a disk drive that must be accessed more slowly. It gets its information from a transactional data source based on a snapshot of information that is passed to the in-memory database regularly. That in itself is fairly standard.

Now comes the difference. The in-memory database takes that information and puts it in a highly compressed, very efficient data structure. Here in a nutshell is how it works: Rather than repeating all the data values for each row, it only keeps a distinct list of values for each field and replaces the values of the fields with highly-indexed integer values. Because much of the transactional system's database structure is based on related fields, the duplication of these fields is what we based our grouping structures on. By compressing these duplications, we save a tremendous amount of space within the data structure. This allows the data from a 50 GB+ ERP database to fit in a few gigabytes of memory space for reporting and analysis purposes.

The data structure runs in memory and has a highly efficient index structure. This means ...