locked
SSAS Tabular data delete options RRS feed

  • Question

  • Hi there
    This is Mazhar, in my professional year project, I developing SSAS Tabular model (for Power BI) for a company, one of the project requirement is to delete and update data for last two weeks in SSAS Tabular database every week. And also automate the same process using SSIS.I am not sure that deleting records of last two weeks(dynamically) possible in SSAS tabular?, or Using stored procedure (Exec SQL Task in SSIS) can perform above task.
    Please help me on this

    Cheers

    Mazhar!

    Tuesday, June 6, 2017 4:09 AM

Answers

  • Hello,

    You can not delete selected data from a tabular cube as you can do it in a database engine with SQL.

    You have to reprocess (load) the required data. You can e.g. use a SQL view which Returns the data for the date range you are looking for and the process the SSAS cube daily.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 6, 2017 6:46 AM
  • Another option here if you have the Enterprise Edition would be to use partitions. You could have one large "history" partition, then two "current week" partitions. Then you full process the 2 current week partitions, then at the end of the week you merge the oldest partition into your large "history" partition and create a new weekly partition.

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

    • Marked as answer by MazharMH Wednesday, June 7, 2017 3:56 AM
    Tuesday, June 6, 2017 11:00 AM

All replies

  • Hello,

    You can not delete selected data from a tabular cube as you can do it in a database engine with SQL.

    You have to reprocess (load) the required data. You can e.g. use a SQL view which Returns the data for the date range you are looking for and the process the SSAS cube daily.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 6, 2017 6:46 AM
  • Another option here if you have the Enterprise Edition would be to use partitions. You could have one large "history" partition, then two "current week" partitions. Then you full process the 2 current week partitions, then at the end of the week you merge the oldest partition into your large "history" partition and create a new weekly partition.

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

    • Marked as answer by MazharMH Wednesday, June 7, 2017 3:56 AM
    Tuesday, June 6, 2017 11:00 AM
  • Thanks for your help!

    Wednesday, June 7, 2017 3:55 AM
  • Thanks both for valuable advice.

    I have imported data in SSAS using query which fetches data from 6 databases. I was asked to keep 2 years of data in SSAS model,So can I create partition against that query? Is it possible to automate merge partitions every week ?, and dynamically create partitions?

    • Edited by MazharMH Wednesday, June 7, 2017 5:33 AM
    Wednesday, June 7, 2017 3:56 AM