Modeling sport event play by play

I'm early on and a little confused on how I should model a DW for a games play by play.

I've got the fact table that shows the home team, away team, player1, player2, etc for each play in a game.

I need to be able to efficiently query the cube for a team regardless of weather or not they are home or away.  As well as filter it down to home or away if I only want to see their home games.

The same problem is for player.  Because wether player is player1 or player2... I need to count the row regardless.  I'm using SSAS and currently have it semi-working using a typical star schema as follows:

FactPlayByPlay (gameid, hometeamid, awayteamid, player1id, player2id, playresultid)
DimTeam(teamid, teamname, leagueid, leaguename)
DimPlayer(playerid, playername)
DimGame(gameid, gamedate, year)
DimPlayResult(playresultid, playresultdesc)

Problem is I can't use something like excel to say, "How many did teamA have in a year".   I can show how many home they have... or how many away they have.  But not the combination together.  Normally in tsql I'd just say give me count where hometeam = teamA or awayteam = teamA.

I'm thinking this should be easy but for whatever reason I'm just not coming up with the solution in my head.  Do I need to make this more complex and make some sort of many to many relationship?  If so how?

Add the home/away information to the game dimension to capture who is home or away. Now you can filter on team and game to get metrics instead of worrying about whether the team is home or away via the team relationship.

BoxesAndLines wrote:Add the home/away information to the game dimension to capture who is home or away.  Now you can filter on team and game to get metrics instead of worrying about whether the team is home or away via the team relationship.

How would that work? If I do that there would have to be a many to many somewhere correct? As in the fact I cant reference the gameid because its not unique in the game dimension anymore. Unless I have home team and away team on the same row for each game... but then that doesnt solve anything... it just would behave the same way.

OK, looking again, it seems like your design should work fine. I can't really help you on how to code this in Excel. In SQL like you've noted, the solution is straightforward.

As far as Excel goes, you can define different queries to pull data in. Just define a query that aggregates the data the way you need it for a particular analysis. You may need to create separate sheets for each type of analysis.