Should I Build A Tabular Model or an OLAP Cube?
If you have Microsoft SQL Server and a need for an Analytics solution you have a dilemma: Multidimensional Cube or Tabular Model?
In many instances you could choose either, but 8 key questions to ask before making your decision are:
Are you planning on deploying to Azure Analysis Services or On-Premise Analysis Services?
Do you wish to be able to extend your analytics model with custom dimension fields/expressions?
Do you wish to embed hyperlinks into your analytics model?
Do you wish to carry out ETL operations within your analytics model or using a service such as SSIS prior to building your analytics model?
Do you wish to have automatic support for Ragged Depth Hierarchies and Custom Rollups?
Do you require writeback capability to your analytics model?
Do you have any Multidimensional or Tabular design experience with your business?
Are you planning on using Power BI to deliver enterprise wide reports and dashboards?
If you answered YES to any of the above then your decision may be straight forward:
Only Tabular Models are supported in Azure Analysis Services
Only Tabular Models allow additional fields for dimensions (referred to as tables in Tabular models).
Only Multidimensional cubes allow Actions to be built into the cube to support hyperlinks.
Only Tabular Modelling allows for ETL work to be carried out in the model
Only Multidimensional Cubes offer automatic support for Ragged Depth Hierarchies and Custom Rollups
Only Multidimensional Cubes support writeback.
Tabular Modelling is easier than Multi-Dimensional modelling
Although Power BI can consume data from both Multi-Dimensional Cubes and Tabular Models, it integrates seemlessly with Tabular Models hosted in Azure Analysis services.
If you have no particular leaning on any of these questions then you have a little more to think about, or maybe not. It is likely that your decision may come down to the internal resource and skills that you have available. If you already have key staff skilled in Multidimensional Cube design you may initially favour developing cubes, but it is worth taking a step back and deciding if the time has come to move to the more modern and intuitive Tabular Modelling solution
Tabular Modelling is a later technology than Multi-Dimensional Cube Modelling, introduced by Microsoft with SQL Server 2012, and uses a language called DAX rather than MDX.
DAX is much closer in syntax and concepts to Excel Formulae working on tables, columns and filters, whereas MDX is a complex language working on the principle of measures, dimension hierarchies, axes and slicers.
The Tabular Storage Engine, Vertipaq, boasts a Columnar Database structure making it very fast to retrieve requested column values, and the Formula Engine is capable of evaluating DAX expressions very quickly. With the right infrastructure, CPU, RAM and storage solution, Tabular models can support in excess of 10 billion rows.
Azure Analysis Services is easily scalable as the model size grows
For those who have worked with Excel and/or Relational Databases Tabular Modelling provides a much more familiar and more intuitive structure, based on tables and relationships. Terms not encountered in the Multi-Dimensional Cube world (well, they have relationships, but not in the way you might think of them!).
Power BI used with Azure Analysis Services enables a blend of Enterprise/Corporate BI and self-service BI
Power BI used with Azure Analysis Services supports automatic refreshes with no need for gateways, and row level security to ensure only those that should have access to data have that access.
This Microsoft Video introduces the Power BI and Azure Analysis Services solution.
Do you need some help in working out what is best for you?
Launching into the world of Enterprise BI solutions is daunting and challenging, but perhaps we can help you to determine the best way forward for you. Here at PTR we specialize in Microsoft Self-service and Enterprise BI solutions incorporating Azure Analysis Services and Power BI. We work with many clients in a wide cross section of industries helping to tackle the challenges of distributed data sets, dirty, unstructured data sources and the need to create a single trustworthy, "one truth" resource that can be confidently used to deliver enterprise wide visuals, reports and dashboards. Email usto find out how we can help you.