Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Monthly budget, daily actuals, and YTD comparisons: level-based measures?

Received Response
1
Views
3
Comments
John Hopkins
John Hopkins Rank 3 - Community Apprentice
edited Aug 13, 2024 6:15PM in Oracle Analytics Cloud and Server

I've got an issue that's in my OBIEE blind spot. Really appreciate the input.

We have a "Appointment Measures" fact, with a LTS at the "Day" time grain. There are several other dimensions that make up the granularity, but the most relevant one is "Scheduling Department". 

We also have a budget by month and Scheduling Department. I've included this table as a separate logical table source in the "Appointment Measures" fact. I also have this table tied to the "Appointment Date" dimension by the first date of the month.

So,

Appointment Measures

  • Appointments Daily Fact table
  • Sched Dept Monthly Budget table

I want a single query that returns:

Actual Appointments Month-to-Date

Budgeted Appointments for the Month

Fiscal YTD Actual Appointments (for example, through 7/17/2016) - defined in metadata using ToDate function

Prior Fiscal YTD Actual Appoints (in this example, through 7/17/2015) - defined in metadata using AGO and referencing Fiscal YTD function

Fiscal YTD Budgeted Appointments (would include the July 2016 amount)

Is there a way to model this (with, I assume, level-based measures) such that time series functions work as expected?

Thanks,

John

Answers

  • John Hopkins
    John Hopkins Rank 3 - Community Apprentice

    I think I've figured out how best to handle this.  Sometimes it takes writing a forum post to sort yourself out.

    Will post solution if it works as expected.

  • John Hopkins
    John Hopkins Rank 3 - Community Apprentice

    To solve this particular problem, I made my Monthly Budgeted Amount a "ToDate" sum of budgeted appointments at the Appointment Date > Appointment Month level.

    Given that the monthly budget amount returns for the first day of the month only, a "ToDate" function will always return the monthly amount, regardless of which day of the month I filter on.

    More testing to do at various levels of aggregation, and I need to inspect the SQL for good measure, but so far it is working.

    -John

  • John Hopkins
    John Hopkins Rank 3 - Community Apprentice

    Well, never mind... this doesn't quite work.

    If I report at the month level but filter at the day level--even if I don't include the DAY in the output--OBIEE is summing "month to date" budget counts across all days of the month.

    So this approach does not work as expected.

    Will look at level-based measures again.

    -John