如何将业务逻辑编程到 OLAP 多维数据集中？
我们正在寻求从关系数据库构建 OLAP 多维数据集。但数据库仅包含原始数据。 “域逻辑”，例如计算、条件逻辑和自定义聚合（即，对满足这些条件的所有行求和，并且不存在其他行，这样等等）都包含在 .NET 代码中。我希望该代码生成的值包含在多维数据集中。
问题 1：您建议采用哪种架构来将域逻辑包含在多维数据集中？我宁愿不...
- 将此逻辑编码到源数据库中的视图、存储过程或 UDF 中。
- 将域逻辑作为计算成员包含到 OLAP 多维数据集中
将我的业务逻辑保留在 .NET 代码中可以轻松维护、扩展和测试。
问题2：这是一个好的架构吗？
- 创建辅助 OLTP 数据库作为数据仓库。
- 然后创建一个自定义 ETL 流程，从源系统中提取数据，进行计算，并将该信息写回 DW 数据库，
- 让多维数据集处理 DW 数据库
We are seeking to build an OLAP cube from a relational DB. But the database contains only raw data. The "domain logic" such as calcuations, conditional logic, and custom aggregations (i.e., sum up all of the rows that meet these conditions and no other row exists such that blah blah blah) is all contained in .NET code. I would like values generated by that code to be included in the cube.
Question 1: What kind of architecture do you recommend to include domain logic in a cube? I would prefer NOT to...
- Code this logic into views, stored procs, or UDFs in the source database.
- Include the domain logic into the OLAP cube as calculated members
Keeping my business logic in .NET code keeps in easy to maintain, scale, and test.
Question 2: Is this a good architecture?
- Create a secondary OLTP database as a data warehouse.
- Then create a custom ETL process to pull data from the source system, do the calculations, and write that information back into the DW database,
- Have the cube process the DW database
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我想说这要看情况。通常，如果您可以避免构建任何 MDX 计算，而只选择第二个问题中的选项，那么您最终会得到一个快速立方体。但是，通常使用 MDX，因为业务计算比 .NET 或 SQL 更容易完成。
一般来说，某些计算在 .NET 或 SQL 中效果更好，而另一些计算则比在 MDX 中更好。特别是，处理细节级数据的低粒度计算通常在进入多维数据集之前可以更快地完成，而其他计算，尤其是处理聚合的计算，在 SSAS 中更快、更容易。
话虽如此，如果您已经实现了所有这些，并且可以轻松使用您所拥有的内容，我不会建议在 SSAS 中复制业务逻辑，即使它适用于聚合，除非您遇到特定问题。如果一切正常并且您只需要一个 OLAP 层，那么在 SSAS 中使用计算结果并没有什么问题 - 这可以并且会加速多维数据集，因为在运行时不会进行任何计算。
I would say that it depends. Typically if you can avoid building any MDX calculations and just go with the option form your second question, you will end up with a fast cube. However, MDX is usually used because business calcs are done more easily than in .NET or SQL.
In general, some calcs are better off in .NET or SQL, while others are better than in MDX. In particular, low grain calcs working with detail level data are typically faster done before they get in the cube, while others, especially ones which work with aggregates, are faster and easier in SSAS.
Having said all this, if you already have it all implemented and you can easily use what you have I would not advise the replication of the business logic in SSAS even if it works on aggregates, unless you are experiencing specific issues. If it all works fine and you only need an OLAP layer, then there is nothing wrong with consuming the results from your calculations in SSAS - this could and would speed up the cubes as there will be no calcs done at runtime.
我认为你在第二季度所述的方法是一个好的开始。假设您的业务逻辑位于访问原始数据的应用程序中 - 将该逻辑公开为 ETL 可用于提取数据的服务/API - 即时执行相关计算。
I think your approach as stated in Q2 is a good start. Assuming your business logic is in the application which accesses the raw data - expose that logic as services / APIs that the ETL can use to pull out the data out - doing the relevant calculations on the fly.