Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Modeling sport event play by play

3 posters

Go down

Modeling sport event play by play Empty Modeling sport event play by play

Post  barfolemew 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

Back to top Go down

Modeling sport event play by play Empty Re: Modeling sport event play by play

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

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

Back to top Go down

Modeling sport event play by play Empty Re: Modeling sport event play by play

Post  barfolemew 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

Back to top Go down

Modeling sport event play by play Empty Re: Modeling sport event play by play

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

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

Back to top Go down

Modeling sport event play by play Empty Re: Modeling sport event play by play

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

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

http://aginity.com

Back to top Go down

Modeling sport event play by play Empty Re: Modeling sport event play by play

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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