Tabular model

Tabular model
Name	Tabular model
Type	Data model
Developer	Microsoft
Initial release	2010s
Written in	DAX, TMSL
Platform	Microsoft SQL Server Analysis Services, Power BI

Tabular model is a columnar, in-memory analytical data model designed for fast query performance and interactive analysis. It integrates with Microsoft SQL Server Analysis Services, Power BI Desktop, and Azure Analysis Services to support business intelligence scenarios across enterprises such as Microsoft Corporation, Amazon Web Services, Google LLC, IBM, and SAP SE. The model leverages technologies and standards associated with DAX (Data Analysis Expressions), xVelocity, and Vertipaq to provide OLAP-like capabilities for reporting, dashboards, and ad hoc analysis.

Overview

Tabular model provides a semantic layer that exposes relational and analytical structures to client tools like Excel, Power BI, Tableau, and QlikView. It contrasts with legacy multidimensional models employed by SQL Server Analysis Services (Multidimensional), emphasizing columnar storage, in-memory processing, and formula languages such as DAX (Data Analysis Expressions). Enterprises including Facebook, Apple Inc., Twitter, Netflix, Airbnb, Uber Technologies, Salesforce, Oracle Corporation, and Adobe Inc. use tabular-style architectures for interactive analytics and self-service reporting scenarios.

Architecture and Components

Core components include storage engines, formula engines, table and column definitions, relationships, measures, and hierarchies. The storage engine is derived from technology like xVelocity and Vertipaq, while the formula engine interprets expressions written in DAX (Data Analysis Expressions). Models are deployed to servers such as Microsoft SQL Server Analysis Services, Azure Analysis Services, and cloud services by Amazon Web Services partners; clients include Excel, Power BI, and Tableau. Security and deployment integrate with Active Directory, Azure Active Directory, and enterprise platforms like Windows Server, SharePoint Server, and Microsoft 365.

Data Modeling and Calculations

Data modeling relies on tables, relationships, calculated columns, and measures. Calculations use DAX (Data Analysis Expressions), influenced by languages and tools used at Microsoft Research and by practices in Stanford University, Massachusetts Institute of Technology, Carnegie Mellon University, and University of California, Berkeley. Tabular models support row-level security and role definitions compatible with Azure Active Directory groups and Windows Server policies. Modeling best practices draw on methods used at organizations such as Goldman Sachs, JPMorgan Chase, HSBC, Citigroup, Deloitte, PricewaterhouseCoopers, Ernst & Young, and KPMG.

Implementation and Tools

Development often uses SQL Server Data Tools, Visual Studio, Power BI Desktop, and third-party utilities from companies like Redgate, Simba Technologies, and Databricks. Deployment targets include Microsoft SQL Server Analysis Services (Tabular mode), Azure Analysis Services, and embedded analytics in Power BI Service. ETL and data integration workflows commonly use SQL Server Integration Services, Azure Data Factory, Informatica, Talend, and SnapLogic. Monitoring and optimization integrate with Azure Monitor, SQL Server Management Studio, System Center, and observability platforms from New Relic, Datadog, and Splunk.

Performance, Optimization, and Scaling

Performance depends on column encoding, compression, cardinality, and model design. Techniques include star schema design inspired by Ralph Kimball methods, aggregations similar to Bill Inmon approaches, careful use of calculated columns versus measures, and partitioning strategies used by Netflix and Amazon.com. Scaling leverages in-memory capacities of servers and cloud elasticity offered by Microsoft Azure, Amazon Web Services, and Google Cloud Platform. Profiling and bottleneck analysis use tools such as SQL Server Profiler, Extended Events, and third-party profilers from Redgate and SolarWinds.

Use Cases and Applications

Common applications include enterprise reporting, interactive dashboards, financial consolidation, sales analysis, customer segmentation, and IoT telemetry analytics. Industries implementing tabular-style models include technology firms like Google LLC, Apple Inc., Microsoft Corporation, Amazon Web Services, Facebook, finance firms like JPMorgan Chase, Goldman Sachs, healthcare systems such as Mayo Clinic and Cleveland Clinic, and retailers like Walmart, Costco, Target Corporation, and Home Depot. Analytical scenarios are supported by visualization tools including Power BI, Tableau, QlikView, and Excel.

History and Development

Tabular modeling evolved from columnar in-memory engines and Microsoft’s shift from purely multidimensional OLAP to tabular architectures around the 2010s. Influences include technologies such as Vertipaq and projects from Microsoft Research, with broader enterprise adoption accelerated by the rise of self-service BI tools like Power BI and Excel Power Pivot. The development trajectory intersected with cloud platform expansions at Microsoft Azure, Amazon Web Services, and Google Cloud Platform and with industry practices from firms like Tableau Software, QlikTech, and SAP SE.

Category:Data modeling Category:Business intelligence