Bear with me, I'm going to try to extract every assumption I can make from this but there's not a lot to work with yet. I'm doing it so I can get you to correct any inaccurate assumptions, and we can go from there.
wshelton 51985 (1/8/2013)
I'm from Higher Education and I'm working on a warehouse so our recruitment folks can compare application counts of certain statuses between today and a year ago today.
So, we're counting daily events by categories (status and/or application)?
The data I'm working with from our ERP has a table that holds application records - there is a record for every application and what term they are starting in, among other things. There is a related table that holds the status associated with that application along with the date that status was made active.
So, a table holding a list of applications, and another table that holds a status event for a particular date with a key relationship.
I need to display a nice, neat, easy to read graph showing the counts between the two dates but here's the kicker - for both dates I only want to display start terms that are/were being recruited for on that date.
You've combined two rules here.
First, you need to limit your data. You only want data that the application table shows a start date within your range? The data in the graph will also be limited to status events that fall within the parameters.
Next, you want a graph of some kind. This is a display component and won't be part of your warehouse directly but some other tool, probably SSRS or Excel. Counts are to be done by total? By month? By day? Need more information here.
So, some questions:
You mention trying to turn this into a fact/dimension structure, but it sounds like you already have that. Your status event table is your fact table with your applications table being a dimension of that. Are you trying to push this into SSAS cubes or something like that, or are you looking to build a new structure? Most of what you've described can be leveraged out of the existing structure, from what it sounds like.
Next, any chance we can see some schema and sample data for your existing tables? If you check out the first link in my signature it'll walk you through what I (and some others) would like to see here to get a better idea of what you're dealing with.
Finally, where are you looking to display this end product in? I doubt your users will be logging into SSMS so my guess is there's a front end somewhere that you want to show this on.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA