Process SSAS Cube using standrad edition.
Hi Team,
Could you Process walk me through steps in processing the SSAS Cube (standard edition) having multiple partitions(>3) for each measure group . Multiple partitions are required as it would impact the processing time in production having large volumes of data . The plan is to have the partitions defined on each Calendar Month. we want to have atleast 2 years of data . So 24 partitions for each measure group. This is achievable using enterprise edition but I don't know how to achieve the same using standard edition .
Please Note : We cannot use the enterprise edition as it costs 4 times to the standard edition cost and the objective is to have a workaround (working solution) to process the cube (with partitions using standard edition).
Please help me with the solution and will appreciate your quick reply .
Thanks,
Srk.
Could you Process walk me through steps in processing the SSAS Cube (standard edition) having multiple partitions(>3) for each measure group . Multiple partitions are required as it would impact the processing time in production having large volumes of data . The plan is to have the partitions defined on each Calendar Month. we want to have atleast 2 years of data . So 24 partitions for each measure group. This is achievable using enterprise edition but I don't know how to achieve the same using standard edition .
Please Note : We cannot use the enterprise edition as it costs 4 times to the standard edition cost and the objective is to have a workaround (working solution) to process the cube (with partitions using standard edition).
Please help me with the solution and will appreciate your quick reply .
Thanks,
Srk.
ASKER
Thanks Icohan ,
I know this is quite possible with the Process Full , but that is not the objective. Here are the questions below ?
1. In Production , we cannot do process full as the data grows every day and if I do process full then it runs for ever and I will have performance issues . Not ideal for Prod. environments , can only implement for test as they are static environments and data doesn't grow.
2. How can I use the Incremental Processing using standard edition (alternative work around solution )?
3. I understand that there could be three partitions allowed for each measure group in standard edition . so can I define the partitions as below for each measure group .
i. Current
This needs to be processed all the time (say for current year 2018) . I mean we have a nightly run ETL Job(scheduled for every nightly run ), where Process Cube is already designed using a SSIS Package . This should pick up the latest partition for 2018 and process the data for the current year.
Please Note : Cube should not Process Full
ii.History
This needs to be processed once in a year (ex:31st dec 2018) when we move to next year , so current partition data should merge in to History partition (can have only two years of data maximum) . So when we move to 1st Jan 2019 , data in current partition should not be there as the data from Current should have already moved to History by then .
Then only the History partition should be processed .
when data is greater than 2 years , the data here should be moved to Archive
iii. Archive
Can hold any data greater than 2 years and less than 3 years .
Anything older than 3 years should be unprocessed .
We can schedule this job weekly for this Archive as it is not priority . But this should only unprocess the data from the Archive partition , not all the partitions
This is the design I have made considering the limitation of the standard edition . But I am little confused , if this is possible to implement technically . If yes , could you please help me through the solution.
Greatly appreciated Icohan .
Thanks,
SRK.
I know this is quite possible with the Process Full , but that is not the objective. Here are the questions below ?
1. In Production , we cannot do process full as the data grows every day and if I do process full then it runs for ever and I will have performance issues . Not ideal for Prod. environments , can only implement for test as they are static environments and data doesn't grow.
2. How can I use the Incremental Processing using standard edition (alternative work around solution )?
3. I understand that there could be three partitions allowed for each measure group in standard edition . so can I define the partitions as below for each measure group .
i. Current
This needs to be processed all the time (say for current year 2018) . I mean we have a nightly run ETL Job(scheduled for every nightly run ), where Process Cube is already designed using a SSIS Package . This should pick up the latest partition for 2018 and process the data for the current year.
Please Note : Cube should not Process Full
ii.History
This needs to be processed once in a year (ex:31st dec 2018) when we move to next year , so current partition data should merge in to History partition (can have only two years of data maximum) . So when we move to 1st Jan 2019 , data in current partition should not be there as the data from Current should have already moved to History by then .
Then only the History partition should be processed .
when data is greater than 2 years , the data here should be moved to Archive
iii. Archive
Can hold any data greater than 2 years and less than 3 years .
Anything older than 3 years should be unprocessed .
We can schedule this job weekly for this Archive as it is not priority . But this should only unprocess the data from the Archive partition , not all the partitions
This is the design I have made considering the limitation of the standard edition . But I am little confused , if this is possible to implement technically . If yes , could you please help me through the solution.
Greatly appreciated Icohan .
Thanks,
SRK.
...So 24 partitions for each measure group.This is achievable using enterprise edition but I don't know how to achieve the same using standard edition .Sorry but according to Microsoft https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645993(v=sql.105) under "Scalability and Performance" - this Partitioning feature is not available in Standard edition.
ASKER
Icohan , I completely agree with you and not disputing the same as 24 partitions/Measure Group can be created using enterprise edition , but sql server standard edition does create 3 partitions . I mean can we not use those 3 partitions name them as (Current/History/Archive) and store the data as mentioned in the above comments . Please help me with the workaround solution sir using standard edition
The problem lies not in storing that data but process the SSAS Incremental or Parallel right?
I mean you could in the end create 24 tables/dimensions instead of using partitioning and fill those "buckets" via triggers on main table or some scheduled (SSIS) jobs however this in my opinion will still not help when you need to process the SSAS cube as unfortunately that feature/functionality is not there in standard edition where all you can do is "Populate FULL" as far as I'm aware.
I mean you could in the end create 24 tables/dimensions instead of using partitioning and fill those "buckets" via triggers on main table or some scheduled (SSIS) jobs however this in my opinion will still not help when you need to process the SSAS cube as unfortunately that feature/functionality is not there in standard edition where all you can do is "Populate FULL" as far as I'm aware.
ASKER
Hi Icohan,
This is what I mean as follows:
To support incremental processing, because when you do incremental processing on a partition in the background SSAS needs to create a new partition, process it and then merge it with your existing partition
https://www.experts-exchange.com/questions/29121565/Process-SSAS-Cube-using-standrad-edition.html?anchor=a42704513¬ificationFollowed=215491451&anchorAnswerId=42704513#a42704513
Please help me with the solution ?
Thanks,
SRK.
This is what I mean as follows:
To support incremental processing, because when you do incremental processing on a partition in the background SSAS needs to create a new partition, process it and then merge it with your existing partition
https://www.experts-exchange.com/questions/29121565/Process-SSAS-Cube-using-standrad-edition.html?anchor=a42704513¬ificationFollowed=215491451&anchorAnswerId=42704513#a42704513
Please help me with the solution ?
Thanks,
SRK.
ASKER
sorry , posted the wrong link , please see below :
https://blog.crossjoin.co.uk/2014/02/12/how-many-partitions-per-measure-group-are-allowed-in-ssas-standard-edition/
https://blog.crossjoin.co.uk/2014/02/12/how-many-partitions-per-measure-group-are-allowed-in-ssas-standard-edition/
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
I know Incremental, Parallel, Proactive Caching, and few other useful features/options are not available in the Standard edition however if your cube is not huge by using SSIS via SQL Scheduled job should do the work for you.