SSAS 2005 : My Best Practices

1. OLTP database

• Use physical table instead of physical view to build your dimension
• Set index on these tables the following articles is very useful to chose which column indexing (http://sqlblogcasts.com/blogs/ssqanet/archive/2008/02/19/sql-server-2005-index-optimization-best-practices.aspx)

2. OLAP database

• for query performance, use user defined hierarchy as much as possible except if you use custom rollup. In this case, Parent-Child hierarchies are more performant.
• Case When mdx has been more performant than IIF in some case. When I used the Case When in a calculation of my cubes the time query was faster. Personaly, I don't hesitate to compare the response time of my query with an IIF statement and with a case when in the cube calculations part.
• Decrease the number of cube calculation. Cause these calculations are evaluated on the fly.

In plus of my best practices, you can find more on the microsoft website (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx)

SSAS 2005 : Aggregation...Important thing to know!

Aggregations are pre-calculated summaries of data. Specifically, an aggregation contains the summarized values of all measures in a measure group by a combination of different attributes.

How aggregations are used ?

To understand aggregations, consider the cube shown in Figure.


This is a small cube consisting of two dimensions, Date and Product, and one measure, Sales Amount.

The cube has three aggregations contributed by different combinations of attribute hierarchies.

• The all-level aggregation stores the grand total Sales Amount.
• The intermediate aggregation stores the total Sales Amount for the year 2004 and Road Bikes product subcategories.
• Finally, assuming that the fact table references Month and Product dimensions, there is also a fact-level aggregation for July 2004 and product Road-42.

Suppose a query asks for the overall Sales Amount value. The server discovers that this query can be satisfied by the all-level aggregation alone. Rather than having to scan the partition data, the server promptly returns the result from the pre-calculated Sales Amount value taken from this aggregation. Even if there isn't a direct aggregation hit, the server may be able to use intermediate aggregations to derive the result. For example, if the query asks for Sales Amount by product category, the server could use the intermediate aggregation, Year 2004 and Subcategory Road Bikes, to derive the total.

Why not create all possible aggregations?

The short answer is that this will be counter-productive because all those aggregations will require enormous storage space and long processing times. The reason is that aggregations are created when the cube is processed.

Last thing, to help the Aggregation Design Wizard make a correct cost/benefit analysis, be sure to keep the EstimatedCount (attribute property) and EstimatedRows (Measure group property) counts up to date.

To make the good estimation you can use a very useful tool named BIDSHelper .

SQL Server 2005 : How to copy a database using SQL Server 2005?

To do this manipulation, you have a lot of way.

Backup-Restore? too long

Create an SSIS package using BIDS? too much manipulation

The solution is ...

the integrated copy tool of SQL Server 2005.

This tool can copy one or several OLTP database and "paste" them on a server. It creates a package and launch the SQL server Agent that executes this one.

You can find it by right-clicking on a database in your SSMS.

After click on Copy Database.

The Copy Database Wizard opens.

1st step: select the source server and the authentification mode

2nd step: select the destination server and the authentification mode

3rd step: choose the transfer method (Detach-Attach or the SQL Management object method to keep the source db online)

4th step: choose the database to move/copy

5th step: specify file names and wether to overwrite database(s) at the destination

... creation of the package that will copy/move the database

Easy and useful!

SSIS 2005 : How to write annotations in a package?

In the SSIS Designer, right-click on the design surface of your Control Flow, Data Flow or Event Handler tab and then click Add annotation.

After you have created your annotation, you can either change the font formatting or group it to an object of your design surface. How to do it? --> right-click on it :-)