A thought on the one minute processing restriction of the resmon database.

Jul 31, 2014 at 2:42 PM
Edited Jul 31, 2014 at 3:15 PM
Recently I hit the one minute processing duration restriction (DB is now 4 GB).

I modified the solution already a little before
  • included Quarter in Time
  • changed Time Format to ODBC YYYY-MM-DD .....
  • changed the Aggregation Function form SUM to LastNonEmpty
  • Added a view more Performance Counter
Now I also added another workaround for the time
  • Added a Member Value to my Time Dimension Key to compute all other attributes in the Query
  • I do not use process add for the time, instead Update
  • And I changed the Query to an MDX Query back on the Time Dimension by adding a couple of more minutes with every processing (by a calculated member).
    • Not nice, but it works for now...
    • I can add as many of those members (statically) as I need for the procesing duration
    • Bad is, you need to start with the SQL Version of the query because initially Time is not processed
WITH 
MEMBER [Time].[Min].NEW1 as null
MEMBER [Time].[Min].NEW2 as null
MEMBER [Time].[Min].NEW3 as null
MEMBER MEASURES.[MinKeyDatetime] as 
CASE 
    WHEN [Time].[Min].CurrentMember IS [Time].[Min].NEW1 THEN cDate(Format(Now(), 'yyyy-MM-dd HH:mm'))
    WHEN [Time].[Min].CurrentMember IS [Time].[Min].NEW2 THEN cDate(Format(Now()+(1/24/60), 'yyyy-MM-dd HH:mm'))
    WHEN [Time].[Min].CurrentMember IS [Time].[Min].NEW3 THEN cDate(Format(Now()+(2/24/60), 'yyyy-MM-dd HH:mm'))
    ELSE cDate([Time].[Min].CurrentMember.MEMBER_VALUE)
END
MEMBER MEASURES.[Min] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM-dd HH:mm')
MEMBER MEASURES.[MinKey] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM-dd HH:mm')
MEMBER MEASURES.[Hour] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM-dd HH')
MEMBER MEASURES.[HourKey] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM-dd HH')
MEMBER MEASURES.[Day] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM-dd')
MEMBER MEASURES.[DayKey] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM-dd')
MEMBER MEASURES.[Month] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM')
MEMBER MEASURES.[MonthKey] as Format(MEASURES.[MinKeyDatetime],'yyyy-MM')
MEMBER MEASURES.[Year] as Format(MEASURES.[MinKeyDatetime],'yyyy')
MEMBER MEASURES.[YearKey] as Format(MEASURES.[MinKeyDatetime],'yyyy')
MEMBER MEASURES.[Quarter] as Format(MEASURES.[MinKeyDatetime], 'yyyy') + '-Q'+CStr(int((Format(MEASURES.[MinKeyDatetime], 'MM')-1)/3)+1)
MEMBER MEASURES.[QuarterKey] as (int((Format(MEASURES.[MinKeyDatetime], 'MM')-1)/3)+1)+Year(MEASURES.[MinKeyDatetime])*10
SELECT {MEASURES.[MinKeyDatetime],MEASURES.[Min],MEASURES.[MinKey],MEASURES.[Hour],MEASURES.[HourKey],MEASURES.[Day],MEASURES.[DayKey],MEASURES.[Month],MEASURES.[MonthKey],MEASURES.[Year],MEASURES.[YearKey],MEASURES.[Quarter],MEASURES.[QuarterKey]} ON 0,
{[Time].[Min].[Min],[Time].[Min].NEW1,[Time].[Min].NEW2,[Time].[Min].NEW3} ON 1
FROM [$Time]
CELL PROPERTIES VALUE
Better Ideas?
Anyone a clue how to best migrate the old DB data to this new version?