Dazzle Business Intelligence Platform
Executive Summary
A business intelligence platform with three core modules — connection management, data modeling, and intelligence visualization — supporting RDBMS connections across SQL Server, Oracle, Sybase, DB2, and MySQL through a single-page application with MVVM architecture.
Business Problem
Organizations needed to extract insights from data spread across heterogeneous database systems without requiring specialized database expertise for each platform. Existing BI tools either locked users into single-vendor ecosystems or required complex ETL pipelines before any analysis could begin. Business analysts spent more time preparing data connections and transforming schemas than actually deriving insights. The challenge was building a unified BI platform that could connect to any major RDBMS through a consistent interface, allow business users to model data relationships visually, and generate interactive visualizations without writing SQL or managing database-specific connection configurations.
Architecture Overview
The platform implements an MVVM (Model-View-ViewModel) architecture with clear separation between data access, business logic, and presentation layers. The .NET 4.0 backend uses WCF services following RESTful patterns for API exposure. NHibernate ORM provides database-agnostic query generation across supported RDBMS platforms. The frontend single-page application uses Backbone.js for MV* structure with web socket connections for real-time features. The architecture supports extensible database driver registration for adding new RDBMS support without core platform changes.
Solution Design
The platform is structured as three integrated modules. The Connection Manager handles RDBMS connectivity across SQL Server, Oracle, Sybase, DB2, and MySQL with a unified connection interface abstracting vendor-specific drivers and authentication methods. The Data Model Manager enables visual schema exploration and relationship mapping with an ORM powered by NHibernate for database-agnostic query generation. The Business Intelligence module provides interactive dashboards and reports built from modeled data. The .NET 4.0 backend exposes WCF services for client-server communication, while the frontend is built as a single-page application using Backbone.js with jQuery for DOM manipulation. Web sockets enable real-time query progress updates and collaborative editing. LESS and SASS preprocessors manage the styling layer. Development environments are standardized using VirtualBox and Vagrant for consistent cross-team setups.
Architecture Diagrams
Technology Stack
Security
The platform manages credentials for multiple database systems requiring secure storage and transmission. Connection credentials are encrypted using AES-256 before storage, with decryption occurring only at connection time in memory. Role-based access controls restrict which users can create connections, modify data models, or access specific database instances. Query execution is sandboxed with configurable resource limits to prevent runaway queries from impacting source databases. All data access is audit-logged with user attribution for compliance.
Scalability
The MVVM architecture separates concerns allowing independent scaling of the query execution layer from the visualization rendering tier. NHibernate's query abstraction supports adding new database platforms through driver configuration without code changes. The web socket infrastructure supports concurrent real-time sessions for collaborative data exploration. Connection pooling manages efficient resource utilization across multiple simultaneous database connections. The modular architecture enables deploying heavier BI computation workloads on dedicated server instances while the UI tier remains lightweight.
Performance Metrics
- Database connection establishment across all supported RDBMS in under 3 seconds
- Query generation through NHibernate ORM executes within 10% of hand-written SQL performance
- Dashboard rendering for datasets up to 100,000 rows completes in under 2 seconds
- Platform supports 200+ concurrent users with active database connections
- Schema discovery and relationship mapping for databases with 500+ tables completes in under 30 seconds
Challenges
- Abstracting query generation across 5 RDBMS platforms with different SQL dialects, data type mappings, and pagination strategies required extensive NHibernate dialect customization
- Visual data modeling needed to balance power-user capabilities with business-user accessibility, requiring iterative UX design with progressive disclosure of advanced features
- Web socket management for real-time collaborative features introduced state synchronization complexity when multiple users modified the same data model simultaneously
- Performance optimization across heterogeneous databases required adaptive query planning that accounted for different optimizer behaviors and indexing strategies per platform
Lessons Learned
- NHibernate ORM with custom dialect mappings effectively abstracts cross-database differences for 90% of query patterns, but complex analytical queries occasionally require platform-specific optimization paths
- Backbone.js MV* patterns provided excellent structure for complex SPA development but required disciplined memory management to prevent view leaks in long-running sessions
- Vagrant-based development environments eliminated environment configuration issues across a distributed team, reducing onboarding time and ensuring consistent test results
- MVVM architecture with WCF services enabled clean API versioning — new frontend features could target updated service endpoints while maintaining backward compatibility for existing clients
- Visual data modeling interfaces benefit enormously from undo/redo functionality — implementing command patterns early prevented costly retrofitting later
Business Outcomes
- Eliminated need for platform-specific database expertise — business analysts connected to any supported RDBMS through a unified interface
- Report creation time reduced by 75% through visual data modeling eliminating manual SQL query writing
- Platform supported 5 major RDBMS platforms without requiring separate connector licenses or tools
- Collaborative data modeling reduced duplicate analysis efforts by enabling teams to share and build upon each other's models
- Standardized Vagrant development environments reduced new developer setup time from 2 days to 30 minutes
Questions
FAQ
How does the platform handle database-specific features that don't have cross-platform equivalents?
The platform exposes a capability discovery mechanism for each connected database. Features common across all platforms are available through the unified interface, while database-specific capabilities (like Oracle's analytical functions or SQL Server's window functions) are surfaced as platform-specific extensions in the query builder. Users see clear indicators when they're using features that limit portability.
How is query performance monitored across different database backends?
Each query execution captures timing metrics, row counts, and execution plans where available. The platform maintains performance baselines per query pattern and alerts when execution time degrades beyond configurable thresholds. Database-specific explain plan visualization helps power users optimize slow queries without leaving the BI interface.
How does the visual data modeling handle schema changes in source databases?
The platform supports schema refresh operations that detect changes in source database structures. Added columns or tables appear as suggestions for model updates. Removed or renamed elements trigger validation warnings on affected data models and reports. A schema versioning system tracks changes over time so data models can be migrated forward when sources evolve.
Future Roadmap
Planned enhancements include natural language query interfaces allowing business users to ask questions in plain English, machine learning-powered data anomaly detection for proactive insight generation, cloud-native deployment with managed database connectivity services, and real-time streaming data support beyond traditional batch-oriented RDBMS queries.