Search for answers here or ask a question
0

Optimizing a Star Schema Data Model

Is there any best practice documentation or tools available for optimizing star schema data models in Pyramid? 

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Elaine, thanks for the question. I fear an answer might take a bit, since many people are heading into the holidays now.

    Like
  • 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

    Like 2
    • imbar Love the summary, agree with it all but have question.  Been using PA for some time and never saw how to add indexes can you point me in the right direction for that? 

      Like
  • 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.

     

    Like
    • Shai Ben Ami   Thank you, had not noticed only knew that Pyramid automatically applies indexing based on the relationships in the model. Did not know we could manually adjust will have to play with it. 

      Like
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 5Replies
  • 30Views
  • 5 Following