Optimizing a Star Schema Data Model
Is there any best practice documentation or tools available for optimizing star schema data models in Pyramid?
5 replies
-
Hi Elaine, thanks for the question. I fear an answer might take a bit, since many people are heading into the holidays now.
-
Hi Elaine Charboneau ,
As you mentioned, the best practice it to use a star schema. Some additional key points to consider are:
1. Use single key (column) joins rather than multiple columns
2. Join columns should be of integer data type
3. Synchronize the keys on the dimension (primary ) and fact (foreign) tables so you have a perfect match (no missing items on either side)
4. Use inner joins
5. Add indexes on joined columns in both tables (dimension and fact)
6. The joined column from the dimension table should be the primary key of the table (hence, distinct)
7. Use star schema rather than snowflake schema, unless the dimensional data is very large and the smaller attributes are frequently used by themselves
Hope this helps,
Imbar -
Hello,
In the Data Modeling tool we have 2 different ways to add indexes:
1. Use the Auto Indexing ribbon button to let pyramid create the indexes automatically.
2. Set indexes manually by using the context menu on the relationship diagram.
Let me know if you need any additional information,
Shai.