New Best Practices Articles Published – Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many… Read more

New Best Practices Whitepaper Published: The Impact of Changing Collations and of Changing Data Types from Non-Unicode to Unicode

When a business grows internationally, its database system must support multilingual characters in tables. Businesses that previously needed to manage only non-Unicode information must now support database tables that include non-Unicode data types such as CHAR or VARCHAR. As the business grows internationally, it must alter its database system to support Unicode data types. It… Read more

Using ByAttribute or ByTable Processing Group Property with Analysis Services 2005

As noted within the Analysis Services 2005 Performance Guide, there are some niche situations where setting the Processing Group property to ByTable provides more optimal processing than the default value of ByAttribute. In a customer scenario, we had discovered that they had two dimensions (each of which has >25 million members and 8-10 attributes) where… Read more

SSAS small/big query interaction

Default SSAS behavior can sometimes result in small queries being slowed down by concurrently running big queries. Recently in working with a customer, we observed the most common queries when run by themselves took about 1 second and some ad-hoc queries against their 110 GB cube took about 3 minutes.  When running both together, small… Read more

Precision Considerations for Analysis Services Users Whitepaper now available

For those of you whom want to know a little more about precision considerations for Analysis Services users, please check out this recently released whitepaper at http://www.microsoft.com/downloads/details.aspx?familyid=bae8beec-9892-4ecd-a9db-292254895f9c&displaylang=en. This white paper covers accuracy and precision considerations in SQL Server 2005 Analysis Services. For example, it is possible to query Analysis Services with similar queries and obtain… Read more

Process only one partition so you can browse the cube

If you want to browse the cube and validate dimensions, hierarchies and calculations using the data from only one partition, here is what you can do.  Deploy the new cube solution, run a process structure for the cube and then process only one of the partitions.  Process Structure will process all the dimensions plus mark… Read more

Using unformatted cell values it may improve query performance

If a report does not use formatted cell values, query time can be reduced.  An example is a Reporting Services report that does its own formatting and therefore would not use the formatted values from SSAS.  By returning only the value of the cell and not its formatted value in MDX, you can achieve better… Read more

Create a default measure in AS2005 cubes

In some cases, it is not obvious or easy to create a default measure in AS2005 cubes.  In fact, there is no default measure by default, which can lead to long query times for queries that specify no measures.  We have seen slow behavior with several query tools that display an initial default query or… Read more

SQLCAT tracks at 2007 SQL PASS

As you may already know from Brian Moran’s SQL Server Magazine article “SQLCAT to Offer Six Sessions at PASS“, the SQLCAT team will be providing 6 SQLCAT tracks at the 2007 SQLPASS event in Denver this year.  Below are the SQLCAT tracks schedule.  (The last session was added after the schedule was published.)   Wednesday, September 19th 9:45am-11:00amDBA-410-MDesigning… Read more