Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Analysis Server and Reporting Services (2008)  Caliculated Memeber throughs error

Author  Topic  Hussain Starting Member 1 Post Posted - 2011-12-30 : 04:54:55 Hi all, Need your's urgent help that would be appreciated alot!! We have a calculated member we created using time Intelligence(a shell member called Non-Holiday Count)"Non-Holiday Count" ( Working Day), is calculated using Legal Entity(dimension), Division (dimension)and COB Date(dimension)(e.g NAM, S&P, For Year 2011 : 252 Working Days) When calculating the "Non-Holiday" count, it is required to ignore any other dimension besides Legal Entity, Division and Date. To Fix this we introduced : Default Member in the "Non-Holiday Count" Measure as follows. ([Time Calculations].[Time Calculations].[Non-Holiday Count]= Sum( ([COB DATE SGK].[DATE SGK].[DATE SGK]), IIf(([RGN HLDY FLAG SGK].[IS BUSINESS DAY].&[1.], [Actual Market of Settlement].[Country].Defaultmember,[Actual Market of Settlement].[Region].Defaultmember, [Asset Class].[Hierarchy].Defaultmember, [Execution Type].[Execution Type].Defaultmember, [FO Book System].[FO Book System].Defaultmember, [Front Office].[GOC Description].Defaultmember,[Front Office].[GOC Geo Level One Description].Defaultmember, [Front Office].[GOC Mngd Seg Description].Defaultmember, [Front Office].[FO Location Hierarchy].Defaultmember, [Front Office].[FO Office Hierarchy].Defaultmember, [Market of Settlement].[Hierarchy].Defaultmember, [Metrics Category].[Metrics Category].Defaultmember, [MSSB Indicator].[INDICATOR VALUE].Defaultmember, [Ownership].[L1 City].Defaultmember,[Ownership].[L2 Country].Defaultmember, [Ownership].[L5 Location].Defaultmember,[Ownership].[Group Hierarchy].Defaultmember, [Ownership].[Owner Hierarchy].Defaultmember, [Report Source System].[Report Source System].Defaultmember, [Source Feed].[Source Feed].Defaultmember, [Source System].[Source System].Defaultmember, [Transaction Type].[Transaction Type Hierarchy].Defaultmember, [Volume Event Type].[Volume Event Type].Defaultmember, [Volume Measure Type].[Volume Measure Type].Defaultmember, [Time Calculations].[Current Date]),1,Null))); “Non-Holiday Count” is used to calculate “Daily Average”(an another calculated member). The Daily Average Calculation Fails, when combination of Large Dimension are used in Query or or Excel with memory Error. (Example : Front Office(till L7 Level), Asset Class(L1 Level), Market Of Settlement(Till Country) Important Items to Consider 1- Front Office Row Count: 347,000 2- Asset Level Row Count: 2150 3- Market Of Settlement: 1138 4- Fact Rows for Week 50 : 35896 5- Time Intelligence (Shell Dimension) is used to do calculations a. Cannot use additional measure group as it cannot be assigned across time calculation measures 6- Customized Code Written to Sum Averages When No “Legal Entity” or “Division” used at all level. So basically it will calculated daily average for every single “Division” and “Legal entity” and then sum it. a. Imp: Data Can be accessed when this code is commented, but not an option for fix. 7- Total Memory on server 16 GB 8- Low Memory Set to 65% 9- After reaching to 12.8 GB it errors our. So Far Tried Following Options 1. Calculate the “All” level for each major hierarchy and the assign “All” level to child member ( Does not written results) 2. Calculating Non-Holiday Count and Tried Finding Max Out of the List ( Query Times Out after 30 min) 3. Freeze and Unfreeze, does not work in Time Intelligence Plz suggest on this.. Thanks in Advance