SQL Server - a schema for a production database and a schema for a reporting database. Should they be the same?

Question

Recently, we used a new production database. The schema for this database is optimized for OLTP. We are also preparing to implement a report server that will be used for reporting purposes. I'm not sure that we should just blindly use the same schema for our reporting database as for our production database, and replicate the data.

For those of you who have dealt with separate production and reporting databases, have you chosen to use the same database scheme for your reporting database or a scheme that is more effective for reporting? for example, perhaps something more denormalized?

Thanks for thinking about this.

Answer 1

I think the database reporting scheme should be optimized for reporting - so you need the ETL process to load your data. In my experience, I quickly realized that the production scheme did not meet my reporting needs.

If you are starting your reporting project, I suggest you create your own reporting database for your reporting needs.

Answer 2

There really are two sides to the story:

• if you stick to the schema the same way, then updating the report database from production is a simple copy (or MERGE in SQL Server 2008). Reports, on the other hand, can be a little more difficult to write and may not run optimally.

• , - , . :

, : ? : , .

? (, SQL Server), , ?

, , , , ( , SSIS), .

Answer 3

( , , , 1,3 . , .

, . , , , , , , , , .

, , , , , . datawarehouse . , , . , .

. , . ( , ), ( , , ).

Answer 4

.

, - .

Data Warehouse/Analysis Services, . , , . , .

, . , , .

Answer 5

, , . , , .

Answer 6

, , , , , , :

- OLTP

,

(, - , ) . , .. , OLTP (, ) - , ( , ). "" . , SQL Server - (, ), OLTP, , , , , . - .

Create a complete data warehouse, including slowly changing sizes, where all data will be taken from the source system.