数据库报告和日常交易 [英] Databases for reporting and daily transactions

问题描述

我有保存数据的一些大金额的系统。使用的数据库是SQL Server中。其中一个表具有围绕300000的行，而且有相当这样大小的表的几号。还有发生在这个表定期更新 - 我们说这是交易数据库，其中交易发生。

I have a system that holds some big amount of data. The database used is SQL Server. One of the tables have around 300000 rows, and there are quite a few number of tables of this size. There happens regular updates on this table - we say this as "transactional database" where transactions are happening.

现在，我们需要实现一个报告功能。一些建筑师乡亲提议不同的数据库是这个数据库+用于报告一些附加表的副本。他们提出这一点，因为他们不想破坏事务数据库功能。对于这一点，数据需要经常移动到报告数据库。我在这里的问题是，是不是真的需要有用于此目的第二个数据库？我们可以利用事务数据库本身报告的目的？由于数据已被移动到不同的数据库中，会出现涉及延迟，如果事务数据库本身用来报告该情况并非如此。
期待一些专家的意见。

Now, we need to implement a reporting functionality. Some of the architect folks are proposing a different database which is a copy of this database + some additional tables for reporting. They propose this because they do not want to disrupt the transactional database functionality. For this, data has to be moved to the reporting database frequently. My question here is, is it really required to have second database for this purpose? Can we use the transactional database itself for reporting purposes? Since the data has to be moved to a different database, there will be latency involved which is not the case if the transactional database itself is used for reporting. Expecting some expert advice.

推荐答案

您需要做一些研究的ETL，数据仓库和报告数据库，我想你可能建筑师的一个好办法来解决这个。既然你不给实际报告的细节我会尽量回答的一般情况。

You need to do some research into ETLs, Data Warehousing and Reporting databases, as I think your architects may be addressing this in a good way. Since you don't give details of the actual reports I'll try and answer the general case.

（声明：我在这方面的工作，我们必须面向这个产品）

(Disclaimer: I work in this field and we have products geared to this)

交易数据库读/更新/插入件之间的良好平衡优化，索引和表normalisations是面向这样的效果。

Transactional databases are optimised for a good balance between read/update/insert, and the indexes and table normalisations are geared to this effect.

报告的数据库是面向非常的读取访问之上的所有其他的事情很理想的。这意味着，一个将适用于一个事务型数据库正常的规范化规则将不适用。事实上高程度的反规范化的可能到位，以使报告查询方式更有效，更易于管理。

Reporting databases are geared to be very very optimal for read access over and above all other things. This means that the 'normal' normalisation rules that one would apply to a transactional database won't apply. In fact high degrees of de-normalisation may be in place to make the report queries way more efficient and simpler to manage.

运行复杂（在延长的数据范围特别聚合，如历史时间帧）上的查询交易数据库，可能会影响性能，使得该数据库的关键用户 - 事务发电机可能受到负面影响。

Running complex (especially aggregations over extended data ranges such as historical time frames) queries on transactional database, may impact the performance such that the key users of the database - the transaction generators could be negatively impacted.

虽然报告数据库可能无法在您的情况需要，你可能会发现它更简单，以保持两个用例分开。

Though a reporting database may not be required in your situation you may find that the it's simpler to keep the two use cases separate.

您对数据延迟关注的是一个真实的。这只能由业务用户谁将消耗的报告来回答。人们常常说，我们希望实时信息的时候，其实很多，如果不是他们所有的要求都覆盖着非实时信息。数据失效的可接受程度只能由他们来回答。

Your concern about the data latency is a real one. This can only be answered by the business users who will consume the reports. Often people say "We want real time info" when in fact lots if not all of their requirements are covered with non real time info. The acceptable degree of data staleness can only be answered by them

其实我建议你把你的研究进一步轻微，看看你的报告关注，而不是只是报告数据库多维数据集。有设计抽象报表担忧全新的水平。

In fact I'd suggest that you take your research slight further and look at multidimensional cubes for your report concerns as opposed just reporting databases. There are designed abstract your reporting concerns to whole new level.

这篇关于数据库报告和日常交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！