Search code examples
mysqlselectolapcubeolap-cube

Where - In Clause in OLAP Cube

I'm using OLAP cube and I want to select data from our MySQL database. What I want is to use "Where-In" clause, as in MySQL:

SELECT Id, CompanyName, City, Country
FROM Supplier
WHERE Country IN ('USA', 'UK', 'Japan')

As far as I understand, in OLAP cube we select data by "slicing" data. So I think we could NOT achieve st similar to "Where In" clause as above. Can sb has answer or confirmation for it?

Solution

  • In MDX a WHERE clause is setting one or more hierarchy members (aka. slicing); filtering is rather achieved using subqueries (i.e., SELECT ... (FROM SELECT ... )).

    In your example, you could select all the countries and then use a subquery to filter only USA/UK/Japan. Or you could select them directly:

    SELECT 
   { Id, CompanyName, City, Country } ON 0,
   { [Geo].[USA], [Geo].[UK], [Geo].[Japan] } ON 1
FROM ...

    Hope that helps.