SSAS 立方体设计、半加性测量和运行总计
我的 SSAS 多维数据集中遇到了一些棘手的设计问题。 这个问题与一般会计实践有关，我有一个包含金融交易（即分类账）的事实表，并且每笔交易都标有交易日期和期间。 该周期与一天或一系列天没有直接关系。 如果用户完成了几个月的工作，那么他们可以在一天中间关闭一段时间。
我需要能够按日期和期间报告应收帐款 (AR)。 我没有使用 SSAS 企业版，因此时间智能半加性选项对我来说不可用，即使可用，它们也只允许一个时间维度使用非标准聚合，我相信在这种情况下我需要两个允许这样做。
应收帐款是一个运行总计，它应该是所选的最新分类帐项目及其之前所有项目的总和。 我知道如何在 MDX 中针对单个时间维度进行此计算，但如何才能使其适用于两个时间维度、交易日期和期间关闭？ 在这种情况下，周期结束是否被视为“时间”维度？ 它确实有一个时间方面，我确实想要从所有时期到现在的总和。
我对如何将两个时间维度关联到一个事实表并为每个维度使用不同的聚合感到困惑。 也许这里最好的解决方案是拥有两个定期快照表（而不是尝试从 FactLedger 表聚合此信息），一个按交易日期聚合，一个按期间聚合，这是我目前倾向于的解决方案，但我希望再有一个观点。
I have what is to me a bit of a tricky design issue in my SSAS cube. The question is related to general accounting practices, I have a fact table containing financial transactions (i.e. a ledger) and each of those transactions is tagged with a transaction date and a period. The period does NOT related directly to a day, or a series of days. Users may close a period in the middle of a day if that is when they have finished their months work.
I need to be able to report on Accounts Receivable (AR) by both date and period. I am not using Enterprise Edition of SSAS so the time intelligence semi-additive options are not availabe to me, and even if they were they would only allow one time dimension to use non-standard aggregation and I believe in this case I need two that allow this.
Accounts Receivable is a running total, it should be the sum of the latest ledger item selected and everything that came before it. I know how do do this calculation in MDX for a single time dimension, but how can I allow this to work with two time dimensions, transaction date, and period close? Is period close even considered a "time" dimension in this case? It does have a temporal aspect to it, and I do want the sums from all periods up to the current.
I am stumped on how to related the two time dimensions to a single fact table and use different aggregation for each. Maybe the best solution here is to have two periodic snapshot tables (instead of trying to aggregate this info from the FactLedger table), one aggregated by transaction date and one by period which is the solution I am currently leaning towards but I would love a second opinion.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您肯定可以在一个多维数据集中拥有多个时间维度，在这种情况下，我实际上只会创建一个公共时间维度，并将其扮演两个角色：交易日期和期间结束。 要对维度进行角色扮演，只需在多维数据集设计器的“维度使用”选项卡中再次将其添加到多维数据集并重命名即可。 适当地设置您的参考文献以区分两个不同的事实列。
或者也许我没有正确理解这个问题。 这听起来很简单。
You can most certainly have more than one time dimension in a cube, and in this case I would actually just create one common time dimension and have it role play as two, transaction date and period close. To role play a dimension, just add it to the cube again in the Dimension Usage tab of the cube designer and rename it. Set up your references appropriately to key off of the two different fact columns.
Or maybe I'm not understanding the issue correctly. This sounds pretty straight-forward.
您可以创建自己的包含句点的时间表，并且可以更改fact_table 的日期时间格式以匹配您的时间表。 那么 1 维就足够了。
You can create your own time-table with periods and you can alter your fact_table's datetime format to match your time-table. Then 1 dimension would be enough.