OLAP and SQL Firewalls For a client or middle-tier to access You will need to open the following firewall ports:SQL Server: 1433 OLAP Services: 2393, 2394, 2725 For more information, please refer to:Q301901 orhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/csvr2002/htm/cs_dp_typical_mjjt.asp   For Yukon (SQL Server 2005), you will need to open as well: Analysis Services 2005: 2383 (default port), 2382 (redirector port)  

Calculate Memory used by OLAPTo calculate memory, the formula is:DimSize = (M x (61 + 4L + Sa + Sk))) + 4P + Sn where M: Number of members in the dimension. L: Number of levels in the dimension, including the ALL level. Sa: Average size required to hold the member names. Sk: Size required to hold the member key.   P: Number of member property settings in the dimension for all levels.   Sp: Size required to hold the distinct member property values for all member levels. 

Lazy Aggregation ErrorIf you get an “Error during lazy aggregation of partition %PartName% in cube %CubeName%, this is typically caused by a lazy aggregation error.  To resolve this, you can: Is the registry key ProcessReadSegmentSize at least 60% of physical memory on the machine?  How much disk space available for the temporary directory? Resolution of either one of these things should remove the Lazy Aggregation Error you are seeing.

How to archive an OLAP database (not using msmdarch.exe) To provide an unsupported method to archive an Olap database instead of using msmdarch.exe. You can backup the file folder containing the Olap data (i.e. c:\Program Files\Microsoft Analysis Services\Data) and the Olap repository SQL database (assumes you migrated the Olap repository to a SQL database).  Note, this will backup the entire server. To backup a specific OLAP database, you can: Archive the c:\Program Files\Microsoft Analysis Services\Data\OlapDB folder and the c:\Program Files\Microsoft Analysis Services\Data\OlapDB.odb file. Backup the Olap Repository SQL database OR copy the pertinent rows from the said database from the OlapObjects table. …

Renaming OLAP database (Unsupported) Below are the unsupported steps to rename and OLAP database. Archive the OLAP database and SQL repository (assumes you migrated the OLAP repository to SQL). Stop Analysis Server Rename the folder that contains the Olap dB (e.g. rename c:\Program Files\Microsoft Analysis Services\Data\blah to c:\Program Files\Microsoft Analysis Services\Data\blah2) Go into the Olap repository SQL dB , and change the name “blah” to “blah2” w/in the OlapObjects table. Examples include: The column ObjectName needs to be changed from “blah” to “blah2” The ObjectDescription column needs to be changed from ‘<DATABASE Name = “blah">….’ to ‘<DATABASE Name = “blah2">…’ Rename of…

Distinct Count To get the full performance out of Analysis Services for distinct count, insure that a distinct count measure is in its own cube and you can perform custom aggregations for the dimensions within the cube.  But the most powerful way to improve performance is to use a partitioning strategy. The idea is to create partitions where the distinct IDs are spread out through all of the partitions (i.e. partition by dimension(s) that have the IDs repeated in most/all partitions); which is slightly counter-intuitive.  At query time, this design forces AS2k to utilize multiple threads to calculate distinct count…