Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

modelling qualifiers

2 posters

Go down

modelling qualifiers Empty modelling qualifiers

Post  johan Tue Mar 27, 2012 5:08 am

Hi guys,

I am confronted with a difficult problem.
One table in the source system contains 3 qualifier columns with 3 qualifiervalue columns, for example:
Qualifier1 Qualifier1Value Qualifier2 Qualifier2Value Qualifier3 Qualifier3Value
Excess kilometer cost 5 Less kilometer cost 3 ReliefVehicleTime Within 3 hours

For each client the Qualifier columns can contain different Qualifier types and values.
The requirement is to have access to those qualifiers in an easy way.
Like counting all customers that have a ReliefVehicleTime within 3 hours, but this qualifier can appear in all 3 qualifier columns.
Is it a good idea to put all qualifiertypes in one column and all qualifiervalues in one column (union) and then 'pivot' the types into column headers for access by the users? Or do you propose another solution?

Thanks for your help!

Johan

johan

Posts : 5
Join date : 2012-02-09

Back to top Go down

modelling qualifiers Empty Re: modelling qualifiers

Post  ngalemmo Tue Mar 27, 2012 7:34 am

Rotate the data so you have one qualifier per row.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

modelling qualifiers Empty Re: modelling qualifiers

Post  johan Tue Mar 27, 2012 8:15 am

That will multiply the number of records by 3 as each record has 3 qualifier columns.

johan

Posts : 5
Join date : 2012-02-09

Back to top Go down

modelling qualifiers Empty Re: modelling qualifiers

Post  ngalemmo Tue Mar 27, 2012 8:38 am

Yes. And... how else would you do it? As you said, a qualifier can appear in any of the three columns. Such a structure is very difficult to query. You can't go to a one column per qualifier structure as loading it would require complex processing, the row could be very wide, plus if they invent a new qualifier you need to alter code and data structures.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

modelling qualifiers Empty Re: modelling qualifiers

Post  johan Tue Mar 27, 2012 8:45 am

We are also looking at a one column per qualifier structure as it offers the best usability for a user. We understand that this requires some maintenance, but we don't think it will change very often.

Johan

johan

Posts : 5
Join date : 2012-02-09

Back to top Go down

modelling qualifiers Empty Re: modelling qualifiers

Post  ngalemmo Tue Mar 27, 2012 9:25 am

Are the users hand-writing SQL queries, or do you have a BI tool? Don't confuse storage with presentation.

How many qualifiers are there? If there are only 5 or 6 and it has been that way for as long as anyone can remember, then fine. If you have dozens, then it is not such a good idea. It is easy enough to rotate the data back to a flat form in a BI tool, or by defining a view. If they add new codes in the future, it is just a matter of changing the BI tool metadata or the view. It doesn't require a full-on development project to fix the load.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

modelling qualifiers Empty Re: modelling qualifiers

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum