Additional Information and Code Samples for Topics Covered in Microsoft OLAP Unleashed

Additional Information and Code Samples for Topics Covered in Microsoft OLAP Unleashed

Errors in the first printing of Microsoft OLAP Unleashed are listed on the Corrections page. This page contains additional material that should have gotten into the book, but did not, as well as code samples relating to topics covered in the book. Please send additional suggestions to tpeterson AT sdgcomputing DOT com.

VB Code for Creating a Simple DTS Package

This code sample creates a complete DTS Package, with two Execute SQL tasks and a data transformation, with precedence constraints defined for the tasks.

Referencing DTS Package Objects from within a Package

Here is a code sample that establishes a reference to the objects used in a DTS package

Dim pkg, con, stp, tsk, cus, prc, trn
'Set reference to package object
Set pkg = DTSGlobalVariables.Parent

Set con = pkg.Connections(“My SQL Server Connection”)
Set con = pkg.Connections(3)

‘You can find the name of a step by right-clicking on the Data Transformation line, choosing Workflow properties, and selecting the second tab. The name of the step is displayed there.
Set stp = pkg.Steps("DTSStep_DTSDataPumpTask_3")

Set stp = pkg.Steps(2)

'Set reference to a task object. The name of the task is not visible in the DTS Designer interface. But you can use the name of the step to get a reference to the step object. You then use the TaskName property of the step to establish an object reference to the task.
Set tsk = pkg. Tasks(stp.TaskName)

'Set reference to the CustomTask object for this data transformation. Most task properties are modified through the custom task object.
Set cus = tsk.CustomTask

'If you are working with a data transformation task, you reference the transformation object as follows:

'To reference a precedence constraint that makes DTSStep_ExecuteSQLTask_2 dependent on DTSStep_ExecuteSQLTask_1, you would do the following

More detail on referencing DTS package objects can be found in Microsoft OLAP Unleashed, Chapter 12, “Programming with the DTS Object Model.”

DTS Code for Dynamically Setting a Source Transformation Query or an ExecuteSQL Query

Most of the properties of a DTS task have to be referenced through the custom task object. Here is code which can be used inside a DTS ActiveX script to establish a reference to the custom task object. In this example, a global variable is used in constructing a new source SQL statement is changed for a data transformation task:

Set pkg = DTSGlobalVariables.Parent
Set stp = pkg.Steps("DTSStep_DTSDataPumpTask_5")
Set tsk = pkg. Tasks(stp.TaskName)

Set cus = tsk.CustomTask

'Construct the sql statement
sql = "select whatever from SourceTable where fld1 > " & DTSGlobalVariables(“NewValue”).Value

'Assign the SourceSQLStatement property of the custom task
cus.SourceSQLStatement = sql

DTS Code for Recovering ActiveX Script when a Data Source has Been Changed

If a data source has been changed, it’s impossible to view the data transformation script that has been previously written. To work around this problem, you can create an ActiveX Script task with this code, which writes out the text of the transformation script to a text file.

Set stp = pkg.Steps("DTSStep_DTSDataPumpTask_1") 'Find the correct step name in the interface

Set trn = cus.Transformations(1) 'If there’s more than one transformation, use the right number

The Importance of Using the Value Property When Referencing Global Variables in a DTS Package

I have experienced memory access violations that have shut down the Enterprise Manager without allowing me to save my work when I have used Global Variables incorrectly. I have even had DTS Packages corrupted, so that I can't open up the most recent version after an access violation has occurred.

The problem has occurred because I have assigned values to Global Variables without specifically referencing its Value property. Value is the default property of a Global Variable, so you can assign a value without specifically referencing the Value property:

This code will work[md]but only some of the time! To avoid memory access violations you should always reference the Value property explicitly:

Before I learned the importance of always referencing the Value property, I discovered that I could also avoid memory access violations by explicitly specifying the datatype of the value I was assigning to a Global Variable:

Also Helps Avoid a Problem[md]DTSGlobalVariables("IntegerVariable") = CLNG(con.ID)

It never hurts to explicitly set the datatype of a variable you are assigning to a Global Variable. But the important thing to remember is to always explicitly reference the Value property.

Corrected Examples for Using DTSRun

There are several errors in the first printing of Microsoft OLAP Unleashed regarding to the use of DTSRun, as described on pages 128 and 129. Here are some corrected examples:

P128, bullet 3 = REMOVE > "This is one of the two lineage variables that uniquely identify the DTS package."

P128, bullet 4 = REMOVE > "This is the other lineage variable. It uniquely identifies the version and the instance of execution of a DTS package."

DTSRun /U User Name /P UserPassword /N PackageName /M PackagePassword /F c:\temp\StarImport.dts

Several places on pages 128 and 129 there are references to the lineage variables being used with DTSRun. Those references are incorrect. It is the DTS Package ID and the DTS Version ID that can be used with DTSRun. In this example, the Package ID is used, together with integrated security (/E):

DTSRun /SserverName /E /R msdb /G {9F99EE87 – FE2F – 11D2 – 91A8 – 00E0980134A1}

Problems with Testing ActiveX Scripts with Lookups when Service Pack 1 is not installed

ActiveX Scripts that use Lookups will always fail when tested with the Test button inside the DTS Designer. This problem has been fixed with Service Pack 1.

Performance Considerations for Using Lookups in Transformations

The use of Lookups in a data transformation can provide a convenient way to access information from additional data sources. Whenever possible, though, you should use joins in the source query for the data transformation, instead of using a Lookup. Your performance will be much better.

There are times, of course, when joins are not possible. The value you want to find might be dependent on programmatic logic that can’t be easily represented in an SQL statement. The Lookup object is an effective tool for finding information from additional sources for your data transformation.

Corrected Discussion on the Benefits of Cube Partitions

The benefits of cube partitions are stated incorrectly on page 446. Here is a corrected version:

A partition stores a portion of a cube. A single default partition is created for a new cube. There are two primary reasons why you might want to create additional partitions:

To assist in managing the processing of large cubes. Partitions can be processed independently or in parallel.

You want to store different parts of your cube with different storage types. You may, for example, want to store part of your cube with MOLAP storage and part of your cube with HOLAP storage.

In the discussion on enhancing performance on page 451, the first sentence should be changed to read:

You can improve performance by using partitions because they can be processed independently or in parallel.

Corrected Discussion on the Effect of Processing Cubes with Shared Dimensions

The discussion in the note on page 431 regarding the processing of cubes with shared dimensions is incorrect. Here is the corrected version:

If a cube contains a shared dimension that has been updated, that shared dimension will be automatically processed when the cube is processed. After that shared dimension is processed, all other cubes that use that shared dimension must be processed before they can be used. The best strategy when updating a shared dimension is to immediately process both the dimension and all cubes that use the dimension.

Using DSO in the OLAPUnleashedStarToCube Utility to Create an All Level for Each Dimension

The version of the OLAPUnleashedStarToCube utility does not automatically create an All level for each dimension in the cube. You can, of course, use the utility and then manually add the All level using the OLAP Manager. It would be much better, though, to create the All level automatically, because it is used for almost all dimensions.

The following code can be added to the utility to automatically create the All level. Place it in the following location:

'Create All Level
Set dsoLevel = dsoDimension.Levels.AddNew("(All)")
dsoLevel.LevelType = levAll
dsoLevel.MemberKeyColumn = "All " & dsoDimension.Name
dsoLevel.ColumnSize = 255 'dmoColumn.Length
dsoLevel.ColumnType = adChar
dsoLevel.EstimatedSize = 1

Building a Cube that Displays Information from Many-to-Many Relationships

In Microsoft OLAP Unleashed, pages 95-97, there is a discussion of the use of a weighting table to resolve many-to-many relationships between facts and dimensions. Here is a description of how you could build a cube that displays the information in these tables.

When you use a weighting table in a star schema to resolve a many-to-many relationship, you are able to assign the relative value of each factor as it is connected to a particular fact. You can build a cube that shows both the total count of a particular and the weighted value of a particular factor. Using the example in the book, you could show both the total number of times a particular promotion was involved in a sale and the weighted value of a particular promotion in sales as two separate measures. If there are multiple promotions connected to a sale, the first total will result in a number greater than the total number of sales, while the weighted value should equal the actual number of sales.

3. Use the count of the promotion_group_id in the fact table as a measure that shows the total number of times a particular promotion was involved in a sale.

5. Build a view on sales_fact, promotion_group, and promotion_weight. Include the promotion_id, the weighting_factor, and all the dimension key values from sales_fact in this view.

9. Create other dimensions as desired, probably creating all the dimensions that you have created in your first cube. You would probably not want to create any other measures.