Dimension key in a fact table should be repetitive?

We do have one to one relationship between a FACT table and Dimension table? Is it correct to have this kind of dimensional model.

It is generally bad practice. You should try to avoid doing so.

We do have a reqirement to design a OLAP model for consolidating the issues identified on ASSETS from various sources\system.

Let us take one sample record;

Issue | Asset | Status |Priority |St_DT |End_Dt | Brief Description | Owner | location | Source
ISS1 | ASS1,ASS2 |Open |High |1-Jan-13 |NULL | issue description | scott | CA | SRC1

We have designed the below model for this scenario:

Dimension_Asset: Conformed dimesion for assets.

Dimension_Asset_Group: Since each issue can have multiple assets we have designed a group table.

Dimension_Date: Conformed and role playing dimension for start and end date

Dimension_Issue: Issue_ID,Owner,Location,Brief Description,Source

Fact_Issue: Issue_ID,Asset_Group_ID,Status,Priority,St_DT,End_DT



Actually issues will be associated with many deatiled text and lattributes. Do we need to capture this information in the FACT_ISSUE table or dimension_issue table. Dimension_Issue (though we cannot call it as dimension since its not repitative data in the fact table), is more like details of the fact table.

Why do you need the asset group? Why can you not have one row per issue/asset?

Why don't you have owner, source, and location as dimensions?

Why does issue need to be a dimension table?

Many Thanks for the reply.

The reason for going asset group, though the issue has impact on multiple assets, they want to
treat the issue as single entry\record.

Sure we will have Owner, Source, Location as dimensions. But will it have a impact on
the ETL Layer, especially the source type will have minimum number of records.

Issue is choosen as degenerate dimension here, since issue is having many details we can not accomated
everything in a fact table hence we have choosen this table.

If you have owner, source & location as dimensions, why would you store it in an issue dimension? These should be FK references to the dimensions in the fact table itself. As far as other attributes of an issue, it is common practice to break those down in a similar manner. If the reference existing dimensions, they should be moved as FK on the fact. Any left over attributes can be clustered into one or more junk dimensions. This allows you to reduce the issue to a simple degenerate value on the fact. You no longer have a 1:1 dimensional relationship.

Why do they want to treat an issue as a single row? What difference does it make? Don't let the business do physical design... they should be providing requirements. 'Treat as a single row' is not a requirement, rather it is a interpretation based on the lack of understanding of how dimensional queries work. Find out what it is they want to do, not how they think it needs to be implemented. In general, keeping rows at their lowest level of granularity is always the best thing to do. It provides the greatest flexibility to report any metric without complex SQL.

many thanks for the reply

we will make the changes as you suggested and keep you posted with the updates

relationship between junk dimension and fact table will be one to one rite?

No. A junk dimension contains one row per unique combination of attributes. The natural key is the attribute values themselves. If you choose a collection of attributes that have low cardinality and/or high correlation, junk dimension tables tend to be pretty small.

Our requirement changes a little bit. Issue can be logged at

1. asset level
2. applicaiton level
3. process level
4. line of business level
5. combination of each of the above entities.

Can i have dimension table for each entity (asset, application, process and line of business)
and a fact table which would have keys of all these dimensions and measures as ratings, score and status.