We have developed a large .NET, SQL Server 2008 Enterprise R2 OLTP web based application. (We do have plans to migrate to SQL Server 2012 as soon as feasibly possible). This is a multi tenant application with customer profiles setup and user accounts with role based permissions.
We have a large number of reports built with SSRS that customers can use. However, we have clients that increasingly want to be able to build their own reports using either SSRS or Crystal Reports. We do not want to give them access to our OLTP system as we do not know what kind of terrible queries they might build hurting our performance. Plus we do make table changes from time to time which would break all of their reports.
What we would like to do is create a basic dataware house that we can give to customers to run on their own servers. This would consolidate many of our well normalized OLTP tables into more consolidated datawarehouse type tables. Possibly following a star schema. When we make database changes we would plan these table changes with the dataware house and provide scripts for customers to run to update their systems to the current version.
We would like the customer's local warehouse to be updated nightly with the new data.
While our system has many tables and lookup tables, there are only 4 main points of interest to the customer: Projects, Inspections done on the projects, What has users of the system done, Payments made on projects.
If we can build a small, well designed datawarehouse package that we can do nightly dumps of this kind of data to the customer that comes included with a few basic reports / cube for them to then build their own items off of we would be in good shape.
We are looking for a consultant to help us with the following:
1. Analyze our table structure and design a dataware house schema that makes sense for the needs listed above. I would think this should follow a start schema, but am open to other ideas.
2. Advise and setup a way to easily perform the nightly ETL processes for the created data warehouse for different customers.
3. Build a sample data cube and a few reports as examples for the customer to help them get moving faster.