MicroStrategy 的最佳数据库结构
我职业生涯的大部分时间都在以星型模式的形式开发数据仓库\集市，因为它们通常与 Microsoft 的分析服务结合使用。然而，我们开始利用 MicroStrategy 9.0.1，我被告知星型模式对于该平台来说并不是最佳的。 MicroStrategy 对此主题没有正式立场，因此我想我应该询问这个社区。我是否应该继续使用非规范化结构，或者我应该考虑针对该平台采用更规范化的方法？
我的目的不是发起一场 Kimball 与 Inmon 等战争，任何现实世界的经验将不胜感激
I have spent most of my career developing data warehouses\marts as Star Schemas because they were typically used in conjunction with Microsoft's Analysis Services. However, we are starting to leverage MicroStrategy 9.0.1, and I have been told that Star Schemas are less than optimal for this platform. MicroStrategy does not have an official stance on this topic so I thought I would ask this community. Should I still continue to use the denormalized structures, or should I consider a more normalized approach in reagards to this platform?
My intent is not to start a Kimball vs. Inmon vs etc war, any real world experience would be appreciated
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
实际上，在 MicroStrategy 中使用星型模式并不是什么大问题。只需要一点时间来适应，它就会使用该格式生成良好的查询。
从一位经验丰富的 MSTR 顾问那里，我听说 MSTR 真正喜欢的数据形状是一种修改过的雪花。其中数据维度被建模为雪花，但每一层都包含其上方层次结构中的表的数据。
我想你可以在快速启动项目中看到这种模式。位于这里：
http://www.microstrategy.com/BI-application-jumpstart/
最终，我认为你应该继续使用最适合你的技术。逻辑数据模型的设置应该不会太麻烦，并且 MSTR 有大量的性能优化技术（缓存、内存中的多维数据集……），您可以在后面应用这些技术来使事情变得更加有趣。
Its actually not a big deal using star schemas with MicroStrategy. It just takes a little getting used to, and it generates fine queries with that format.
From a very seasoned MSTR consultant, I've heard the data shape MSTR really likes is a sort of modified snowflake. Where the data dimensions are modeled as a snow flake, but each tier contains the data of the tables in the hierarchy above it.
I think you can see the pattern in the jumpstart project. Located here:
http://www.microstrategy.com/BI-application-jumpstart/
Ultimately, I think you should continue with the techniques that work best for you. The setup of the logical data model shouldn't be too much trouble, and MSTR has a ton of performance optimization techniques (caching, in-memory cubes, ...) that you can apply afterwords to juice things up.
我在土耳其的一家银行工作，我们与 MicroStrategy 合作已经超过 3 年了。我们确实有 20 多个不同的项目在不同的数据库和不同的模式类型上运行。当正确设计（和实现）时，MSTR 非常有能力处理星型模式，并且确实生成相当不错的 sql 语句。我应该说，在设计架构时习惯 MSTR 的父子和查找/事实表处理可能会很麻烦。但一旦克服了它，就很方便了。
I work for a bank in Turkey, and we have been working with MicroStrategy for over 3 years. We do have over 20 different projects running on different databases, and different schema types. When designed(and implemented) correctly, MSTR is quite capable of handling star schemas, and do generate moderately nice sql statements. Getting used to MSTR's parent-child and lookup/fact table handling when designing the arcitecture can be a hassle, i should say though. But once you get over it, it's quite convenient.
在过去的八年里，我很高兴（或以其他方式）与 MicroStrategy 合作。我认为可以公平地说，该产品被设计为与第三范式的模式一起使用。也就是说，使用以这种方式设计的模式在工具中对对象进行建模将是最简单的。
正如 Ugur 所说，MSTR 非常能够使用星型模式，并且根据您的数据，使用星型模式（出于性能目的）可能会更好，即使建模在微策略项目。
I've had the pleasure (or otherwise) of working with MicroStrategy for the past eight years. I think it would be fair to say that the product was designed to be used with a schema in the third normal form. That is to say, it will be easiest to model your objects in the tool with the schema designed in this manner.
As Ugur says, MSTR is quite capable of working with a star schema, and depending on your data, it may be better to use a star schema (for performance purposes) even if the modelling is a little (or a lot) harder in the MicroStrategy project.
当我们于 2007 年开始走上 MicroStrategy 道路时，与我们合作的 MicroStrategy 顾问告诉我们，星型模式还可以，但他们的技术在雪花模式下效果最好。不同之处在于维度已标准化，即您拥有日、周、月、季度和年维度表，而不是时间维度表。因为我们是交通物流行业，所以我们的数据仓库关系比较复杂，但是数据量不是很大；高“表与太字节比”。在正统形式中，星形和雪花模式都仅通过一致的维度来连接事实表，并且一度我们考虑在事实表之间进行连接的“混合”模式。最终，我们选择了最适合公司的标准化数据仓库结构。
我们花了几个月的时间在仓库表之上开发和完善 MicroStrategy 架构对象的标准，最终开发出非常强大的模式。这些模式并未得到很好的认可，据我所知，这些模式并未在其他 MicroStrategy 客户中广泛使用。他们生成了非常复杂的 SQL，并且我们获得了出色的响应时间，即使对于临时报告也是如此，因为我们使用 Netezza 作为数据仓库。缺点是遵循该模式所需的应用程序对象数量比其他模式高得多，并且开发新指标的专业知识水平很高。我们成功培训所有 BI 用户使用现有指标（由专业 BI 团队开发）。该 BI/DW 解决方案目前正在积极使用。
因此，我认为 MicroStrategy 并不是为标准化数据仓库模式而构建的，尽管他们的技术非常可靠，并且足以在这样的数据库上运行。他们首选的模式是雪花，带有标准化维度表和标准事实表。
When we started down the MicroStrategy path in 2007, the MicroStrategy consultants who we worked with told us that a star schema was ok, but their technology worked best with a snowflake schema. The difference is that the dimensions are normalized, i.e. instead of a Time dimension table, you have Day, Week, Month, Quarter and Year dimension tables. Because we operated in the transportation and logistics industries, our data warehouse had many complex relationships, but not a huge data volume; a high "table-to-terabyte ratio". In orthodox form, both star and snowflake patterns join fact tables only through conformed dimensions, and for a time we considered a "hybrid" schema with joins between fact tables. In the end, we chose a normalized data warehouse structure, as the best fit for the company.
We spent many months developing, and refining our standards for MicroStrategy schema objects on top of our warehouse tables, and ended up developing very robust patterns. These patterns were not well recognized, and to my knowledge not widely used with other MicroStrategy customers. They generated very complex sql, and we received excellent response time, even for ad-hoc reports, as we used Netezza as our data warehouse. The down side was the number of application objects necessary to follow the pattern was much higher than for other patterns, and the level of expertise to develop new metrics was high. We successfully trained all of our BI users to use existing metrics (developed by the specialist BI team). This BI/DW solution is in active use today.
Therefore, I submit that MicroStrategy was not built for a normalized data warehouse schema, although their technology is very solid, and robust enough to operate on such a database. Their preferred pattern is snowflake, with normalized dimension tables and standard fact tables.