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). This would be a common ID with values: W001, W002, W003, W004, I001, I002, I003, I004, T001, T002, T003 so I could see all of them and search them on a same ID.
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!
Hi,
Try something like this:
Data:
LOAD ID_WORK as ID,
TICKET_TYPE,
DETAIL,
etc.
From Works Source Table;
Concatenate (Data)
LOAD
ID_INCIDENT as ID,
TICKET_TYPE,
DETAIL,
etc
From Incidents Source Table;
Concatenate (Data)
LOAD ID_TASK,
TICKET_TYPE,
ID_PARENT as ID,
DETAIL,
etc.
From Tasks Source Table;
Now when you select an ID with a task associated with it, such as I003, the data model will also select records of TICKET_TYPE Task that belong to that ID.
TICKET_TYPE | ID | ID_TASK |
---|---|---|
Incident | I001 | |
Incident | I002 | |
Incident | I003 | |
Incident | I004 | |
Task | I003 | T001 |
Task | I004 | T002 |
Task | W001 | T003 |
Work | W001 | |
Work | W002 | |
Work | W003 | |
Work | W004 |
Good luck
Andrew
Hi Manuel, yes but I was trying to find some other answers for a better or more precise solution to my requirement.
Hi,
If you want to handle this in a easily way:
Join the ID_PARENT to WORKS and INCIDENTS table
Create a complex key using the ID_PARENT and the key of each table
Add a new flag field to distinct the source data
Concatenate them
Thanks,
Carlos
Hi,
there are two approaches to this:
Link table
Concatenated Fact table.
The approach given above by Andrew is a concatenated fact, which in my experience tends to be the better approach.
Marcus