combining two slightly different datasets

[bertusavius]

I have two tables

TableA
[TABLE="class: grid, width: 272"]
<TBODY>[TR]
[TD]name</SPAN>
[/TD]
[TD]date</SPAN>
[/TD]
[TD]property2</SPAN>
[/TD]
[TD]key</SPAN>
[/TD]
[/TR]
[TR]
[TD]eventa</SPAN>
[/TD]
[TD="align: right"]1-1-2012</SPAN>
[/TD]
[TD]xxx</SPAN>
[/TD]
[TD]1-1-2012xxx</SPAN>
[/TD]
[/TR]
[TR]
[TD]eventb</SPAN>
[/TD]
[TD="align: right"]2-5-2012</SPAN>
[/TD]
[TD]yyy</SPAN>
[/TD]
[TD]2-5-2012yyy</SPAN>
[/TD]
[/TR]
[TR]
[TD]eventc</SPAN>
[/TD]
[TD="align: right"]2-5-2012</SPAN>
[/TD]
[TD]yyy</SPAN>
[/TD]
[TD]2-5-2012yyy</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


of which I generate this pivot table:

[TABLE="width: 96"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]1-1-2012
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2-5-2012
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
</TBODY>[/TABLE]



also tableB
[TABLE="width: 266"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]name
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]date
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]property2
[/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]value
[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]key(unique)
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]action1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl65, bgcolor: transparent"]1-1-2012xxx
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]action2
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2-5-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]55
[/TD]
[TD="class: xl65, bgcolor: transparent"]2-5-2012yyy
[/TD]
[/TR]
</TBODY>[/TABLE]


Now I'd like to combine the two by creating a relationship between the two key columns above.

What I'd like to see is this:

[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]1-1-2012
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]99
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2-5-2012
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]55
[/TD]
[/TR]
</TBODY>[/TABLE]


but what I get is this:

[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]1-1-2012
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]99
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]99
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2-5-2012
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]xxx
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]yyy
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]99
[/TD]
[/TR]
</TBODY>[/TABLE]


How could I relate those two values correctly?

 

[Laurent C]

As I see it, you used :
tableB[date]
tableA[property2]
countrows measure on tableA
A measure based on tableB[value]

Using property2 from tableB (your dimension) should solve the issue.

Explanation: for given measures, PowerPivot will show all combinations of attributes for which facts exist for at leat one measure.

In your case, this translates to:
Show all combinations of tableB[date] and tableA[property] for which there exists some tableB[value].

Of course for every value in tableB[date] there exists some tableB[value] independantly of what the value of tableA[property2] is.

When you have a dimension table, it is best to hide the redundant key columns from the fact table, to avoid this type of confusions.

 

[bertusavius]

I understand your point.

However the problem is that in neither of the tables, the property2 value is unique; thats exactly the reason I created a calculated column of keys.
I should have made that clearer in the example.


As you might remember: the property2 represents the bucket field of my previous problem.
Table B represents this shift information in with wich a aggregate totals per time-bucket.

Table A represents a database of revenue of which time-bucket information is a property. This is a separate model.

I'd like to combine the two models to align the cost of personel per time-bucket with the revenue in the same time-bucket.

 

[Laurent C]

Say you have the following structure, with the appropriate - and hopefully obvious - relationships :
Dates {date}
Buckets {bucket}
Costs {date, bucket, cost}
Revenue {date, bucket, revenue}

Now you could simply have a pivot table with:
Dates[date] , Buckets[bucket] on rows
SUM(Costs[revenue]), SUM(Revenue[revenue]) in values.

In other words, you should add a Date table to your model, if you do not have one already. Link TableA and TableB to the Tijd and Date dimensions. Link all other tables to your Date dimension as required. Hide the keys in the data tables.

 

[bertusavius]

Adding a date table was indeed the solution.

With your help I think I'll be able to make the model complete.

Many thanks indeed.

 

[JavierGuillen]

On the same approach proposed by Laurent, here is a link to a blog entry I wrote some time ago discussing this exact same issue. I may help as well in cases in which neither table has the complete list of lookup values:

Merging data in PowerPivot « Javier Guillén