I've become increasingly frustrated while trying to figure out why some dimensions, when process updated, require a processing of all the partitions in the measure group within that processing job.
We've got a cube with a single measure group with anywhere from 90 to 113 partitions (90 day partitions and a partition added every hour through the day). Every hour we update this cube adding a new partition to the measure group, also process updating all the dimensions within it. When three out of eight dimensions process during the hourly job, this adds 30 minutes of processing time every hour. It's unacceptable and I've spent days on trying to figure out why this happens.
At first I thought it was aggregations, however I removed all aggregations. Second, I thought it may be due to only dimensions that have hierarchies, however processing the Date dimension does not process all the partitions. Third, I thought it may be because data changed. However, I can update the dimension five times in a row without adding any data or partitions to the cube and still get get the processing of the partitions. Fourth, does this only happen to reference dimensions. Not the case.
My question is this: when and why does this happen?
See examples below:
Example one: Does NOT process the partitions within the measure group:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Object> <DatabaseID>Sales</DatabaseID> <DimensionID>v Dim Calendar</DimensionID> </Object> <Type>ProcessUpdate</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch> Processing Dimension 'Date' completed successfully. Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; Duration: 0:00:03 Processing Dimension Attribute '(All)' completed successfully. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; Duration: 0:00:00 Processing Dimension Attribute 'Year' completed successfully. 3 rows have been read. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 SQL queries 1 SELECT DISTINCT [dbo_vDim_Calendar].[CalendarYEAR] AS [dbo_vDim_CalendarCalendarYEAR0_0] FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] Processing Dimension Attribute 'Day Of The Week' completed successfully. 8 rows have been read. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 SQL queries 1 SELECT DISTINCT [dbo_vDim_Calendar].[DayNameOrderID] AS [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS [dbo_vDim_CalendarDayName0_1] FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] Processing Dimension Attribute 'Day Of Month' completed successfully. 32 rows have been read. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 SQL queries 1 SELECT DISTINCT [dbo_vDim_Calendar].[DayNumInMonth] AS [dbo_vDim_CalendarDayNumInMonth0_0] FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] Processing Dimension Attribute 'Month' completed successfully. 18 rows have been read. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 SQL queries 1 SELECT DISTINCT [dbo_vDim_Calendar].[YEARMo] AS [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS [dbo_vDim_CalendarCalendarYEAR0_2] FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] Processing Dimension Attribute 'Day' completed successfully. 498 rows have been read. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 SQL queries 1 SELECT DISTINCT [dbo_vDim_Calendar].[FullDate] AS [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS [dbo_vDim_CalendarYEARMo0_2] FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] Processing Dimension Attribute 'CalendarDWID' completed successfully. 498 rows have been read. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 SQL queries 1 SELECT DISTINCT [dbo_vDim_Calendar].[CalendarDWID] AS [dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS [dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS [dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] AS [dbo_vDim_CalendarDayNumInMonth0_3] FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] Processing Hierarchy 'Date' completed successfully. Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; Duration: 0:00:01 Processing Cube 'Sales Current' completed successfully. Start time: 5/12/2006 3:46:25 PM; End time: 5/12/2006 3:46:31 PM; Duration: 0:00:06 Processing Measure Group 'Sales measures' completed successfully. Start time: 5/12/2006 3:46:29 PM; End time: 5/12/2006 3:46:29 PM; Duration: 0:00:00
Example two: Processes all the partitions within the measure group.
when you create partitions, do you associate this partition to a specific slice of the cube?
use realtime partitions and the proactive caching feature. and/or incremental processing.
I have seen a webcast where a billion rows database and the associated cubes are updated in realtime. The cache of the cube himself is updated, this mean that adding new content in the cube fill the cache at the same time so the cube continue to be in warm mode. A normal process remove the cache and the cube become cold and slower to respond during the first accesses.
> I've become increasingly frustrated while trying to figure out why some > dimensions, when process updated, require a processing of all the > partitions in the measure group within that processing job.
> We've got a cube with a single measure group with anywhere from 90 to 113 > partitions (90 day partitions and a partition added every hour through the > day). Every hour we update this cube adding a new partition to the measure > group, also process updating all the dimensions within it. When three out > of eight dimensions process during the hourly job, this adds 30 minutes of > processing time every hour. It's unacceptable and I've spent days on > trying to figure out why this happens.
> At first I thought it was aggregations, however I removed all > aggregations. Second, I thought it may be due to only dimensions that have > hierarchies, however processing the Date dimension does not process all > the partitions. Third, I thought it may be because data changed. However, > I can update the dimension five times in a row without adding any data or > partitions to the cube and still get get the processing of the partitions. > Fourth, does this only happen to reference dimensions. Not the case.
> My question is this: when and why does this happen?
> See examples below:
> Example one: Does NOT process the partitions within the measure group:
> <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> > <Parallel> > <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > <Object> > <DatabaseID>Sales</DatabaseID> > <DimensionID>v Dim Calendar</DimensionID> > </Object> > <Type>ProcessUpdate</Type> > <WriteBackTableCreation>UseExisting</WriteBackTableCreation> > </Process> > </Parallel> > </Batch> > Processing Dimension 'Date' completed successfully. > Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; > Duration: 0:00:03 > Processing Dimension Attribute '(All)' completed successfully. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; > Duration: 0:00:00 > Processing Dimension Attribute 'Year' completed successfully. 3 rows have > been read. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > SQL queries 1 > SELECT > DISTINCT > [dbo_vDim_Calendar].[CalendarYEAR] AS [dbo_vDim_CalendarCalendarYEAR0_0] > FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > Processing Dimension Attribute 'Day Of The Week' completed successfully. > 8 rows have been read. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > SQL queries 1 > SELECT > DISTINCT > [dbo_vDim_Calendar].[DayNameOrderID] AS > [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS > [dbo_vDim_CalendarDayName0_1] > FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > Processing Dimension Attribute 'Day Of Month' completed successfully. 32 > rows have been read. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > SQL queries 1 > SELECT > DISTINCT > [dbo_vDim_Calendar].[DayNumInMonth] AS [dbo_vDim_CalendarDayNumInMonth0_0] > FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > Processing Dimension Attribute 'Month' completed successfully. 18 rows > have been read. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > SQL queries 1 > SELECT > DISTINCT > [dbo_vDim_Calendar].[YEARMo] AS > [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS > [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS > [dbo_vDim_CalendarCalendarYEAR0_2] > FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > Processing Dimension Attribute 'Day' completed successfully. 498 rows > have been read. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > SQL queries 1 > SELECT > DISTINCT > [dbo_vDim_Calendar].[FullDate] AS > [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS > [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS > [dbo_vDim_CalendarYEARMo0_2] > FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > Processing Dimension Attribute 'CalendarDWID' completed successfully. 498 > rows have been read. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > SQL queries 1 > SELECT > DISTINCT > [dbo_vDim_Calendar].[CalendarDWID] AS > [dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS > [dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS > [dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] > AS [dbo_vDim_CalendarDayNumInMonth0_3] > FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > Processing Hierarchy 'Date' completed successfully. > Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > Duration: 0:00:01 > Processing Cube 'Sales Current' completed successfully. > Start time: 5/12/2006 3:46:25 PM; End time: 5/12/2006 3:46:31 PM; > Duration: 0:00:06 > Processing Measure Group 'Sales measures' completed successfully. > Start time: 5/12/2006 3:46:29 PM; End time: 5/12/2006 3:46:29 PM; > Duration: 0:00:00
> Example two: Processes all the partitions within the measure group.
> when you create partitions, do you associate this partition to a specific > slice of the cube?
> use realtime partitions and the proactive caching feature. and/or > incremental processing.
> I have seen a webcast where a billion rows database and the associated > cubes are updated in realtime. > The cache of the cube himself is updated, this mean that adding new > content in the cube fill the cache at the same time so the cube continue > to be in warm mode. A normal process remove the cache and the cube become > cold and slower to respond during the first accesses.
> "Jesse O." <jespe...@hotmail.com> wrote in message > news:%23vQmrehdGHA.3388@TK2MSFTNGP05.phx.gbl... >> I've become increasingly frustrated while trying to figure out why some >> dimensions, when process updated, require a processing of all the >> partitions in the measure group within that processing job.
>> We've got a cube with a single measure group with anywhere from 90 to 113 >> partitions (90 day partitions and a partition added every hour through >> the day). Every hour we update this cube adding a new partition to the >> measure group, also process updating all the dimensions within it. When >> three out of eight dimensions process during the hourly job, this adds 30 >> minutes of processing time every hour. It's unacceptable and I've spent >> days on trying to figure out why this happens.
>> At first I thought it was aggregations, however I removed all >> aggregations. Second, I thought it may be due to only dimensions that >> have hierarchies, however processing the Date dimension does not process >> all the partitions. Third, I thought it may be because data changed. >> However, I can update the dimension five times in a row without adding >> any data or partitions to the cube and still get get the processing of >> the partitions. Fourth, does this only happen to reference dimensions. >> Not the case.
>> My question is this: when and why does this happen?
>> See examples below:
>> Example one: Does NOT process the partitions within the measure group:
>> <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> >> <Parallel> >> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" >> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> >> <Object> >> <DatabaseID>Sales</DatabaseID> >> <DimensionID>v Dim Calendar</DimensionID> >> </Object> >> <Type>ProcessUpdate</Type> >> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> >> </Process> >> </Parallel> >> </Batch> >> Processing Dimension 'Date' completed successfully. >> Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; >> Duration: 0:00:03 >> Processing Dimension Attribute '(All)' completed successfully. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; >> Duration: 0:00:00 >> Processing Dimension Attribute 'Year' completed successfully. 3 rows >> have been read. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> SQL queries 1 >> SELECT >> DISTINCT >> [dbo_vDim_Calendar].[CalendarYEAR] AS [dbo_vDim_CalendarCalendarYEAR0_0] >> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> Processing Dimension Attribute 'Day Of The Week' completed successfully. >> 8 rows have been read. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> SQL queries 1 >> SELECT >> DISTINCT >> [dbo_vDim_Calendar].[DayNameOrderID] AS >> [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS >> [dbo_vDim_CalendarDayName0_1] >> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> Processing Dimension Attribute 'Day Of Month' completed successfully. 32 >> rows have been read. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> SQL queries 1 >> SELECT >> DISTINCT >> [dbo_vDim_Calendar].[DayNumInMonth] AS >> [dbo_vDim_CalendarDayNumInMonth0_0] >> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> Processing Dimension Attribute 'Month' completed successfully. 18 rows >> have been read. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> SQL queries 1 >> SELECT >> DISTINCT >> [dbo_vDim_Calendar].[YEARMo] AS >> [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS >> [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS >> [dbo_vDim_CalendarCalendarYEAR0_2] >> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> Processing Dimension Attribute 'Day' completed successfully. 498 rows >> have been read. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> SQL queries 1 >> SELECT >> DISTINCT >> [dbo_vDim_Calendar].[FullDate] AS >> [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS >> [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS >> [dbo_vDim_CalendarYEARMo0_2] >> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> Processing Dimension Attribute 'CalendarDWID' completed successfully. >> 498 rows have been read. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> SQL queries 1 >> SELECT >> DISTINCT >> [dbo_vDim_Calendar].[CalendarDWID] AS >> [dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS >> [dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS >> [dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] >> AS [dbo_vDim_CalendarDayNumInMonth0_3] >> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> Processing Hierarchy 'Date' completed successfully. >> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> Duration: 0:00:01 >> Processing Cube 'Sales Current' completed successfully. >> Start time: 5/12/2006 3:46:25 PM; End time: 5/12/2006 3:46:31 PM; >> Duration: 0:00:06 >> Processing Measure Group 'Sales measures' completed successfully. >> Start time: 5/12/2006 3:46:29 PM; End time: 5/12/2006 3:46:29 PM; >> Duration: 0:00:00
>> Example two: Processes all the partitions within the measure group.
generally a reprocess is required when there is a change in a hierarchy. for example, if an employee is in New York and change to Boston, then the hierarchy changes which required a process of the cubes to recalculates the aggregations correctly. Changing a label (like the name of the customer) required only a simple update (if the key column is not the same as the label column).
if you anticipate this type of changing, you have to use the slow changing feature in the dimension. I have not use it in AS2005 but in AS2000 yes. this option will slow down accesses to the cubes because the system will keep only aggregates at the top (all member) and bottom levels (the employee) intermediate levels are recalculate when asked by the user. So when a employee change, there is no need to reprocess the cubes because the aggregation for boston and new york are not stored on the disk.
With AS2005 there is a proactive caching option at the dimension level, but I have not use it, if this caching works like in a cube... this could help you by reaggregate data when a table in the database change.
take a close look at the proactive caching features.
"Jesse O." <jespe...@hotmail.com> wrote in message
>> when you create partitions, do you associate this partition to a specific >> slice of the cube?
>> use realtime partitions and the proactive caching feature. and/or >> incremental processing.
>> I have seen a webcast where a billion rows database and the associated >> cubes are updated in realtime. >> The cache of the cube himself is updated, this mean that adding new >> content in the cube fill the cache at the same time so the cube continue >> to be in warm mode. A normal process remove the cache and the cube become >> cold and slower to respond during the first accesses.
>> "Jesse O." <jespe...@hotmail.com> wrote in message >> news:%23vQmrehdGHA.3388@TK2MSFTNGP05.phx.gbl... >>> I've become increasingly frustrated while trying to figure out why some >>> dimensions, when process updated, require a processing of all the >>> partitions in the measure group within that processing job.
>>> We've got a cube with a single measure group with anywhere from 90 to >>> 113 partitions (90 day partitions and a partition added every hour >>> through the day). Every hour we update this cube adding a new partition >>> to the measure group, also process updating all the dimensions within >>> it. When three out of eight dimensions process during the hourly job, >>> this adds 30 minutes of processing time every hour. It's unacceptable >>> and I've spent days on trying to figure out why this happens.
>>> At first I thought it was aggregations, however I removed all >>> aggregations. Second, I thought it may be due to only dimensions that >>> have hierarchies, however processing the Date dimension does not process >>> all the partitions. Third, I thought it may be because data changed. >>> However, I can update the dimension five times in a row without adding >>> any data or partitions to the cube and still get get the processing of >>> the partitions. Fourth, does this only happen to reference dimensions. >>> Not the case.
>>> My question is this: when and why does this happen?
>>> See examples below:
>>> Example one: Does NOT process the partitions within the measure group:
>>> <Batch >>> xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> >>> <Parallel> >>> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" >>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> >>> <Object> >>> <DatabaseID>Sales</DatabaseID> >>> <DimensionID>v Dim Calendar</DimensionID> >>> </Object> >>> <Type>ProcessUpdate</Type> >>> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> >>> </Process> >>> </Parallel> >>> </Batch> >>> Processing Dimension 'Date' completed successfully. >>> Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; >>> Duration: 0:00:03 >>> Processing Dimension Attribute '(All)' completed successfully. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; >>> Duration: 0:00:00 >>> Processing Dimension Attribute 'Year' completed successfully. 3 rows >>> have been read. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> SQL queries 1 >>> SELECT >>> DISTINCT >>> [dbo_vDim_Calendar].[CalendarYEAR] AS [dbo_vDim_CalendarCalendarYEAR0_0] >>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>> Processing Dimension Attribute 'Day Of The Week' completed >>> successfully. 8 rows have been read. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> SQL queries 1 >>> SELECT >>> DISTINCT >>> [dbo_vDim_Calendar].[DayNameOrderID] AS >>> [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS >>> [dbo_vDim_CalendarDayName0_1] >>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>> Processing Dimension Attribute 'Day Of Month' completed successfully. >>> 32 rows have been read. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> SQL queries 1 >>> SELECT >>> DISTINCT >>> [dbo_vDim_Calendar].[DayNumInMonth] AS >>> [dbo_vDim_CalendarDayNumInMonth0_0] >>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>> Processing Dimension Attribute 'Month' completed successfully. 18 rows >>> have been read. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> SQL queries 1 >>> SELECT >>> DISTINCT >>> [dbo_vDim_Calendar].[YEARMo] AS >>> [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS >>> [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS >>> [dbo_vDim_CalendarCalendarYEAR0_2] >>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>> Processing Dimension Attribute 'Day' completed successfully. 498 rows >>> have been read. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> SQL queries 1 >>> SELECT >>> DISTINCT >>> [dbo_vDim_Calendar].[FullDate] AS >>> [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS >>> [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS >>> [dbo_vDim_CalendarYEARMo0_2] >>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>> Processing Dimension Attribute 'CalendarDWID' completed successfully. >>> 498 rows have been read. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> SQL queries 1 >>> SELECT >>> DISTINCT >>> [dbo_vDim_Calendar].[CalendarDWID] AS >>> [dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS >>> [dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS >>> [dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] >>> AS [dbo_vDim_CalendarDayNumInMonth0_3] >>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>> Processing Hierarchy 'Date' completed successfully. >>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>> Duration: 0:00:01 >>> Processing Cube 'Sales Current' completed successfully. >>> Start time: 5/12/2006 3:46:25 PM; End time: 5/12/2006 3:46:31 PM; >>> Duration: 0:00:06 >>> Processing Measure Group 'Sales measures' completed successfully. >>> Start time: 5/12/2006 3:46:29 PM; End time: 5/12/2006 3:46:29 PM; >>> Duration: 0:00:00
>>> Example two: Processes all the partitions within the measure group.
This behaviour could be depend on whether your dimension has flexible or rigid attribute relationships. BOL did not have a very succinct definition, but the following is from one of the tutorials:
...you can specify that the relationship is either flexible or rigid. If you define a relationship as rigid, Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as rigid actually changes, Analysis Services generates an error during processing unless the dimension is fully processed
Time inherently has rigid relationships (you can't change which month a particular date belongs to). But I believe all other dimension default to flexible relationships which would mean that processing them would result in all the aggregations (not the leaf level data) being dropped and re-processed.
> generally a reprocess is required when there is a change in a hierarchy. > for example, if an employee is in New York and change to Boston, then the > hierarchy changes which required a process of the cubes to recalculates the > aggregations correctly. > Changing a label (like the name of the customer) required only a simple > update (if the key column is not the same as the label column).
> if you anticipate this type of changing, you have to use the slow changing > feature in the dimension. I have not use it in AS2005 but in AS2000 yes. > this option will slow down accesses to the cubes because the system will > keep only aggregates at the top (all member) and bottom levels (the > employee) > intermediate levels are recalculate when asked by the user. > So when a employee change, there is no need to reprocess the cubes because > the aggregation for boston and new york are not stored on the disk.
> With AS2005 there is a proactive caching option at the dimension level, but > I have not use it, if this caching works like in a cube... this could help > you by reaggregate data when a table in the database change.
> take a close look at the proactive caching features.
> "Jesse O." <jespe...@hotmail.com> wrote in message > news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... > > No, no slice set. We're using MOLAP. Thanks for your suggestion of > > proactive caching.
> > I'm still confused as to why some dimensions process partitions while > > others don't.
> >> when you create partitions, do you associate this partition to a specific > >> slice of the cube?
> >> use realtime partitions and the proactive caching feature. and/or > >> incremental processing.
> >> I have seen a webcast where a billion rows database and the associated > >> cubes are updated in realtime. > >> The cache of the cube himself is updated, this mean that adding new
My guess here is that it is not aggregations but rather indexes being processed.
I would suggest running Profiler against the server during processing and seeing the events there -- the events for the partition should be for ProcessIndex...
The actual processing of partitions doesn't appear to be significant here though. It shows as:
Also, are you doing ProcessUpdate on all the dimensions in one operation (using Batch/Parallel)? This would unify the cube processing into the same transaction so that it only needs to happen once instead of repeated for each of the dimensions.
This posting is provided "AS IS" with no warranties, and confers no rights Please do not send email directly to this alias. This alias is for newsgroup purposes only.
"Darren Gosbell" <j...@newsgroups.nospam> wrote in message
> This behaviour could be depend on whether your dimension has flexible or > rigid attribute relationships. BOL did not have a very succinct > definition, but the following is from one of the tutorials:
> ...you can specify that the relationship is either flexible or rigid. If > you define a relationship as rigid, Analysis Services retains > aggregations when the dimension is updated. If a relationship that is > defined as rigid actually changes, Analysis Services generates an error > during processing unless the dimension is fully processed
> Time inherently has rigid relationships (you can't change which month a > particular date belongs to). But I believe all other dimension default > to flexible relationships which would mean that processing them would > result in all the aggregations (not the leaf level data) being dropped > and re-processed.
> In article <#DAflH9dGHA....@TK2MSFTNGP02.phx.gbl>, willg...@hotmail.com > says... >> generally a reprocess is required when there is a change in a hierarchy. >> for example, if an employee is in New York and change to Boston, then the >> hierarchy changes which required a process of the cubes to recalculates >> the >> aggregations correctly. >> Changing a label (like the name of the customer) required only a simple >> update (if the key column is not the same as the label column).
>> if you anticipate this type of changing, you have to use the slow >> changing >> feature in the dimension. I have not use it in AS2005 but in AS2000 yes. >> this option will slow down accesses to the cubes because the system will >> keep only aggregates at the top (all member) and bottom levels (the >> employee) >> intermediate levels are recalculate when asked by the user. >> So when a employee change, there is no need to reprocess the cubes >> because >> the aggregation for boston and new york are not stored on the disk.
>> With AS2005 there is a proactive caching option at the dimension level, >> but >> I have not use it, if this caching works like in a cube... this could >> help >> you by reaggregate data when a table in the database change.
>> take a close look at the proactive caching features.
>> "Jesse O." <jespe...@hotmail.com> wrote in message >> news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... >> > No, no slice set. We're using MOLAP. Thanks for your suggestion of >> > proactive caching.
>> > I'm still confused as to why some dimensions process partitions while >> > others don't.
>> >> when you create partitions, do you associate this partition to a >> >> specific >> >> slice of the cube?
>> >> use realtime partitions and the proactive caching feature. and/or >> >> incremental processing.
>> >> I have seen a webcast where a billion rows database and the associated >> >> cubes are updated in realtime. >> >> The cache of the cube himself is updated, this mean that adding new
> My guess here is that it is not aggregations but rather indexes being > processed.
> I would suggest running Profiler against the server during processing and > seeing the events there -- the events for the partition should be for > ProcessIndex...
> The actual processing of partitions doesn't appear to be significant here > though. It shows as:
> Also, are you doing ProcessUpdate on all the dimensions in one operation > (using Batch/Parallel)? This would unify the cube processing into the same > transaction so that it only needs to happen once instead of repeated for > each of the dimensions.
> This posting is provided "AS IS" with no warranties, and confers no rights > Please do not send email directly to this alias. This alias is for > newsgroup > purposes only.
> "Darren Gosbell" <j...@newsgroups.nospam> wrote in message > news:MPG.1ed32ff1a7122d298991a@news.microsoft.com... >> This behaviour could be depend on whether your dimension has flexible or >> rigid attribute relationships. BOL did not have a very succinct >> definition, but the following is from one of the tutorials:
>> ...you can specify that the relationship is either flexible or rigid. If >> you define a relationship as rigid, Analysis Services retains >> aggregations when the dimension is updated. If a relationship that is >> defined as rigid actually changes, Analysis Services generates an error >> during processing unless the dimension is fully processed
>> Time inherently has rigid relationships (you can't change which month a >> particular date belongs to). But I believe all other dimension default >> to flexible relationships which would mean that processing them would >> result in all the aggregations (not the leaf level data) being dropped >> and re-processed.
>> In article <#DAflH9dGHA....@TK2MSFTNGP02.phx.gbl>, willg...@hotmail.com >> says... >>> generally a reprocess is required when there is a change in a hierarchy. >>> for example, if an employee is in New York and change to Boston, then >>> the >>> hierarchy changes which required a process of the cubes to recalculates >>> the >>> aggregations correctly. >>> Changing a label (like the name of the customer) required only a simple >>> update (if the key column is not the same as the label column).
>>> if you anticipate this type of changing, you have to use the slow >>> changing >>> feature in the dimension. I have not use it in AS2005 but in AS2000 yes. >>> this option will slow down accesses to the cubes because the system will >>> keep only aggregates at the top (all member) and bottom levels (the >>> employee) >>> intermediate levels are recalculate when asked by the user. >>> So when a employee change, there is no need to reprocess the cubes >>> because >>> the aggregation for boston and new york are not stored on the disk.
>>> With AS2005 there is a proactive caching option at the dimension level, >>> but >>> I have not use it, if this caching works like in a cube... this could >>> help >>> you by reaggregate data when a table in the database change.
>>> take a close look at the proactive caching features.
>>> "Jesse O." <jespe...@hotmail.com> wrote in message >>> news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... >>> > No, no slice set. We're using MOLAP. Thanks for your suggestion of >>> > proactive caching.
>>> > I'm still confused as to why some dimensions process partitions while >>> > others don't.
>>> >> when you create partitions, do you associate this partition to a >>> >> specific >>> >> slice of the cube?
>>> >> use realtime partitions and the proactive caching feature. and/or >>> >> incremental processing.
>>> >> I have seen a webcast where a billion rows database and the >>> >> associated >>> >> cubes are updated in realtime. >>> >> The cache of the cube himself is updated, this mean that adding new
I'm not so sold into Proactive Caching yet, at least in our environment.
We have a typical datawarehouse which is loaded and updated every hour through a batch. Our process cube job runs every five minutes to check to see if a batch has completed. If a batch has completed, the associated dimensions are updated and a new partition is added to the cube. When there are 24 batches completed for that day, we delete the hour partitions for that day and create and process a day partition.
The amount of data we process every hour is large. Two of our dimensions have two million plus members and our largest fact table grows by four million rows an hour. We're planning on storing 90 days of data in this cube which is processed hourly, and another cube which processes daily and has all historical data. Our pain point right now is the length to process the dimensions every hour. It constitutes 95% of our processing time.
I don't see a real need for proactive caching since it generally seems like it's done against the transaction system, not the datawarehouse. Part of that is due to my inexperience in SSAS2005 and not really having a good grasp on proactive caching.
> generally a reprocess is required when there is a change in a hierarchy. > for example, if an employee is in New York and change to Boston, then the > hierarchy changes which required a process of the cubes to recalculates > the aggregations correctly. > Changing a label (like the name of the customer) required only a simple > update (if the key column is not the same as the label column).
> if you anticipate this type of changing, you have to use the slow changing > feature in the dimension. I have not use it in AS2005 but in AS2000 yes. > this option will slow down accesses to the cubes because the system will > keep only aggregates at the top (all member) and bottom levels (the > employee) > intermediate levels are recalculate when asked by the user. > So when a employee change, there is no need to reprocess the cubes because > the aggregation for boston and new york are not stored on the disk.
> With AS2005 there is a proactive caching option at the dimension level, > but I have not use it, if this caching works like in a cube... this could > help you by reaggregate data when a table in the database change.
> take a close look at the proactive caching features.
> "Jesse O." <jespe...@hotmail.com> wrote in message > news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... >> No, no slice set. We're using MOLAP. Thanks for your suggestion of >> proactive caching.
>> I'm still confused as to why some dimensions process partitions while >> others don't.
>>> when you create partitions, do you associate this partition to a >>> specific slice of the cube?
>>> use realtime partitions and the proactive caching feature. and/or >>> incremental processing.
>>> I have seen a webcast where a billion rows database and the associated >>> cubes are updated in realtime. >>> The cache of the cube himself is updated, this mean that adding new >>> content in the cube fill the cache at the same time so the cube continue >>> to be in warm mode. A normal process remove the cache and the cube >>> become cold and slower to respond during the first accesses.
>>> "Jesse O." <jespe...@hotmail.com> wrote in message >>> news:%23vQmrehdGHA.3388@TK2MSFTNGP05.phx.gbl... >>>> I've become increasingly frustrated while trying to figure out why some >>>> dimensions, when process updated, require a processing of all the >>>> partitions in the measure group within that processing job.
>>>> We've got a cube with a single measure group with anywhere from 90 to >>>> 113 partitions (90 day partitions and a partition added every hour >>>> through the day). Every hour we update this cube adding a new partition >>>> to the measure group, also process updating all the dimensions within >>>> it. When three out of eight dimensions process during the hourly job, >>>> this adds 30 minutes of processing time every hour. It's unacceptable >>>> and I've spent days on trying to figure out why this happens.
>>>> At first I thought it was aggregations, however I removed all >>>> aggregations. Second, I thought it may be due to only dimensions that >>>> have hierarchies, however processing the Date dimension does not >>>> process all the partitions. Third, I thought it may be because data >>>> changed. However, I can update the dimension five times in a row >>>> without adding any data or partitions to the cube and still get get the >>>> processing of the partitions. Fourth, does this only happen to >>>> reference dimensions. Not the case.
>>>> My question is this: when and why does this happen?
>>>> See examples below:
>>>> Example one: Does NOT process the partitions within the measure group:
>>>> <Batch >>>> xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> >>>> <Parallel> >>>> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" >>>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> >>>> <Object> >>>> <DatabaseID>Sales</DatabaseID> >>>> <DimensionID>v Dim Calendar</DimensionID> >>>> </Object> >>>> <Type>ProcessUpdate</Type> >>>> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> >>>> </Process> >>>> </Parallel> >>>> </Batch> >>>> Processing Dimension 'Date' completed successfully. >>>> Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; >>>> Duration: 0:00:03 >>>> Processing Dimension Attribute '(All)' completed successfully. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; >>>> Duration: 0:00:00 >>>> Processing Dimension Attribute 'Year' completed successfully. 3 rows >>>> have been read. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> SQL queries 1 >>>> SELECT >>>> DISTINCT >>>> [dbo_vDim_Calendar].[CalendarYEAR] AS >>>> [dbo_vDim_CalendarCalendarYEAR0_0] >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>>> Processing Dimension Attribute 'Day Of The Week' completed >>>> successfully. 8 rows have been read. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> SQL queries 1 >>>> SELECT >>>> DISTINCT >>>> [dbo_vDim_Calendar].[DayNameOrderID] AS >>>> [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS >>>> [dbo_vDim_CalendarDayName0_1] >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>>> Processing Dimension Attribute 'Day Of Month' completed successfully. >>>> 32 rows have been read. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> SQL queries 1 >>>> SELECT >>>> DISTINCT >>>> [dbo_vDim_Calendar].[DayNumInMonth] AS >>>> [dbo_vDim_CalendarDayNumInMonth0_0] >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>>> Processing Dimension Attribute 'Month' completed successfully. 18 rows >>>> have been read. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> SQL queries 1 >>>> SELECT >>>> DISTINCT >>>> [dbo_vDim_Calendar].[YEARMo] AS >>>> [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS >>>> [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS >>>> [dbo_vDim_CalendarCalendarYEAR0_2] >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>>> Processing Dimension Attribute 'Day' completed successfully. 498 rows >>>> have been read. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> SQL queries 1 >>>> SELECT >>>> DISTINCT >>>> [dbo_vDim_Calendar].[FullDate] AS >>>> [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS >>>> [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS >>>> [dbo_vDim_CalendarYEARMo0_2] >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>>> Processing Dimension Attribute 'CalendarDWID' completed successfully. >>>> 498 rows have been read. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> SQL queries 1 >>>> SELECT >>>> DISTINCT >>>> [dbo_vDim_Calendar].[CalendarDWID] AS >>>> [dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS >>>> [dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS >>>> [dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] >>>> AS [dbo_vDim_CalendarDayNumInMonth0_3] >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >>>> Processing Hierarchy 'Date' completed successfully. >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >>>> Duration: 0:00:01 >>>> Processing Cube 'Sales Current' completed successfully. >>>> Start time: 5/12/2006 3:46:25 PM; End time: 5/12/2006 3:46:31 PM; >>>> Duration: 0:00:06 >>>> Processing Measure Group 'Sales measures' completed successfully. >>>> Start time: 5/12/2006 3:46:29 PM; End time: 5/12/2006 3:46:29 PM; >>>> Duration: 0:00:00
>>>> Example two: Processes all the partitions within the measure group.
One of the things that you could look at to speed up updates is snowflaking your large dimensions. Here is a good Blog on the issue. http://sqljunkies.com/WebLog/sqlbi/archive/2005/10/07/17040.aspx I have not had the opportunity to test this, but my dimensions are not nearly as large and we only update daily.
I am also going to be setting most of my dimensions to fixed for my 2nd itteration to try and stop the index rebuilds that need to happen all the time.
"Jesse O." wrote: > I'm not so sold into Proactive Caching yet, at least in our environment.
> We have a typical datawarehouse which is loaded and updated every hour > through a batch. Our process cube job runs every five minutes to check to > see if a batch has completed. If a batch has completed, the associated > dimensions are updated and a new partition is added to the cube. When there > are 24 batches completed for that day, we delete the hour partitions for > that day and create and process a day partition.
> The amount of data we process every hour is large. Two of our dimensions > have two million plus members and our largest fact table grows by four > million rows an hour. We're planning on storing 90 days of data in this cube > which is processed hourly, and another cube which processes daily and has > all historical data. Our pain point right now is the length to process the > dimensions every hour. It constitutes 95% of our processing time.
> I don't see a real need for proactive caching since it generally seems like > it's done against the transaction system, not the datawarehouse. Part of > that is due to my inexperience in SSAS2005 and not really having a good > grasp on proactive caching.
> What do you guys think?
> "Jeje" <willg...@hotmail.com> wrote in message > news:%23DAflH9dGHA.564@TK2MSFTNGP02.phx.gbl... > > generally a reprocess is required when there is a change in a hierarchy. > > for example, if an employee is in New York and change to Boston, then the > > hierarchy changes which required a process of the cubes to recalculates > > the aggregations correctly. > > Changing a label (like the name of the customer) required only a simple > > update (if the key column is not the same as the label column).
> > if you anticipate this type of changing, you have to use the slow changing > > feature in the dimension. I have not use it in AS2005 but in AS2000 yes. > > this option will slow down accesses to the cubes because the system will > > keep only aggregates at the top (all member) and bottom levels (the > > employee) > > intermediate levels are recalculate when asked by the user. > > So when a employee change, there is no need to reprocess the cubes because > > the aggregation for boston and new york are not stored on the disk.
> > With AS2005 there is a proactive caching option at the dimension level, > > but I have not use it, if this caching works like in a cube... this could > > help you by reaggregate data when a table in the database change.
> > take a close look at the proactive caching features.
> > "Jesse O." <jespe...@hotmail.com> wrote in message > > news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... > >> No, no slice set. We're using MOLAP. Thanks for your suggestion of > >> proactive caching.
> >> I'm still confused as to why some dimensions process partitions while > >> others don't.
> >>> when you create partitions, do you associate this partition to a > >>> specific slice of the cube?
> >>> use realtime partitions and the proactive caching feature. and/or > >>> incremental processing.
> >>> I have seen a webcast where a billion rows database and the associated > >>> cubes are updated in realtime. > >>> The cache of the cube himself is updated, this mean that adding new > >>> content in the cube fill the cache at the same time so the cube continue > >>> to be in warm mode. A normal process remove the cache and the cube > >>> become cold and slower to respond during the first accesses.
> >>> "Jesse O." <jespe...@hotmail.com> wrote in message > >>> news:%23vQmrehdGHA.3388@TK2MSFTNGP05.phx.gbl... > >>>> I've become increasingly frustrated while trying to figure out why some > >>>> dimensions, when process updated, require a processing of all the > >>>> partitions in the measure group within that processing job.
> >>>> We've got a cube with a single measure group with anywhere from 90 to > >>>> 113 partitions (90 day partitions and a partition added every hour > >>>> through the day). Every hour we update this cube adding a new partition > >>>> to the measure group, also process updating all the dimensions within > >>>> it. When three out of eight dimensions process during the hourly job, > >>>> this adds 30 minutes of processing time every hour. It's unacceptable > >>>> and I've spent days on trying to figure out why this happens.
> >>>> At first I thought it was aggregations, however I removed all > >>>> aggregations. Second, I thought it may be due to only dimensions that > >>>> have hierarchies, however processing the Date dimension does not > >>>> process all the partitions. Third, I thought it may be because data > >>>> changed. However, I can update the dimension five times in a row > >>>> without adding any data or partitions to the cube and still get get the > >>>> processing of the partitions. Fourth, does this only happen to > >>>> reference dimensions. Not the case.
> >>>> My question is this: when and why does this happen?
> >>>> See examples below:
> >>>> Example one: Does NOT process the partitions within the measure group:
> >>>> <Batch > >>>> xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> > >>>> <Parallel> > >>>> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" > >>>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > >>>> <Object> > >>>> <DatabaseID>Sales</DatabaseID> > >>>> <DimensionID>v Dim Calendar</DimensionID> > >>>> </Object> > >>>> <Type>ProcessUpdate</Type> > >>>> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> > >>>> </Process> > >>>> </Parallel> > >>>> </Batch> > >>>> Processing Dimension 'Date' completed successfully. > >>>> Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; > >>>> Duration: 0:00:03 > >>>> Processing Dimension Attribute '(All)' completed successfully. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; > >>>> Duration: 0:00:00 > >>>> Processing Dimension Attribute 'Year' completed successfully. 3 rows > >>>> have been read. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > >>>> Duration: 0:00:01 > >>>> SQL queries 1 > >>>> SELECT > >>>> DISTINCT > >>>> [dbo_vDim_Calendar].[CalendarYEAR] AS > >>>> [dbo_vDim_CalendarCalendarYEAR0_0] > >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > >>>> Processing Dimension Attribute 'Day Of The Week' completed > >>>> successfully. 8 rows have been read. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > >>>> Duration: 0:00:01 > >>>> SQL queries 1 > >>>> SELECT > >>>> DISTINCT > >>>> [dbo_vDim_Calendar].[DayNameOrderID] AS > >>>> [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] AS > >>>> [dbo_vDim_CalendarDayName0_1] > >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > >>>> Processing Dimension Attribute 'Day Of Month' completed successfully. > >>>> 32 rows have been read. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > >>>> Duration: 0:00:01 > >>>> SQL queries 1 > >>>> SELECT > >>>> DISTINCT > >>>> [dbo_vDim_Calendar].[DayNumInMonth] AS > >>>> [dbo_vDim_CalendarDayNumInMonth0_0] > >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > >>>> Processing Dimension Attribute 'Month' completed successfully. 18 rows > >>>> have been read. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > >>>> Duration: 0:00:01 > >>>> SQL queries 1 > >>>> SELECT > >>>> DISTINCT > >>>> [dbo_vDim_Calendar].[YEARMo] AS > >>>> [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS > >>>> [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] AS > >>>> [dbo_vDim_CalendarCalendarYEAR0_2] > >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > >>>> Processing Dimension Attribute 'Day' completed successfully. 498 rows > >>>> have been read. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > >>>> Duration: 0:00:01 > >>>> SQL queries 1 > >>>> SELECT > >>>> DISTINCT > >>>> [dbo_vDim_Calendar].[FullDate] AS > >>>> [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS > >>>> [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS > >>>> [dbo_vDim_CalendarYEARMo0_2] > >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] > >>>> Processing Dimension Attribute 'CalendarDWID' completed successfully. > >>>> 498 rows have been read. > >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; > >>>> Duration: 0:00:01 > >>>> SQL queries 1 > >>>> SELECT > >>>> DISTINCT > >>>> [dbo_vDim_Calendar].[CalendarDWID] AS > >>>> [dbo_vDim_CalendarCalendarDWID0_0],[dbo_vDim_Calendar].[FullDate] AS > >>>> [dbo_vDim_CalendarFullDate0_1],[dbo_vDim_Calendar].[DayNameOrderID] AS > >>>> [dbo_vDim_CalendarDayNameOrderID0_2],[dbo_vDim_Calendar].[DayNumInMonth] > >>>> AS
> One of the things that you could look at to speed up updates is > snowflaking > your large dimensions. Here is a good Blog on the issue. > http://sqljunkies.com/WebLog/sqlbi/archive/2005/10/07/17040.aspx > I have not had the opportunity to test this, but my dimensions are not > nearly as large and we only update daily.
> I am also going to be setting most of my dimensions to fixed for my 2nd > itteration to try and stop the index rebuilds that need to happen all the > time.
> "Jesse O." wrote:
>> I'm not so sold into Proactive Caching yet, at least in our environment.
>> We have a typical datawarehouse which is loaded and updated every hour >> through a batch. Our process cube job runs every five minutes to check to >> see if a batch has completed. If a batch has completed, the associated >> dimensions are updated and a new partition is added to the cube. When >> there >> are 24 batches completed for that day, we delete the hour partitions for >> that day and create and process a day partition.
>> The amount of data we process every hour is large. Two of our dimensions >> have two million plus members and our largest fact table grows by four >> million rows an hour. We're planning on storing 90 days of data in this >> cube >> which is processed hourly, and another cube which processes daily and has >> all historical data. Our pain point right now is the length to process >> the >> dimensions every hour. It constitutes 95% of our processing time.
>> I don't see a real need for proactive caching since it generally seems >> like >> it's done against the transaction system, not the datawarehouse. Part of >> that is due to my inexperience in SSAS2005 and not really having a good >> grasp on proactive caching.
>> What do you guys think?
>> "Jeje" <willg...@hotmail.com> wrote in message >> news:%23DAflH9dGHA.564@TK2MSFTNGP02.phx.gbl... >> > generally a reprocess is required when there is a change in a >> > hierarchy. >> > for example, if an employee is in New York and change to Boston, then >> > the >> > hierarchy changes which required a process of the cubes to recalculates >> > the aggregations correctly. >> > Changing a label (like the name of the customer) required only a simple >> > update (if the key column is not the same as the label column).
>> > if you anticipate this type of changing, you have to use the slow >> > changing >> > feature in the dimension. I have not use it in AS2005 but in AS2000 >> > yes. >> > this option will slow down accesses to the cubes because the system >> > will >> > keep only aggregates at the top (all member) and bottom levels (the >> > employee) >> > intermediate levels are recalculate when asked by the user. >> > So when a employee change, there is no need to reprocess the cubes >> > because >> > the aggregation for boston and new york are not stored on the disk.
>> > With AS2005 there is a proactive caching option at the dimension level, >> > but I have not use it, if this caching works like in a cube... this >> > could >> > help you by reaggregate data when a table in the database change.
>> > take a close look at the proactive caching features.
>> > "Jesse O." <jespe...@hotmail.com> wrote in message >> > news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... >> >> No, no slice set. We're using MOLAP. Thanks for your suggestion of >> >> proactive caching.
>> >> I'm still confused as to why some dimensions process partitions while >> >> others don't.
>> >>> when you create partitions, do you associate this partition to a >> >>> specific slice of the cube?
>> >>> use realtime partitions and the proactive caching feature. and/or >> >>> incremental processing.
>> >>> I have seen a webcast where a billion rows database and the >> >>> associated >> >>> cubes are updated in realtime. >> >>> The cache of the cube himself is updated, this mean that adding new >> >>> content in the cube fill the cache at the same time so the cube >> >>> continue >> >>> to be in warm mode. A normal process remove the cache and the cube >> >>> become cold and slower to respond during the first accesses.
>> >>> "Jesse O." <jespe...@hotmail.com> wrote in message >> >>> news:%23vQmrehdGHA.3388@TK2MSFTNGP05.phx.gbl... >> >>>> I've become increasingly frustrated while trying to figure out why >> >>>> some >> >>>> dimensions, when process updated, require a processing of all the >> >>>> partitions in the measure group within that processing job.
>> >>>> We've got a cube with a single measure group with anywhere from 90 >> >>>> to >> >>>> 113 partitions (90 day partitions and a partition added every hour >> >>>> through the day). Every hour we update this cube adding a new >> >>>> partition >> >>>> to the measure group, also process updating all the dimensions >> >>>> within >> >>>> it. When three out of eight dimensions process during the hourly >> >>>> job, >> >>>> this adds 30 minutes of processing time every hour. It's >> >>>> unacceptable >> >>>> and I've spent days on trying to figure out why this happens.
>> >>>> At first I thought it was aggregations, however I removed all >> >>>> aggregations. Second, I thought it may be due to only dimensions >> >>>> that >> >>>> have hierarchies, however processing the Date dimension does not >> >>>> process all the partitions. Third, I thought it may be because data >> >>>> changed. However, I can update the dimension five times in a row >> >>>> without adding any data or partitions to the cube and still get get >> >>>> the >> >>>> processing of the partitions. Fourth, does this only happen to >> >>>> reference dimensions. Not the case.
>> >>>> My question is this: when and why does this happen?
>> >>>> See examples below:
>> >>>> Example one: Does NOT process the partitions within the measure >> >>>> group:
>> >>>> <Batch >> >>>> xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> >> >>>> <Parallel> >> >>>> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" >> >>>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> >> >>>> <Object> >> >>>> <DatabaseID>Sales</DatabaseID> >> >>>> <DimensionID>v Dim Calendar</DimensionID> >> >>>> </Object> >> >>>> <Type>ProcessUpdate</Type> >> >>>> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> >> >>>> </Process> >> >>>> </Parallel> >> >>>> </Batch> >> >>>> Processing Dimension 'Date' completed successfully. >> >>>> Start time: 5/12/2006 3:46:22 PM; End time: 5/12/2006 3:46:25 PM; >> >>>> Duration: 0:00:03 >> >>>> Processing Dimension Attribute '(All)' completed successfully. >> >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:23 PM; >> >>>> Duration: 0:00:00 >> >>>> Processing Dimension Attribute 'Year' completed successfully. 3 >> >>>> rows >> >>>> have been read. >> >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> >>>> Duration: 0:00:01 >> >>>> SQL queries 1 >> >>>> SELECT >> >>>> DISTINCT >> >>>> [dbo_vDim_Calendar].[CalendarYEAR] AS >> >>>> [dbo_vDim_CalendarCalendarYEAR0_0] >> >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> >>>> Processing Dimension Attribute 'Day Of The Week' completed >> >>>> successfully. 8 rows have been read. >> >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> >>>> Duration: 0:00:01 >> >>>> SQL queries 1 >> >>>> SELECT >> >>>> DISTINCT >> >>>> [dbo_vDim_Calendar].[DayNameOrderID] AS >> >>>> [dbo_vDim_CalendarDayNameOrderID0_0],[dbo_vDim_Calendar].[DayName] >> >>>> AS >> >>>> [dbo_vDim_CalendarDayName0_1] >> >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> >>>> Processing Dimension Attribute 'Day Of Month' completed >> >>>> successfully. >> >>>> 32 rows have been read. >> >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> >>>> Duration: 0:00:01 >> >>>> SQL queries 1 >> >>>> SELECT >> >>>> DISTINCT >> >>>> [dbo_vDim_Calendar].[DayNumInMonth] AS >> >>>> [dbo_vDim_CalendarDayNumInMonth0_0] >> >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> >>>> Processing Dimension Attribute 'Month' completed successfully. 18 >> >>>> rows >> >>>> have been read. >> >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> >>>> Duration: 0:00:01 >> >>>> SQL queries 1 >> >>>> SELECT >> >>>> DISTINCT >> >>>> [dbo_vDim_Calendar].[YEARMo] AS >> >>>> [dbo_vDim_CalendarYEARMo0_0],[dbo_vDim_Calendar].[MonthName] AS >> >>>> [dbo_vDim_CalendarMonthName0_1],[dbo_vDim_Calendar].[CalendarYEAR] >> >>>> AS >> >>>> [dbo_vDim_CalendarCalendarYEAR0_2] >> >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> >>>> Processing Dimension Attribute 'Day' completed successfully. 498 >> >>>> rows >> >>>> have been read. >> >>>> Start time: 5/12/2006 3:46:23 PM; End time: 5/12/2006 3:46:24 PM; >> >>>> Duration: 0:00:01 >> >>>> SQL queries 1 >> >>>> SELECT >> >>>> DISTINCT >> >>>> [dbo_vDim_Calendar].[FullDate] AS >> >>>> [dbo_vDim_CalendarFullDate0_0],[dbo_vDim_Calendar].[DayLongName] AS >> >>>> [dbo_vDim_CalendarDayLongName0_1],[dbo_vDim_Calendar].[YEARMo] AS >> >>>> [dbo_vDim_CalendarYEARMo0_2] >> >>>> FROM [dbo].[vDim_Calendar] AS [dbo_vDim_Calendar] >> >>>> Processing Dimension Attribute
To process multiple objects at once with AMO (in a Batch, in Parallel), this is sample code (sorry it's C#, I don't have it available in VB.NET):
Server s = new Server();
s.Connect("localhost");
try { // We'll put AMO in capture-mode; this means that all .Process, .Update etc commands // are saved in a log (Server.CaptureLog), instead of being sent to the server. And then // we can run the commands from the log in a single Batch (eventually in Parallel). s.CaptureXml = true;
// Now we'll call the .Process methods as normally. s.Databases["my database id"].Dimensions["my dimension id1"].Process(); s.Databases["my database id"].Dimensions["my dimension id2"].Process();
// Once we called Process on all the objects we want, we'll exit capture-mode and // we'll run the script. s.CaptureXml = false; // we exit the capture mode XmlaResultCollection results = s.ExecuteCaptureLog(true, true); // transactional and parallel
// Now we'll check the results foreach (XmlaResult result in results) { foreach (XmlaMessage message in result.Messages) { Console.WriteLine(message.Description); if (message is XmlaError) { // the processing failed, there is at least one error reported here. } } }
}
finally { s.Disconnect();
}
Adrian Dumitrascu
"Jesse O." <jespe...@hotmail.com> wrote in message
> "Akshai Mirchandani [MS]" <aksh...@online.microsoft.com> wrote in message > news:eoT5REFeGHA.4304@TK2MSFTNGP05.phx.gbl... >> My guess here is that it is not aggregations but rather indexes being >> processed.
>> I would suggest running Profiler against the server during processing and >> seeing the events there -- the events for the partition should be for >> ProcessIndex...
>> The actual processing of partitions doesn't appear to be significant here >> though. It shows as:
>> Also, are you doing ProcessUpdate on all the dimensions in one operation >> (using Batch/Parallel)? This would unify the cube processing into the >> same transaction so that it only needs to happen once instead of repeated >> for each of the dimensions.
>> This posting is provided "AS IS" with no warranties, and confers no >> rights >> Please do not send email directly to this alias. This alias is for >> newsgroup >> purposes only.
>> "Darren Gosbell" <j...@newsgroups.nospam> wrote in message >> news:MPG.1ed32ff1a7122d298991a@news.microsoft.com... >>> This behaviour could be depend on whether your dimension has flexible or >>> rigid attribute relationships. BOL did not have a very succinct >>> definition, but the following is from one of the tutorials:
>>> ...you can specify that the relationship is either flexible or rigid. If >>> you define a relationship as rigid, Analysis Services retains >>> aggregations when the dimension is updated. If a relationship that is >>> defined as rigid actually changes, Analysis Services generates an error >>> during processing unless the dimension is fully processed
>>> Time inherently has rigid relationships (you can't change which month a >>> particular date belongs to). But I believe all other dimension default >>> to flexible relationships which would mean that processing them would >>> result in all the aggregations (not the leaf level data) being dropped >>> and re-processed.
>>> In article <#DAflH9dGHA....@TK2MSFTNGP02.phx.gbl>, willg...@hotmail.com >>> says... >>>> generally a reprocess is required when there is a change in a >>>> hierarchy. >>>> for example, if an employee is in New York and change to Boston, then >>>> the >>>> hierarchy changes which required a process of the cubes to recalculates >>>> the >>>> aggregations correctly. >>>> Changing a label (like the name of the customer) required only a simple >>>> update (if the key column is not the same as the label column).
>>>> if you anticipate this type of changing, you have to use the slow >>>> changing >>>> feature in the dimension. I have not use it in AS2005 but in AS2000 >>>> yes. >>>> this option will slow down accesses to the cubes because the system >>>> will >>>> keep only aggregates at the top (all member) and bottom levels (the >>>> employee) >>>> intermediate levels are recalculate when asked by the user. >>>> So when a employee change, there is no need to reprocess the cubes >>>> because >>>> the aggregation for boston and new york are not stored on the disk.
>>>> With AS2005 there is a proactive caching option at the dimension level, >>>> but >>>> I have not use it, if this caching works like in a cube... this could >>>> help >>>> you by reaggregate data when a table in the database change.
>>>> take a close look at the proactive caching features.
>>>> "Jesse O." <jespe...@hotmail.com> wrote in message >>>> news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... >>>> > No, no slice set. We're using MOLAP. Thanks for your suggestion of >>>> > proactive caching.
>>>> > I'm still confused as to why some dimensions process partitions while >>>> > others don't.
>>>> >> when you create partitions, do you associate this partition to a >>>> >> specific >>>> >> slice of the cube?
>>>> >> use realtime partitions and the proactive caching feature. and/or >>>> >> incremental processing.
>>>> >> I have seen a webcast where a billion rows database and the >>>> >> associated >>>> >> cubes are updated in realtime. >>>> >> The cache of the cube himself is updated, this mean that adding new
> To process multiple objects at once with AMO (in a Batch, in Parallel), > this is sample code (sorry it's C#, I don't have it available in VB.NET):
> Server s = new Server();
> s.Connect("localhost");
> try > { > // We'll put AMO in capture-mode; this means that all .Process, .Update > etc commands > // are saved in a log (Server.CaptureLog), instead of being sent to the > server. And then > // we can run the commands from the log in a single Batch (eventually > in Parallel). > s.CaptureXml = true;
> // Now we'll call the .Process methods as normally. > s.Databases["my database id"].Dimensions["my dimension id1"].Process(); > s.Databases["my database id"].Dimensions["my dimension id2"].Process();
> // Once we called Process on all the objects we want, we'll exit > capture-mode and > // we'll run the script. > s.CaptureXml = false; // we exit the capture mode > XmlaResultCollection results = s.ExecuteCaptureLog(true, true); // > transactional and parallel
> // Now we'll check the results > foreach (XmlaResult result in results) > { > foreach (XmlaMessage message in result.Messages) > { > Console.WriteLine(message.Description); > if (message is XmlaError) > { > // the processing failed, there is at least one error > reported here. > } > } > } > } > finally > { > s.Disconnect(); > }
>> "Akshai Mirchandani [MS]" <aksh...@online.microsoft.com> wrote in message >> news:eoT5REFeGHA.4304@TK2MSFTNGP05.phx.gbl... >>> My guess here is that it is not aggregations but rather indexes being >>> processed.
>>> I would suggest running Profiler against the server during processing >>> and seeing the events there -- the events for the partition should be >>> for ProcessIndex...
>>> The actual processing of partitions doesn't appear to be significant >>> here though. It shows as:
>>> Also, are you doing ProcessUpdate on all the dimensions in one operation >>> (using Batch/Parallel)? This would unify the cube processing into the >>> same transaction so that it only needs to happen once instead of >>> repeated for each of the dimensions.
>>> This posting is provided "AS IS" with no warranties, and confers no >>> rights >>> Please do not send email directly to this alias. This alias is for >>> newsgroup >>> purposes only.
>>> "Darren Gosbell" <j...@newsgroups.nospam> wrote in message >>> news:MPG.1ed32ff1a7122d298991a@news.microsoft.com... >>>> This behaviour could be depend on whether your dimension has flexible >>>> or >>>> rigid attribute relationships. BOL did not have a very succinct >>>> definition, but the following is from one of the tutorials:
>>>> ...you can specify that the relationship is either flexible or rigid. >>>> If >>>> you define a relationship as rigid, Analysis Services retains >>>> aggregations when the dimension is updated. If a relationship that is >>>> defined as rigid actually changes, Analysis Services generates an error >>>> during processing unless the dimension is fully processed
>>>> Time inherently has rigid relationships (you can't change which month a >>>> particular date belongs to). But I believe all other dimension default >>>> to flexible relationships which would mean that processing them would >>>> result in all the aggregations (not the leaf level data) being dropped >>>> and re-processed.
>>>> In article <#DAflH9dGHA....@TK2MSFTNGP02.phx.gbl>, willg...@hotmail.com >>>> says... >>>>> generally a reprocess is required when there is a change in a >>>>> hierarchy. >>>>> for example, if an employee is in New York and change to Boston, then >>>>> the >>>>> hierarchy changes which required a process of the cubes to >>>>> recalculates the >>>>> aggregations correctly. >>>>> Changing a label (like the name of the customer) required only a >>>>> simple >>>>> update (if the key column is not the same as the label column).
>>>>> if you anticipate this type of changing, you have to use the slow >>>>> changing >>>>> feature in the dimension. I have not use it in AS2005 but in AS2000 >>>>> yes. >>>>> this option will slow down accesses to the cubes because the system >>>>> will >>>>> keep only aggregates at the top (all member) and bottom levels (the >>>>> employee) >>>>> intermediate levels are recalculate when asked by the user. >>>>> So when a employee change, there is no need to reprocess the cubes >>>>> because >>>>> the aggregation for boston and new york are not stored on the disk.
>>>>> With AS2005 there is a proactive caching option at the dimension >>>>> level, but >>>>> I have not use it, if this caching works like in a cube... this could >>>>> help >>>>> you by reaggregate data when a table in the database change.
>>>>> take a close look at the proactive caching features.
>>>>> "Jesse O." <jespe...@hotmail.com> wrote in message >>>>> news:uN%233so8dGHA.4108@TK2MSFTNGP03.phx.gbl... >>>>> > No, no slice set. We're using MOLAP. Thanks for your suggestion of >>>>> > proactive caching.
>>>>> > I'm still confused as to why some dimensions process partitions >>>>> > while >>>>> > others don't.
>>>>> >> when you create partitions, do you associate this partition to a >>>>> >> specific >>>>> >> slice of the cube?
>>>>> >> use realtime partitions and the proactive caching feature. and/or >>>>> >> incremental processing.
>>>>> >> I have seen a webcast where a billion rows database and the >>>>> >> associated >>>>> >> cubes are updated in realtime. >>>>> >> The cache of the cube himself is updated, this mean that adding new
