Where to link budget numbers? Help with concatenation

[andersonc]

Hi, I'm having an issue with bringing in budget and KPI goals into my data model. I've read Henric's article on generic keys and I'm not sure if that would be a possible solution here.

My Fact table (FTable) is linked to the Site table by Site_Key. The site table contains sales rep and manager information. My Fact table has sales transaction details by day.

I'm trying to incorporate KPI goals. The KPIs are manager based and I can work with them by day/week/month so granularity isn't really a concern here. First I tried concatenating to the Site table, but then I couldn't link to my calendar. When I tried concatenating the goals to the Fact table, I couldn't link to any manager. I have multiple calendars since each sales transaction has an order date and an effective date.

Sorry I am new at this and struggling to come up with a solution. Thank you kindly.

Below is a picture of my data model.

[Anil_Babu_Samineni]

Could be the KPI's are not having any primary key to fact and/or Manager for Rep table?

[marcus_sommer]

I think your approach of concatenating the budgets to the sales should work whereby you will need to prepare/enrich the data before so that they also contain the proper keys to your associated tables.

- Marcus

[andersonc]

Thank you Anil. That is correct, the two main tables are linked by Site Key and the KPIs have no Site Keys associated with them.

[andersonc]

Thank you Marcus. I am afraid of getting synthetic keys because I feel I'd need to link my KPI data to both tables. The only field that currently links them is Site Key and the KPI data has no Site Key as it is manager based. Maybe I should create a dummy Site Key?

[marcus_sommer]

Yes, you will need to create the proper keys and maybe also some kind of distribution of the data to the right granularity.

- Marcus

[Anil_Babu_Samineni]

You can make one dummy field using ApplyMap() to link from that table