Hi OLAP Gurus,
I am designing cube with the snowflake model ( attached below are the
samples of the tables ) :
Questions are :
(1) should I have PK on Fact Table ?
If Yes, then If I have e.g:10 dimension then my PK will have 10 key
column ?
(2) Should I have Index on each FK column on Fact Table ?
(3) On Product_Dim : should I have Index on ProductCategoryId and SupplierId
?

Thanks for your help,
Krist

SalesFact :
------------
DateId (FK to Time_Dim)
ProductId (FK to Product_Dim)
CustomerId (FK to Customer_Dim)
SalesAmt
Qty

Time_Dim : Product_Dim : Customer_Dim :
------------- -------------- ---------------
DateId ProductId CustomerId
Year ProductName CustomerName
Month ProductCategoryId
Day SupplierId

* ProductCategoryId and SupplierId will be snowflaked to ProductCategory
table and Supplier table.

Hi Tristant,


what do you mean by that? your dimensions could all/also be based on the
fact-table. So the number of keys (I think you mean relations) to other
tables has nothing to do with the number of dimensions

JOIN/ON clause. So that's something you have to decide. Also - indexes are
not always the best thing - if, for example, the table is updated very
frequently.

in my opinion (please someone correct me if I'm wrong): AS retrieves all
records on a table anyways - so indexes won't help you in that case. AS
builds it's own keys internally and manages that, so indexes really only
help if you use VIEWS as a base for the cube and you filter records in them.

regards, Tilli

First of all I would use surrogate keys, it looks like you are using natural
keys. I typically do put a column in the fact table with the IDENTITY
property. A lot depends on the storage mode you are using for the cube. Keep
in mind that if you are using MOLAP, your indexes will not speed up querying
the cube but will speed up the processing of that cube. However, those
indexes will slow down your ETL work. If you are using the recommended
MOLAP, I would not index any other column besides your FK links (and again,
only to speed processing)

HTH

See this link for good info on cube performance -
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

--
Ray Higdon MCSE, MCDBA, CCNA
---
"tristant" <krislioe (AT) cbn (DOT) net.id> wrote

Hi Ray, Thanks for the reply.
Now I am considering using Surrogate key ,
- Do you mean PK of the Fact Table is the Identity column ? What if the
records in fact table has the possibility of not only
inserted but also Updated ? (e.g, due to fluctuation of foreign exchange)
- And PK of the Dimension tables also all are on Surrogate key ?

Thanks,
Krist

"Ray Higdon" <sqlhigdon (AT) nospam (DOT) yahoo.com> wrote

Good questions. You typically would have a stage fact table that has all
your natural keys first then translate it to having all surrogate keys. This
way in your ETL you are updating or inserting into facts and dims based on
their natural keys.

Yes, but like above, your update lookups or logic would be done prior to the
final fact table creation

cubes and smaller cubes as well.

For design questions no one is better than Ralph Kimball, see this link
http://www.ralphkimball.com/html/articles.html

--
Ray Higdon MCSE, MCDBA, CCNA
---
"tristant" <krislioe (AT) cbn (DOT) net.id> wrote