Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
2
Guests Online:
74

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

Fact Tables vs. Dimension Tables

Please wait...

Fact Tables vs. Dimension Tables

Postby Andre on Wed Apr 25, 2012 4:45 pm

I'm browsing a cube and I noticed that there were a couple of dimensions which were derived from fact tables. The bottom line is that the data mart is not normalized. Is there a reason why I should recommend to normalize that specific fact table in order to create corresponding dimension tables? On that note, though it is not best practice to have dimensions be derived from fact tables, what underlying issues does this create? Ex. Performance, Data Integrity.

I recall in school I learned that fact tables were for measures and dimensions tables were for filters. I looked online and it states clearly that its two different tables and how it matters in terms of growth, but nothing states why it shouldn't be done and the negative effects that it entails.
Andre
Apprentice
Apprentice
 
Posts: 11
Joined: Tue Apr 10, 2012 7:17 pm
Location: Chicago, IL
Unrated

Re: Fact Tables vs. Dimension Tables

Postby SQLDenis on Wed Apr 25, 2012 5:54 pm

Can you give an example..is your time/date dimension for example in the fact table instead of in a dimension? Size will definitely become an issue over time but maybe this is a very small cube and someone decided to go this route instead
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Fact Tables vs. Dimension Tables

Postby Andre on Wed Apr 25, 2012 8:21 pm

I'm not sure what constitutes as a small cube. The fact table has about 5 million, and 2 million records in it. There is a fact table of claims and it has a bunch of attributes in it. The table is then used to derive the measure and dimension for claims. The dimensions have attributes like isX, isY and reasons for pending or status.

From my understanding of how dimensions work as filters, there won't be an issue for data integrity since the dimension and fact are based off the same table. Could this become a problem in the future if they were to try to apply the dimension to another measure which isn't based off of the same fact table and there is no correlation?

There are a couple other odd fact tables within the cube that have a dimension and measure sharing their own fact tables.

So as long as the dimension and measure from the same fact table is being used and not mixed and matched with other dimension/measures there won't be any problem? Or should this be addressed now in case fact tables might be added in the future that might desire using existing dimensions? Also by chance is there performance issues with this combination method that is being utilized?

Sorry for all the questions.
Andre
Apprentice
Apprentice
 
Posts: 11
Joined: Tue Apr 10, 2012 7:17 pm
Location: Chicago, IL
Unrated

Re: Fact Tables vs. Dimension Tables

Postby Andre on Mon Apr 30, 2012 2:03 am

To answer my own question (if anyone cares for an update), I'm fairly sure after extensive searching that I'm working with degenerative dimensions.
Andre
Apprentice
Apprentice
 
Posts: 11
Joined: Tue Apr 10, 2012 7:17 pm
Location: Chicago, IL
Unrated