Is there a way to use a dimension table for two different columns in a fact table

[#1]

Hi Gurus,

I am new to QV and I want to know the correct way to approach such situations.

I have a fact table with employees and I need to make two possible dimensions: city of residence and city of birth.

As both dimensions contain the same data what is the best way to do this?

The simplest is to make two tables with identical data one for residence and one for birth.

Can you suggest me a better approach? Should I have the data for the cities twice?

Thanx for your help,

YF

[guytzumer · Accepted solution]

The data in the row-level is not necessrily equal,

Sooo.. it mean that you need to have the data on a separate table or two fields etc..

[#3]

Two fields is not really an option (no need of making odd combinations). After all the two columns does not have anything in common besides the dimension table data 🙂

So there is no clever way of re-using the same table for different dimensions.

I was thinking something like

Dimension table:

city name

birthID

resID (both cols have same data)

Fact table:

birhtID

resID (cols pointing to the real data in the first table)

etc

[johnw]

Here's one way:

EmployeeCities:
Employee, CityType, City
Bob, Residence, Paris
Bob, Birth, London

But probably the more common way IS to just have two master lists of cities. Which is better depends on what you're doing, and technically you can do both (by having three master lists).

If you keep one master list, that makes it easy to answer questions like "which employees were either born in or now reside in London?"

If you keep two master lists, that makes it easy to answer questions like "which employees were born in London and now reside in Paris?". It sounds like this might be what you mean by "making odd combinations", in which case this does nothing useful for you.

If you keep three master lists, you can answer both questions easily, but the users will likely get quite confused about which fields they're supposed to be selecting values for. Again, if you don't need odd combinations, then you won't need this approach.

[#5]

Thanx for your help guys 🙂

Seems I tried to do something that QV does not like, however I am mislead by the long time spent on Oracle tech stack.

[johnw]

y.fingarov wrote:Seems I tried to do something that QV does not like

Well, it's not so much that QlikView dislikes what you're trying to do, just that it doesn't have a specific solution to it. So you must therefore solve it with your data model.

Still, generally speaking, my opinion is that if you CAN solve a problem in the data model, you SHOULD solve it in the data model. That may mean, of course, that QlikView requires more data modeling experience than some other products to get the best results. I don't really have experience with other BI products to know, though.