Modelling multi valued fields

[trevorho]

Hi Community.

I have a bit of a modelling challenge related to how to handle multi value fields captured on the source system. Users capture fields for a ticket record. The majority of fields are single value type fields so this makes it easy to create a ticket dimension with each field as an attribute on the dimension. Until multi value fields come along! Let’s assume we have two multi value fields - there are more but let’s keep it relatively simple first. The two multi valued fields are Team and Error Type. The user can leave each blank, or populate one or  many selections. So in the worst case scenario there might be 3 teams (A, B, and C) and 4 error types (1, 2, 3, and 4) selected and they are not related to each other. I have modelled this as a fact table with a ticket key for the ticket dimension and a ticket category key for the ticket category dimension. The ticket category dimension  has a category group (team or error type) and category value (A to C, 1 to 4). So basically the fact has 7 rows for the same ticket and the seven multi value selections from the source system.

My problem is that the users want to be able to filter by a multi value field, as in show me all tickets where Team B has been selected but at the same time also have one or more multiple value fields side by side on one row. In a more simple example of one team and one error type being selected it comes out as two rows in the cube,  the first showing the team and blank for error type and the second row showing the error type and blank for the team.

My current line of thought is to keep the individual category fields, e.g Team and Errorv type as filter fields and then creating 6 separated fields for placement as rows. That way it will show one ticket row with two fields for “Team selections” and “Error Selections”.

Thoughts anyone?

Thanks 

Trevor

[trevorho]

Sure, this is made up data:

• Ticket "TKT001" is captured with amongst other fields, Teams (A, B, and C) and Error Types (1,2,3,4)
• Ticket 'TKT002" is captured with amongst other fields, Teams (A) and Error Types (1)

My two dimensions:

DimTicket

TicketKey	TicketCode	Other fields
1	TKT001	Blah Blah
2	TKT002	More blah blah

DimTicketCategory

TicketCategoryKey	TicketCategory	TicketCategoryValue
1	Team	A
2	Team	B
3	Team	C
4	Error Type	1
5	Error Type	2
6	Error Type	3
7	Error Type	4

Fact Table

TicketKey	TicketCategoryKey
1	1
1	2
1	3
1	4
1	5
1	6
1	7
2	1
2	4

This is the view they want:

Filter: Team

Filter: Error Type

Ticket No	Team	Error Type
TKT001	A,B,C	1,2,3,4
TKT002	A	1

while still being able to filter for individual teams or error types

My current view looks like this :

Ticket No	Team	Error Type
TKT001	A
TKT001	B
TKT001	C
TKT001		1
TKT001		2
TKT001		3
TKT001		4
TKT002	A
TKT002		1

Hope this makes sense

Trevor

[v-chenwuz-msft]

Hi @trevorho ,

Please half of it is understandable, but the second half is not quite clear what you want the result to look like, can you draw it or show it in excel? If possible, please attach the example data.

Best Regards

Community Support Team _ chenwu zhu