Geeks With Blogs
James Rogers BI from the trenches... Analysis Services
I cut my DBA teeth on IBM's DB2 UDB EEE (enough acronyms) back in the 90's. I have always appreciated the scalability that is available with a MPP architecture. So a couple of years ago I was excited to hear that SQL Server was going to have a MPP architecture available. Now I am at SQLPass Summit 2011 and seeing the PDW (Parallel Data Warehouse) and the hardware offerings by Dell and HP to scale that up and out. However, I was disappointed to learn that this technology does not extend to Analysis ......

Posted On Thursday, October 13, 2011 12:01 PM

Analysis Services (SSAS) - Unexpected Internal Error when processing (ProcessUpdate). Workaround/Resolution
/* EDIT - This problem has been fixed in the latest SQL Server 2008 R2 Cumulative Update package. It was identified that changing the aggregation design without reprocessing the aggregations prior to a ProcessUpdate on the dimensions causes this problem and it is a bug */ Many implementations require the use of ProcessUpdate to support Type 1 slowly changing dimensions. ProcessUpdate drops all of the affected indexes and aggregations in partitions affected by data that changes in the Dimension on ......

Posted On Monday, December 20, 2010 5:51 AM

It is a well-published approach to using the EXISTING function to decode AGGREGATE members and nested sub-query filters. Mosha wrote a good blog on it here and a more recent one here. The use of EXISTING in these scenarios is very useful and sometimes the only option when dealing with multi-select filters. However, there are some limitations I have run across when using the EXISTING function against an AGGREGATE member: The AGGREGATE member must be assigned to the Dimension.Hierarchy being detected ......

Posted On Friday, June 11, 2010 9:43 PM

Performance, Named Sets, Calculated Measures, PerformancePoint and ProClarity
Many legacy Analysis Services (or OLAP Services) implementations use ProClarity as a querying tool and publishing framework. The server infrastructure of ProClarity allows crafty implementers to push much of the customization in KPI’s and additional measures into the “Presentation” layer of the infrastructure. This is easier to manage and change in many cases than creating named sets and calculated members in the cubes as this generally takes a SSAS administrator. With Microsoft purchasing ProClarity ......

Posted On Wednesday, February 13, 2008 10:15 PM

Finding and killing SSAS Sessions
If you have a reasonably large Analysis Services implementation with users running adhoc queries via multiple tools (ProClarity, Excel, etc...) then you have probably run into problems with long-running queries. While longer-running queries may be acceptable in many data warehousing implementations, there will occasionally be a query (maybe even more than one ;) ) that causes resource issues and runs beyond the established limits of reason for your environment. Analysis Services does not have a native ......

Posted On Tuesday, January 22, 2008 6:47 PM

SSAS Usage-Based Optimization, OlapQueryLog (Enabling and Disabling), Aggregations and Structure Changes.
Analysis Services has a useful feature called Usage-Based Optimization. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio (BIDS). Information on how to configure (or disable) the query logging required for this is fairly hard to find. The Analysis Services Properties identified by "Log \ QueryLog" in the Analysis Services Properties window are used to configure the query logging used by Usage-Based Optimization: The definition ......

Posted On Tuesday, November 2, 2010 3:31 AM

Tuning Analysis Services (SSAS) for better Parallel Aggregation/Index Processing
One of the key performance concepts in Analysis Services is the design and use of good Aggregations to support user queries. However, Analysis Services (Both 2005 and 2008) by default is not configured very well to take advantage of multi-processor environments for parallel processing of these indexes, particularly for large MOLAP partitions. Keep in mind, using a large portion of available processors for aggregation building is only desirable in environments where a processing window is allocated ......

Posted On Tuesday, November 2, 2010 12:33 AM

Copyright © James Rogers | Powered by: