0

Improved Support for Data Models with Different Granularities

Hello PA-Team!

I’ve run into a challenge with Pyramid when working with data at different granularities. When comparing monthly plan data with daily actuals, the system multiplies plan values due to how it handles joins and aggregations.

Data Structure:

  • Monthly Plan Data: Plan values at a monthly level (e.g., monthly sales targets).
  • Daily Actual Data: Actual values at a daily level (e.g., daily sales).
  • Calendar Dimension: Links plan and actual data at both monthly and daily levels.

Current Behavior: When joining monthly and daily data through the calendar, Pyramid treats the monthly plan value as if it applies to each day, creating a many-to-many relationship. This leads to a multiplication effect; for instance, a plan of 100 turns into 3,000 in a 30-day month.

Impact: This behavior causes incorrect totals, making it difficult to compare daily actuals with the monthly plan.

Example: Using four tables (Fact, Plan-Fact, Calendar, Bridge), Pyramid multiplies monthly plan values by the number of days, unlike Power BI, which shows the correct total without multiplication.

Suggested Enhancements:

  • Granularity-Aware Relationships: Support relationships that maintain constant plan values across different granularities.
  • Improved Aggregation Functions: Add aggregation options that correctly handle measures across time levels.

Thanks!

2 replies

null
    • "making the sophisticated simple"
    • AviPerez
    • 3 wk ago
    • Reported - view

    This is not a legit product request that requires enhancements.

    The reason that your data doesn't behave the way you want is because you have built weak or erroneous semantic/data models. The joining concepts in Pyramid follow and materialize the approach used in SQL. (And I think we'd all agree that SQL is a robust, reliable technology and concept.)

    So, this is moved to the Q&A section where someone may be able to help you design your data, model and joins correctly so that you produce the analytical outcomes you are after.

    • Senior Director of Product
    • Ian_Macdonald
    • 14 hrs ago
    • Reported - view

    Hi 

    Why complicate things with a bridge table? It's not needed.

    Just join your calendar table to your Actuals by Date and your Plan by Month, either the 1st of the month date or the year month name:

    Then we can report by month with correct results:

    Hope that helps.

    Ian

Content aside

  • 14 hrs agoLast active
  • 2Replies
  • 26Views
  • 3 Following