Calculating universal percentage in OWC

 MDX  10 Responses »
Sep 232009
 

Recently, I was asked to write a calculation that calculates the ratio to parent on rows that doesn’t depend on cube structure and that also works in OWC. The first part I managed to dig somewhere from the past, the second proved to be much more demanding task. Having bypassed several obstacles and spent significant portion of time on this subject, I decided to pass the knowledge in form of a blog article. Here’s the calculation:

CREATE MEMBER CURRENTCUBE.
[Measures].[Universal share percent for OWC in BIDS %] AS

   iif( IsError( Extract( Axis(0), Measures ).Item(0) ),
   iif( IsError( Extract( Axis(1), Measures ).Item(0) ),
   iif( Axis(1).Item(0).Item(
 Axis(1).Item(0).Count - 1
)
.Hierarchy.CurrentMember.Level.Ordinal = 0,
1,
Extract( Axis(2), Measures ).Item(0)
/
(
Axis(1).Item(0).Item(
Axis(1).Item(0).Count - 1
).Hierarchy.CurrentMember.Parent,
Extract( Axis(2), Measures ).Item(0)
)
),
iif( Axis(0).Item(0).Item(
Axis(0).Item(0).Count - 1
)
.Hierarchy.CurrentMember.Level.Ordinal = 0,
1,
Extract( Axis(1), Measures ).Item(0)
/
(
Axis(0).Item(0).Item(
Axis(0).Item(0).Count - 1
).Hierarchy.CurrentMember.Parent,
Extract( Axis(1), Measures ).Item(0)
)
)
),
iif( IsError( Axis(1).Item(0) ),
1,
iif( Axis(1).Item(0).Item(
Axis(1).Item(0).Count - 1
)
.Hierarchy.CurrentMember.Level.Ordinal = 0,
1,
Extract( Axis(0), Measures ).Item(0) /
(
Axis(1).Item(0).Item(
Axis(1).Item(0).Count - 1
).Hierarchy.CurrentMember.Parent,
Extract( Axis(0), Measures ).Item(0)
)
)
)
),FORMAT_STRING = "Percent",
VISIBLE = 1;

What’s good about it is that it can be used in any project.

I also enhanced it a bit, to support other clients/controls as well (SSMS included).

Finally, it can be used as a template for other calculations that use Axis() function and are intended to work in Cube Browser.

ATTENTION: The improved variant of the same calculation can be found in a more recent blog article here.

Cube Browser (OWC) and its MDX

 SSAS clients and controls  1 Response »
Sep 232009
 

 

Cube Browser is often referred to by BI developers when working with SSAS cubes. Actually, that’s just another name for OWC (Office Web Components), a control integrated in BIDS that generates MDX in the background for each pivot table end-user makes. If you’re a BI developer, there are few things I believe you should know about OWC:

  • It never mixes (crossjoins) measures with other dimensions.
  • It puts measures on columns only when there is no other dimension in pivot.
  • It puts measures on rows whenever columns or rows are occupied.
  • It moves measures to pages (Axis 2) when both columns (Axis 0) and rows (Axis 1) are occupied.
  • It creates subcube, doesn’t use subselect.
  • It always creates session sets, one per hierarchy and axis.
  • It cascades them (includes previously defined set in a new set’s definition by crossjoining it with another hierarchy).
  • It only refers to session sets, besides measures, in its final query.

For a simple preview of a cube, for what I believe OWC is incorporated in BIDS in the first place, it makes no difference how the MDX behind looks like. For advanced stuff, like the use of Axis() function or similar, it matters a lot. If you find yourself in problems, consider adjusting your calculations to OWC specifics mentioned above. When done, calculations should work as normal.