Mashing up different tables in PowerPivot/ using hierachies

[Jan-Philipp]

Hey everyone,

maybe someone can help me, I have a problem in mashing up two different tables in PowerPivot for further Pivot analysis.

1. The 1st table „Analytics Input“ contains export data from Google Analytics and can be updated automatically. It contains information about advertising campains and the columns are Source, Campaign, Date and Visitors.
2. The 2nd table „Cost Input“ contains various cost and performance metrics about the same advertising campaigns. Columns are Campaign, Campaign ID, Date, Spent, Impressions, Clicks etc.

Although both tables relate to the same campaigns, they cannot be mashed up directly, since the campaigns are not always named the same way in the two tables. Consequently, I created sort of an „Campaign Converter“ matching the campaigns from the 1st table to the ones from the 2nd table via the Campaign ID in the 2nd table. You’ll find this one as a 3rd table, called „Campaigns“.

I also created a 4th table with a single list of dates in order to match the first two tables.

Both the „Analytics Input“ and the „Cost Input“ are connected to the „Campaigns“ table as well as tot he „Date“ – table. The relationships are also shown in the screenshots attached.

Although the present data model allows for deeper Pivot analysis, I still have some problems if it comes to some special operations. For instance, it is not possible to display Source from the „Analytics Input“ in rows in the Pivot table. Excel suggests that relationships between tables might be needed. Unfortunately I cannot math the 1st table directly to the 2nd table because the 2nd table does not contain a Source column. Can this problem be solved for instance by using hierachies? (Note: One campaign can be related to several Sources and vice versa one Source can be related to several campaigns.)

Do I have to change the relationships between the tables or even the whole data model?

Find some screenshots following these links:
Imageshack - analyticsinput.png
Imageshack - costinput.png
Imageshack - campaigns.png
Imageshack - datesp.png
Imageshack - tablerelationships.png
Imageshack - tablerelationshipsindia.png
Imageshack - pivoty.png

Thanks a lot in advance! Please let me know in case the problem is not clear or if you need further information!

Best,
JP