🗃️Posts

The following section contains a list of the posts grouped by topic. Just click on the link to jump to the post. General Book Reviews Brent Dawson (2023) "Planning for Successful Data Migration" (Part I, Part II) Jürgen Faisst (2022) "International Business Communication Standards (IBCS Version 1.2: Conceptual, perceptual, and semantic design of comprehensible business reports, presentations, and dashboards" (link) Lynn Beighley (2011) "Head First SQL: Your Brain on SQL - A Learner's Guide" 1st Ed. (link) Zhamak Dehghani (2021) "Data Mesh: Delivering Data-Driven Value at Scale" (link) Laurent Bossavit (2015) "The Leprechauns of Software Engineering" (link) Willard C Brinton (1915) "Graphic Methods for Presenting Facts" (link) Debunked Zhamak Dehghani's Data Mesh Monolithic Warehouses and Lakes Quote of the Day Never-Ending Stories in Praxis Inflection Points and the Data Mesh The Good, the Bad and the Ugly Concept Documents Data Vault 2.0 Dynamics 365 Invoice Capture Methodologies Self-Service BI SSIS SSRS Azure Synapse & Microsoft Fabrics Azure Tools & Services The Azure Diagrams Architecture Advisor (Test Drive) Azure Synapse Building a Modern Data Warehouse with Azure Synapse SQL Reloaded Alternatives for Better Code Maintainability in SQL Server & Azure Synapse Part I Part II Automatic Statistics Creation & Dropping for CETAS based on CSV File Format in Serverless SQL Pool Copilot Copilot in Azure SQL Database is in Private Preview Data Management Views for the Synapse serverless SQL pool Documenting External Tables Misusing Views in Serverless SQL Pool Monitoring the Synapse serverless SQL pool with Dynamics Management Views Part I Part II Querying Azure Synapse Metadata for the D365 CRM & FO Tables Exported via Azure Synapse Link for Dataverse SQL Antipatterns  Part I: JOINs, UNIONs & DISTINCT  Temporary Tables and Tempdb in Serverless SQL Pool
Business Intelligence
Introduction Between Potential, Reality, Quality and Stories Between Products, Partners, People and Processes Enterprise Reporting Issues New Technologies, Old Challenges Part I: An Introduction Part II: ETL vs. ELT Data Architecture  Monolithic vs. Distributed Architecture Part I: Zhamak Dehghani's Data Mesh - Debunked Part II: Architectural Choices Part III: Architectural Applications Data Products  Part I: A Lego Exercise Part II: The Complexity Challenge Data Projects Why Data Projects Fail to Deliver Real-Life Impact Part I: First Thoughts Part II: There's Value in Failure Part III: Failure through the Looking Glass Part IV: Making It in the Statistics Data Science Back to the Future  Part I: About Beginnings Part II:  Big Data vs. Business Strategies Generalists vs Specialists in the Field of Data Science Notes Team Data Science Process (TDSP) Myths Monolithic Warehouses and Lakes Graphical Representation Graphics We Live By Part I: The Analytics Marathon Part II: Discount Rates in MS Excel Part III: Exchange Rates in Power BI Part IV: Area Charts in MS Excel Part V: Pie Charts in MS Excel Part VI: Conversion Rates in Power BI Part VII: Reading a Conversion Rates Chart with ChatGPT and Copilot Part VIII: List of Items in Power BI Part IX: Word Clouds in Power BI Part X: Pie and Donut Charts in Power BI and Excel Bar Charts Line Charts Scatter & Bubble Charts HR Data Overreaching in Data Visualizations Reports vs. Data Visualizations The Analytics Marathon visuals (Notes) heatmaps sparklines Metrics  KPIs Part I: An Introduction Part II: Between Certainty and Uncertainty Microsoft Fabric Part I: Notebooks Part II: Domains and the Data Mesh I - The Challenge of Structure Matching Part III: The Metrics Layer 🆕 Part IV: Is Microsoft Fabric Ready? Part V: One Person Can’t Learn or Do Everything Part VI: Data Stores Comparison Delta Tables in Action CRUD Operations I: Basic Operations CRUD Operations II: Looking at the Files Import data from CSV Files into Delta Table Views and other Data Objects Table Metadata II: Updating a table's COMMENT attributes Table Metadata I: General Information SQL Reloaded Part I: Monitoring the Warehouse Notes Azure Data Factory (ADF) data governance data stores data warehouse dataflows (Gen1) dataflows (Gen2) delta lake delta tables lakehouse medallion architecture metrics layer Microsoft Fabric mirroring notebooks OneLake Polaris parquet format Real-Time Analytics shortcuts Perspectives Part I: General Issues Part II: The Complexity Myth Part III: Self Service BI Part IV: How Big is Your Report? Part V: Data Soup - From BI to Analytics Part VI: Data Soup - Reports vs Data Visualization Part VII: Insights - Aha Moments! Part VIII: Insights - The Complexity Perspective Part IX: Dashboards are Dead & Other Crap Part X: The Top 5 Pains of a BI/Analytics Manager Part XI: Ways of Thinking about Data Part XII: From Data to Data Models Part XIII-XV: From Data to Storytelling (Part I, II, III)  Part XVI: On the Cusps of Complexity Part XVII: Creating Value for Organizations Part XVIII: There’s More to Noise Part XIX: Data Visualization between Art, Pragmatism and Kitsch Part XX: From BI to AI Part XXI: Data Visualization Revised Power BI Learning curve Part I: Metrics Layer An Introduction Notes Metrics Layer [private preview] Visual Calculations Preparatory Steps for Creating a Power BI Report Working with Visual Calculations (new feature) Part I: Test Drive Part II:  Simple Tables with Square Numbers as Example Part III: Matrix Tables with Square Numbers as Example Software Engineering A One Man Show Part I: Some Personal Background and a Big Thanks! Part II: In the Cusps of Complexity Part III: The Microsoft Fabric Part IV: Data Roles between Past and Future Part V: Focus on the Foundation Part VI: The Lakehouse Perspective A Software Engineer's Perspective  Part I: Houston, we have a Problem! Part II: Major Knowledge Gaps Part III: More of a One-Man Show Part IV: The Loom of Interactions Part V: From Process Management to Mental Models in Knowledge Gaps Part VI: The Data Citizen Part VII: Think for Yourself! Mea Culpa Part II: The Beginnings Part V: All-Knowing Developers are Back in Demand? Approaching a Query Learning SQL Reports A Reporting Guy’s Issues Addressable Questions in Reports’ Creation I Best Practices Choosing Report’s Attributes Filtering Internal Kitchen How Big Is Your Report? How Many Reporting Systems You Need? Levels of Accuracy in Reporting Levels of Detail in Reporting Reports Types Reports’ Lifecycle - Definition Strategy What is a BI Strategy? Python Drawing Concentric Circles with matplotlib.pyplot Installing PySpark and GraphFrames on a Windows 10 Machine Plotting Data with the Radar Chart Test Drive PySpark and GraphFrames R Language Data Summaries without Using a DataFrame Data Transformations Part I: Temperatures' comparison between F° and C° Drawing Function Plots Part I: Basic Curves Part II: Basic Curves & Inflection Points Hello World in R & Working with Data Frames Using the lessR Package in Microsoft Fabric's Notebooks (Test Drive) Visualizing the Iris Dataset SSRS (& Paginated Reports) Covid-19 Data Project Matrix Report Display - Fifth Magic Class Parameter Dependencies in Dropdowns- Fourth Magic Class Poor Design of Parameters Ranking Rows in Reports Report Formatting - Third Magic Class Report Parameters - Second Magic Class SQL Server CPU Utilization The Good, the Bad and the Ugly Wizarding a Report Graphical Representations II - Sixth Magic Class Test Drives Cognos & SQL Server 2008 - The First Report
Data Management Business Rules Part I: An Introduction Data Culture Part I: No Silver Bullet Part II: Leadership is Necessary but not Sufficient Part III: A Tale of Two Cities Part IV: Quo vadis? [Where are you going?] Part V: Quid nunc? [What now?] Data Driven The Data-Driven Enterprise Data Governance Part I: No Guild of Heroes Part II: Heroes Die Young Part III: Taming the Complexity Data Integration From Disintegration to Integration Data Literacy Data Literacy as Second Language Data Operations One Database, Two Vendors, No Love Story or Maybe… Data Profiling Part I: An Introduction Part II: Using CLR functions Data Quality  An Introduction Data Cleansing Data Migration Data Migration’s Perspective I (A Bird’s-Eye View) Dimensions Accuracy Completeness Conformity Consistency Referential Integrity Uniqueness Information Systems' Perspective Data Security SQL Injection I - Introduction Data Soup Data Lakes & other Puddles Data Structuredness From Business Intelligence to Analytics Master Data Management (MDM)  Understanding Master Data Management’s Integration Challenges (Answer) Notes Data Master Data Management
Data Migrations Introduction Introduction What is Data Migration? Planning Approaches to Planning a Data Migration for an ERP Implementation Create a Data Migration Strategy for Dynamics 365 Conceptualization Part I: Goals, Objectives & Requirements Part II: Plan vs. Concept vs. Strategy Part III: Heuristics Part IV: Data Access Part V: The Extraction Layer Part VI: The Data Migration Layer Part VII: The Data Import Layer Facts, Principles and Practices Guiding Principles The SQL Server Perspective Licensing Costs and Edition Choices Data Quality’s Perspective  Part I: A Bird’s-Eye View Hands-On Migrating AdventureWorks to Dynamics 365 - Products In-house Built Solutions Part I: Introduction Part II: The Import Layer Part III: The Preparation Layer Programming Functions Useful in Data Migrations Useful Functions in Data Conversion
Data Warehousing
Data Vault 2.0 The Good, the Bad and the Ugly Data Warehousing A Personal Journey Data Load Optimization - A Success Story Data Warehousing and Microsoft Dynamics 365 A Few Issues to Consider (Part I) Building a Data Lakehouse for Dynamics 365 Environments with Serverless SQL Pool Data Warehousing and Microsoft Dynamics 365 Dynamics 365, the Data Lakehouse and the Medallion Architecture ETL An Introduction Push vs. Pull SSIS packages vs. SQL code Synchronization The Extract Subprocess The Load Subprocess The Transform Subprocess Microsoft Fabric Data(base) Mirroring in Microsoft Fabric (New feature) SSIS Aggregate Data Flow Transformation Percentage Sampling Data Flow Transformation Second Magic Class - SQL Server to Oracle Data Export The Conditional Split Data Flow Transformation The Good, the Bad and the Ugly The Union All Data Flow Transformation Third Magic Class - Data Flow Task Using Oracle as Data Source Visual Studio 2010 CR doesn’t support SSIS Projects Wizarding an SSIS Package - First Magic Class
Database Management Systems (DBMS)
Concepts Definitions Part I: The Stored Procedure Case Part II: What's in a Name Challenges TSQL Challenge 24 Cheat sheets DELETE vs. TRUNCATE TABLE Database objects Dependencies I - Introduction Views I Views II Table Value Constructors at Work Temporary Tables vs. Table Variables and TempDB Databases An Introduction Event Streaming Databases - More of a Kafka’s Story Running a Statement for Each Database - CLR Version Searching a Value within a Database SQL Reloaded A Look into Reverse Advices on SQL logic split in Web Applications Aggregates Number of records COUNT ROWCOUNT in action The CLR Version The DMV Approach Before and After Drop If Exists Splitting a String Trimming Strings Best practices Query Writing I Concatenation Common table expressions CRUD  Incremental Update Technique Saving Data With Stored Procedures Self-Join in Update Query I Self-Join in Update Query II Stored Procedures from Metadata Part I: Insert & Update Part II: Get, Dropdown & Delete Updating Data With Values From The Same Table Cursor and Linked Server for Data Import Cursors and Lists Data Processing Preparing test data Processing JSON Files with Complex Structure in SQL Server 2016+ Processing JSON Files with Flat Matrix Structure in SQL Server 2016+ Randomized Data Selection SQL Server and Excel Data Trivial Equalities in Queries Useful Functions in Data Conversion Ways of Looking at Data Part I Part II Part III Part IV: Hierarchies Part V: Hierarchies When Queries Look Like Ducks XML on SQL Server 2005 Dates GetDate SQL Server 2000/2005 More Date-related Functionality I More Date-related Functionality II Some Notes on Dates Dynamic Queries Evaluating Textual Expressions with CLR UDFs Handling duplicates Dealing with data duplicates on SQL Server Deleting Sequential Data From a Table Joins Self-Join in Update Query I Self-Join in Update Query II Self-Joins and Denormalized Data Loading in Normalized Models The Power of Joins Part I - An Introduction Part II - Horizontal Joins Part III - Vertical Joins Part IV - Multi-Joins Under Control Part V - Semi-Joins and Anti-Joins Part VI - Not-Equal Joins Just in CASE Part I: Introduction Part II: Clauses and Joins Part III: Quest for Performance Part IV: Other Scenarios Part V: Dynamic Queries Functions Useful in Data Migrations Lists List Based Aggregations - On Hand example Lists as Parameters in Stored Procedures Lists, Sets and a Little Math Table-valued Functions and List of Values Math Prime Numbers To be or not to be a Prime 6 out of 49 Part I: Getting the data Part II: Modeling the Data Part III: Basic Data Analysis Part IV: More Basic Data Analysis Misusing Views and Pseudo-Constants Null-ifying the World Out of Space PIVOT Operator Example Partition-based functions Successive Price Increases/Discounts via Windowing Functions and CTEs STRING_AGG and STRING_SPLIT at Work, and a Bit of Pivoting String_Split Function Tricks with Strings via STRING_SPLIT,  PATINDEX and TRANSLATE window functions Simple Aggregations (SQL Server 2022) Running Totals (SQL Server 2022) Ranking (SQL Server 2022) Window Functions Pulling the Strings of SQL Server  Part I - An Introduction Part II - Creation and Selection Part IV - Spaces, Trimming, Length and Comparisons Part V - Character Indexes Part VI - Subparts of a String Part VII - List of Values Part VIII - Insertions, Deletions and Replacements Part IX - Special Characters Part X - Dynamic Queries The STUFF function Oracle Hierarchies in Oracle Troubleshooting ANSI 92 JOIN syntax error No records returned by queries Oracle vs SQL Server Averages I Date Conversion Date Operations Date Validation Handling Missing Dates Running Totals Query Patterns in SQL Server Aggregate Functions Inserts Joins and Subqueries Matrix Representation Window Functions SQL Server Administration Database Recovery on SQL Server 2017 End of Life for 2008 and 2008 R2 Versions Feature Bloat Killing Sessions - Killing ‘em Softly and other Snake Stories Monitoring the Database Logs System.OutOfMemoryException in SQL Server Management Studio and other 32-bit Drawbacks Features New Hello World in R & Working with Data Frames SQL Server 2012 is almost here Undocumented Part I: Reading the SQL Server Log Part II: Execute Command for Each Table Part III: CPU Utilization via the Ring Buffer Part IV: DBCC SQLPERF Wish List Replace From Notes buffer pool  checkpoints compression garbage collection live query statistics logging minimally logged locking memory memory manager NUMA objects cursor [defs] databases [defs] model database [defs] tempdb database [defs] functions aggregate functions [defs] analytics functions system functions table-valued functions user-defined functions indexes [defs] clustered index [defs] clustered vs non-clustered indexes composite indexes [defs] covering indexes [defs] index selectivity [defs] non-clustered indexes [defs] keys composite key [defs] foreign key [defs] primary key [defs] stored procedures [defs] system stored procedures [defs] user-defined stored procedures tables [defs] contained database resource database scalable shared database stretched databases table variables tempdb temporal databases temporary tables triggers [defs] views [defs] user-defined views indexed views system views parameter sniffing partitions plan cache queries correlated subquery [defs] query optimization query plan query processing subqueries real-time operational analytics  resource governor resource monitor service broker sessions transaction log [defs] transactions [defs] variables Troubleshooting Adventure Works - one year later Adventure Works installation error on Vista AdventureWorks requires FILESTREAM enabled Could not load file or assembly Microsoft.MSXML… Errors Information Schema Views Logical errors Login Failed for User Microsoft SQL Server 2008 installation error Porting 32 bit CLR UDFs on 64 bit Platforms Problems in SQL Server 2000 DTS Packages Root Blocking Sessions Searching the Error Log Who is Active
ERP Systems Data Migration Create a Data Migration Strategy for Dynamics 365 Data Warehousing Dynamics 365, the Data Lakehouse and the Medallion Architecture ERP Implementations Introducing an Upfront Proof-of-Concept Setup ERP Software Predictions for 2010 It’s a Matter of Complexity It’s all about Partnership Part I Part II It’s all about Scope Functional Requirements Nonfunctional Requirements & MVP Preventing the Failure Simplifying the Implementation Project The Implementation Process Seems to be Broken The right ERP software Dynamics 365 general The Good, the Bad and the Ugly Finance Invoice Capture  features setup areas process diagram thoughts The Good, the Bad and the Ugly Notes Fixed Assets Vendor Invoices Processing  setup areas Operations "Create Product" Process Diagram in Dynamics 365 for Finance & Operations SQL queries AX 2009 vs. D365 FO - Product Prices with two Different Queries Base Enums Metadata Blocked Products in Dynamics 365 F&O Dynamics AX 2009: Deleting Obsolete Companies Released Products by Legal Entity in Dynamics 365 F&O Security Queries Useful Queries for D365 FO (and not only) Oracle e-Business Suite APPS internals II APPS internals I
Metablogging Blogging Introduction Changing the Status quo Past, Present and Future
MS Office MS Excel Excel for SQL Developers Part I - Range to Delimited List of Values Part II: Insert Statements Part III: Creating a Stored Procedure from Table’s Metadata Part IV: Differences Between Two Datasets Part V: Formatting Output to HTML MS Access Is MS Access or MS Excel the Answer to your Problem? MS Access vs. LightSwitch - About Starts and Ends of Software Products The Limitations of MS Access Database Why I dislike MS Access
Process Management Dynamics 365 for Finance & Operations "Create Product" Process Diagram "Invoice Capture" Process Diagram ITIL "Manage Problem" Process Diagram
Project Management
Methodologies:  Agility under Eyeglasses  Part I Part II The Good, the Bad and the Ugly Project Planning Planning Correctly Misundersood Part I Part II Part III Part IV Some Thoughts on Planning Part I Part II The Butterflies of Project Management Project Tools The Choice of Tools in Project Management Project Management Projects' Dynamics  Introduction Motion Redefining Projects' Success I The Agile Manifesto Reloaded Introduction Requirements Management
Software Engineering
Application Design Rapid Prototyping Who Messed with My Data? Documentation Lessons Learned Concept Documents: The Good, the Bad and the Ugly Programming A Problem Solving Approach to Troubleshooting I Believe and Not Doubt?! Football and Software Development Good Programmer, Bad Programmer Keeping Current or the Quest to Lifelong Learning for IT Professionals Mea Culpa I Misconceptions about Programming I Misconceptions about Programming II Pair Programming Programmer, Coder or Developer? Programming as Art The Dark Side of Programming To get or not Certified?! What is Programming About?
Strategic Management
Lean Management Between Value and Waste Part I: Introduction Notes Strategy Performance Management First Time Right (The Aim toward Operational Excellence) Over-Educated, Yet Under-Qualified? The Need for Perfection vs. Excellence Simplicity Part I: Simple, but not that Simple Part II: A System's View Part III: Part IV: Designing for Simplicity Part V: ERP Implementations' Story I Part VI: ERP Implementations' Story II Strategy Strategic Perspectives Part I: Agile vs. Lean Organizations Part II: The Elephant in the Room Defining the Strategy Quality Acceptance Criteria for Strategies and Concepts Strategic Planning The Reason behind a Strategy The Impact of New Technologies A Nail Keeps the Shoe The Technology-oriented Patient Checking the Vital Signs Inflection Points and the Data Mesh Systems Engineering Complex Systems Never-Ending Stories in Praxis (Quote of the Day)  A Play of Problems (Much Ado about Nothing) Simplicity Part I: Simple, but not that Simple Part II: A System's View Part IV: Designing for Simplicity