Hi,
I have been playing with PowerPivot for a few weeks and am stumped on how to structure my tables based on my business problem. I am working from CRM Dynamics data to attribute revenue, cost, and profit relative channels and opportunities. I strung together a somewhat working example before that fell short of true table integration but accomplished most of my goal. Then I realized that a relationship that I thought was 1-to-1 was actually 1-to-many and now I don’t even know where to begin. The flow of the relationships makes this difficult. So for example, if we are looking at Opportunity XYZ that has revenue of 9,000 and it uses 2 Web Demos and 1 Conference, we would attribute 3000 for each channel. I did a somewhat ok database that worked until I realized that Opportunities can take more than one Appointment. My original solution basically had one long flattened table on Opportunity, but now that many Appointments can be assigned to one Opportunity, we need to do these calculations on a different sheet.
These are my tables that have been exported from CRM and how I envision the flow
Opportunity table: captures all opportunities and lists the campaign associated (either none or one) with the opportunity.
Opportunity ID*
Campaign ID
Revenue
Date opportunity closed
Campaign table: This is a lookup to the Opportunity table and also has the cost of a campaign (Which can be split over many opportunities) and the channel type, which will be a lookup to a table referenced by another table.
Campaign ID*
Cost
Channel Type ID
Date Campaign complete
Appointment table: Even though these are marketing channels like Campaigns, they do not have the same relationship with Opportunities, making my problem very hard. There are two types of Appointments-Web Demo and Customer Visit—and there can be many of each per Opportunity. This table lookup is the Opportunity fact table, which I know is not good. Bridge table needed? Would it help to break Web Demo and Customer Visit apart into separate tables?
Appointment ID*
Channel type ID
Cost
Opportunity ID
Date Appointment complete
Channel Type: This is where I am trying to combine the appointments and campaigns so eventually, I can create one table. It is a single column showing all the channels, 4 of which are campaigns (Mini-Conf, User Conf, Webcast, Tradeshow) and 2 of which are appointments (Web Demo, Customer Visit).
Channel type ID*
Date: Date table is created. I would love to use to link the different tables, but its not working. I don't even have it on the diagram because at this point, the other issues are more pressing
Date
YearMonth
Thanks in advance!
Rob
I have been playing with PowerPivot for a few weeks and am stumped on how to structure my tables based on my business problem. I am working from CRM Dynamics data to attribute revenue, cost, and profit relative channels and opportunities. I strung together a somewhat working example before that fell short of true table integration but accomplished most of my goal. Then I realized that a relationship that I thought was 1-to-1 was actually 1-to-many and now I don’t even know where to begin. The flow of the relationships makes this difficult. So for example, if we are looking at Opportunity XYZ that has revenue of 9,000 and it uses 2 Web Demos and 1 Conference, we would attribute 3000 for each channel. I did a somewhat ok database that worked until I realized that Opportunities can take more than one Appointment. My original solution basically had one long flattened table on Opportunity, but now that many Appointments can be assigned to one Opportunity, we need to do these calculations on a different sheet.
These are my tables that have been exported from CRM and how I envision the flow
Opportunity table: captures all opportunities and lists the campaign associated (either none or one) with the opportunity.
Opportunity ID*
Campaign ID
Revenue
Date opportunity closed
Campaign table: This is a lookup to the Opportunity table and also has the cost of a campaign (Which can be split over many opportunities) and the channel type, which will be a lookup to a table referenced by another table.
Campaign ID*
Cost
Channel Type ID
Date Campaign complete
Appointment table: Even though these are marketing channels like Campaigns, they do not have the same relationship with Opportunities, making my problem very hard. There are two types of Appointments-Web Demo and Customer Visit—and there can be many of each per Opportunity. This table lookup is the Opportunity fact table, which I know is not good. Bridge table needed? Would it help to break Web Demo and Customer Visit apart into separate tables?
Appointment ID*
Channel type ID
Cost
Opportunity ID
Date Appointment complete
Channel Type: This is where I am trying to combine the appointments and campaigns so eventually, I can create one table. It is a single column showing all the channels, 4 of which are campaigns (Mini-Conf, User Conf, Webcast, Tradeshow) and 2 of which are appointments (Web Demo, Customer Visit).
Channel type ID*
Date: Date table is created. I would love to use to link the different tables, but its not working. I don't even have it on the diagram because at this point, the other issues are more pressing
Date
YearMonth
Thanks in advance!
Rob
