office (715) 262-3601
Additional Information and Code Samples for Topics Covered in Microsoft OLAP Unleashed
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 reference to a connection by name or by number
Set con = pkg.Connections(“My SQL Server Connection”)
Set con = pkg.Connections(3)
'Set reference to a step – again you can use a name or a number
‘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:
Set tran = cus.Transformations(1)
'To reference a precedence constraint that makes DTSStep_ExecuteSQLTask_2 dependent on DTSStep_ExecuteSQLTask_1, you would do the following
Set stp = pkg.Steps(“DTSStep_ExecuteSQLTask_2”)
Set prc = stp.PrecedenceConstraints(“DTSStep_ExecuteSQLTask_1”)
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:
Dim pkg, stp, tsk, cus, sql
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.Option Explicit
Function Main()
Dim pkg, stp, tsk, cus, trn, prp, fso, fil
set pkg = DTSGlobalVariables.Parent
Set stp = pkg.Steps("DTSStep_DTSDataPumpTask_1") 'Find the correct step name in the interface
Set tsk = pkg.Tasks(stp.TaskName)
set cus = tsk.CustomTask
Set trn = cus.Transformations(1) 'If there’s more than one transformation, use the right number
set prp = trn.TransformServerProperties
Set fso = CreateObject("Scripting.FileSystemObject")
set fil = fso.CreateTextFile("c:\temp\script.txt") 'Change path as necessary
fil.WriteLine(prp("Text").Value)
fil.Close
Main = DTSTaskExecResult_Success
End Function
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:
The Wrong Method - DTSGlobalVariables("IntegerVariable") = con.ID
This code will work - but only some of the time! To avoid memory access violations you should always reference the Value property explicitly:
The Right Method - DTSGlobalVariables("IntegerVariable").Value = con.ID
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 - 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 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."
Using DTSRrun with a package saved as a file:
DTSRun /U User Name /P UserPassword /N PackageName /M PackagePassword /F c:\temp\StarImport.dts
Using DTSRun with a package saved in the repository:
DTSRun /S ServerName /U UserName /P UserPassword /N PackageName /R msdb
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.
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:
Procedure: cmdCreateCube_Click
After the following line: dsoDimension.FromClause = lstDimensionTable.List(idx)
In the text of the book, page 513, 8 lines above the bottom of the page.
'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.
You could implement these two measures in Microsoft's OLAP Services as follows:
1. Use the sales_fact table as the basis for the cube's fact table.
2. Build a dimension using promotion_group, promotion_weight, and promotion, ignoring the weighting_factor field.
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.
4. Create other dimensions and measures is this cube as desired.
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.
6. Build a second cube, using the view as the basis for the cube's fact table.
7. Build a dimension from the promotion table.
8. Use the sum of the weighting_factor in the fact table as a measure that shows the weighted value of a particular promotion.
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.
10. Create a virtual cube from the two cubes that you have created. Include all dimensions and measures from both cubes