Back to the version with Inheritance.
This page shows a Data Mart to support a generalised Ad-Hoc Enquiry facility.
The Background :-
Many Cities around the world have Tourist Attractions.
A survey is being made of the popularity of these Attractions.
For example, San Francisco has the Golden Gate Bridge,
New York has the Empire State Building, Ground Zero and the Guggenheim Museum.
London has Buckingham Palace, Windsor Castle and the London Eye
Paris has the Eiffel Tower.
- Individuals, called Survey Takers, ask Visitors if they find the Attractions interesting.
- They have a daily quota, such as 300, of the number of Visitors they should include in their Survey.
- The Visitors are asked if they find the Attraction very interesting (Grade A) or very disppointing (Grade D), with Grades B and C in between.
- The results of the Survey are transmitted from the PDAs of to a SQL Server 2005 Database.
This populates the Data Mart with the following fields :-
- Date and time of a Visit
- Survey Taker Name
- Name of Tourist Attraction
- Country of origin of the Visitor
- Grade given by the Visitor(A,B,C or D)
The requirement is to provide a facility to ask general questions that include any combination of field for any period of time.
Examples are :-
1) What percentage of Quota of Checks did a particular Inspector check last week ?
2) How many Grade D's were given for a specific Attraction ?
3) What was the percentage of A,B,C and D Grades last month ?
4) What was the total number and percentage of Attractions Inspected in June ?
5) What are the Top 5 Countries in any given period ?
I see this as a Table in the Data Mart with the ability to use Dynamic SQL to interpret the run-tine fields that the User specifies.
Is this feasible ?
So far, I have developed Template reports in in MS Report Services which provide flexibility in predefined limits but the Users really want complete flexibility.