TRANS-it is the Data Academy module which helps you move stuff from "A" to "B" - think of it as your own, personal, "man with a van"... but without the scary tattoos!
Suppose you have a "development" server, where you build your Data Loaders and engineer your Datasets; and a "live" server, where you run your real-life imports, and provide access for your client applications. Once upon a time, the only way to move stuff from "dev" to "live" was to back-up the entire SQL database in one environment and restore it to the other. It was risky, and if you wanted to move just a single Dataset, you were straight out of luck. This is why we built TRANS-it - it will export Data Academy modules, like Data Loaders and Datasets, to a file, which you can copy over to your "live" server and re-import into Data Academy. There is even an API, so you can "script" your transfers for additional peace-of-mind.
Unlike the rest of Academy, TRANS-it is a Windows application, so to use it, you'll need to be sitting on the Data Academy desktop, where Data Services is installed. You'll find a short-cut to the application in your "Programs -> Data Academy" directory on the "Start" menu. So climb aboard and fasten your seatbelts - it's green lights all the way to Memphis!
Moving Academy Modules
We're going to start off by showing you how to move a Data Loader module from your "dev" server to your "live" environment. Open TRANS-it, and select the menu option File -> Open -> From Server. Click on the appropriate radio-button on the left, and you'll see a list of valid LOAD-it Data Loaders. (Note that only generated Data Loaders can be moved!)
Select your Data Loader and click "OK". The Data Loader will now be analyzed (this might take a few seconds), and some key properties thrown into the client window.
Don't worry about the Properties for the moment - just click File -> Save -> To File, and save the Data Loader to your local file system. (Don't panic about the passwords - the file is encrypted, so a casual browser can't see them; although you should still be careful with the file, as anyone with access to TRANS-it will be able to see them.) Saved TRANS-it files are called "trucks" and have the extension ".trk".
Now copy the resulting file over to your live server, and run-up TRANS-it there. Use File -> Open -> From File to open the truck. The more eagle-eyed reader will already have spotted that the Data Loader referenced a database called "DA_Test", but this probably isn't the name of your "live" database, so you can go right ahead and change it, along with any of the other properties (such as those passwords) which are incorrect in the new environment.
Having changed the server names, all you need to do is select File -> Save -> To Server to import this Data Loader back into your live Data Academy environment. If you already have a Data Loader with the specified UID, it will be over-written; else a new Data Loader will be created, including the SSIS packages and import tables, where appropriate.
Moving Database Objects
To move other database objects, such as Views, Stored Procedures or User Defined Functions, you need to add the objects to the Data Academy GOVERN-it. You can then select GOVERN-it from the Open dialog.
Note that you have the ability to select "All Objects" or just a single database object.
One word of caution - re-importing the objects into your "live" Data Academy database will over-write any existing version of the object, so be careful to only edit objects on your "development" system.
Scripting Your Imports & Exports
Scripting your imports and exports enables you to create an end-to-end release process. To allow you to do this, TRANS-it generates a Visual Basic project, which you can edit in Visual Studio, which includes support for Intellisense and auto-complete on the TRANS-it objects themselves.
Within TRANS-it, click the menu options Tools -> Create Script, and select a directory where TRANS-it will build your project. A Visual Basic project file - TRANS-itScript.vbproj - will be created there, which you can open in Visual Studio 2005/8. (Opening it in 2008 will cause the conversion wizard to run - just let it!) Some skeleton code will have been created in the main module, allowing you to jump right in an start scripting!
Script Samples
To save all the Transformation Spaces in an Academy Database:
'Loop through all the Transformation Spaces in the Data Academy database
For Each objTransformation Space As DataServices.Transformation SpaceRow In _
DataServices.Transformation SpaceTable.GetTable()
'Create a new Transformation Space "truck"
Dim objTruck As New TruckTransformation Space
'Load-up the truck
objTruck.Load(objTransformation Space.Transformation SpaceID)
'Save the truck to the local file system
objTruck.Save("c:\export\" & objTransformation Space.Transformation SpaceName & ".trk")
Next
To save a GOVERN-it View called VW_SalesByRegion:
'Create a new "truck"
Dim objTruck As New TruckMagistrate
'Load-up the truck - the object MUST be in Magistrate!
objTruck.LoadByName("VW_SalesByRegion")
'Save the truck to the local file system
objTruck.Save("c:\export\VW_SalesByRegion.trk")
To save a Data Loader called "HR Import":
'Create a new "truck"
Dim objTruck As New TruckDataLoader
'Load-up the truck
objTruck.LoadByName("HR Import")
'Save the truck to the local file system
objTruck.Save("c:\export\HR Import.trk")
To restore a CUBE-it LITE cube, changing the Analysis Server name:
'Open the "truck"
Dim objTruck As TruckCube = _
TruckCube.Load("c:\import\sales_cube.trk")
'Change the server name
objTruck.Cube.AnalysisServer = "DA_LIVE"
'Restore the cube to Data Acadmey "live"
objTruck.Restore()
To save all Microsoft Access (mdb) Data Loaders:
'Loop through all the Access Data Loaders
For Each objDataLoader As DataServices.DataLoaderRow In _
DataServices.DataLoaderTable.GetTable( _
"SourceType = @SourceType", _
"@SourceType", DataServices.DTSSourceType.MSAccess)
'Create a new "truck"
Dim objTruck As New TruckDataLoader
'Load-up the truck
objTruck.Load(objDataLoader.DataLoaderID)
'Save the truck to the local file system
objTruck.Save("c:\export\" & objDataLoader.DataLoaderName & ".trk")
Next
To save ALL objects, copy and call the following routine:
Private Sub SaveAll(ByVal strPath As String)
'If the directory doesn't exist...
If Not System.IO.Directory.Exists(strPath) Then
'... then require that the user create it!
Throw New Exception("The directory " & strPath & _
" does not exist. Please create it.")
End If
'Data Loaders
For Each objObject As DataServices.DataLoaderRow In _
DataServices.DataLoaderTable.GetTable()
Dim objTruck As New TruckDataLoader
objTruck.Load(objObject.DataLoaderID)
objTruck.Save(System.IO.Path.Combine(strPath, _
objObject.DataLoaderName & ".trk"))
Next
'Transformation Spaces \ StarSpaces
For Each objObject As DataServices.Transformation SpaceRow In _
DataServices.Transformation SpaceTable.GetTable()
Dim objTruck As New TruckTransformation Space
objTruck.Load(objObject.Transformation SpaceID)
objTruck.Save(System.IO.Path.Combine(strPath, _
objObject.Transformation SpaceName & ".trk"))
Next
'Cubes (CUBE-it LITE)
For Each objObject As DataServices.CubeBuilderRow In _
DataServices.CubeBuilderTable.GetTable()
Dim objTruck As New TruckCube
objTruck.Load(objObject.CubeBuilderID)
objTruck.Save(System.IO.Path.Combine(strPath, _
objObject.CubeName & ".trk"))
Next
'CubesSpaces (CUBE-it)
For Each objObject As DataServices.SupaQbCubeSpaceRow In _
DataServices.SupaQbCubeSpaceTable.GetTable()
Dim objTruck As New TruckCubeSpace
objTruck.Load(objObject.CubeSpaceID)
objTruck.Save(System.IO.Path.Combine(strPath, _
objObject.CubeSpaceName & ".trk"))
Next
'Orchestrator
For Each objObject As DataServices.OrchestratorRow In _
DataServices.OrchestratorTable.GetTable()
Dim objTruck As New TruckOrchestrator
objTruck.Load(objObject.OrchestratorID)
objTruck.Save(System.IO.Path.Combine(strPath, _
objObject.OrchestratorName & ".trk"))
Next
'ScoreCard
For Each objObject As DataServices.ScoreCardRow In _
DataServices.ScoreCardTable.GetTable()
Dim objTruck As New TruckScoreCard
objTruck.Load(objObject.ScoreCardID)
objTruck.Save(System.IO.Path.Combine(strPath, _
objObject.Description & ".trk"))
Next
'Magistrate
Dim objMagistrateTruck As New TruckMagistrate
objMagistrateTruck.Load(-1)
objMagistrateTruck.Save(System.IO.Path.Combine(strPath, _
"Magistrate.trk"))
End Sub
To restore ALL objects from a directory, copy and call the following routine:
Private Sub RestoreAll(ByVal strPath As String)
For Each strFile As String In _
System.IO.Directory.GetFiles(strPath, "*.trk")
Dim objTruck As LogistixBase = LogistixBase.Load(strFile)
objTruck.Restore()
Next
End Sub