Typical 2 years Sales Data Mart contains detail and aggregates in order to support time traverse metrics like 4 Week Shipments, 13 Week Shipments and so on...
The typical dimensional or normalized data structure marts creates more challenges from processing as well as data delivery stand point. In spite of managing varied level of aggregates and pre-calculated metrics, evolving/changing business queries often access detail fact and that does not provides expected performance
Yes I understand that we can spend more on technology, procure more powerful database servers like Teradata, Netezza etc. but is that the only solution. In fact did spend lot of money on buying technologies and additional Teradata nodes, but still the data still explodes exponentially from no of rows stand point.
The business evolving needs always slice-n-dice data from all dimensional contexts, and quite impossible to create so many aggregate layers to support all the business queries. The aggregate process layer even become more complex with change in dimensional hierarchies either on temporary basis or data anomalies, but that leads towards aggregate re-build or an adjustments. Let's take an example if a company has distribution channel across 500K stores (+200 attributes) within US, approx. 10K products (+100 attributes) in catalogue and lets say partnering nearly thru 2000 distributor warehouses (+100 attributes), the data explode exponentially in a typical dimensional or normalized data model say billions of records. Definitely from technology stand point you can add more nodes to distribute processing, but is that something even need to look at from data structure stand point.
Please do note that Normalization is to ease processing, dimensional is to bring data analysis closer to the business process outlook, how about data structures to ease access performance?
Any simpler approach to ease processing as well data analysis delivery to optimize business 360 dive-in analysis?