7 ms·

Thanks for sharing your thoughts. What's the difference in use-case between OLAP and tabular?

by blahi 10y ago

greggyb 10y ago
For reference for other readers, OLAP (On-Line Analytical Processing) is both the name for a type of database workload and a former designation of a Microsoft product, SQL Server Analysis Services, designed to fill that role. As of SQL Server 2012 (arguably 2008R2), SSAS offers two modes of operation, Multidimensional (formerly OLAP), and Tabular. Both of these modes are designed to fit the OLAP workload. SSAS Multidimensional is the same engine and query language that has been around forever in SSAS - a dimensional data store on-disk, scripted in MDX. SSAS Tabular is the newer engine built on top of Vertipaq. The query and expression language is DAX. Presently, either language can be used to query a cube/model operating in either mode, though development is still Multidimensional-MDX and Tabular-DAX. The way I like to characterize the two is that Multidimensional mode is what your pedantic uncle would put together if he read and loved the Kimball books and the only language he knew was SQL. It looks like SQL, but is semantically VERY different. Tabular is designed to capture the Excel analyst market. DAX is designed to look like Excel formulas, but semantically DAX and the Tabular engine are very similar to SQL. Neither of these is intended as a negative description. Multidimensional is excellent for huge datasets, and works great for anything that can map cleanly onto the abstraction of dimensions and measure groups. Tabular, like I mentioned, maintains many semantic similarities to SQL. The primary abstractions are tables and relationships, with an addition of the idea of filter context that can be automatically propagated. Since the primary operations are on tables and relations, the model is much more general and I find that I'm often able to map DAX to SQL in my head in real time as I write code. There are of course things that are easier in one language than the other, and more difficult. DAX is not a general-purpose query language, though. It is designed to support analytical queries, and so it has a great deal of nuance around manipulating filter context and aggregating data. Multidimensional allows much more fine-grained specification of cube behavior, but consequently demands a slower development cycle. For enterprise scale BI and semantic modeling, I think it is absolutely best in class. Tabular is a very powerful engine that allows you to get pretty close to Multidimensional in some dimensions, with a much lower development overhead. The Tabular storage engine does demand that all data be resident in memory. There is a pass-through query mode which acts essentially as a DAX->SQL translation layer, called DirectQuery, which can remove this requirement, but typically it demands an absolute beast of a RDBMS (not limited to only MS SQL Server) on the back end. Even though Tabular offers a more flexible semantic model, it still works best with something approximating a dimensional model. The further you depart from a dimensional model, the more expertise you need in DAX to handle it. It's not bad, but the population of people comfortable enough to work at that level is vanishingly small. This has been a collection of random thoughts. There isn't a single better mode between the two. Both have strengths, but without a specific use case or set of constraints it is difficult to give concrete advice. The two modes fit the same general use case, but have nuances.
- dragonwriter 10y ago
  > Tabular is designed to capture the Excel analyst market. DAX is designed to look like Excel formulas And, in fact, Excel's "Power Pivot" feature (and uses DAX for calculations) is powered by Excel running its own instance of SSAS Tabular.
  - greggyb 10y ago
    Yes. Power Pivot is the reason that I said Tabular arguably came to SQL Server 2008R2, which can run Analysis Services in a mode to support a Power Pivot workbook hosted on SharePoint. There was no standalone SSAS Tabular, but the Vertipaq engine and DAX were run on a SQL Server instance. Power BI Desktop (and hosted data models on the PowerBI.com cloud service) is also powered by the Tabular engine and utilizes DAX as a query and modeling language. Microsoft is pushing Tabular hard. It's probably the right choice for most organizations, and at worst serves as a great prototyping tool for a Multidimensional model (when we have clients who need Multidimensional for whatever reason, we often still do initial development and pilots on Tabular).
    - blahi 10y ago
      Thanks for the answers. Really appreciated! I am analyst and I deal with databases, but in a very specific way. The way I tried to explain to myself the difference is that OLAP is used if you have a lot of fine-grained hierarchies accessed by a big number of people. In this scenario it would make sense to make all those aggregations ahead of time because a columnar store would have to calculate them each time somebody slices and that would be waay to expensive, even if you have state of the art caching.
      - greggyb 10y ago
        That is a useful distinction to make, but as in all programming tasks, profiling will get you further than intuition when tuning for performance.

Thanks for sharing your thoughts. What's the difference in use-case between OLAP and tabular?

by blahi 10y ago

Thanks for sharing your thoughts. What's the difference in use-case between OLAP and tabular?

For reference for other readers, OLAP (On-Line Analytical Processing) is both the name for a type of database workload and a former designation of a Microsoft product, SQL Server Analysis Services, designed to fill that role. As of SQL Server 2012 (arguably 2008R2), SSAS offers two modes of operation, Multidimensional (formerly OLAP), and Tabular. Both of these modes are designed to fit the OLAP workload. SSAS Multidimensional is the same engine and query language that has been around forever in SSAS - a dimensional data store on-disk, scripted in MDX. SSAS Tabular is the newer engine built on top of Vertipaq. The query and expression language is DAX. Presently, either language can be used to query a cube/model operating in either mode, though development is still Multidimensional-MDX and Tabular-DAX. The way I like to characterize the two is that Multidimensional mode is what your pedantic uncle would put together if he read and loved the Kimball books and the only language he knew was SQL. It looks like SQL, but is semantically VERY different. Tabular is designed to capture the Excel analyst market. DAX is designed to look like Excel formulas, but semantically DAX and the Tabular engine are very similar to SQL. Neither of these is intended as a negative description. Multidimensional is excellent for huge datasets, and works great for anything that can map cleanly onto the abstraction of dimensions and measure groups. Tabular, like I mentioned, maintains many semantic similarities to SQL. The primary abstractions are tables and relationships, with an addition of the idea of filter context that can be automatically propagated. Since the primary operations are on tables and relations, the model is much more general and I find that I'm often able to map DAX to SQL in my head in real time as I write code. There are of course things that are easier in one language than the other, and more difficult. DAX is not a general-purpose query language, though. It is designed to support analytical queries, and so it has a great deal of nuance around manipulating filter context and aggregating data. Multidimensional allows much more fine-grained specification of cube behavior, but consequently demands a slower development cycle. For enterprise scale BI and semantic modeling, I think it is absolutely best in class. Tabular is a very powerful engine that allows you to get pretty close to Multidimensional in some dimensions, with a much lower development overhead. The Tabular storage engine does demand that all data be resident in memory. There is a pass-through query mode which acts essentially as a DAX->SQL translation layer, called DirectQuery, which can remove this requirement, but typically it demands an absolute beast of a RDBMS (not limited to only MS SQL Server) on the back end. Even though Tabular offers a more flexible semantic model, it still works best with something approximating a dimensional model. The further you depart from a dimensional model, the more expertise you need in DAX to handle it. It's not bad, but the population of people comfortable enough to work at that level is vanishingly small. This has been a collection of random thoughts. There isn't a single better mode between the two. Both have strengths, but without a specific use case or set of constraints it is difficult to give concrete advice. The two modes fit the same general use case, but have nuances.

dragonwriter 10y ago
> Tabular is designed to capture the Excel analyst market. DAX is designed to look like Excel formulas And, in fact, Excel's "Power Pivot" feature (and uses DAX for calculations) is powered by Excel running its own instance of SSAS Tabular.
- greggyb 10y ago
  Yes. Power Pivot is the reason that I said Tabular arguably came to SQL Server 2008R2, which can run Analysis Services in a mode to support a Power Pivot workbook hosted on SharePoint. There was no standalone SSAS Tabular, but the Vertipaq engine and DAX were run on a SQL Server instance. Power BI Desktop (and hosted data models on the PowerBI.com cloud service) is also powered by the Tabular engine and utilizes DAX as a query and modeling language. Microsoft is pushing Tabular hard. It's probably the right choice for most organizations, and at worst serves as a great prototyping tool for a Multidimensional model (when we have clients who need Multidimensional for whatever reason, we often still do initial development and pilots on Tabular).
  - blahi 10y ago
    Thanks for the answers. Really appreciated! I am analyst and I deal with databases, but in a very specific way. The way I tried to explain to myself the difference is that OLAP is used if you have a lot of fine-grained hierarchies accessed by a big number of people. In this scenario it would make sense to make all those aggregations ahead of time because a columnar store would have to calculate them each time somebody slices and that would be waay to expensive, even if you have state of the art caching.
    - greggyb 10y ago
      That is a useful distinction to make, but as in all programming tasks, profiling will get you further than intuition when tuning for performance.