Is it possible to show absence of data in DeepSee?

Question

Question

Evgenii Ermolaev · Apr 29, 2021

Let's say I have a persistent class Sales.
I need to create a report that shows the amount of sales for each branch.
By simply using the class Sales for the source of the cube, I can show what I want with one little exception - the branches that did not have any sales are not shown.
Is there a way around that that does not involve creating a whole new table just for this cube or using a Data connector (they have horrible documentation and I am unable to figure out how to use them properly)?

Product version: Caché 2018.1

Discussion (3)2

Log in or sign up to continue

Peter Steiwer · Apr 30, 2021

We also have a sample that uses %InjectFact() inside of the %OnBuildCube() Method. Documentation here.

This injects the cities "Cambridge", "Chelsea", and "Somerville" into the HoleFoods cube. These city members will exist in the dimension table, but there is no associated data (there could be, but in this example there isn't). When using the City level on rows and the default NONEMPTY, we see the following:

When we turn "Show Empty" on:

We now see these members with no data:

0 0

score 0 · Answer 1 · 2021-04-29T09:57:30-04:00

Hi Evgenii,

If the list of branches will not change over time (or will change very infrequently, so that you can manually update your cube model when it does), you might consider manually specifying the members for the Branch level, as documented here.

If you do this, keep in mind that the Analyzer adds the NON EMPTY keyword to many MDX queries by default, which will prevent members with no data from being shown in the resulting pivot table. You may need to remove this keyword from MDX queries where you want the members with no data to show up.

score 0 · Answer 2 · 2021-04-29T22:55:54-04:00

Could you please elaborate more?
I have created a cube based on 'Sales' persistent class. Then I created an hierarchy and added a Branch level based on Branch property. Then I compiled the class. After that I used Studio to manually add members to that level and built the cube. I removed the 'NON EMPTY' part of the MDX query.
As a result, now it only show facts for branches that are specifically mentioned in 'Members' tag of the cube definition AND have a matching fact in the table.