It has been pleasing to see enhancements to Tabular in each SQL release, which has reduced the need for Multidimensional. However, there are still a few features that Tabular does not support, necessitating new developments with Multidimensional. The standout feature that Tabular does not support is composite primary keys. Ralph Kymball, needs to take some responsibility. His Star Schema modeling methodology employs single column surrogate keys for all dimension tables. Kymball has advanced data modeling a great deal, but I think it's wrong to blindly generate surrogate keys when natural primary keys exist. Kymball methodology requires fact and dimension tables to be physically generated from underlying normalized tables. This involves a lot of work, reconciliation and performance cost, when the underlying tables could be used directly, with their composite primary keys.
The really sad fact is SSAS documentation, comparing Multidimensional and Tabular, does not even list composite keys as a feature worth comparing. It's as if, no one would require a cube technology supporting composite primary keys. See https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas
Even something simple like a Type 2 dimension already has a perfectly good natural key consisting of the source system's business key (eg ClientId) and EffectiveFromDate. So, why not just add EffectiveFromDate_Client to the fact table? A complete dimension and fact table rewrite is unnecessary!
I'm hoping that data warehouse developers see the sense of natural composite keys and that Tabular supports them in the near future. In the meantime, I will only be creating Tabular models on the rare occasions where composite keys are not part of the data structures.
If you would like to see Tabular support composite keys, please vote on this connect item. https://connect.microsoft.com/SQLServer/feedback/details/3144082
For a performance comparison of Tabular versus Multidimensional see https://richardlees.blogspot.com/2019/08/aws-redshift-performance-versus-sql.html
Hi Richard,
ReplyDeleteyour blog arbticle is very interesting and you pointed out an important drawback of the tabluar model.
Have you filed a connect item?
regards
Christian
Thank you Christian, Yes, a connect item is a good idea, although my experience hasn't been great with connect. I have one here https://connect.microsoft.com/SQLServer/feedback/details/760107/multidimensional-cube-is-not-retained-in-memory-even-though-there-is-plenty-of-ram for SSAS cache, which I consider the biggest performance limitation of Multidimensional. It has 140 votes.
ReplyDeleteAnyway, I like your suggestion, and I have created a connect item https://connect.microsoft.com/SQLServer/feedback/details/3144082
Please support this suggestion by voting on the connect item.
Thank you again,