Can a fact table have no keys at all? or if it can, is it a good design? If a fact table do not have any dimensions, on what basis is it analyzed?
What if a fact table has primary key/s only and no foreign key/s?
+1
A:
in a good design, every table will have a Primary Key.
the use of foreign keys will depend on what/how you are trying to constrain the tables values. give more specific info on your situation if you want a more specific answer
KM
2009-06-25 19:35:50
I was looking into a fact table and one of the columns I found it suspicious. So, I wanted to see which dimension table it s extracting value from. For that reason, I created a new project in SSAS and created a new DSV (i dont know if I can see fact tables and dependencies from any other method. I can think of SSMS but I dint see and foreign key in the fact table. So Visual studio DSV is a sure method for me) and from ADD/Remove dialog box, I added the fact table, but when I clicked on related tables, there was non...
sagar
2009-06-25 19:43:40
+2
A:
Speaking inexactly, the foreign keys link you to the tables that break your fact table into categories and sub-categories.
So if the fact table was
create table stores (id, kindOfStore, sales)
Then the kindOfStore would be your dimension- if that was it, then you could argue that a separate table for kindOfStore is overkill (except for the space wasted saying kind of store = "Food" instead of "Kind_id= 8". If you have sub categories, it make sense to link to a diminsion table like
create table kindOfStore (id, Variety, Specialization, Subspecialization)
It would be space inefficient space wise to store the Variety, Specialization and Subspecialization in the fact table.
The resulting schema is star schema and data warehouses are optimized to deal with those schemas, although newer and faster data warehouse engines seem to be so fast that even a non-star schema is pretty fast.
Datawarehouses denormalize (use fewer tables) the fact tables as compared to a OLTP database, but by no means does that mean that you should strive for a single table solution.
MatthewMartin
2009-06-25 19:48:44
Ok ...i understand that even without dimension tables, a fact table itself is good to be analyzed. What if there are dimension tables and are not foreign keyed to the fact table. Thas totally bizarre I think...
sagar
2009-06-25 19:56:07
You mean say a star schema (multiple tables), but no one executed the ALTER TABLE ORDERS ADD FOREIGN KEY (foo) REFERENCES...? The logical relationship is still there. In practice when someone forgets to add the keys inevitably that leads referential integrity problems. And if someone did run into problems with the keys (say during ETL) then they should drop the keys and re-add them as soon as possible.
MatthewMartin
2009-06-25 20:25:39
+1
A:
Dimemnsional modeling is designed to allow the fact to have extra details hung off it, describing the attributes that can be "rolled up" and aggregated into meaningful summary information. It is a characteristic of datawarehousing (a primarily READ environment), but can also have it's place in the OLTP, modeling truly transactional data against primary facts (think transactions against a bank account, which could be financial transactions, notes and customer tombstone revisions - all of which have a common link back to the bank account entity).
Prime amongst the set of details usually hung off the fact are the TIME and PLACE dimensions.
If your fact doesn't exist in time or space, it could conceivably exist without an entry on those dimensions (though I can't for the life of me figure out when a fact would be like that).
If further, the other dimensions are small and contained (meaning no other fact shares them), you could get away with rolling them into the original fact table as an ENUM.
The end result would be a single fact table, with multiple small dimensions represented as ENUMS.
But it would be an extremely weird case for some really weird data...
James
2009-06-25 19:59:44
A:
The case I can recall is when you use a table which contains attributes for dimension listing purposes and the tool requires to setup/flag/identify a table or alias as a fact table.
Imagine a sales DB, opportunity table contains a long long list of attributes, right? Your customer says "I wanna get a list of all opportunity names, ID's and people assigned as oppty owners"... Then you may create an alias or a synonym or map the same table in your logical design.
Degenerate dimensions may be another case... so... although the table is a real fact table, functionality provided is pretty much the same, isn't it?