一个或多个事实表? [英] One or multiple fact tables?

问题描述

我正在尝试建立一个数据集市.

I am trying to build a data mart.

我有很多方面，并有几项措施-事实.在业务方面，每项措施都与各个方面相关.有一种标准的方法，就是所有措施都有一个大的事实表.

I have lot of dimensions, and couple of measures - facts. Every measure is connected to all dimensions in term of business. There is the standard approach that there will be one big fact table with all measures.

但是我有一个主意:如果我为每个量度都有单独的事实表怎么办?它对数据库性能，解决方案可扩展性等有何作用?

But I have an idea: What If I have separate fact tables for each measure? What it will do with database performance, solution extensibility etc?

:: 在真正复杂的公司环境中，将存在基于olap多维数据集的巨大解决方案.因此需求首先是易于扩展和维护，然后是性能

:: there will be huge solution based on olap cubes in really complex corporate environment. So the needs are easy extensibility and maintenance at first, then performance

推荐答案

悬崖笔记:我看到一个事实表/度量的概念带来了可扩展性问题.

如果您的目标是可扩展性和维护性"，那么您可以考虑遵循行业标准的方法...例如Kimball，并首先按照业务流程对事实进行分组，其次按照符合维度的粒度进行分组.我很乐意争辩说，这将提供最大的灵活性和可扩展性.可扩展性，同时还通过标准化确保可扩展性和维护性.

if your goal is "extensibility and maintenance", then you might consider following an industry-wide standard methodology...such as Kimball and group your facts first by business process and second by granularity with conformed dimensions. This, I'd be happy to argue, will provide maximum flexibility & scalability while also ensuring extensibility and maintenance via standardization.

此外，如果您为每个度量设计一个单独的事实表，则SAN管理员会讨厌您的胆量，因为您正在通过度量数量有效地增加空间需求...至少在最初时，而当前所有度量都适合单一事实表.

Plus, your SAN-admins will hate your guts if you design a separate fact table for each measure because you are effectively increasing the space requirements by the number of measures...at least initially while all your measures currently fit in a single fact table.

针对DW的查询性能也将是一个问题...如果您的San-admin仍在与您交谈并且慷慨大方，则必须扫描报告或临时查询(或查找... )足以为您提供必要的空间，以便在需要组合多个度量的任何时候都可以正确索引事实表.

Query performance against the DW will also be an issue...reports and/or ad-hoc queries will have to scan (or seek...if your san-admins are still talking to you and are generous enough to grant you the necessary space to properly index your litter of fact tables) anytime you need to combine more than 1 measure.

我们甚至还没有讨论过olap多维数据集，但是我已经对您将遇到的处理问题感到恐惧.完整表扫描嘉豪...或分区扫描...如果您在事实表中实现分区...您现在应该在SQL 2012上实现这一目标...我正在为您寻找ColumnStore索引!!

We haven't even discussed the olap cubes yet, but I'm already dreading the processing issues you will have. Full table-scans galore ...or partition-scans...if you implement partitioning in your fact tables...which you should now with SQL 2012 on the horizon...I'm looking at you ColumnStore Index!!

这篇关于一个或多个事实表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！