Direct Query failure with big dimension

[#1]

Hello,

For the sake of clarity let's say I have a very simple star schema with one dimension and one fact table. My dimension represents the employees and the store they belong to :

StoreID < EmployeeID

The fact table represent the sales acheived by the employees with the schema ('SaleID', 'EmployeeID', 'Amount'). Of course I have a huge amount of facts it's why I want to use Direct Discovery.

Now the problem comes from the fact that one store has generally more than a thousand of employees. I have the folowwing script :

DIRECT QUERY

DIMENSION

        EmployeeID

MEASURE

        Amount

FROM FactTable;

[Employees]:

LOAD

    StoreID,

    EmployeeID

FROM [Employees.QVD] (qvd)

I want to display the top 10 employees for a specific store picked up by the user. But when a store is selected, the application crash with the following error (from QV logs) : "ORA-01795: maximum number of expressions in a list is 1000"

Actually it gives the impression that QV generates a query like :

SELECT EmployeeID, sum(Amount) FROM FactTable WHERE EmployeeID IN (ID1, ID2, ID3, ............, IDXXXXX)

And Oracle doesn't like it too much....

Does somebody know a workaround ??

Thanks

[lft · Accepted solution]

Hi Thibault,

I answered to you by mail but I'm posting my reply here so that everyone can see it :

There is no workaround on the Oracle side : the limit of 1000 occurences in a IN clause is hardcoded.

Your only solution is to change the way you query Oracle : you could create a view in your database that FULL OUTER JOINs your Fact and Employee tables on the Employee Id leaving you with the following Direct Query :

DIRECT QUERY

DIMENSION

    EmployeeID,

    StoreID

MEASURE

    Amount

FROM FactView;

The generated SQL query should then look like the following :

SELECT EmployeeID, sum(Amount) FROM FactTable WHERE StoreID IN (ID1, ID2)

Thus solving your problem

Best regards,

Loic