none
How to Keep historical data in SSAS

    Question

  • Hi everyone,

    I'm new to SSAS and trying to understand the concept of historical data.

    We have a booking table and want to keep the status of the booking (Active/Cancelled).

    So I assume we will need to take snapshots once a week. But can't find articles about the snapshot process. 

    * How do we set up a snapshot?

    * Where does it save the snapshot to?

    * We have 300k rows in the fact table we want to save each week. Does it copy all the rows?

    * How can we call the snapshot data when requesting information from the fact table?

    Scenario:

    Someone made a booking this week and cancelled the booking the next week. I want to count the booking as active if I'm running report for the first week, but count as cancelled if I'm running the report for the second week.

    Please help, I'm desperate :(

    Thanks

    Monday, July 22, 2013 8:23 PM

All replies

  • can someone please direct me to the right tutorial to learn about the snapshot process? Thanks
    Tuesday, July 23, 2013 5:12 PM
  • Hello,

    There are no kind of "snapshots" available in SSAS.

    The cube contains those data you load to, means the data from the data source. In common a DWH is used as data source for a cube, so you can control via DWH, which data will be loaded to the cube.

    On option is to use partitioning in SSAS; only available in Enterprise Edition. You could load history data to one (or more) partition and current data to an other partition; each partition can be processed separate.


    Olaf Helper

    Blog Xing

    Tuesday, July 23, 2013 5:19 PM
  • Because everywhere I read, I see people are talking about SSAS is the right solution for use snapshots to keep historical data.

    for example: 

    http://stackoverflow.com/questions/10880938/ssas-is-a-periodic-snapshot-the-right-choice
    Another example: 
    http://stackoverflow.com/questions/8494916/how-to-keep-historical-values-of-a-data-point-in-ssas

    Tuesday, July 23, 2013 6:25 PM
  • Hi Maor,

    We don't have "Snapshot" concept in SQL Server Analysis Services. The OLAP data source usually refers to specialized tools that make warehouse data easily available, which provides server technologies that help speed up query and reporting processing.

    Analysis Services implements OLAP with technologies that simplify and quicken the process of designing, creating, maintaining, and querying aggregate tables while avoiding data explosion issues.

    As Olaf described above, we can use partition to store historical data. For more information about partition, please see:
    http://technet.microsoft.com/en-us/library/ms175688.aspx

    Best Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, July 24, 2013 12:56 PM
  • Hi Maor,

    We don't have "Snapshot" concept in SQL Server Analysis Services. The OLAP data source usually refers to specialized tools that make warehouse data easily available, which provides server technologies that help speed up query and reporting processing.

    Analysis Services implements OLAP with technologies that simplify and quicken the process of designing, creating, maintaining, and querying aggregate tables while avoiding data explosion issues.

    As Olaf described above, we can use partition to store historical data. For more information about partition, please see:

    Best Regards,


    Elvis Long
    TechNet Community Support


    Thank you for the reply Elvis,

    Is the partition feature designed to do what I need? or you are saying "it can do" what I need?

    Do you know any tools that designed to work with SSAS to provide this type of needs?

    Thank you again

    Wednesday, July 24, 2013 1:06 PM