DN Tables

This section outlines the DN table created for Problem Management for generating multi-valued tables based on the table name and group name, displaying the attributes created within the group. This can be used for reporting purposes, as only the differential data is updated each time it is executed.

To deploy the DN table, perform the following steps:

1. Run the text file below named "PM_RPT_Relation_ProblemRecord_Attribute.txt". The script will create a table named as “PM_RPT_Relation_ProblemRecord_Attribute”.
   
   

   PM_RPT_Relation_ProblemRecord_Attribute.txt Expand source

   create table dbo.PM_RPT_Relation_ProblemRecord_Attribute 
   (
   [ID] int identity(1,1) not null
   ,[Attribute_id] int null
   ,[Group_ID] int null
   ,[Original_Sup_Function] nvarchar(20) null
   ,[Original_Group_Name] nvarchar(200) null
   ,[Original_Attribute_Name] nvarchar(300) null 
   ,[Sup_Function] nvarchar(20) null
   ,[Group_Name] nvarchar(100) null
   ,[Attribute_Name] nvarchar(100) null 
   ,Table_Name nvarchar(400)
   ,Table_Object_id int
   ,Table_Column_id int
   ,Active nvarchar(50) null
   ,[Updated_Date] datetime null
   ,Control_Type nvarchar(200) null
   ,Control_Sub_Type nvarchar(200) null
   ,IsMultiValued bit null
   )
   go
   
   alter table dbo.PM_RPT_Relation_ProblemRecord_Attribute 
   add constraint Pk_PM_RPT_Relation_ProblemRecord_Attribute primary key nonclustered ([ID]) 
   go
   
   create clustered index IX_PM_RPT_Relation_ProblemRecord_Attribute_1882 on dbo.PM_RPT_Relation_ProblemRecord_Attribute 
   ([Attribute_id]) 
   go

2. Run the text file below named “DN_Replication_PM_Custom_MVCustomAttributes_Details.txt”. The script will create a table named as “DN_Replication_PM_Custom_MVCustomAttributes_Details”.
   
   

   DN_Replication_PM_Custom_MVCustomAttributes_Details.txt Expand source

   CREATE TABLE [dbo].[DN_Replication_PM_Custom_MVCustomAttributes_Details](
   	[Problem_Ticket_Id] [numeric](18, 0) NULL,
   	[RowID] [numeric](18, 0) NULL,
   	[Attribute_ID] [numeric](18, 0) NULL,
   	[Attribute_Name] [nvarchar](100) NULL,
   	[AttributeValue] [nvarchar](max) NULL,
   	[Updated_Time] [datetime] NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO

3. Run the text file below named “UDF_RPT_DN_PM_GetDropdownValue.txt”. The script will create a function named as “UDF_RPT_DN_PM_GetDropdownValue”.
   

   UDF_RPT_DN_PM_GetDropdownValue.txt Expand source

   CREATE TABLE [dbo].[DN_Replication_PM_Custom_MVCustomAttributes_Details](
   	[Problem_Ticket_Id] [numeric](18, 0) NULL,
   	[RowID] [numeric](18, 0) NULL,
   	[Attribute_ID] [numeric](18, 0) NULL,
   	[Attribute_Name] [nvarchar](100) NULL,
   	[AttributeValue] [nvarchar](max) NULL,
   	[Updated_Time] [datetime] NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO

4. Run the embedded files named “USP_PM_RPT_LoadReportingData_06.txt”. The script will create a Stored Procedure named as “USP_PM_RPT_LoadReportingData_06”
   

   USP_PM_RPT_LoadReportingData_06.txt Expand source

   CREATE TABLE [dbo].[DN_Replication_PM_Custom_MVCustomAttributes_Details](
   	[Problem_Ticket_Id] [numeric](18, 0) NULL,
   	[RowID] [numeric](18, 0) NULL,
   	[Attribute_ID] [numeric](18, 0) NULL,
   	[Attribute_Name] [nvarchar](100) NULL,
   	[AttributeValue] [nvarchar](max) NULL,
   	[Updated_Time] [datetime] NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO

5. Run the command. – “Exec [USP_PM_RPT_LoadReportingData_06]”

   Above command will create single valued tables and data into table named as follows.

   The naming of the tables as explained as follows:

   PM_RPT_DN_ <Sup Function>_< Group Name >_Multi_Valued

   Example: Select * from PM_RPT_DN_IT_Customer_Multi_Valued

   where < Sup Function > is IT and < Group Name > is Customer
   
   

6. Create and schedule the SQL agent job in the instance with below configuration details.  
   Job Name: PM DN Custom <Customer Name> Multi Valued Attribute

   Frequency: Every 30 mins

   Duration: Daily

How to Rollback (In case of emergency)

To rollback the changes made with the DN table, perform the following steps:

1. Delete the SQL agent job. Job Name: PM DN Custom <Customer Name> Multi Valued Attribute
   
   
2. Run the command – “DROP procedure USP_PM_RPT_LoadReportingData_06”.
   This will drop the stored procedure – “USP_PM_RPT_LoadReportingData_06”.
   
   
3. Run the command.
   This will drop the table – “DROP TABLE dbo.DN_Replication_PM_Custom_MVCustomAttributes_Details”
   This will drop the table – “DROP TABLE dbo.PM_RPT_Relation_ProblemRecord_Attribute”.
   
   
4. Also, Drop all the Dynamically created DN tables in the below format:
   'PM_RPT_DN_'+<Sup Function>+'_'+<Group Name>+'_Multi_Valued'