I'm working for a company running a software product based on a MS SQL database server, and through the years I have developed 20-30 quite advanced reports in PHP, taking data directly from the database. This has been very successful, and people are happy with it.

But it has some drawbacks:

  • For new changes, it can be quite development intensive
  • The user can't experiment much with the data - it is locked to a hard-coded view
  • It can be slow for big reports

I am considering gradually going to a OLAP-based approach, which can be queried from Excel or some web-based service. But I would like to do this in a way that introduces the least amount of new complexity in the IT environment - the least amount of different services, synchronization jobs etc!

I have some questions in this regard:

1) Workflow-related:

  • What is a good development route from "black box SQL server" to "OLAP ready to use"?
  • Which servers and services should be set up, and which scripts should be written?
  • Which are the hardest/most critical/most time-intensive parts?

2) ETL:

  • I suppose it is best to have separate servers for their Data Warehouse and Production SQL?
  • How are these kept in sync (push/pull)? Using which technologies/languages?
  • For me SSIS looks overly complicated, and the graphical workflow doesn't appeal much to me -- I would rather like a text based script that does the job. Is this feasible?
  • Or is it advantagous to use the graphical client with only one source and one destination?

3) Development:

  • How much of this (data integration, analysis services) can be efficiently maintained from a CLI-tool?
  • Can the setup be transferred back and forth between production and development easily?

I'm happy with any answer that covers just s

sql-server ssis data-warehouse olap business-intelligence
Edit
Report