Dimension key in a fact table should be repetitive?

View previous topic View next topic Go down

Dimension key in a fact table should be repetitive?

Post  manickam on Tue Oct 08, 2013 2:30 am

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

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  ngalemmo on Tue Oct 08, 2013 2:52 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

need suggestion

Post  manickam on Tue Oct 08, 2013 3:14 am

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.


manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  ngalemmo on Tue Oct 08, 2013 11:01 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  manickam on Wed Oct 09, 2013 3:29 am

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.

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  ngalemmo on Wed Oct 09, 2013 12:49 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  manickam on Thu Oct 10, 2013 5:33 am

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?

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  ngalemmo on Thu Oct 10, 2013 9:19 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  manickam on Tue Oct 15, 2013 6:54 am

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.

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Dimension key in a fact table should be repetitive?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum