Modeling sport event play by play

View previous topic View next topic Go down

Modeling sport event play by play

Post  barfolemew on Mon Dec 23, 2013 11:55 am

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?

barfolemew

Posts : 2
Join date : 2011-08-03

View user profile

Back to top Go down

Re: Modeling sport event play by play

Post  BoxesAndLines on Mon Dec 23, 2013 1:59 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling sport event play by play

Post  barfolemew on Thu Jan 02, 2014 12:03 pm

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.

barfolemew

Posts : 2
Join date : 2011-08-03

View user profile

Back to top Go down

Re: Modeling sport event play by play

Post  BoxesAndLines on Thu Jan 02, 2014 5:28 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling sport event play by play

Post  ngalemmo on Fri Jan 03, 2014 12:27 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modeling sport event play by play

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum