The trouble with data cubes
Data cubes are a vital part of today’s business analytics landscape. In geometry, a cube is 3-dimensional. In technology, data cubes can be multi-dimensional. Data cube technology allows users to slice-and-dice information in various ways to explore the relationship between data points.
While data cube business analytics tools are essential for analysis of business data across the enterprise, deploying them as the only source of modeled data is not sustainable. They represent an immense advantage over older tools, but still have a number of limitations.
- Building the data cubes can be a long, slow process. Cubes can aggregate information from multiple sources. The more sources and the more data, the longer this process will take. Be sure you have dedicated some heavy-duty hardware to handle this job.
- Limitation of data fields. A cube is intended to be used for rapid analysis of limited data sets. Cubes are not designed to function with large data sets, and have a limited range of historical data as well as fields (dimensions) that can be loaded. Dividing large data sets across multiple cubes creates duplication and loading problems, resulting in very high maintenance and resource costs.
- You’re not looking at real-time data. The data that is pulled into data cubes has already been summarized. Detail can be found in the source system, but users can’t drill into it from the cube. The advantage of this is that analysts have a “snapshot in time” they can use as a benchmark, plus the system will run faster.
- Cubes must be optimized for performance. There’s an art and a science to aggregating all this data. If users are complaining that it is taking too long to manipulate data, you’ll need an expert on hand who can optimize the relational database design and find ways to improve cube performance.
- Cubes are not designed to be a data repository. Cubes being used as a sole data source for analytics and reporting will ultimately accumulate transactional data and fail to load. Reporting performance from them will slow gradually as the size of the cube grows.
Having worked with business analytics tools throughout my career, we decided to build a data foundation which operates as the data source for all cubes across different business functions. A single data model containing all data across AX modules enables users to build cubes rapidly because the dimensional model used to build a cube is identical to the dimensional model in the data warehouse.
DataCONNECT, mcaConnect’s data warehousing solution for Dynamics 365 and Dynamics AX, is able to be deployed extracting multiple data sources external to AX, such as payroll, billing, logistics, factory management, CRM, Excel, Access and more.
Combining all business-critical data into one single consolidated data model allows for diverse cubes to be built without requiring any transformation. For users not needing a multi-dimensional view of data, the data warehouse is also able to be queried directly from any BI Platform.
The DataCONNECT solution has several distinct advantages over cube technology.
- You retain the ability to drill down into the source data
- Lowest level of detail is always available
- Provides nearly real-time analytics
- Hundreds of leading and lagging business indicators are made available to load to cubes
- You get answers significantly faster than you would with building data cubes from the ground up
- Historical information can also be accessed
Want to learn more? Download the DataCONNECT fact sheet