다음을 통해 공유

SSAS Time Dimension with semi-additive Measures; Dynamic set; Calculated Members

  • 아티클

Below are some restatement about some facts in the SSAS/MDX world:

1.       SSAS cannot handle two time dimensions (dimension type = time) which has semi-additive measures (such as Average of children)

a.       Work around: Instead we have to use

  •    either calculated measure to construct the AVG using the measure which has SUM as the default aggregate function.
  •    or have to separate measure group and have one time dimension for each measure group. (we found it works very well this way as we still want to use built-in semi-additive aggregate function)

b.      Link: http://blog.oraylis.de/2009/10/role-playing-time-dimensions-and-semi-additive-measures/

2.       Dynamic set cannot change with rows/columns, it can only be affected by where clause or subcube.

3.       Subcube/subselect does affect (AUTO EXISTS) dynamic set, query axes, and of course real measures

a.       Example: to get a static number such as Sum of a real measure for a set of dimension members’ and this set of dimension members are affected by date ranges chosen:

1) you need to create a dynamic set (it is by design affected by subcube/subselect) such as create set dynamic TestSet as NonEmpty(Customer.Customer.CustomerID.members, measure.FlagByDateAndCustomer)

2) put date range into subcube or subselect

  1. and then you just create a calculated member: Measures.StaticTest as Sum(TestSet, TheRealMeasure); this way even though you put date range on the rows, the calculated member Measures.StaticTest will remain the same and only works on the set of dimension members instead of the whole dimension members.

b.      If you want the calculated member to change with the date (when you put date on the rows) you can of course just use the key word existing.

c. The reason you use dynamic set is that you do not want to Sum the whole dimension members. Calculated member always looks outside of the subcube/subselect:  if you had used Measures.StaticTest as Sum(Customer.Customer.CustomerID.members, TheRealMeasure), you would have got the sum for the whole dimension members.

d. Link: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx