none
Partitioning in SSAS tabular Model RRS feed

  • Question

  • We built tabular model using SSAS 2016.

    The model is like single fact[around 300 fields] and 30 dimensions[100 fields]. we used views to fetch data from each table for tabular model.

    Fact holds data nearly 350 GB[around 50 million data]. In tabular model we derived extra columns around 400 fields through fact view. We are haven't used any derived column in SSAS.

    overall we can say 800 fields in tabular model. We have around 100 GB RAM. Tabular model DB file size showing as 4 GB in physical drive after processing it.

    I have few questions.

    1) Deriving column through SSAS tabular model is best  or Deriving column through sql query[in view] is best ?

    2) We are planning to apply partitioning. Will the partitioning helps to tune tabular data load ? or will it be helpful to give fast result to the users when users hit the queries to tabular model.

    3) How many parallel users can access this tabular model without memory issue? if 50 users access it will we get any memory issue?

    4) Is Tabular model db file[4 GB file in my case] holds all data in compressed format in the physical location ?

    Please clarify.

    Thursday, May 30, 2019 2:54 AM

All replies

  • 1) Deriving column through SSAS tabular model is best  or Deriving column through sql query[in view] is best ?

    If you are planning on doing partitioning so that you only have to process recent partitions, then deriving columns through a SQL view is better. This is because calculated columns re-calculate over all rows in every partition in the table every time you process. Where as columns from a view will be loaded directly into the partition being processed, other partitions will not be affected.

    2) We are planning to apply partitioning. Will the partitioning helps to tune tabular data load ? or will it be helpful to give fast result to the users when users hit the queries to tabular model.

    Partitioning only affects the data load. It has little impact on query speed unless you create lots of small partitions in which case it can actually hurt performance

    3) How many parallel users can access this tabular model without memory issue? if 50 users access it will we get any memory issue?

    It's really hard to say it depends on how well optimized your model is and what sort of queries are being run. 50 users should not be a problem for a 4Gb model on a server with 100Gb RAM. But at the same time if someone tries to do a detailed data extraction of all 50M records and hundreds of columns the server will have to try to materialize the entire resultset in memory before sending it to the client which could require more than 100GB of memory.

    4) Is Tabular model db file[4 GB file in my case] holds all data in compressed format in the physical location ?

    if you are talking about the .db folder under the data directory, then yes this is all the compressed data. The server always holds all this data in memory, the folder on disk is only used if the Tabular service is restarted for any reason. At which point the server will reload the model into memory from the .db folder.


    http://darren.gosbell.com - please mark correct answers

    Thursday, May 30, 2019 3:25 AM
    Moderator