dimensions

[JavierM]

Hi,

I am a develolper which is involved in an application of creating fact and dimension tables to be send to PBI.

Mi question is if is correct, allowed, than a foreign key in a fact table can appoint to more than one dimensions tables.

Please have a look at my diagram to understand better my question.

The caseid FK in the fact uses 3 dimension tables with the same FK. i have no idea if this could produces results with sense.

Thanks a lot for your help in the forum

[muchinski · Accepted solution]

Tecnically, yes, you can do it. But the question is: Does it makes sense?

If CaseID is a surrogate key for the case dimension, maybe you case dimension is more or lass the union of your Dim1, Dim2 and Dim3.

BUT...

if this Dims 1, 2 and 3 are really different and you, for any reason, are just using a shared ID for them, So maybe you have more than 1 fact table.

Once you are trying to use an star schema, take a look on -> http://www.kimballgroup.com (because they defined it)

aaand, you should be able to get results from a DW in a simple way (I mean....99% of the cases should be just a sum and a filter)

[JavierM]

Hi,

Thanks a lot for your reply.

It is not the best project for a developer to work with fact tables.

The scenario is that the application has Products, in object type Products are included: Computer, Printing device, Cars, Cofee Machines,elevators... and any kind of product that can create an incident reported by the user.

There are different subtypes of products,the columns which are common for all of them are stored in a table, and the specific column names of a product are stored in a different one.

A computer and a car have common attributes : productid, departmentid, costunit, etc... but a car has not a cpu serial number and also has a ram memory size.That means that specific information is stored in a different table.

The question is if i create a general table to store all the columns of different products : DIMENSION_PRODUCTS or if a create a COMPUTER_DUIMENSION  and a CAR_DIMENSION and a PRINTER_DIMENSION?

I Could use one unique table but will there be a sense when if the fact table only is for computer FK?  I am sure the fact will be for different kind of products.

But of course there is a possibility that in the fact we put more than one product and ask a report , so for these reason i would have a fact with some products appointing to a different tables, and for this i ask if a FK can have multiple dimensions, sorry for my long question and thansk again for your help

[muchinski]

Hi,

In this case, it is clear that you have only the product dimension and one column that you will create named category, which will be used do classify them between car, elevator, etc.

Don't care about attributes that are specific for one category. It is completely normal to have some null fields in a dimension

[Greg_Deckler]

Yes, you can have multiple dimensions all point to the same foreign key in a fact table.