Data Model - Multiple Facts - Qlik Community

microwin88x · ‎2016-06-21

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). 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!

Anonymous · ‎2016-06-21

Hi microwin,

You have answered the same on Data Model - 3 Main Tables‌, isn't it?

Regards!

effinty2112 · ‎2016-06-21

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

microwin88x · ‎2016-06-21

Hi Manuel, yes but I was trying to find some other answers for a better or more precise solution to my requirement.

CarlosAMonroy · ‎2016-06-21

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

marcus_malinow · ‎2016-06-21

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