Data Model - 3 Main Tables

[microwin88x]

Hello,

I have 3 main tables: Works, Incidents and Tasks.

WORKS:

ID_WORK	TICKET_TYPE	DETAIL	SERVICE	SUPPORT	GROUP	ASSIGNEE	REQUEST_USER	ZONE	BUILDING	VIP_MEMBER	ENVIRONMENT	CATEGORY1	CATEGORY2	CATEGORY3	CREATE_DATE	CREATE_TIME	REAL_START_DATE	REAL_START_TIME	REAL_END_DATE	REAL_END_TIME	LAST_MODIF_DATE	LAST_MODIF_TIME	SUBMITTER	ADMIN	PRIORITY
W001	Work	Password Issue	SRV001	Helpdesk	Helpdesk	USER001	REQUSER015	D	IV	NO	3	APPS	SAP	REQUIREMENT	15/05/2016	11:20:13	18/05/2016	11:21:13	3/6/2016	2:45:32	03/06/2016	2:45:32	USR001	ADM001	Low
W002	Work	Printer Config	SRV002	Helpdesk	Helpdesk	USER002	REQUSER016	D	V	NO	1	APPS	OFFICE	REQUIREMENT	15/05/2016	12:00:10	18/05/2016	12:00:10	3/6/2016	2:45:32	03/06/2016	2:45:32	USR002	ADM003	Low
W003	Work	Requirement	SRV001	Helpdesk	Helpdesk	USER003	REQUSER017	A	I	NO	3	PHONE	ADMIN	ISSUE	15/05/2016	11:57:50	18/05/2016	11:57:50	3/6/2016	2:45:31	03/06/2016	2:45:35	USR003	ADM006	Low
W004	Work	Service Config	SRV003	Technology	IT	USER005	REQUSER015	B	II	NO	0	APPS	SOFTWARE	REQUIREMENT	15/05/2016	10:35:03	18/05/2016	13:05:07	22/05/2016	2:45:01	22/05/2016	2:45:08	USR004	ADM007	Low

INCIDENTS:

ID_INCIDENT	TICKET_TYPE	DETAIL	SERVICE	SUPPORT	GROUP	ASSIGNEE	REQUEST_USER	ZONE	BUILDING	VIP_MEMBER	ENVIRONMENT	CATEGORY1	CATEGORY2	CATEGORY3	CREATE_DATE	CREATE_TIME	LAST_MODIF_DATE	LAST_MODIF_TIME	STATUS	PRIORITY	RESOLUTION	CLOSE_DATE	CLOSE_TIME	IMPACT	URGENCY
I001	Incident	Mail Support	SRV005	Reports	Web 2.0	USER008	REQUSER010	A	I	NO	0	APPS	SOFTWARE	ERROR	18/05/2016	17:52:55	30/05/2016	2:00:12	Closed	High	Start	04/06/2016	2:00:12	2	High
I002	Incident	Software Install	SRV007	Reports	Web 2.0	USER009	REQUSER011	A	I	YES	1	APPS	SOFTWARE	ERROR	18/05/2016	20:15:29	30/05/2016	14:10:42	Closed	Low	Transfer	30/05/2016	14:10:43	4	Low
I003	Incident	Phone Renewal	SRV001	Reports	IT	USER001	REQUSER012	A	II	YES	3	APPS	SOFTWARE	ERROR	19/05/2016	20:58:28	19/05/2016	2:15:45	Closed	Low	Recieved	19/05/2016	2:11:45	3	Low
I004	Incident	Process Error	SRV003	Technology	IT	USER002	REQUSER012	C	III	NO	1	APPS	SOFTWARE	ERROR	20/05/2016	12:40:17	22/05/2016	2:00:03	Closed	Low	Done	22/05/2016	2:00:03	3	Low

TASKS:

ID_TASK	TICKET_TYPE	ID_PARENT	DETAIL	SUPPORT	GROUP	REQUEST_USER	ZONE	BUILDING	VIP_MEMBER	ENVIRONMENT	CATEGORY1	CATEGORY2	CATEGORY3	CREATE_DATE	CREATE_TIME	REAL_START_DATE	REAL_START_TIME	REAL_END_DATE	REAL_END_TIME	PRIORITY	ENVIRONMENT	SUBMITTER	LAST_MODIF_DATE	LAST_MODIF_TIME
T001	Task	I003	Permissions Issue		Edenor 2.0	REQUSER010	A	I	YES	0	APPS	SOFTWARE	ERROR	18/05/2016	17:50:15	31/05/2016	16:55:40			High	0	USR005	31/05/2016	16:55:40
T002	Task	I004	Folder Request	Technology	Operations	REQUSER011	B	II	NO	1	APPS	SOFTWARE	REQUIREMENT	18/05/2016	12:56:59	31/05/2016	14:15:12	18/05/2016	14:15:52	High	1	USR007	18/05/2016	14:15:52
T003	Task	W001	Process Config	Technology	Operations	REQUSER012	B	I	NO	0	APPS	SOFTWARE	REQUIREMENT	18/05/2016	13:05:45	18/05/2016	14:09:43	18/05/2016	14:09:43	Low	0	USR009	18/05/2016	14:09:45

Each table has an ID:

• For WORKS: W001, W002, W003
• For INCIDENTS: I001, I002, I003
• For TASKS: T001, T002, T003

First of all, I need to have a UNIQUE ID to manage all IDs from all 3 tables. For example, having an ID_MAIN where I could have all values from: ID_WORK, ID_INCIDENT and ID_TASK (W00x, I00x and T00x) with frequency = 11 on this case (4+4+3).

As you may have noticed, I have many common fields over the 3 tables. And there are also specific fields for every table.

I wanted to know which is the best way to create my data model. My general request is the following so far:

- Quantity of Tickets (Works/Incidents/Tasks) by: CREATE_DATE/GROUP/CATEGORY1/STATUS/PRIORITY

- Another thing to considerate is the ID_PARENT in table TASKS. The thing is that each TASK is assigned to a WORK or INCIDENT. That means that for example if I have in table TASKS ID_MAIN=T001, I could have ID_PARENT=I003, meaning that TASK belongs to an INCIDENT. I say this because I will have to determine which tasks are assigned to a WORK or an INCIDENT. Please, note that the field ID_PARENT is only available in table TASKS.

How would you solve this?

Thank you!!!

[sunny_talwar]

There is two ways to handle data like this (three fact tables).

1) Concatenate the three tables into one table

2) LinkTable to create a implicit join between the three tables

Read about both these options here:

Concatenate vs Link Table

[microwin88x]

Hello Sunny. How could you get to know which WORKS/INCIDENTS have TASKS if I try one of those options? Thanks.

[sunny_talwar]

I will have to go through your post more thoroughly to provide a better answer. I wanted to give a general direction for now. If no body gets back to you, I will try to respond back at a later point.

Best,

Sunny

[oscar_ortiz]

What is the relationship between the Works and Incidents table?

If there is no connection, you'll probably have to follow the Link Table process.

Using your ID_PARENT field to connect to both your ID_INCIDENT and ID_WORK fields.

[muthukumar77]

Hi,

Try this,

WORK_INCIDENT:
LOAD
ID_WORK as ID,
TICKET_TYPE,
DETAIL,
SERVICE,
SUPPORT,
GROUP,
ASSIGNEE,
REQUEST
from WORK;

CONCATENATE(WORK_INCIDENT)

LOAD
ID_INCIDENT as ID,
TICKET_TYPE,
DETAIL,
SERVICE,
SUPPORT,
GROUP,
ASSIGNEE,
REQUEST
from INCIDENT;

RIGHT JOIN(WORK_INCIDENT)

LOAD
ID_TASK,
TICKET_TYPE,
ID_PARENT as ID,
DETAIL,
SUPPORT,
GROUP,
REQUEST_USER,
ZONE
from
TASK;

[avinashelite]

Since the task table has both Incidents and the Work (refer ID_Parent column), you can directly join this tables my renaming the ID_PARENT, ID_WORK, ID_INCIDENT as KEY

[Anonymous]

Hi microwin88x,

I ever use autoconcatenate to work with this, because I just can control all Null fields.

MainData:

LOAD

    ID_WORK                    as ID_MAIN,

    TICKET_TYPE,

    DETAIL,

    SERVICE,

    SUPPORT,

    GROUP,

    ASSIGNEE,

    REQUEST_USER,

    ZONE,

    BUILDING,

    VIP_MEMBER,

    ENVIRONMENT,

    CATEGORY1,

    CATEGORY2,

    CATEGORY3,

    CREATE_DATE,

    CREATE_TIME,

    REAL_START_DATE,

    REAL_START_TIME,

    REAL_END_DATE,

    REAL_END_TIME,

    LAST_MODIF_DATE,

    LAST_MODIF_TIME,

    SUBMITTER,

    ADMIN,

    PRIORITY,

    Null()                    as STATUS,

    Null()                    as RESOLUTION,

    Null()                    as CLOSE_DATE,

    Null()                    as CLOSE_TIME,

    Null()                    as IMPACT,

    Null()                    as URGENCY,

    Null()                    as ID_PARENT

RESIDENT Tab1;

MainData:

LOAD

    ID_INCIDENT                    as ID_MAIN,

    TICKET_TYPE,

    DETAIL,

    SERVICE,

    SUPPORT,

    GROUP,

    ASSIGNEE,

    REQUEST_USER,

    ZONE,

    BUILDING,

    VIP_MEMBER,

    ENVIRONMENT,

    CATEGORY1,

    CATEGORY2,

    CATEGORY3,

    CREATE_DATE,

    CREATE_TIME,

    LAST_MODIF_DATE,

    LAST_MODIF_TIME,

    STATUS,

    PRIORITY,

    RESOLUTION,

    CLOSE_DATE,

    CLOSE_TIME,

    IMPACT,

    URGENCY,

    Null()                    as REAL_START_DATE,

    Null()                    as REAL_START_TIME,

    Null()                    as REAL_END_DATE,

    Null()                    as REAL_END_TIME,

    Null()                    as SUBMITTER,

    Null()                    as ADMIN,

    Null()                    as ID_TASK,

    Null()                    as ID_PARENT   

RESIDENT Tab2;

MainData:

LOAD

    ID_TASK                    as ID_MAIN,

    TICKET_TYPE,

    ID_PARENT,

    DETAIL,

    SUPPORT,

    GROUP,

    REQUEST_USER,

    ZONE,

    BUILDING,

    VIP_MEMBER,

    ENVIRONMENT,

    CATEGORY1,

    CATEGORY2,

    CATEGORY3,

    CREATE_DATE,

    CREATE_TIME,

    REAL_START_DATE,

    REAL_START_TIME,

    REAL_END_DATE,

    REAL_END_TIME,

    PRIORITY,

    ENVIRONMENT,

    SUBMITTER,

    LAST_MODIF_DATE,

    LAST_MODIF_TIME,

    Null()                    as SERVICE,

    Null()                    as ASSIGNEE,

    Null()                    as ADMIN,

    Null()                    as STATUS,

    Null()                    as RESOLUTION,

    Null()                    as CLOSE_DATE,

    Null()                    as CLOSE_TIME,

    Null()                    as IMPACT,

    Null()                    as URGENCY   

RESIDENT Tab3;

LEFT JOIN (MainData)

LOAD

    ID_MAIN                    as ID_PARENT,

    TICKET_TYPE                as PARENT_ID_TYPE

RESIDENT MainData;

Regards!

[Anonymous]

Hi ,

Since the tables WORKS and INCIDENTS have similar symmetry (have similar columns), so we can concatenate them and join with TASKS table.

Best regards,

Kaveri

[#10]

Hi,

The best approach is to CONCATENATE the 3 tables :

after changing the column name

ID_work as ID

ID_incident as ID

ID_task as ID

Now your first requirement is satisfied.

Suppose you have Quantity of tickets from incident table  ......you can use:  count({<Ticket type ='Incident '>}ID)

2nd requirement:

How many task belong to Incident.......

if Match(ID,IDparent),count ({<Ticket type ='Incident '>}ID)

if Match(ID,IDparent),count ({<Ticket type ='Work '>}ID)

Thanks

Khushboo