We're building a database of demographics data for analysis; this data is all over the place, from crime data to census data to other stuff. We want to be able to dig in via geography, metric, or time period, and while we've done a lot of normalization so we can find out a lot of stuff about City A, it's obviously difficult to pull population data from one table and assault cases from another or data by year from three or four different data tables without lots of JOINing and UNIONing due to it's relational nature.
I know OLAP is meant for some of this, but when we're going to be adding data all the time, are we going to be overwhelmed with updating data cubes all the time? And when users might often want very granular information, will all the aggregation calculations just be little-used overhead?
Is there another alternative, data structure or ORM type that would make this easier? I hate to invest the time in a particular tool like SSAS if there is a better-fitting tool out there. Thanks for any input!
[EDIT - I do intend to warehouse the data, and apply something like SSRS to it; I'm more asking if there are any factors in my situation where OLAP might not be the best choice or if there is a more suitable storage mechanism of any time.]
You need a data warehouse of some description in order to clean up and aggregate the data. This is suitable to data that comes from different sources ("this data is all over the place").
For reporting you will probably need some sort of BI system.
Both of these are not simple applications/systems.