none
MS BI - Columnstore vs SSAS Tabular

    Question

  • I was a MS BI developer, been largely in SAP for a few years now.  I am revisiting the architecture of a successful MS BI datamart I wrote years ago to see how it can be modernized.

    In SAP environment (HANA) I can make a view on a table that has a columnstore and that works as a pivotable/ad hoc data source.. what I generically call a "model".  In MS BI I need to load my relational db datamart into an SSAS Tabular datastore to get this same effect.  If SSAS Tabular and SQL columnstores are the same underlying tech, why do I need the extra layer?  SSAS Tabular, as I recall, is a very flat structure without all the niceties of SSAS Multidimensional from a navigation standpoint.  Perhaps this has changed.

    Anyone going primarily with a Source-->Tabular architecture?  I am trying to reduce the layers because I find they make me less agile.  My old SQL datamarts have a SSIS/Staging/Star Data Mart/Cube setup and require a fair amount of work to add fields/tables.

    In SAP environment I can also use SLT to have real-time updates to the staging layer, leading to overall a very lean environment to get real-time BI.  I know I sound like an SAP advertisement but that is not my purpose.  I am just trying to compare and get the best architecture with my MS BI setup.  As you probably know, those kinds of decisions stick with you.

    In an ideal world, I update logic in one place to add extra fields and that's it!

    Thank you for your responses.

    -Ken

    Thursday, July 12, 2018 2:39 AM

All replies

  • Hi Ken,

    Thanks for your question.

    >>>If SSAS Tabular and SQL columnstores are the same underlying tech, why do I need the extra layer? 
    SSAS provides much more than query performance.
    1) A user friendly interface (the model), relationships, hierarchies, KPIs, role security, perspectives, custom drill through – a rich metadata layer of business logic.
    2) An analytical language of DAX to simplify and extend your BI queries.
    In short, you can get the benefits of both SSAS and your columnstore index. SQL queries will be passed to your relational database and you’ll have semantic layer on top to define business logic and better analyze the data. If you have Enterprise Edition (required for DirectQuery) and you have referential integrity in your SQL database such that your SSAS DirectQuery model can send inner join queries, then this could be a great option.

    For more detailed information about SSAS Tabular VS ColumnStore, Please refer to articles:
    xVelocity engines compared: VertiPaq vs ColumnStore 
    whitepaper VertiPaq vs ColumnStore Comparison

    For information about why do we need SSAS, please refer to below blog:
    https://insightsquest.com/2016/05/08/why-do-we-need-ssas-2016-or-in-general/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 12, 2018 4:28 AM
    Moderator