August 9, 2015 at 8:32 pm
Hello, I am trying to learn about star schema databases and would like to know if Facts must always be a unique/primary key?
I am trying to create a fact table based on Invoice information. I have an invoice detail table which has an invoice number that appears multiple times because one invoice has multiple orders.
Can anyone recommend and good star schema examples that also have sample data to download?
August 10, 2015 at 6:09 am
OLAP Data modeling is a complicated topic. Fact tables need a primary key. I have never used a composite key, but I guess this is not an error. Unlike the dimensions, which should have surrogate keys, a fact table can get by without it, at least in the situations I've run into.
Get a copy of The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling by Ralph Kimball. You have a lot to work through, but as I recall one of the chapter has an invoice model.
August 10, 2015 at 11:13 am
Yes, but couldn't a fact table have a surrogate with a secondary composite key that is declared unique ?
August 10, 2015 at 11:18 am
Yes, but couldn't a fact table have a surrogate with a secondary composite key that is declared unique ?
Absolutely. Some would say that's overkill since there are many who don't even think primary and foreign keys are a necessary part of an OLAP design. I strongly disagree. In the case you've described, I would likely have an identity key with a unique index for the composite columns. This assumes you don't need to track row history, but I haven't ever had to do that on a fact table.
August 10, 2015 at 11:24 am
Now you have two recommendations to get a copy of that book.
August 10, 2015 at 12:06 pm
Thank you!
August 12, 2015 at 2:57 pm
To create a fact table, do I literally copy the ID's from demension tables? Also, if I do not have a primary key for a demension table to I create one?
August 12, 2015 at 3:28 pm
Do not pass go. Do not collect $200. Go directly to the bookstore and by a copy of Ralph Kimball's book. he explains how to build a star schema/Data Warehouse data model. Until you understand what a data warehouse structure looks like, you're going to be really lost.
August 12, 2015 at 7:10 pm
Go directly to the bookstore
or order it from Amazon. In any case, he's right. You will just have to take the time to dig in and learn. It's not easy but it's a good field to really understand if you like it.
August 12, 2015 at 7:44 pm
but be sure to get the second edition (or later). The first one was a bit wonky.
August 12, 2015 at 8:13 pm
Thank you I will get the book! =)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply