Open source and commercial tools to build dynamic queries of fact tables in star schema
Mondrian OLAP server 
http://mondrian.pentaho.com/

Pentaho Schema Workbench (for creating the cube schema)
http://mondrian.pentaho.com/documentation/workbench.php

Pentaho Aggregation Designer (for finding useful aggregations and building them)
http://infocenter.pentaho.com/help/index.jsp?topic=%2Faggregation_designer_guide%2Fconcept_pad_overview.html

Saiku AJAX pivot tables
http://analytical-labs.com/

Pentaho Report Designer for designing reports. 

Pentaho Data Integration (Kettle) for ETL into data warehouse
http://kettle.pentaho.com/

DataCleaner for quality control