Getting started: Help with data model - used dynamic column selection in Excel before PP

[psgoldberg]

I am new to PowerPivot and was making great progress in rethinking a large Excel model to use the techniques. Here are the existing data structures:

Major data table (1M records)
[TABLE="width: 174"]
<tbody>[TR]
[TD]Game[/TD]
[TD]Brand[/TD]
[TD]Type[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD]a[/TD]
[TD]x[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD]a[/TD]
[TD]y[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD]b[/TD]
[TD]x[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BBB12[/TD]
[TD]a[/TD]
[TD]x[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]BBB12[/TD]
[TD]a[/TD]
[TD]y[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]CCC13[/TD]
[TD]b[/TD]
[TD]x[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]CCC13[/TD]
[TD]a[/TD]
[TD]y[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

For each game there are a set of rates, one each for a given demographic. This is external data provided in the form:

[TABLE="width: 167"]
<tbody>[TR]
[TD]Game[/TD]
[TD]male[/TD]
[TD]female[/TD]
[TD]dog[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]BBB12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]CCC13[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

For a given demographic (slice selection), say "dog", I need to multiply each value for a game in the first table by the rate for that demographic in the game. Without PP, I would use an index or similar function to find the right column to use. So I would use 33 for game AAA12 and 15 for game CCC13 to multiply against each of the values in the first table. Pivoting uses these mulitplied values.

From other posts, it seems I need to normalize the data of table 2 to be like this which is straightforward:

[TABLE="width: 147"]
<tbody>[TR]
[TD]Game[/TD]
[TD]Demo[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD]male[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD]female[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]AAA12[/TD]
[TD]dog[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]BBB12[/TD]
[TD]male[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]BBB12[/TD]
[TD]female[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]BBB12[/TD]
[TD]dog[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]CCC13[/TD]
[TD]male[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]CCC13[/TD]
[TD]female[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]CCC13[/TD]
[TD]dog[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

Question: How do I relate the new tables to the Game table since both have multiple (non-unique) values for Game. I'd want to filter on the demographic (at which point the second table has unique values for Game) so a simple relationship will exist.

 

[scottsen]

Answer might vary a bit depending on # of rows in the demographic table, but....

What is probably throwing you off is the lack of a clean lookup table for game. Your choices:
* make one
* use the demographic table as the game lookup table, in which case.... you don't want to unpivot (since you can't make that many to many relationship)
* Don't relate them at all. Just handle it in measures as a "disconnected slicer" sorta thing, using calculate+filter or LOOKUPVALUE or something.

I can imagine something like...
=IF (HASONEVALUE(MajorDate[Game]), CALCULATE(AVERAGE(Demographic[Rate]), FILTER(Demograhic, Demographic[Game] = VALUES(MajorData[Game]))))

It feels a bit wonky.

 

[psgoldberg]

Thanks for taking a shot at this. It just didn't seem that this would be as confounding as it is. I read the chapter on disconnected slicers in the Collie book closely to find a way and nothing was quite on point - I couldn't quite get the formula above to work right.

In the end, I realized with a linked table (the demographic table is not that large) I could just modify the demographic table in Excel: add a new column which is a formula which refers to the slice selection to find the right column (index/match) and then just use that single column in Power Pivot. It worked but haven't gotten to see if performance or other issues arise.

I must say that after devoting most of the past week to learning PP, I am rather disappointed with the stability and deployment challenges, this problem aside. Powerful yes. But I had numerous crashes just using the tool in 2013. I upgraded to 64 bit just to see if that helped (maybe a little). The data I work with is not that large so this seemed unnecessary. Now I am trying to reproduce the same model in 2010 (mixed environment) and despite posts to the contrary, this seems even more unstable. Some issues are documented (can't open xlStart personal macros - an annoyance); lost the complete model when I clicked on a relationship and PP disappeared; and finally, a relationship that is perfect in 2013 tells me there are duplicate records in my lookup table (there are none that I can find) and the tables in 2010 and 2013 are identical.

I think this experiment is ending soon.

 

[scottsen]

Interesting. There was an update released just a few weeks ago for 2013 with a bunch of bug fixes... and that does feel a bit better to me. I really don't have many problems as long as I am using 64 bit... in either version (2010/2013).

I have *never* seen power pivot like about duplicates. Usually I have multiple blanks that i didn't notice

 

[psgoldberg]

While I have got everything working (I recopied the data a few times never finding dups but it finally accepted it), I fully realize that needing to run an update for the linked tables and then a refresh without any automation (2010) and also requiring PP to be installed for all users (on different Excel versions) just is not going to work in the real world. An interesting learning experience but now shelved...