MS SSAS-计算的成员以汇总每行基础数据出现的维度计数
我通常使用自定义的Java内存OLAP Cube，但是我的客户要求我在SSAS女士中建模一个立方体。我以前从未使用过它，但是我已经有几个星期了，大部分是直接的（基于我事先组织事实和昏暗的表格）。
我想根据基本事实表中的字符串字段（Buysell，作为字符串）创建两个标准计数（BuyCount和SellCount）。
在我的Java中，我只使用一个计数来创建一个测量，该计数与=“ b”的过滤器（我知道它会变成MDX是count（filter（...）。
因此，我在vs 2022中找到了计算区域设计师
。
COUNT(FILTER([Deal].[Buy Sell].MEMBERS,[Deal].[Buy Sell].CURRENTMEMBER.MemberValue ="B"))
成员
我也
COUNT(FILTER([Deal].[Buy Sell].Children,[Deal].[Buy Sell].CURRENTMEMBER.MemberValue ="B"))
正确的是，
只有2个
- 鉴于书籍
- 和国家是尺寸，
书籍
的
您应该看到该尺寸 tilleytech.com
I usually work with a custom Java in-memory OLAP cube however my client has asked me to model a cube in MS SSAS. I haven't used it before but I'm a few weeks in and most of it is straightforward (based on me organising fact and dim tables beforehand in SQL Server).
I want to create two standard count measures (BuyCount and SellCount) based on a string field in the underlying fact table (BuySell which is [B | S] as a string).
In my Java I just create a measure using a count with a filter against ="B" (which I know would get turned into MDX to be something like COUNT(FILTER(...).
So I found the calculations area in VS 2022 Designer.
I have added a calculated member as a measure and it's assigned to the Measures group I have (where standard measures are correctly aggregating).
I wrote this to test out BuyCount:
COUNT(FILTER([Deal].[Buy Sell].MEMBERS,[Deal].[Buy Sell].CURRENTMEMBER.MemberValue ="B"))
and this publishes and works however it returns only 1, which is correct in that there are only 2 members of the dimension.
I just tried this too:
COUNT(FILTER([Deal].[Buy Sell].Children,[Deal].[Buy Sell].CURRENTMEMBER.MemberValue ="B"))
So what am I missing and need to change in order for this to work and aggregate the Buy count of the underling rows returned for a given query?
E.g.
- Book, BuyCount
- Country, BuyCount
Given that Book and Country are dimensions and we should see the buy count for that dimension.
Thanks
Leigh
tilleytech.com
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
因此，我进行了尝试，但同时，我在这里问了这个问题，我在Microsoft Tech论坛上发布了问题，因为这是他们的产品之一！ :)
因此，我正确地意识到我的上述计算只是根据过滤器计算维度的成员。
答案是在“衡量标准组”中添加核心行列量度（创建新度量时的下拉列表中的选择计数）。
我还将B/S字符串分为具有身份列的表格，然后在事实表中用IDS（1和2）代替了B/S，因此它更清洁，我认为更多的性能（基于东西存储的方式，在事实表中）。
然后将您的维度与度量的混合组合为元组，以放置在计算成员的MDX表达式窗口中：
从
我的MS问题中： https://learn.microsoft.com/en-us/aswers/questions/questions/914130/mss-ssas-calculation-member-to-to-wo--to-to-aggregate-count-count-count-count-a.html？
So I carried on trying stuff but also, at the same time I asked the question here, I posted on the Microsoft Tech Forums as after all it is one of their products! :)
So I had correctly realised my above calc was merely counting the members of the dimension based on the filter.
The answer was to add a core RowCount measure in the Measures group (select count of rows in dropdown when creating new measure).
I also separated out my B/S strings into a table with IDENTITY column and then in my fact table replaced the B/S with the IDs (1 and 2) so it's cleaner and i think more performant (based on the way stuff gets stored in the fact table).
Then combine a mix of your dimension with the measure as a tuple to be placed in the MDX expression window of a CalculatedMember:
and
from my MS question: https://learn.microsoft.com/en-us/answers/questions/914130/ms-ssas-calculated-member-to-aggregate-count-of-a.html?childToView=916215#answer-916215