Here at PTR we specialize in Microsoft Self-service and Enterprise BI solutions incorporating Azure Analysis Services and Power BI. 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.

This Microsoft Video introduces the Power BI and Azure Analysis Services solution.ĭo you need some help in working out what is best for you?

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. Power BI used with Azure Analysis Services enables a blend of Enterprise/Corporate BI and self-service BI Terms not encountered in the Multi-Dimensional Cube world (well, they have relationships, but not in the way you might think of them!). With the right infrastructure, CPU, RAM and storage solution, Tabular models can support in excess of 10 billion rows.Īzure Analysis Services is easily scalable as the model size growsįor 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. 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. 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.ĭAX 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. 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 It is likely that your decision may come down to the internal resource and skills that you have available. If you have no particular leaning on any of these questions then you have a little more to think about, or maybe not. Tabular Modelling is easier than Multi-Dimensional modellingĪlthough Power BI can consume data from both Multi-Dimensional Cubes and Tabular Models, it integrates seemlessly with Tabular Models hosted in Azure Analysis services. Only Multidimensional Cubes support writeback. Only Multidimensional Cubes offer automatic support for Ragged Depth Hierarchies and Custom Rollups Only Tabular Modelling allows for ETL work to be carried out in the model Only Multidimensional cubes allow Actions to be built into the cube to support hyperlinks. Only Tabular Models allow additional fields for dimensions (referred to as tables in Tabular models). Only Tabular Models are supported in Azure Analysis Services If you answered YES to any of the above then your decision may be straight forward:

In many instances you could choose either, but 8 key questions to ask before making your decision are:Īre you planning on deploying to Azure Analysis Services or On-Premise Analysis Services?ĭo you wish to be able to extend your analytics model with custom dimension fields/expressions?ĭo you wish to embed hyperlinks into your analytics model?ĭo you wish to carry out ETL operations within your analytics model or using a service such as SSIS prior to building your analytics model?ĭo you wish to have automatic support for Ragged Depth Hierarchies and Custom Rollups?ĭo you require writeback capability to your analytics model?ĭo you have any Multidimensional or Tabular design experience with your business?Īre you planning on using Power BI to deliver enterprise wide reports and dashboards? If you have Microsoft SQL Server and a need for an Analytics solution you have a dilemma: Multidimensional Cube or Tabular Model?

Ssas tabular model