Improve Performance at the Aggregation Level
You can improve OLAP performance when you set a cube's aggregation level. When you build a cube, you set the aggregation level according to the desired speedup in processing queries. (Speedup describes how much faster queries run with precreated aggregations than without aggregations.) The system estimates the speedup based on the I/O amount that the system requires to respond to queries. The total possible number of aggregations is the product of the number of members from each dimension. For example, if a cube has two dimensions and each dimension has three members, then the total possible number of aggregations is 9 (3 x 3). In a typical cube, the number of aggregations possible is extremely large, so calculating all of them in advance isn't desirable because of the required storage space and the time it takes to create the aggregations. Imagine a cube with four dimensions, each with 10,000 members. The total possible number of aggregations is 1016. When you tell SQL Server 7.0 OLAP Services to calculate aggregations for a 20 percent speedup, OLAP Services picks key aggregations (which are distributed across the cube) to minimize the time required to determine any other aggregations at query time.
—Russ Whitney
Mastering OLAP, "OLAP Performance" January 2000, InstantDoc ID 7638
Using Children to Automatically Update Products
Let's say you want to write an MDX query that shows sales for all hot beverage products for each month of the year. That task sounds simple enough, but what if you add and remove products from your product list each month? How would you write the query so you don't have to update it every time you update your list of products? Here's a trick to help: Use the descendants or children function. The example query that Listing 1 shows uses both of these functions. Try running Listing 1's query in the MDX Sample program. The descendants and children functions are powerful.
—Brian Moran and Russ Whitney
Mastering OLAP, "MDX Tip of the Month" May 1999, InstantDoc ID 5250
Saving DTS Information to a Repository
To save Data Transformation Services (DTS) information into the Microsoft Repository, choose SQL Server Repository as the location for saving the package. Then, use the Advanced tab on the Package Properties to set the scanning options, which Figure 1 shows. Doing so causes DTS to call the OLE DB scanner to load all source and target catalogs into the Repository. If you don't set the scanning options, DTS creates DTS Local Catalogs as the reference for all source and target catalogs, which can make locating the databases impossible. Each subsequent save replicates this reference, so you can't keep comments and other descriptive information updated.
You can run into problems when you try to save certain DTS transformations to a repository. If you use a script to perform a simple transformation and you choose the source columns explicitly (not from a query), all the transformation data is captured, as you can see in the transformation model in "The Open Information Model," March 2000, InstantDoc ID 8060. If you choose a query as the transformation source, that source becomes objects that aren't part of the OLE DB imported data. This choice makes following the connection back to the true source objects difficult. Also, the query isn't parsed to create a connection between the query columns and the columns you select the data from. So in many cases, the connection between source and target is available, but in some, it isn't. You can solve these problems by writing a program to resolve the references in a repository or by using a custom model along with the DTS model to store the source target mappings.
—Patrick Cross and Saeed Rahimi
"Using the Microsoft Repository" March 2000, InstantDoc ID 8029