Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
andersonc
Contributor
Contributor

Where to link budget numbers? Help with concatenation

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.

data model.png

6 Replies
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful