none
Incremental cube processing

    Question

  • Hello,

    Let me first explain my business scenario. In our BI application the primary source is Oracle EBS. As per requirement, there can be one or more quarters could remain open in ERP for back dated change. As a result, in daily ETL (SSIS and SQL stored procs), first  we are deleting all data of open quarter and then extracting it again from source to capture any backdated and latest change into the Data Mart. Say, now FY2014-Q4 is last closed quarter in ERP, which is maintained in a config table. So, we are daily deleting all transaction data from 1 April 2014 and then loading it again as per business requirement. That means, all data before 1 April is not changed at any time and data onwards 1 April will change. Say after a few days, when FY2015-Q1 (till June 2014) will be closed by business, we will fetch only data 1 July onwards, other data will remain static.

    Now, we are doing "Process Full" for the entire cube (dimension and measures) - for closed quaters and open quaters. Since the data prior to 1 April is not changing in Data Mart, how can we do a similar things like where based on a certain date (which is configurable) only recent trsansaction data will be processed in SSAS cube and the remaining data which is not getting changed in Data Mart, will remain as it is, like a static one.

    Would appreciate any technical guidance on how to solve this issue using SSAS/ SSIS. We have 2012 Enterprise version.


    Best Regards, Arka Mitra.



    • Edited by Arka Mitra Saturday, August 30, 2014 5:51 AM more clarifications
    Saturday, August 30, 2014 4:55 AM

Answers

  • Hi,

    Did you consider partitioning cube ? Create at least two partitions- Closed Qtr and Open Qtry. You can process open Qtr daily. Once you close a partition, you will have to process the whole cube or find another method to merge partition.

    Each partition will have different query based on the date range stored in the configurable table.

    regards

    Arun

    Tuesday, September 02, 2014 9:04 PM
  • Hi Arka,

    According to your description, there are colsed and open quarter data in your database, it will take long time to prcess all the cube, now what you want is process the data for open quarter only, right?

    In this case, you can create partition for those open and colsed quarters as Aru said. In Analysis Services, a partition provides the physical storage of fact data loaded into a measure group. Processing is more efficient because partitions can be processed independently and in parallel, on one or more servers. And over time, you can merge it to colsed partition. Here some document about partition for you reference.
    http://msdn.microsoft.com/en-IN/library/ms175318.aspx
    http://msdn.microsoft.com/en-in/library/hh230823.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, September 03, 2014 3:21 AM

All replies

  • Hi,

    Did you consider partitioning cube ? Create at least two partitions- Closed Qtr and Open Qtry. You can process open Qtr daily. Once you close a partition, you will have to process the whole cube or find another method to merge partition.

    Each partition will have different query based on the date range stored in the configurable table.

    regards

    Arun

    Tuesday, September 02, 2014 9:04 PM
  • Hi Arka,

    According to your description, there are colsed and open quarter data in your database, it will take long time to prcess all the cube, now what you want is process the data for open quarter only, right?

    In this case, you can create partition for those open and colsed quarters as Aru said. In Analysis Services, a partition provides the physical storage of fact data loaded into a measure group. Processing is more efficient because partitions can be processed independently and in parallel, on one or more servers. And over time, you can merge it to colsed partition. Here some document about partition for you reference.
    http://msdn.microsoft.com/en-IN/library/ms175318.aspx
    http://msdn.microsoft.com/en-in/library/hh230823.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, September 03, 2014 3:21 AM
  • Thanks Arun.

    We have went ahead with your suggestions to build a incremental cube processing solution in SSIS. I need a few more clarifications.

    1. We have around 30 dimensions. For dimensions, I am doing Process Full daily, as I have seen Process Update takes longer time. Process full takes around 10 minutes, whereas update takes 25 odd minutes.

    2. For open period partition, I am doing Process Full daily. For closed period partition, I am doing Process Index daily. Do I really need to Process Index ? Or should I left the partition as it is.

    3. I have seen if there's some error while processing the cube, I am getting error again while doing Process Update for closed period partition. The error is:
    [Analysis Services Execute DDL Task] Error: Errors in the metadata manager. The process type specified for the "xxxx" partition is not valid since it is not processed.

    Your response is appreciated.


    Best Regards, Arka Mitra.

    5 hours 41 minutes ago