Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wgpubs
Yak Posting Veteran
67 Posts |
 Posted - 2005-03-29 : 12:53:33
|
| Hi thereI have no prior experience working with Analysis Services nor do I know if it is even the right direction for a reporting project dealing with data gathered from an in-house survey system. Here are some questions I have and some specifics on the survey system I have in place and what kind of things I'm trying to report on. Like I said, I'm totally green on this stuff so any and all help is very much appreciated :)1. Looking for a good intro to OLAP in SQL 2K ... any good links, resources, etc...? Same for querying OLAP data?2. Given the below system and needs, is OLAP the right approach? And if it is, any help on how I should create the OLAP entities (fact table, dimensions, etc...) would be helpful! I'm looking to using this in conjunction with Reporting Services for actual report generation btw.**** Survey System Tables ****Basically their is a hierarchical GROUP structure that RESPONSES are organized in. Each RESPONSE's answer to a survey ITEM is stored in the RESPONSEANSWER table (either in the AnsValue column if their is a domain of allowable answers or in the AnsText column if it is a free-formed text answer). Further, ITEM's can get categorized by ITEMDIMENSIONs. The RESPONSEANSWER table already has over 1 million records after 4 surveys.- GROUP (GroupID, ParentGroupID (null), GroupName)- RESPONSE (RspID, GroupID (null))- ITEMDIMENSION (ItemDimID, ItemDimName)- ITEM (ItemID, ItemDimID, ItemText)- RESPONSEANSWER (RspID, ItemID, AnsValue(null), AnsText(null))**** Types of queries/reports I need to make on the data *****Here are the types of queries I want to make against the data:1. For a given ITEM show me the average answer, the % of people who provided a particular answer, the # of people who choose one of the top two answers, the # of people who choose one of the bottom two answers, the # of RESPONSEs who answered the question, etc ....2. For RESPONSES in a given GROUP (and its descendent GROUPs), show me all the data from 1 above for a given ITEM.3. For RESPONSES in a given GROUP (and its descendent GROUPs), show me all statistical data with regards to how they answered all ITEMS associated to a given ITEMDIMENSION.4. Show me all the data from 1 above for a given ITEM based on how a user answered the question in another ITEM.Thanks very much - wg |
|
|
|
|
|
|
|
Should I got with OLAP? Where to start?