TARGIT InMemory Database, offered for use with TARGIT Decision Suite, is similar to Microsoft’s tabular engine. Both are column-store databases.
Column-store databases allow for large data set storage in a small space when loaded in-memory. Rather than repeating all the data values for each row like a traditional relational database would do, a column-store database only keeps a distinct list of values for each field and replaces the values of the transactions with highly indexed integer values so it can quickly look up the values and present the data set.
This allows a 50GB+ database to fit into a few GB of memory space.
However, unlike Microsoft’s tabular engine, the TARGIT InMemory Database can be used with standard edition of SQL Server and most other databases. Data can even be loaded directly from the source systems into the TARGIT InMemory Database using the TARGIT InMemory ETL Studio. This means there is no longer a reliance on SQL Server.
What is an InMemory database?
An in-memory database primarily relies on main memory for data storage in contrast to more traditional database management systems that employ a disk storage mechanism. Once a database is loaded, data is “in memory” and ready-to-go, therefore it is able to retrieve and present information far more quickly than previously possible.
Following importation the TARGIT InMemory Database can be used as a data warehouse to be refreshed only when an update is required. You do not need to reload from a third-party source each time your pc is reset.
Benefits of InMemory
In our experience most data sets loaded into analysis services cubes will benefit from in-memory loading for several reasons.
- Query performance will be significantly improved as there will no longer be a reliance on disk IO to deliver the data. Instead, it will go straight to memory, which is significantly faster.
- Combining data from across several dimensions is faster.
- There is no need to cross analyze data files from the file system (to find non-empty data, like in cubes).
- Data can be joined directly in-memory (similar to a relational database).
Thus, the TARGIT InMemory Database can be used to solve the performance bottlenecks common with traditional analysis services cubes.
Additionally, a set of BI functions have been added to the engine to replace calculations we are currently executing in MDX. For example, the balance calculation for Finance Balance and Inventory Balance can be made using the built-in balance function in the TARGIT InMemory Database. This is because it is a built-in function and data is sitting in memory, the performance is significantly better than the MDX calculated members done today.