Ad Hoc model is a key to achieve self serving BI capabilities, designing the model which helps in evaluating the business is tricky.Modellers need to adapt different design techniques for warehouse & OLTP models. OLTP model is more challenging due to normalized db structure by nature and Warehouse model is simpler and more flexible for future enhancements. Intuitive meta-models are vital for successful BI solution.

Modellers would face some of the core issues like.

Dimensional Warehouse Model

Atomic Data-Grain: Clearly understanding of lowest captured reporting data structure in the model is vital, since it;s the foundation of fact data. Generally, users have a tough time in recognizing lowest stored data while executing the business process (functional system). Grain report will help users, objective of this report is to illustrate the lowest possible captured business data.

Multi grained Fact: Usability of multi grained fact table should be assessed cautiously, advantages and disadvantages have to be weighed carefully before mixing the grains. Reward of having multi grain fact is greater while evaluating across the business processes, synergy analysis might be potential eye opener and can find various trends for analysts. Conversely, Multi grained fact can be a nightmare for single business process analysis. Deriving single grain fact table from multi business process grains is a viable solution, keeping both meta-models will empower users by giving choice to analyze single or multi business process.

Bridges: Integrate Bridges diligently to star schema for better usability. Standalone bridges will not add any value from ad hoc reporting perspective, adopting group table design pattern and linking to fact will provide a complete solution.

OLTP Model

OLTP model is complex due to normalized db structure, database is designed for executing business process. Modellers will have a hard time in presenting transactional structure for business evaluation.

Identifying Fact Dimension Tables: Recognizing potential fact. dimension source tables is a preliminary task, modeller need to go through functional system thoroughly, record each transaction and map to OLTP database. Basically, modellers have to do part of the ETL (extraction) job of recognizing the source tables and grouping them for each dimension.

Collapsing joins using views: Model with less number of tables will be more stable with predictable query formation, dimensional & fact source tables can be consolidated into fewer tables by using views. The whole idea is to create artificial de normalized data structure to eliminate modelling complexities, taking this approach might create some query performance issues.

Query engine: Thorough understand of SQL generator engine is required for designing efficient models, modellers should identify generated query patterns and skilfully tweak the meta-model for efficient SQLs. Often; predicting OLTP model query from engine is convoluted, lot of planning is required; while designing; scalable OLTP model.

Finally, Modellers should distinguish, how query engine reads dimensions, facts, bridges, outrigger, cardinalities and table stats for generating optimized SQLS.