What are the supported methods for handling fact-level data in Atoti?
Fact-level data can be stored in two ways:- Internal datastore: Atoti’s in-memory datastore is optimized for fast, multidimensional analytics. It stores data directly in memory, enabling low-latency query responses.
- External databases: External systems store data persistently. Atoti connects to these sources and queries them dynamically using DirectQuery.
Cost considerations
Using an external database via DirectQuery reduces memory usage and infrastructure costs, especially for large datasets. Performance depends on the external system’s responsiveness and availability. A hybrid approach is possible: keep recent, frequently accessed data in memory and store historical or “cold” data in an external database.What is the difference between the internal datastore and DirectQuery?
| Feature | Internal datastore (in-memory) | DirectQuery (external) |
|---|---|---|
| Storage location | In memory | External database |
| Load performance | Fast, low latency | Depends on external system |
| Data freshness | Always up to date thanks to the built-in real-time capability | Depends on external system |
| Memory usage | High | Low |
| Versioning support | Yes | Limited |
How Atoti stores data in the Datastore
Atoti’s in-memory datastore uses advanced techniques for high performance and compression:- Columnar storage: Data is stored by column rather than by row. Searching within a single column is faster than scanning across multiple rows. Less data needs to be scanned, therefore filtering and joining operations execute more quickly and efficiently. Performance is improved.
- Indexes: Key fields are automatically indexed to accelerate query performance. Indexes enable compression strategies and reduce redundancy in stored data. In addition, data is located and retrieved more quickly with an index.
- Dictionaries: Columns are dictionarized to reduce memory usage and improve query time.
How DirectQuery works with external data warehouses
DirectQuery enables Atoti to perform analytics on data stored in external warehouses without loading it into memory.- Benefits: Reduces in-memory storage requirements and lowers infrastructure costs for large datasets.
- How it works: Atoti cubes use DirectQuery connectors to translate cube requests into external database queries (mostly SQL). By default, no data is stored in memory. However, aggregate providers can be configured to cache frequently accessed results if needed.
How to use the Datastore and DirectQuery together
Atoti supports combining its in-memory datastore with DirectQuery connectors to external databases. The tables in the in-memory datastore are not joined with tables accessed through DirectQuery. But the data in the in-memory datastore can be used alongside the data accessed through DirectQuery in the same cube. This hybrid approach allows you to optimize performance without increasing infrastructure costs. Frequently accessed or frequently updated data is stored in memory while less critical data is in the external systems.Why use a hybrid approach?
- Faster measure calculations: Lookup data stored in memory can be accessed instantly during measure calculations, avoiding round-trips to the external database.
- Reduced memory usage: Fact tables and their related tables remain in the external database, keeping the memory footprint small.
How does it work?
- In-memory datastore: Stores lookup tables as isolated stores. These are accessed during measure calculations.
- DirectQuery: Handles the fact table and tables joined to it. Queries are executed on the external database.
Related reading
Find out more about configuring the datastore in Atoti:- Atoti Java SDK uses a specific API
- Atoti Python SDK creates the datastore directly from the tables