Hi Emily,
I have already struggled with this a lot. And today I took your question as kind of a last attempt to wrestle down the beast. But I am quite sure that this - replacing "no values" with some sort of thing - will not work for your example, sorry to say...

The root cause is the underlying data model. Let's say in your screenshot we see the model's complete fact table. This means the fact table consists of 31 datasets which are built as a combination of two dimensions and a corresponding value. For example, the 3-tuple ("Bikes", "Acme", $56.14M) is one of these datasets. But the 3-tuple ("Accessories", "Acme", NULL) or ("Accessories", "Acme", $0.00) or ("Accessories", "Acme", ) is not part of the fact table. It simply does not exist, like ten other combinations. And therefore all your efforts defining a working formula with IFNULL or similar did not succeed because they cannot work on nothing.

And this is the reason why "Show Empties" does not succeed, either: We are not talking about "empty" but about "non-existing"! The help file for "Show/Hide Empties" (https://help.pyramidanalytics.com/Content/Root/MainClient/apps/Discover/PRO/Query%20Functions/Show%20Empties.htm) brings a simple example:

Here, the fact table actually consists of the 3-tuple (2008, "USA", NULL), i.e. this specific combination of YEAR and COUNTRY really exists! It has no Sales value - it is NULL - but this NULL value could be made visible by a formula. But the 3-tuple (2009, "Australia", NULL) does not exist. Here you won't succeed in replacing this NULL by some sort of thing.

So, one way to solve this dilemma would be to change the data model: By replacing the fact table's inner joins with outer joins you would fill up the fact table with such NULL-tuples. In the end (aka discovery) you could replace them by a value you have formulated... ;-)

Another way could be that Pyramid extends its "Show Empties" functionality with a option to "Show Non-Existing". This is something which - in my opinion - should be managed on the visualization level, i.e. there should be an option to fill empty table cells like in your example with a certain value (0.0, "n.a.", "--" etc.). But this would raise some interesting questions as well, e.g. what should happen in a tabular grid?

Here are no empty cells - but still non-existing combinations! Should the new option fill this visualization with the "missing" 11 combinations like in the matrix grid?!?

But as there have been so many questions and posts and explanations for this: Do you think a feature request/product idea for this would make sense? Or do you know if Pyramid is already planning something like this?Moshe YossefIan Macdonald

THANKS for reading, regards,
Michael

Hi Emily KundMichael Daun

A very good explanation by Michael Daun of this problem.

If you had a data table of the structure you describe in your data source and queried it with Pyramid, you would be able to test for nulls for say Acme and Accessories, because the row "Accessories" exists in the table, but there is no value for Acme, hence it is null.

However, your table is actually a cross tabulation of Product Category and Manufacturers, generated from a table that contains columns of Product Categories, Manufacturers and Sales. Thus the "nulls" are a construct of the output format, not the underlying data. AsMichael Daun points out, those combinations of Product Category, Manufacturer and Sales do not exist in the underlying data and hence cannot be tested for null as they don't exist.

Pyramid's Semantic Model provides a multidimensional view of the data, but it is that,  a view, not the data itself. Multidimensional Databases, such as SQLServer Analysis Services and SAP BW handle this by pre-processing the data and marking where there are no values of the intersection of dimension values like Accessories and Acme. they provide a special function "IsEmpty()" to allow for testing of this type of situation. Pyramid, while providing a dimensional view of the data is querying the (usually) relational database directly and this option does not exist.

In order to support it we would need to post process the entire output grid somehow. But even there, the problem doesn't go away because the data is held in relational form right up to when it gets displayed on a grid as a crosstab. 

We'll keep working on it and maybe one day have an answer.

Hope that helps.

Ian 

Hi Emily Kund, there is a way to achieve this, although it has it's limits.

Adding to Michael Daun 's post here.

The problem like he wrote so well is that this isn't a null value, it is a no intersection.

How to resolve:

1. create a right joins model:

2. create a formula: that returns the data for all manufacturers and all product categories - and multiply by 0 - this will attend to the missing intersections (it basically ignores the manufacturer and category in the cell).

 

3.  add the sales:

this will return a 0 for all the cells that don't have an intersection:

However this has it's downsides:

a. as you can see, now all dimensions may have a "null element" - which needs to be excluded.

b. If you use a different set of columns, it will require it's own measure.

c. if you add filters, they should all be set to "all" in the measure - in my example, if there's a manufacturer who didn't sell at all in Germany, it would be omitted, and I would need to add "all countries" to my measure.

If your use case is a publication or a dashboard that is only used for showing data (so nobody will start switching or adding columns and hierarchies).

 

I hope this helps :-)

Moshe