Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel