Schema Design Alternatives for Multi-Granular Data Warehousing

Data warehousing is widely used in industry for reporting and analysis of huge volumes of data at different levels of detail. In general, data warehouses use standard dimensional schema designs to organize their data. However, current data warehousing schema designs fall short in their ability to model the multi-granular data found in various real-world application domains. For example, modern farm equipment in a field produces massive amounts of data at different levels of granularity that has to be stored and queried. A study of the commonly used data warehousing schemas exposes the limitation that the schema designs are intended to simply store data at the same single level of granularity. This paper on the other hand, presents several extended dimensional data warehousing schema design alternatives to store both detail and aggregated data at different levels of granularity. The paper presents three solutions to design the time dimension tables and four solutions to design the fact tables. Moreover, each of these solutions is evaluated in different combinations of the time dimension and the fact tables based on a real world farming case study.