Chapter 3: Loading data from xml files into DM Tables

Loading dimension data into jobs dimension table

Load the data into the dimension table with the script on the DB server based on that we copied the files into the source directory of the DB server (D:\Temp)

1cRevertCognosMachinesMainCI Example
truncate table XMLwithOpenXML;

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK ‘D:\Temp\1cRevertCognosMachinesMainCI.xml’, SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT INTO JJOBS_DIM
SELECT displayName, name, url, number
FROM OPENXML(@hDoc, ‘/multiJobProject/build’)
WITH
(
displayName varchar(100) ‘../displayName’,
name varchar(100) ‘../name’,
url varchar(100) ‘../url’,
number varchar(100) ‘number’
)

EXEC sp_xml_removedocument @hDoc
GO

select * from JJOBS_DIM;

 

Loading jobs daily process data into jobs fact table

Load the daily jobs data into the fact table with the script on the DB server based on that we copied the files into the source directory of the DB server (D:\Temp)

–1cRevertCognosMachinesMainCIlastbuild
truncate table XMLwithOpenXML;

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK ‘D:\Temp\1cRevertCognosMachinesMainCIlastbuild.xml’, SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT INTO JLASTJOBS_FACT
SELECT
cast(dateadd(ms, timestamp-(timestamp/1000)*1000,dateadd(ss, timestamp/1000+3*60*60, ‘19700101’)) as date),
dateadd(ms, timestamp-(timestamp/1000)*1000,dateadd(ss, timestamp/1000+3*60*60, ‘19700101’)) as jobtimestamp,
‘1.c Revert Cognos Machines (Main CI)’ as jobname,fullDisplayName, id, url, duration, result,case when result=’FAILURE’ then 0 else 1 end as result_id, getdate() as pop_timestamp
FROM OPENXML(@hDoc, ‘/multiJobBuild/changeSet’)
WITH
(
timestamp bigint ‘../timestamp’,
fullDisplayName varchar(100) ‘../fullDisplayName’,
id varchar(100) ‘../id’,
duration integer ‘../duration’,
result varchar(100) ‘../result’,
url varchar(100) ‘../url’
)

EXEC sp_xml_removedocument @hDoc
GO

Loading pipeline jobs daily connections data into pipeline jobs fact table

Load the daily connected specific jobs data into the fact table with the script on the DB server based on that we copied the files into the source directory of the DB server (D:\Temp)

–1cRevertCognosMachinesMainCIrellastbuild
truncate table XMLwithOpenXML;

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK ‘D:\Temp\1cRevertCognosMachinesMainCIrellastbuild.xml’, SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT INTO JPIPELINEJOBS
SELECT ‘1.c Revert Cognos Machines (Main CI)’ as jobname,fullDisplayName, id, NULL As upstreamProject, NULL as upstreamBuild, getdate() as pop_timestamp
FROM OPENXML(@hDoc, ‘/multiJobBuild’)
WITH
(
upstreamBuild varchar(100) ‘action/cause/upstreamBuild’,
upstreamProject varchar(100) ‘action/cause/upstreamProject’,
id integer ‘id’,
fullDisplayName varchar(100) ‘fullDisplayName’
)

EXEC sp_xml_removedocument @hDoc
GO

Building major fact pipeline Jobs fact table

Populating all data regrading the pipelines, Jobs, connections between them and the measures associated to them. This table will be the source for the monitoring reports.

truncate table JPIPELINEJOBS_FACT;

WITH c AS
(
SELECT id, jobname +’ #’ + run_id as run_id, jobname, upstreambuild, upstreamproject +’ #’ + upstreambuild as upstreamproject, jobname +’ #’+ run_id AS topParentID
FROM JPIPELINEJOBS
WHERE upstreambuild is null

UNION ALL

SELECT T.id, T.jobname +’ #’ + T.run_id as run_id, T.jobname, T.upstreambuild, T.upstreamproject +’ #’ + T.upstreambuild as upstreamproject, c.topparentid
FROM JPIPELINEJOBS AS T
INNER JOIN c ON T.upstreamproject +’ #’ + T.upstreambuild = c.run_id

WHERE T.jobname +’ #’ + T.run_id <> T.upstreamproject +’ #’ + T.upstreambuild
)

insert into JPIPELINEJOBS_FACT (jobdate, pipelineid, pipelinename, jobname, jobtimestamp, run_id, duration, result, result_id,pop_timestamp)

select
fj.jobdate,
c.topparentid as pipelineid,
dp.pipelinename,
fj.jobname,
fj.jobtimestamp,
fp.jobname +’ #’ + fp.run_id,
case when d.jobtype=’freeStyleProject’ then fj.duration else 0 end as duration ,
fj.result,
fj.result_id,
getdate() as pop_timestamp

from
JPIPELINEJOBS fp,
JLASTJOBS_FACT fj,
JJOBS_DIM d,
JPIPELINE_DIM dp,
c

where
fp.jobname=fj.jobname and fp.run_id=fj.run_id
and fj.jobname=d.name
and d.name=dp.name
and c.run_id= fp.jobname +’ #’ + fp.run_id

order by
fj.jobdate,
c.topparentid,
dp.pipelinename,
fj.jobtimestamp

select * from JPIPELINEJOBS_FACT;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s