Handling targets at a higher granularity than fact data

[bartwelvaarts]

hi, I have a fact table with orders containing order_id, product_id, date_id and amount. I also have dimensions such as a master calendar based on date_id and a product dimension based on product_id, which includes a product_group field.

I want to add targets to the model, but these targets are defined at a different granularity than the orders. Targets are set at product_group and month level (using the first day of the month), while orders exist at product and day level.

I created a separate targets table with product_group, month_start_date and target_amount and tried to rely on the associative model rather than joins. However, when there are no orders in a particular month, the target for that month does not appear either. The same issue occurs when using joins, since the existence of targets seems to depend on the presence of fact rows.

What is the recommended way to handle this difference in granularity so that targets exist independently of the fact data and still appear for months where there are zero orders.

thanks in advance

[Rushi21]

Hi ,

 Create a composite key ProductGroup&'|'&YearMonthofDate.

You can use this key to link Target table and Fact table. Date used must be master calendar date which would have all the dates in it.

Now irrespective of order present for a month or not you would see target for that month in table.

[bartwelvaarts]

yes indeed, but i also wan't it linked to my mastercalendar, this way months/years for a target are null (if not exists in order table).

[marcus_sommer · Accepted solution]

It's logically quite simple - just concatenate both facts into a single fact-table. It will avoid all struggles with missing data on any sides as well as associating them to further dimensions.

The different granularity might be addressed on various ways, for example by distributing the monthly data to a (work) daily level (joining them in beforehand with the calendar and diving the monthly value with the number of days in this month) or by adding also appropriate forecast-information which leads to a comparison of targets against forecasts. The products might be handled similarly or simpler the product-group is also added to the orders.