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

    • Let me connect you
    • Community_chris
    • 1 yr ago
    • Reported - view

    Hi Elaine, thanks for the question. I fear an answer might take a bit, since many people are heading into the holidays now.

    • imbarmarinescubar_pyram
    • 1 yr ago
    • Reported - view

    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,

      • Irad_Gillett
      • 1 yr ago
      • Reported - view

      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? 

    • Shai_Ben_Ami
    • 1 yr ago
    • Reported - view


    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,



      • Irad_Gillett
      • 1 yr ago
      • Reported - view

      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. 

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 63Views
  • 4 Following