1. TARGIT Community
  2. Community
  3. Data Warehouse Developer
Knowledge Base

Include empty members

Christian Münster ·

I maintain a sales cube with daily turnover data. We have several dimensions including Company, Customer and Date; just to name a few.

In the examples used here, I have two customers: Me (1902236) and some random person (1010002) for kicks and diversity. My account is represented in two of our companies (N100 and S300), and the random person is represented in one called (J203).

We both have invoices in either N100, S300 or J203; respectively.

Enabling Auto-Filter, My Company selector looks like this; as expected:

If I filter on a specific Company (say N100), I expect to see data strictly within that Company. Like so: 

So far so good - everyone is happy :)

 

Now; Here is my question:

If I chose to enable Include Empty Members, every customer is suddenly visible, despite having a filter on N100:

 

Even more oddly; if I remove the Company filter, I get three customer rows for every single company, showing only return figures in companies that have been invoiced (which is correct on its own). 

  • What's with all companies?
  • Is this expected behaviour?
  • If so, why?
  • Surely; the point of including empty members is to show customers within the filters, but with no data, for reporting purposes within a certain segment. 
  • Why would I need to see customers outside selected filter and why doesn't it show every single customer ever, if it's ignoring selected filters?
  • Have I missed a relation in the CUBE structure somewhere? If so, how come its working just fine without Including Empty Members?

If all filters were respected, I see value in this for our account managers, who wants a list of all their customers, for the last three months, with or without return figures.

1

Comments

1 comment
Sort by DateVotes
  • Ole Dyring

    Hi Christian,

    I believe it is working as expected.

    1. When you enable 'Include empty members' on an object, the object will show all dimension members from its included dimensions (regardless with or without data).
    2. If you set a criteria on any specific dimension, this criteria will override the 'Include empty members' setting, but only for that specific dimension (or hierarchies/attributes based on the same dimension table). In other words, all other dimensions in the object will still show all dimension members.

    Trying to explain your screenshots. Screenshot #1:

    In this screenshot, I am presuming that 'Customer_Info', 'CUSTOMER', 'Custpricegroup' and 'Lineofbusiness' are all dimensions related to (coming from the same dimension table) as the 'Customer Account' dimension.

    In your criteria bar you have filtered on both Company and Customer - essentially overriding the 'Include empty members' setting. Still, only one of the customer entries show data (1.746,00), as expected.

    If you removed the Customer Account criteria (set it to 'No selection') you would see all customers ever - with probably just a few of them showing data.

    Screenshot #2:

    In this screenshot you removed the criteria from the Company dimension, but kept the criteria on the two Customer members. The result is showing all companies ever for those two customers.

    Why is it showing two rows for customer 1902236? I dont' know, so I am guessing. In the previous screenshot, you included 'Custpricegroup' to split 1902236 into two separate rows. Maybe that attribute is still included, but hidden, in the second screenshot?

     

    So, in conclusion 'Include empty members' may not be the solution you expect it to be with regard to "...account managers, who wants a list of all their customers, for the last three months, with or without return figures...". In this scenario, you would probably want to apply 'Account Manager' as a filter - but that will not affect an include empty member Customer dimension - since they are two different dimensions. Also, if you apply a 'last three months' filter, it will not affect an include empty member Customer dimension - since they are two different dimensions.

     

    However, you might want to look into this article: https://community.targit.com/hc/en-us/articles/360017289978-ShowAll-Measure

    The method described in this article has on some occasions proved to be the necessary work-around when 'Include empty members' didn't do the job.

    Best regards,

    Ole

    0
Please sign in to leave a comment.