Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!!!
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:
Hello Sunny. How could you get to know which WORKS/INCIDENTS have TASKS if I try one of those options? Thanks.
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
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.
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;
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
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!
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
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