Ad hoc Meta Model

1
Ad hoc Meta Model
2
Topic

• Modeling Challenges for Warehouse OLTP
• Various techniques for achieving efficient model
• Model intuitiveness and usability
• Formulating QA testing strategy
• Blunders

3
Modeling Challenges for Warehouse OLTP

• Achieving one model across various DBs like
  DB2,Oracle Sqlserveretc
• Multi Language
• Multi grained facts
• Knowing atomic Data

4
Modeling Challenges for Warehouse OLTP..Cont

• Ensuring efficient Query formation and complete
  understanding of SQL generation Engine
• Handling One-to-many Many-to-many
• Bridges Outriggers
• Rollup Behaviors of measures

5
Various techniques for achieving efficient model

• Single fact model
• Collapsing joins using views
• Applying various indexing techniques

6
Model intuitiveness and usability

• Mapping model to business process
• Folder structure UI
• Not to Over crowd the model
• Creating sample grain reports of the model
• Proactively reviewing the model UI every 6 months

7
Formulating QA testing strategy

• Checking ETL audit reports
• Check Grains report
• Comparing Sql queries with previous version
• Creating test report which compares source
  warehouse data

8
Blunders

• Creating a complex canned report with various
  prompts/numerous columns and using as Ad hoc tool
• Gathering Model requirements based on canned
  reports instead of business process
• Providing too many canned reports will kill Ad
  Hoc tool usage

9
QA

• Ajay Patwari
• ajayp_at_4cs.com