Urgent question about derived non-agg metric

[valinno]

Hello all,

We have need to derive non-agg metric from that generates fiscal month number of latest date ID. We cannot use applysimple Datepart function to extract month number due to fiscal months.

Ideally, what method would allow join between FACT table and Lookup table to retrieve fiscal month number of latest date. Certain metrics calculate over range of dates while other should be restricted to the last one only.

Our calculations are needed to represent Annualized metrics where we divide by month number or, alternatively, count of months, and multiply by 12.

These represent the fact and lookup tables.

FACT Table:
Date ID -PK
Employee ID -PK
Retire Count
Separate Count

Lookup Table:
Date ID -PK
Month ID
Month Num

We need to have metrics that are:

(Sum(Retire Count) / Max(Month Num)) *12
(Sum(Separate Count) / Max(Month Num)) *12

We also tried non-agg metric using settings of ending fact and ending lookup but may be overlooking some requirement. Since the column is on another table, is there a recommended way to use attribute (of numeric type) on lookup table within metric on fact table.

Please tell us your suggestions.

Thanks.

Val

 

[entaroadun]

First of all, are the Retire and Separate Count columns on your fact table rolling sums YTD? In other words, does a row for the month of November contain counts for just the month of November or for the months of January to November? If it contains only the month of November, then annualizing this value - Sum(Retire Count)/11*12, won't work.

Also, this methodology won't work for reports that cross multiple fiscal years, if the requirement exists.

Does the fact table contain a row for every month for a given employee? For example, does employee 123 have a row for January - December, or will there be any months missing? If so, then you can't do a row count to get the # of months to annualize with.

Please give some more detail on your data structure and reporting requirements.

 

[valinno]

entaroadun,

Thanks for responding quickly. I wish that our fact table was built with rolling sums so that calcs could be applied to only one As Of date.

However, due to our 2-hr nightly time window for all DW processing, our DW and DM fact tables are updated daily with only the rows corresponding to current As of Date. That date is resolved from Fiscal Year timer periods Fiscal Year and Fiscal Period (i.e. Months 1 thru 12). The fact table needs to support, normal single AsOf date queries as well as Year to Date queries.

However, not all metrics are aggregatable. Headcount is a point-in-time metric but the Retire Count, Separation Count, etc. are aggregatable and able to be rolled up. As a result, while some metrics have to be limited to last period, others have to aggregate across up to 12 As Of periods back in time to calc Year to Date for Jan. through Dec.

For Last Headcount metric we used Sum(Headcount) with extra dimensionality of AsOf date with grouping by ending fact. However, for Fiscal Month Number this is working. What suggestions do you have to use the Month Number from lookup table within the metric?

Since we are using Fiscal Months instead of Calendar Months, we cannot simply use Applysimple("Datepart(month,#0)",[AsOfDate]) in metric. Are we overlooking some MSTR functions or pass-through method to derive the needed Month Num.

Looking forward to suggestions.

Val

 

[entaroadun]

Based on my understanding of your metric requirements and reporting requirements, I think the only way to make this metric work properly is to add a field to your lookup table that represents fiscal day. Then, create a fiscal date attribute with the fiscal year, fiscal month, and fiscal day fields as the unique key.

Then, change your metric to: Sum([Retire Count]) / Count([Fiscal Date]) * 360.


Example:

Employee ID 123 has two records in the fact table: 1/1/2004 and 1/3/2004. The value for Retire Count for 1/1 is 1 and the value for 1/3 is 2. There is no record for 1/2, and we want to count that day as a "0".

I want a report that has the Retire Count from 1/1 - 1/5 for this employee ID. The desired result will be the annualized total for these five days.

Therefore, the total is (1+0+2+0+0). Remember, we assume that missing fact records for 1/2, 1/4, and 1/5 mean that we want to count a "0" for those days. The Sum([Retire Count]) with a filter on 1/1-1/5 will add up only the 1/1 and 1/3 records, giving us the desired result - 3.

The # of days we want to adjust to annualize is 5. If we did a date count on the fact table, we would only get 2. Therefore, we need a count on the lookup table. This count will only work if the lookup table is populated with every day. The Count([Fiscal Date]) metric will get this, since it will do a count directly off of the lookup table, not the fact table.

The result will be (3) / (5) * 360, or 216.