Retrieving information about local cube creation
Scheduling CubeSlice
The CubeSlice Creator can be run from the command line, which enables you to schedule the creation of local cube and other CubeSlice files. You can run the CubeSlice Creator:
- As a step in a DTS or SSIS Package, using an Execute Process Task
- As a SQL Server job
- From a batch file
- Using any other scheduler
When running the CubeSlice Creator from a command line you specify the name of the CubeSlice Specification (CUV) file that contains the information for the local cubes you want to create. You also must use the –c parameter, which causes CubeSlice to open, create the files, and automatically close. The only part of the CubeSlice interface that is displayed is the Cube Creation progress window. Here’s what the command line looks like:
“C:\Program Files\CubeSlice 9\Bin9\cscreatr.exe” "c:\temp\warehouse.cus" –c
File creation information in the CubeSlice Repository
All local cube and CubeSlice file creation information is stored in a database called the CubeSlice Repository. The location of the CubeSlice Repository is displayed in the About CubeSlice box, available from the Help menu. By default this database is stored in the following Microsoft Access file:
My Documents\My CubeSlice Files\Administrative\CubeSliceRepository.mdb
The default location of the repository for CubeSlice 8.50 is:
C:\Program Files\CubeSlice\Bin\CubeSliceRepository.mdb
There are two tables in the CubeSlice Repository that contain cube creation information:
CubeSliceExecution contains one record for every time local cubes are created with a CUS file. This record contains the following fields
CubeSliceExecutionID – Primary Key
ExecutionStartTime
ExecutionEndTime
ExecutionTimeInSec
SuccessfulCubes – Number of local cube files that were created.
FailedCubes - Number of local cube files that failed.
SkippedCubes - Number of local cube files that were skipped – most often because they had no data in them.
AlreadyExistingCubes - Number of local cube files that were not created because they already exist, and the choice was made in the CUS to not overwrite existing cubes.
SuccessfulCUVs – Number of CubeSlice View files created.
SuccessfulCUDs - Number of CubeSlice Data files created.
SuccessfulShareFiles - Number of CubeSlice View files created.
SuccessfulZIPFiles - Number of zip files created.
FileCreationResult – Summarized result. Often has a value of “Creation Complete.” Can have other values, such as “Creation canceled due to lack of disk space.”
The CubeSliceLog table contains one record for every attempted creation of a local cube and other file. Here are the fields in this table:
CubeSliceLogID – Primary Key
CubeSliceExecutionID- Foreign Key to the CubeSliceExecution table
CreationEndTime
ActionType – Has one of the following values: Local Cube Creation, CUV Creation, CUD Creation, Adding File to Zip, Zip File Creation
ShortFileName – The file name being created
Result – One of these values: Succeeded, Failed, Skipped, Already Exists
Information – The reason a file failed or was skipped. If an error occurred, contains the error message.
Details – Sometimes has extra information. This field is usually not used.
CreationStartTime
CreationTimeInSec – For a local cube, just the time spent executing the command to create the cube.
FileName – Full path and file name.
ServerCubeSize – Reported size of the server cube.
LocalCubeSize – Local cube size. This value is not always populated.
CompressionRatio – Compression ratio when a zip file is being created.
ZipFileSize – Size of the zip file.
Getting results of CubeSlice execution inside of Data Transformation Services (DTS)
I have created a DTS package that shows how this information can be extracted when CubeSlice is run from inside of DTS. In this package I extract the information into a text file and used it to raise a DTS error, but it could also be entered into a separate database, used to trigger an e-mail notice to a user, used to change the tasks that are run in the DTS package, or entered into the DTS task execution log.
This package has the following steps:
An Execute SQL Task that loads a global variable called PreviousMaxCubeSliceKey. It isn’t necessary to do this if you have a DTS package that calls CubeSlice once. But if you are running CubeSlice several times in the same package, you can save the previous maximum key value before starting and then use that value to prepare a combined report showing the results of all the times you have run CubeSlice.
An Execute Process Task that runs CubeSlice, using a CUS file called warehouse.cus, which attempts to create 6 local cube files from the FoodMart 2000 sample database. Five of the cubes are created and one is skipped because it has no data.
A Transform Data Task that selects the most recent summary record from the CubeSlice Repository and writes that record to a text file.
A Transform Data Task that selects all the detail records for the most recent CubeSlice execution and writes that record to a text file.
A Transform Data task that writes basic information about the failed and skipped local cube creation to a text file. This Transform Data task uses the global variable that was saved before CubeSlice was run. By using this global variable, you can save information from CubeSlice being executed several times.
A Transform Data task that raises an error if any of the local cubes failed or was skipped. When running CubeSlice from an Execute Process task, it is not possible to directly retrieve error information. However, a Transform Data task can be used like this to raise an error if there have been any failures and/or skips.