SQL Server Analysis Service: Need to suppress empty results

Question

We have a very sparse cube in SSAS. The size of the cube is only 50M bytes in MOLP storage. When writing your own MDX query, you can use NON EMPTY/NONEMPTY to suppress/filter out empty results. But we have a 3rd party tool (Cognos Analysis Studio) for our Business Users to run ad-hoc analysis against the cube. At first the users drag in dimensions and measures using Cognos Analysis Studio, the initial results come out with empty results. Then the users try to suppress the empty results. It takes an extremely long time to come up with non-empty results.

Since the MDX is generate by Cognos, we don't have control over how the MDX is generated to run against the cube. Our users using this cube are only interested in non-empty results. We'd like to know if there is a behavior control in a SSAS cube to always return non-empty results.

Question:

How can we set (or achieve) a cube-wide behavior to always suppress empty results?

Answer 1

I think the feature you're looking for is called "zero suppression". Click on “Settings”, “Suppress”, “Zeros and Empty Cells”. This changes the default which will only suppress empty cells and not cells filled with zeros. Or, click on a column in the report. Click on the “Zero Suppression” icon at the top. Any row that is filled with zeroes will disappear.

You can google on "zero suppression cognos" to get more explanations about this.

Answer 2

I don't know if Cognos Analysis Studio allow using sets. If so, try creating non empty of dimension attribute against default cube measure in MdxScript. Then use this set instead of original dimension attribute.
For example

CREATE CURRENTCUBE.[Set1] as NonEmpty([Dim1].[Atr1].allmembers, [Measures].DefaultMember);