Database Reference
In-Depth Information
ALTER TABLE [dbo].[SSASToSSRSObjects]
ADD CONSTRAINT [FK_SSASToSSRSObjects_SSRSObjects] FOREIGN KEY([SSRSObjectId])
REFERENCES [dbo].[SSRSObjects] ([SSRSObjectId])
GO
We can import the trace data from the trace file into a table in the metadata database using Microsoft's
fn_trace_gettable()
function. This function is designed specifically for just this purpose and is executed as
shown in Listing 18-4. Here we identify the location of the file and the file name that we want to import data
from. The code also takes the data the function returns and imports it into a new table we called ETLTraceData_
Mar15_2012. This code creates the new table for us automatically using the appropriate columns and data types
and imports the trace data as well!
Listing 18-4.
Importing Trace Data from a Trace File
Declare @Path nVarchar(500) = 'C:\_BISolutions\PublicationsIndustries\TraceFiles\'
Declare @TraceFileName nVarchar(100) = 'ELTProcessingPerformance_Mar15_2012.trc'
SELECT [TraceFileName] =@TraceFileName, *
INTO ETLTraceData_Mar15_2012
FROM fn_trace_gettable(@Path + @TraceFileName, default)
Go
Once the new table is created and filled, we can query the table using code similar to that shown in Listing 18-5.
By performing aggregate functions on the trace data within, we can calculate the average time it takes to do the ETL
processing.
Listing 18-5.
Getting Performance Information from the Trace Data
SELECT
[TimeOfTrace] = Max(StartTime)
, [Time it took to do this ETL processing] = Sum(Duration)
FROM ETLTraceData_Mar15_2012
Go
SELECT
[TraceFileName]
, [TextData]
, [Duration]
--,* -- All the other columns
FROM ETLTraceData_Mar15_2012
Figure
18-22
shows the results of these statements. Notice that the aggregated sum of the duration of each of
the ETL statements gives us a useful benchmark to measure against.
