locked
How to prevent data full loading while build your tabular model(SSAS)? RRS feed

  • Question

  • HI all,

    Currently, I am creating a Tabular Model using SSAS 2016, after selecting the tables into model, all the data in the table will be loaded into model, how to prevent this behavior, because the data volume is very huge, my understanding is the model is only tables and their logic relationship, there is no need to load all the data. 

    So is there one property to configure, then only load part data of table into model.

    Thanks,

    VWFC_BI

    Monday, February 27, 2017 2:59 AM

Answers

  • there is no need to load all the data. 

    Hello,

    Tabular mode SSAS is an in-memory engine and of course it has to load the complete data into the model for calculation, how else should it work. Only if you run DirectQuery mode the data of the source is directly accessed.

    See Tabular Models (SSAS) for more details.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by VWFC_BI Friday, March 3, 2017 2:32 AM
    Monday, February 27, 2017 5:49 AM

All replies

  • there is no need to load all the data. 

    Hello,

    Tabular mode SSAS is an in-memory engine and of course it has to load the complete data into the model for calculation, how else should it work. Only if you run DirectQuery mode the data of the source is directly accessed.

    See Tabular Models (SSAS) for more details.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by VWFC_BI Friday, March 3, 2017 2:32 AM
    Monday, February 27, 2017 5:49 AM
  • Hi

    This is not really possible as the model would not know how to sample the data in such a way that  a suitable subset of all the required data was imported, this problem affects dimensions more than fact which don't have any dependencies.

    What I do to make this processing faster is to build a view over each of the tables in my data warehouse. I can then modify the views in development to return only a subset. This view could look something like this.

    CREATE VIEW [Cube].[FactSales]

    as

    Select top 10000 [insert column name here] from [dbo].[FactSales]

    # I've created a schema called [Cube] fro all my abstractions, but you do not have to do this.

    I would do this only on the fact tables and not on the dimensions but if you have an intelligent way to filter dimensions then feel free to do that. 

    Your second option is to use partitions, if you have a partition for every month in FactSales, then you can create one or two partition in development so that the table is small but have all the required partitions in production (you will want to automate this).  

    Monday, February 27, 2017 5:56 AM
  • Are you getting out of memory? How much does it huge? You can use SSAS Task ssis package to load the data into to the model as well (being part of the job)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, February 27, 2017 6:22 AM
  • Hi

    This is not really possible as the model would not know how to sample the data in such a way that  a suitable subset of all the required data was imported, this problem affects dimensions more than fact which don't have any dependencies.

    What I do to make this processing faster is to build a view over each of the tables in my data warehouse. I can then modify the views in development to return only a subset. This view could look something like this.

    CREATE VIEW [Cube].[FactSales]

    as

    Select top 10000 [insert column name here] from [dbo].[FactSales]

    # I've created a schema called [Cube] fro all my abstractions, but you do not have to do this.

    I would do this only on the fact tables and not on the dimensions but if you have an intelligent way to filter dimensions then feel free to do that. 

    Your second option is to use partitions, if you have a partition for every month in FactSales, then you can create one or two partition in development so that the table is small but have all the required partitions in production (you will want to automate this).  

    Hi MickaelAdrianJohnson,

    You understand your question clearly, It seems I have to prepare one small volume DB for development, this tool is not intelligence to only load part of data. I remember SSIS is intelligence to only load some sample data if we click preview, this is what I just want to see for this tool. Maybe I can submit one suggestion to Product group:)

    Thanks,

    Challen Fu

    Monday, February 27, 2017 7:15 AM
  • Are you getting out of memory? How much does it huge? You can use SSAS Task ssis package to load the data into to the model as well (being part of the job)

    Hi Uri,

    To developers, they just need to build the model, model is just tables name and their relationship, there is no need to load the true data into it while we creating it or only contain part of the data. It should contain full data after we deploy it to production. This is my understanding.

    Thanks,
    VWFC_BI


    • Edited by VWFC_BI Monday, February 27, 2017 7:19 AM more information
    Monday, February 27, 2017 7:18 AM
  • Hi Olaf,

    My question is development process not on the production. However, "DirectQuery" can also solve my problem, if I turn on DirectQuery, no data will be loaded into my Tabular Model.

    Thanks a lot Olaf.

    Thanks,
    Challen Fu


    • Edited by VWFC_BI Friday, March 3, 2017 2:33 AM
    Friday, March 3, 2017 2:32 AM