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!