Chapter 1: Create Jenkins DM tables

In this chapter we will create Jenkins DM tables that will be populated on next chapters and the monitoring reports will be based on.


Dimension Tables


                                                       XMLwithOpenXML                                                          

This will be the main table for loading the data from the xml files into the DM tables:

drop TABLE XMLwithOpenXML;

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
);

select * from XMLwithOpenXML;

                                                              JJOBS_DIM                                                                 

This will be the jenkins jobs dimension table. This table will include important information regarding the jobs.

drop table JJOBS_DIM;

CREATE TABLE JJOBS_DIM
(
ID INT IDENTITY PRIMARY KEY,
displayName varchar(100) NULL,
name varchar(100) NULL,
jobtype varchar(100) NULL,
url varchar(1000) NULL

);

                                                               VIEWS_DIM                                                               

This will be the jenkins jobs dimension table. This table will include important information regarding the views the show group of jobs.

drop table JVIEWS_DIM;

create table JVIEWS_DIM
(
ID INT IDENTITY PRIMARY KEY,
viewid integer,
viewname varchar(100),
jobname varchar(100),
url varchar(100),
color varchar(100)
);

select * from JVIEWS_DIM;

                                                             JPIPELINE_DIM                                                              

This will be the Jenkins pipeline dimension table. This table will include important information regarding the pipelines on the CI server. The pipeline stores the connections between upstream and downstream projects.

drop table JPIPELINE_DIM;

create table JPIPELINE_DIM
(
ID INT IDENTITY PRIMARY KEY,
pipelineid integer,
pipelinename varchar(100),
name varchar(100),
url varchar(100),
color varchar(100)
);

select * from JPIPELINE_DIM;

                                                             JLASTJOBS_FACT                                                             

This will be the jenkins last jobs fact table. This table will include information regarding the runs of the jobs on the CI server.

drop table JLASTJOBS_FACT;

create table JLASTJOBS_FACT
(
ID INT IDENTITY PRIMARY KEY,
jobdate date,
jobtimestamp datetime,
jobname varchar(100),
fullDisplayName varchar(100),
run_id varchar(100),
url varchar(100),
duration integer,
result varchar(100),
result_id integer,
pop_timestamp datetime
);

select * from JLASTJOBS_FACT;

                                                              JPIPELINEJOBS                                                               

This will be the jenkins pipeline and jobs dimension table. This table will include information regarding the connection between pipelines and jobs in the server.
drop table JPIPELINEJOBS;

create table JPIPELINEJOBS
(
ID INT IDENTITY PRIMARY KEY,
jobname varchar(100),
fullDisplayName varchar(100),
run_id varchar(100),
upstreamProject varchar(100),
upstreamBuild varchar(100),
pop_timestamp datetime
);

select * from JPIPELINEJOBS;

                                                        JPIPELINEJOBS_FACT                                                          

This will be the jenkins fact major table.This table will store all the relevant data for the monitoring reports.

drop table JPIPELINEJOBS_FACT;

create table JPIPELINEJOBS_FACT
(
ID INT IDENTITY PRIMARY KEY,
jobdate date,
pipelineid varchar(100),
pipelinename varchar(100),
jobname varchar(100),
jobtimestamp datetime,
run_id varchar(100),
duration integer,
result varchar(100),
result_id integer,
pop_timestamp datetime
);

select * from JPIPELINEJOBS_FACT;

Advertisements

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