Preparing the data
This section contains steps and samples to create and populate a table and a view that contain a subset of the data from the BMC Remedy AR System data source. For more information about why data preparation is necessary and the information to help you decide whether you should create a materialized view or an SQL view, see Building a Pod from a Relational Database.
Since we know that we want to create a pod that displays information about the incidents, the groups to which they are assigned, and the days on which the incidents are reported (in our appropriate time zone), we need to create the following two data views on top of the underlying BMC Remedy AR System database:
- The incident summary view is used to perform translation of the enumerated values, transform epoch integer dates to timestamp values, and aggregate the data by priority, status, and incident reported date. So that the Pod Builder can display the incident reported date in the appropriate time zone, this incident summary view requires the time zone information table.
- The time zone information table is a lookup table that contains information about each time zone, such as offset from GMT and start and end dates for Daylight Savings Time for each year. This table is used by the incident summary view to look up the relevant information for the selected timezone when transforming the epoch integer date fields stored in the BMC Remedy AR System database to the timezone adjusted timestamp fields presented by the incident summary view.
The BMC Dashboards for BSM product expects that your timestamps be in GMT so that it can properly apply the client timezone offset and daylight savings time calculations. The BMC Remedy AR System product stores data in GMT, so no manipulation of that data is required before preparing your data for use by the BMC Dashboards for BSM product. If your timestamps are not in GMT, the times displayed in the pods will not be accurate.
Creating the incident summary view
This section contains DDL for creating and populating the incident summary view. You can copy and paste the DDL below to create your own incident summary view. You can choose to create a materialized view, but you must specify “CREATE MATERIALIZED VIEW” below instead of “CREATE VIEW”.
The sample DDL below is set for the CST timezone. To specify your own timezone, update the TIMEZONE_ID AND TIMEZONE values shown in the boldface line towards the end of the statement below. Currently, the DDL for this view is set to time zone ID CST, the America/Chicago time zone.
To create and populate the incident summary view INCIDENT_SUMMARY_DAILY, we use the following DDL:
SELECT TIMEZONE_ID,
TIMEZONE,
Date_Point,
PRIORITY AS Priority_Enum,
CASE WHEN PRIORITY = 2 THEN 'Medium'
WHEN PRIORITY = 1 THEN 'High'
WHEN PRIORITY = 0 THEN 'Critical'
WHEN PRIORITY = 3 THEN 'Low'
ELSE NULL
END AS Priority,
STATUS AS Status_Enum,
CASE WHEN STATUS = 0 THEN 'New'
WHEN STATUS = 1 THEN 'Assigned'
WHEN STATUS = 2 THEN 'In Progress'
WHEN STATUS = 3 THEN 'Pending'
WHEN STATUS = 4 THEN 'Resolved'
WHEN STATUS = 5 THEN 'Closed'
WHEN STATUS = 6 THEN 'Cancelled'
ELSE NULL
END AS Status,
ASSIGNED_GROUP AS Assigned_Group,
COUNT(INCIDENT_NUMBER) AS Num_Incidents
FROM (SELECT TIMEZONE_ID,
TIMEZONE,
(TO_TIMESTAMP((TO_CHAR(REPORTED_DATE_TZ_ADJUSTED, 'DD-MON-YY')), 'DD-Mon-YY')) - TZ_ADJUSTMENT AS Date_Point,
PRIORITY,
STATUS,
ASSIGNED_GROUP,
INCIDENT_NUMBER
FROM (SELECT TIMEZONE_ID,
TIMEZONE,
INCIDENT_NUMBER,
PRIORITY,
STATUS,
ASSIGNED_GROUP,
REPORTED_DATE,
REPORTED_DATE_TIMESTAMP,
REPORTED_DATE_YEAR,
GMT_TIMEDIFFERENCE,
REPORTED_DATE_TIMESTAMP + TZ_ADJUSTMENT AS
REPORTED_DATE_TZ_ADJUSTED,
TZ_ADJUSTMENT
FROM (SELECT TZ.TIMEZONE_ID,
TZ.TIMEZONE,
INC.INCIDENT_NUMBER,
INC.PRIORITY,
INC.STATUS,
INC.ASSIGNED_GROUP,
INC.REPORTED_DATE,
INC.REPORTED_DATE_TIMESTAMP,
INC.REPORTED_DATE_YEAR,
TZ.GMT_TIMEDIFFERENCE,
CASE WHEN TZ.DST_FLAG = 'N' THEN
TZ.GMT_TIMEDIFFERENCE/24
ELSE CASE WHEN
INC.REPORTED_DATE_TIMESTAMP >= TZ.START_DST_TIMESTAMP AND
INC.REPORTED_DATE_TIMESTAMP <= TZ.END_DST_TIMESTAMP THEN
(TZ.GMT_TIMEDIFFERENCE/24) + 1/24
ELSE
TZ.GMT_TIMEDIFFERENCE/24
END
END AS TZ_ADJUSTMENT
FROM (SELECT INCIDENT_NUMBER,
PRIORITY,
STATUS,
ASSIGNED_GROUP,
REPORTED_DATE,
REPORTED_DATE_TIMESTAMP,
TO_CHAR(REPORTED_DATE_TIMESTAMP,
'yyyy') AS REPORTED_DATE_YEAR
FROM (SELECT INCIDENT_NUMBER,
REPORTED_DATE,
PRIORITY,
STATUS,
ASSIGNED_GROUP,
TO_DATE('01-01-1970','MM-DD-YYYY') + REPORTED_DATE / 86400 AS REPORTED_DATE_TIMESTAMP
FROM ARADMIN.HPD_HELP_DESK)) INC
LEFT OUTER JOIN
ARADMIN.TIMEZONE_DETAIL_INFO TZ
ON TZ.DATE_YEAR = INC.REPORTED_DATE_YEAR AND TZ.TIMEZONE_ID IN ('CST') AND TZ.TIMEZONE IN ('America/Chicago'))))
GROUP BY TIMEZONE_ID, TIMEZONE, Date_Point, PRIORITY, STATUS, ASSIGNED_GROUP
ORDER BY TIMEZONE_ID, TIMEZONE, Date_Point, PRIORITY, STATUS, ASSIGNED_GROUP
The following image contains part of the view that results from the DDL:
(Click the image to expand it.)
Creating the time zone information table
This section contains sample DDL and information about SQL statements to create the time zone information table.
To create the time zone information table TIMEZONE_DETAIL_INFO, we use the following data definition language (DDL):
(
REGION_FLAG varchar2(1),
TIMEZONE varchar2(50),
TIMEZONE_ID varchar2(50),
DATE_YEAR char(4),
GMT_TIMEDIFFERENCE varchar2(10),
DST_FLAG varchar2(1),
START_DST_TIMESTAMP varchar2(4000),
END_DST_TIMESTAMP varchar2(4000)
);
To populate the time zone information table, we run insert statements. A sample insert statement is shown below. The full set of insert statements is found at http://communities.bmc.com/communities/docs/DOC-12137.
(REGION_FLAG,TIMEZONE,TIMEZONE_ID,DATE_YEAR,GMT_TIMEDIFFERENCE,DST_FLAG,
START_DST_TIMESTAMP,END_DST_TIMESTAMP) VALUES
('N','America/Mazatlan','MST','2000','-07.00','Y',
{ts '2000-04-02 09:00:00.000'},{ts '2000-10-29 07:59:59.000'});
Once you run the insert statements, the time zone information table will look similar to the following sample table:
(Click the image to expand it.)
Once the table and view is created, we can proceed with creating a new data source and then a data view that uses the prepared data.