--- type: work area: work status: active date: 2026-05-03 created: 2026-05-03 updated: 2025-09-30 tags: - work --- ## Why choose Clickhouse ### Optimal Database Solutions for Power BI Analytics at Scale **DuckDB emerges as the clear winner for local development environments with 32GB RAM constraints, while Azure Synapse Analytics provides the most seamless cloud migration path for 336 million row datasets growing by 8 million rows monthly.** This analysis reveals potential cost savings of 25-72% through strategic database selection and optimization techniques that can improve query performance by up to 96%. Based on comprehensive research across columnar databases, Power BI integration patterns, and cloud solutions, organizations can achieve sub-second query performance for snapshot comparisons while maintaining cost-effective scalability. The optimal approach involves a phased migration strategy starting with local DuckDB development, progressing to Azure Synapse for production workloads, with potential cost optimization through ClickHouse Cloud for high-volume analytics. ## Local database solutions excel within memory constraints For handling 336 million rows (129GB) on a 32GB RAM machine with monthly growth of 8 million rows, **DuckDB provides the most practical solution** among local columnar databases. Its streaming execution engine processes data in chunks without full materialization, using 80% of physical RAM by default while automatically spilling to disk when needed. This approach has proven successful with 11GB files on 4GB machines, making it well-suited for larger-than-memory datasets. **ClickHouse local offers superior performance but requires extensive configuration** for 32GB environments. While it consistently ranks #1 in analytical benchmarks and provides exceptional compression ratios (5-10x), it typically requires minimum 32GB RAM for production workloads. The risk of memory pressure makes it conditional for this use case, requiring careful tuning of cache sizes and thread limits. **PostgreSQL with cstore_fdw presents significant limitations** due to the deprecated status of the columnar extension, supporting only PostgreSQL 9.3-12. TimescaleDB offers better prospects if data has time-series characteristics, leveraging up to 95% compression ratios and 10x query speedup on compressed data. Apache Druid, while excellent for real-time analytics, introduces excessive complexity for single-machine deployments with multiple competing processes on limited RAM. The performance hierarchy for local solutions places DuckDB first for balanced performance and reliability, ClickHouse second with extensive tuning requirements, and TimescaleDB third for time-series specific workloads. ## Power BI integration requires strategic mode selection **Composite models offer the optimal balance** for 129GB+ datasets by combining Import mode for dimension tables with DirectQuery for large fact tables. This hybrid approach leverages VertiPaq engine performance for filtering while avoiding Power BI's storage capacity constraints of 1GB (Pro) or 10GB (Premium without Large Model format). **DirectQuery enables unlimited dataset sizes** but introduces query performance dependencies on source database optimization. Microsoft recommends DirectQuery only when source databases can deliver interactive results under 5 seconds, with 225-second timeouts for individual queries and 1 million row limits per result set. Each visual generates separate queries to source systems, making database performance critical. **Incremental refresh strategies become essential** for monthly data additions, providing 10-50x performance improvements over full refresh cycles. The configuration requires Premium capacity for DirectQuery hybrid scenarios, with recommended settings of 2-5 years archive period and 1-3 months refresh period for monthly data patterns. Change detection using audit columns can refresh only modified periods, reducing resource consumption by 50-70%. For snapshot comparison scenarios, **synchronized slicers and optimized DAX measures** significantly improve performance. Relative date slicers perform fastest, followed by single-select options, while multi-select date slicers create the highest performance cost. Pre-calculated comparative measures and bookmark functionality for predefined comparisons enhance user experience. ## Cloud migration delivers cost savings and scalability **Azure Synapse Analytics provides the most seamless Power BI integration** with native connectors, Azure AD authentication, and optimized DirectQuery performance. Cost analysis shows Azure Synapse at $1,500-4,000 monthly for typical usage patterns, with serverless SQL pools for exploration and dedicated pools for production workloads. **ClickHouse Cloud offers the most cost-effective solution** at $800-2,200 monthly, providing up to 72% cost savings compared to Snowflake while delivering superior query performance. However, Power BI integration relies on beta connectors with limited feature support, making it suitable for cost-sensitive scenarios with longer implementation timelines. **Migration strategies should follow phased approaches** to minimize disruption. The optimal path involves setting up Azure Synapse Serverless for immediate analytics (weeks 1-4), deploying dedicated SQL pools for high-performance workloads (weeks 5-8), and expanding to advanced analytics platforms like Databricks (weeks 9-16). This approach maintains business continuity while providing systematic capability enhancement. **Cloud platforms demonstrate significant performance advantages** through auto-scaling, result caching, and specialized optimizations. BigQuery handles 100+ concurrent users effectively, Snowflake provides excellent multi-cluster auto-scaling, and Databricks offers superior ML/AI integration for advanced analytics workloads. ## Data modeling optimization drives performance gains **Temporal partitioning on extraction dates** provides the foundation for efficient snapshot comparisons by enabling partition elimination during queries. Monthly partitions allow optimal filtering between any two snapshots while maintaining minimum 1 million rows per partition for columnstore index effectiveness. Partition switching enables quick data loading, reducing maintenance from hours to seconds. **Clustered columnstore indexes** deliver optimal performance for analytical workloads with 7-10x compression ratios and up to 100x performance improvements. For snapshot comparison queries aggregating data across time periods, columnstore provides superior performance compared to traditional row-based storage. **Slowly Changing Dimensions Type 2** work best for dimensional changes between snapshots, while **temporal fact tables** handle volatile attributes more efficiently. The temporal fact table pattern adds effective_date and termination_date directly to fact tables, managing record counts by creating new records only when changes occur. **Compression strategies significantly impact storage costs** with ZSTD providing optimal balance (6-8x compression) for snapshot storage. Delta Lake offers additional benefits through ACID transactions, time travel queries, and automatic optimization commands, with banking implementations achieving 30% faster queries and 25% storage cost reduction. ## Implementation roadmap ensures success **Phase 1 foundation** (months 1-3) should establish DuckDB for local development with proper star schema implementation and incremental refresh configuration. Performance testing with expected user loads validates the approach before cloud migration. Budget allocation of $150,000-300,000 covers initial implementation including professional services. **Phase 2 scaling** (months 4-12) involves migrating to Azure Synapse with F128 (P2) capacity starting point, scaling to F256 (P3) as datasets approach 25GB memory requirements. Advanced features like hybrid tables support real-time requirements while automation handles scaling based on performance metrics. **Phase 3 enterprise scale** (year 2+) integrates data lake capabilities with Direct Lake, leverages AI and machine learning features, and implements comprehensive data governance frameworks. Regular TCO analysis and capacity right-sizing optimize costs while maintaining performance. **Expected ROI reaches 200-300% within the first year** based on industry benchmarks, with break-even timelines of 12-18 months for typical enterprise deployments. Organizations report 270% ROI, 60% increase in customer satisfaction, and 40-60% reduction in reporting time through systematic Power BI optimization. ## Conclusion The optimal database solution for Power BI analytics with 336 million row datasets involves a strategic progression from DuckDB local development to Azure Synapse cloud production, potentially incorporating ClickHouse Cloud for cost optimization. Success requires careful attention to data modeling, incremental refresh strategies, and performance optimization techniques that can deliver 96% query performance improvements while reducing total cost of ownership by 25.7% compared to alternative solutions. Organizations implementing this approach should expect 12-16 week migration timelines, annual licensing costs of $50,000-200,000 depending on capacity requirements, and significant productivity gains through automated reporting and faster decision-making capabilities. ## Mission Description Avec les extractions (sur power bi on télécharge la donnée depuis une source comme excel) réalisés le mois dernier on va faire une comparaison des montants (changeant) avec l’extraction du mois actuel. Dans un premier temps on veut constater un écart et savoir d’où il vient. Cela peut aussi être avec des mois antérieurs (5/6 mois derniers). L’analyse commence par réunir les fichiers des montants (cf Hamza) pour avoir la base de donnée d’un mois entier. En fonction de ce que Phillipe demande on pousse l’analyse sur un champs voulue (ex : steering partner). Pour calculer l’écart ne pas oublier de multiplier par -1 une ligne si toute la ligne positif. Le but est de comprendre l’écart produit entre un clearable et un cleared et pouvoir l’expliquer. Etape 1 = Choisir la BDD - Clickhouse (BDD type Olap) = Open source (gratuit) (méta-microsft), Permet une analyse de données simplifié ; Nécessite néanmoins un serveur Etape 2 = Nettoyer les données et les préparer : Sous python Nettoyage : - Supprimer les espaces - Régler les problèmes de type de données Préparation : - Marquer les données Horscope (les supprimer) - Ajouter une colonne du nom du mois + date d’extraction Etape 3 = Importer les données dans la BDD - Ajouter dans la BDD les extractions des AR - Ajouter dans la BDD la table des cost center pour identifier les périmètre Inbound EE (PCD, OV, iFast) et supprimer les données Horscope pour libérer de la mémoire. Etape 4 = Conception du Dashboard - Calcul d’écart - Conception des visuels - Possibilité de changement de mois d’extraction - Conception des mesures Chaque mois, les données des activitys reports sont extraits concernant les 12 derniers mois. Le logiciel devra être puissant pour pouvoir supporter une grande quantité de données (Minimum Excel 64 bits ou Power Bi voire Python (cf. Hamza – Polars module dans Python semble aussi adapté à cette mission).) Critère user friendly : le fichier doit être simple d’utilisation, la transition d’extraction doit se faire de manière simple, l’utilisateur (exemple : L’utilisateur étant Henri, il ne sait pas forcément coder donc ce doit être un outil pensé utilisateur). Les extractions doivent être choisies simplement et l’outil permettra une flexibilité de modification d’extraction. # **Plan PowerPoint – Mission & Choix de ClickHouse** --- ### **Slide 1 – Titre** - **Titre :** Analyse des écarts de montants avec Power BI et ClickHouse - **Sous-titre :** Construire un outil robuste, scalable et simple d’utilisation - Ton nom, date --- ### **Slide 2 – Contexte de la Mission** - Chaque mois, on extrait les **Activity Reports (AR)** couvrant les 12 derniers mois. - Objectif : comparer les montants entre deux extractions (ex. mois dernier vs mois actuel). - Exemple concret : expliquer les écarts entre **clearable** et **cleared**. --- ### **Slide 3 – Besoin Métier** - Détecter et expliquer les écarts (montants changeants). - Pouvoir analyser selon le champ demandé (ex. Steering Partner). - Outil **user-friendly** : utilisable par des non-codeurs (ex. Henri). - Gérer des **volumes massifs** (plusieurs centaines de millions de lignes). --- ### **Slide 4 – Étapes de la Mission** 1. **Choisir la base de données** 2. **Nettoyer et préparer les données (Python)** - suppression espaces, correction types - suppression horscope - ajout colonne mois + date extraction 3. **Importer dans la base** - AR + cost centers - filtrage horscope pour optimiser mémoire 4. **Construire le dashboard Power BI** - calculs d’écarts - visuels interactifs (choix du mois, comparaisons multi-mois) --- ### **Slide 5 – Pourquoi une Base de Données est Nécessaire** - Excel et Power BI seuls atteignent vite leurs limites. - On doit gérer **336M+ lignes**, +8M/mois. - Une base de données permet : - stockage centralisé - rapidité des requêtes - flexibilité pour les analyses --- ### **Slide 6 – Bases Testées** - **DuckDB** → rapide en local, limité à la mémoire de la machine. - **Azure Synapse** → intégration Power BI excellente, mais coûteux. - **ClickHouse** → très rapide, open source, excellent rapport performance/prix. --- ### **Slide 7 – Qu’est-ce que ClickHouse ?** - Base de données **colonnaire** → optimisée pour l’analytique. - Open source, utilisée par grandes entreprises (Yandex, Cloudflare, etc.). - Conçue pour interroger **des milliards de lignes en secondes**. --- ### **Slide 8 – Avantages de ClickHouse** - ⚡ **Vitesse** : sub-second queries sur énormes volumes. - 📉 **Compression** : stockage 5–10x plus petit. - 💰 **Coût** : jusqu’à 72% d’économies par rapport à Synapse/Snowflake. - 🔄 **Scalabilité** : supporte la croissance des données. --- ### **Slide 9 – Comparaison Simple** |Base|Avantages|Limites|Coût| |---|---|---|---| |DuckDB|Simple, local|Pas pour très gros volumes|Bas| |Synapse|Top intégration Power BI|Très coûteux|Élevé| |**ClickHouse**|Rapide, scalable, moins cher|Nécessite tuning|Moyen| --- ### **Slide 10 – Intégration avec Power BI** - **DirectQuery** : Power BI interroge directement ClickHouse → pas de limite de taille. - **Incremental refresh** : seules les nouvelles données sont chargées → 10-50x plus rapide. - **Visuels interactifs** pour comparer plusieurs mois d’extraction. --- ### **Slide 11 – Roadmap de Mise en Œuvre** 1. **Phase 1 – Test local** (petit dataset, validation). 2. **Phase 2 – Passage en production** (ClickHouse serveur + Power BI). 3. **Phase 3 – Optimisation** (compression, monitoring, extension aux cas avancés). --- ### **Slide 12 – Conclusion** - Mission = expliquer les écarts de montants de manière simple et rapide. - Excel/Power BI seuls insuffisants → besoin d’une base robuste. - **ClickHouse** est le meilleur compromis : - rapide ⚡ - scalable 🔄 - économique 💰 - C’est la **solution idéale** pour notre usage.