BI Answers: Filtering data in all columns

Question

Hello all,

I try to set up a filter which present data in every part of dimension. For Example:

I want to see every customer who bought something in every location of my Company:

My Company contains 3 locations: A, B and C

Customer     - Location     - Value

Customer A - Location A - $100.000

Customer A - Location B - $50.000

Customer A - Location C - $10.000

Customer B - Location A - $100.000

Customer B - Location B - $10.000

Customer C - Location C - $100.000

In this example, I want to filter only Customer A, because B did not purchase anything in location C and C did not purchase anything in locations A and B.

I am able to proceed with Count function, but this solution is completely uneffective as I am facing thousands of locations and customers, so I try to set up a filter on data base, but for now I have no idea how should it looks.

Can anyone fix this one?

3416947 · Answer

Ok, thank you Thomas for your answer.

However, I got million rows error so was unable to use it.

Finally I used union and filter it by Case function.

It is uneffective, but works.

Thomas Dodds · Answer

Agreed a count is ineffective ... and a filter on the database reduces the table to only serving up the combinations where a customer has purchases from all three locations ... really what you want?

Maybe think about the setup of a materialized view that is refreshed everyday with the customers that have a positive value in ALL locations-- model that as it's own fact table.  A fact star answers a specific set of questions ...

The view could be something similar to:

SELECT Customer,

Location,

Value

FROM tbl

WHERE Customer IN

(SELECT Customer

FROM tbl

WHERE Location IN ('A','B','C')

GROUP BY Customer

HAVING SUM(

CASE

WHEN Location = 'A'

THEN 1

ELSE 0

END) > 0

AND SUM(

CASE

WHEN Location = 'B'

THEN 1

ELSE 0

END) > 0

AND SUM(

CASE

WHEN Location = 'C'

THEN 1

ELSE 0

END) > 0

)