CODEWITHSUNDEEP
sqlssasolap
aveek
aveek

Reputation: 338

How to show dimension members for which there are no values in fact in SSAS Multi-Dimensional?

I have two tables in my Ticket Management Application, "ExpositionPeriods" and "OrganisedVisits".

ExpositionPeriods - Defines the periods for which tickets can be purchased.

OrganisedVisits - Stores the tickets purchased information.

In the example below, we have 5 periods available, and tickets have been purchased for 2 of the periods.

enter image description here

The customer wants a report which shows "Number of visitors against each available period". That means if, for any period which doesn't have a visitor, the report should show "0" for that period. Something like this.

enter image description here

So far so good. Since the production database is humongous (~500 GB), it is not advisable to report on this database directly. Things turn to be challenging when I create an OLAP cube out of this schema and try to achieve the same report functionality in the cube. It seems the cube actually performs an action similar to SQL INNER JOIN as opposed to a LEFT OUTER JOIN and hence I do not see those Periods for which there are no tickets sold.

enter image description here

Is this how SSAS actually behaves? Am I missing out any particular setting that will indicate the SSAS engine to process the cube in a different manner so as to include the missing periods as well? Please note, end customers don't have access to MDX/DAX scripts, they can only use the cube by drag-drop measure and dimensions like in Excel pivot table.

Upvotes: 3

Views: 985

Answers (2)

Denis Suvolokin
Denis Suvolokin

Reputation: 1

You can define such scope in "calculations" as 

SCOPE 
([Measures].[Visitors]);
THIS=IIF(ISEMPTY([Measures].[Visitors]),0,[Measures].[Visitors]);
END SCOPE;

So you have zeros instead of nulls and side effect. You or customer can't hide empty cells, cause now it's not empty enough.

Upvotes: 0

MrHappyHead
MrHappyHead

Reputation: 442

In your image the browser is carrying out a non empty on rows on the date dimension. If you want to show the dates with no visitors then select the option to show empty cells.

Upvotes: 1

Related Questions