Fact as Dimensions and One to Many relation

[ame54]

I have warehouse design question im not sure how i should model it.

NOTE: i am working in Microsoft SSAS environment when it comes to questions of type of relations

i have data mart for assets in an organization.

The fact table ASSETS also acts as a dimension , it holds attribute like :

asset ID
asset year of manufacture...etc
and measures like:

asset purchase price
asset cost..etc
I need assets as fact since there are also other dimensions like country related to assets and i want to group measures based on these dimensions.

This Dimension has related table of Spare Parts (this is a 1-N relation) this table also is fact table:

Some of attributes :

sparepart ID
sparepart serial number..etc
Measures:

count of spare parts
spare part purchase price..etc


What would be the proper way to handle such a  relation ?

currently im creating foreign key from SPARE parts table, to primary table holding assets based on the asset unique ID.

And on relation between dimensions and facts:

Asset Dimensions ----(regular relation)---->> Spare parts Measure Group
Spare parts Dimension ---(MANY To MANY)---->> Asset Measure Group


Some of questions i want to answer are:

What is total purchase price for assets in a country ?
What is number of spare parts for an asset ?


I would prefer if i could remove the MANY to MANY relation since write back might become a requirement and as far as i know it does not work for that type of relation.



Thank you for any help, and sorry for the long winded question.

[nick_white]

Hi - happy to be proved wrong by someone as there is always an exception to any rule, but as far as I'm aware you can't have a table that is both a fact and dimension. A dimension should consist of a surrogate key and a set of attributes; a fact table should consist of surrogate keys and measures (with optionally, degenerate dimensions) - these two types of table are not interchangeable.

If I have understood what you are trying to do then you need an Asset Dimension which will be linked to your Asset fact table (containing measures related to the Asset) and also linked to your Spare Parts fact table (containing measures related to Spare parts)

[ame54]

hmm okz i think i get you
with reference to single table being fact and measure this is a link of SSAS Fact table relation type:

http://technet.microsoft.com/en-us/library/ms167409.aspx


so ur saying i should add another table that will act as fact table for assets and move attributes that are measures to it, and not only have a logical separation but also a physical separation.
and also do the same for the spare parts by having two tables one with attributes and one with measures.

So then if i want to say i want number of spare parts for this asset there's a foreign key relation from spare parts fact table to asset dimension.

I get that and i can see how that works.

But one more thing if i want user to be able to put [Asset ID] from the asset dimension, then place under it [Sparepart Serial Number], with this design, will the OLAP still be able to group them properly ?
Say i can get something like this:
AssetID   SparePartID
A            1
A            2
B            3
C            4
C            5

Or if i want to see for each country number of spare parts in it ? should i have a foreign key from spare parts fact table directly to Country Dimension , or should i use a reference relation such as:
Country --> Assets --> SpareParts

Thnx for the help

[nick_white]

I've had a look at the link and what it is talking about is degenerate dimensions. These have a very specific use: where all the attributes from a source object have been assigned to Dimension tables and you are left with a single attribute that you still need to report on; these are often items like invoice numbers, ticket numbers, etc. Instead of creating a Dimension table that holds just a surrogate key and this single attribute, and putting that SK on the fact table, the attribute is held directly on the fact table as a degenerate dimension. Use of DDs should be a last resort in your design, once you are certain that each attribute cannot be more sensibly held in a proper Dim table; also, if you end up with more than a couple of DDs on a fact table then I would always review the model design - as you are either modelling something unusual (which may be the case and your design is perfectly valid) or more likely your design needs to change.

Regarding the rest of your questions...
To keep the discussion simple I will ignore complexities such as snowflaking, bridge tables, etc. Given that, the only relationships between the tables in dimensional models are between the surrogate keys on the dimension tables and the foreign keys on the Fact tables (i.e. all dimensions are related through a fact table and always using surrogate keys that you have assigned and not the keys that exist in your source/OLTP system).
As long as you do this then you can filter or group the measures in your Fact table by any of the attributes in your Dim tables and it will all work

BTW: Kimball recommends not holding a separate Geography Dim but instead include the location attributes in the Dim of the object they refer to. For example, if you have a Store Dimension then add its address attributes to that Dimension. You mention having a Country Dim so you may want to review this (though it could be that for your particular circumstances having a standalone Country Dim does make sense)

[Sponsored content]