dimension used in several fact tables

[#1]

Hi

I have problem understanding the basic of data modelling. I have for instance some dim tables that link like money code and country, or country code and Country.

I need these dim in several fact tables, so I get circular references. So does this mean that I will be forced to join the fact tables?

[MK_QSL]

There are many ways... Join, Keep, Concatenate and Mapping Load.

Can help you only if you load your sample apps.

[ashwanin]

If you need help as a beginners then you can refer to the document :

Data Modeling Tutorial

[rbecher]

Hi Mikael,

I suggest to join the facts only if they have the same grain (or, same dimensonality). If not, use a concatenated fact table.

- Ralf

[Peter_Cammaert]

One of the weird rules in QlikView data modelling is that it's often better to un-normalize. QlikView likes to compress, but doesn't like to follow too many associative links. In your case, I would simply join the relevant data right into your facts tables. For example: remove the Country definition table(s) by simply joining Country names into the tables that make use of country codes.

This will work in cases where you have heterogeneous data, like a table with Customers and a table with Suppliers that both need country names.

This will also work with similar facts tables, but usability will go down if for example you want to select a single Country name, and see related facts from two or more tables. You'll have to select the same country from two different list boxes.

As mentioned above, there are a number of solutions to handle this situation:

• Concatenate the facts tables: useful with similar facts that have many dimension fields in common (like orders and offers)
• Join the facts together: very efficient but tricky. As Ralf says, facts must have a lot in common and should have the same granularity (or you won't be able to join in a sensible way)
• Or - and this works in many other situations - invert your data model and put the dimensions in the center and the facts on the outside. You'll be using what is commonly called a "Link Table".

The community offers a lot of examples and explanations on all of these issues.

Good luck,

Peter

[#6]

Ralp/Peter, what is this "grain" thing, can you explain with other words?

Thx Peter, I am out of mark as helpful and I cannot know whether any of you is correct more than others.

I will look a bit more on the methods.

Peter: "For example: remove the Country definition table(s) by simply joining Country names into the tables that make use of country codes."

So you will never need dim tables, I though it was the point with dim to ensure less of the same data???

(Or is this related to Mapping only)?

The case is that I have at least two dim tables, one with country code and country Name, but also a region code and region name. I must make sure that I cannot have a region in a wrong country. I think it is called a one to many relation?

Country and region is used in most of the fact tables, so the fact tables should from the region code be able to call the region and thereby country.

But also the facts are different cases (I think), so perhaps I cannot link?  Join the dim or fact have so far been a bad idea.

[iktrayanov]

Grain or granularity is for example if you have an invoice with line items you can have your fact table at different grain at line item level or at invoice level.

[#8]

thx, ah just that lol, got it