locked
Design in Tabular Model Solution is slow RRS feed

  • Question

  • Hi,

    I have a SQL 2012 Enterprise Ed using BIDS version 2010.

    In my Tabular Model, I have several large tables with calculations/Measures.  Each time when I add a new Measure and deploy the solution, it will take a long time (2 hours) to process the Model.

    Is there a way to deploy the Model w/o refreshing at all?  So, I can close my solution, then, execute a XMLA query to full process from SSMS w/o waiting for a long time?


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, December 12, 2014 4:06 PM

Answers

  • Hi,

    After playing with the tabular metadata I found an ugly way of doing this but you should be very cautious in doing this. I never recommend this way but as you insist to find a way I am presenting this. Follow the steps in the attached screenshot.

    Again, I would never recommend this way but before you touch your original project keep several backups of it. Play with a sample and once you are accustomed with this then apply. 

    I think you should work on SUB-SET of the data you have.

    Well, I actually have a dream to increase my points upto 1000 and retire forever ! Now I am not sure once the site moderators find this ugly answer posted by me, they are going to drop my points to 0 LOL. 

    Best regards...


    Chandima Lakmal Fonseka


    • Edited by Lakmal_Fonseka Saturday, December 13, 2014 12:31 AM
    • Marked as answer by light_wt Saturday, December 13, 2014 12:48 AM
    Saturday, December 13, 2014 12:24 AM

All replies

  • Hi,

    Did you try the Processing Option > Do Not Process command? 

    MSDN Link which describes the Processing Options (Check the Deployment Properties section)

    http://msdn.microsoft.com/en-us/library/gg492138.aspx#bkmk_deploy_props

    Best regards...


    Chandima Lakmal Fonseka



    Friday, December 12, 2014 5:01 PM
  • Thanks for your reply.

    I see.  SOrry, it wasn't exactly it.

    Let's say, you click Table > Table Properties and there is a custom sql 

    when that customer sql is edited and closed in "Edit Table Property", it will take a long time as it will read in all the data for a large dataset.

    The option mentioned doesn't shorten the time at all.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, December 12, 2014 7:08 PM
  • Hi,

    In this case as an alternative can you not restore your existing SQL database with a different name and then make a sub-set of the data by deleting majority of the rows from the database you restored? Then you develop your cube using this subset of data. At the time you deploy the cube you can point to the proper database which your cube going to fetch data from. this can be done if you use the SSAS deployment wizard. Here is the screenshot how to point to the proper SQL database at the time of the deployment.

    This is the method I follow if the source SQL database has a large dataset.

    Best regards...


    Chandima Lakmal Fonseka

    Friday, December 12, 2014 8:01 PM
  • interesting point.

    My need is to be able to close the "Edit Table Property" fast w/o loading the data, all within BIDS.  I will tell it to load using SSMS with Full processing.  Any idea?


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, December 12, 2014 9:53 PM
  • Hi,

    After playing with the tabular metadata I found an ugly way of doing this but you should be very cautious in doing this. I never recommend this way but as you insist to find a way I am presenting this. Follow the steps in the attached screenshot.

    Again, I would never recommend this way but before you touch your original project keep several backups of it. Play with a sample and once you are accustomed with this then apply. 

    I think you should work on SUB-SET of the data you have.

    Well, I actually have a dream to increase my points upto 1000 and retire forever ! Now I am not sure once the site moderators find this ugly answer posted by me, they are going to drop my points to 0 LOL. 

    Best regards...


    Chandima Lakmal Fonseka


    • Edited by Lakmal_Fonseka Saturday, December 13, 2014 12:31 AM
    • Marked as answer by light_wt Saturday, December 13, 2014 12:48 AM
    Saturday, December 13, 2014 12:24 AM
  • I always expose my data to Tabular using views. When I need to do something like this, I simply add an impossible predicate (WHERE 1= 0) to my view definition. You will still be reading in all of the data, but in this case all of the data is 0 rows. Once the model is deployed you can then edit the view to remove that predicate.
    Tuesday, December 16, 2014 12:13 AM