Power BI Tips: Exasol in DirectQuery mode

We recently explored capabilities of Power BI with Exasol in DriectQuery mode. Interestingly it led us to some positive observations. It performs well and more flexible than Google Big Query. This blog will help you to resolve a few issues which may cause when you try to create BI and analytical report in Power BI with Exasol database in a DirectQuery mode.

Prerequisites:

Please check out our blogs:

• Connect to Exasol in Power BI
• Power BI Tips: Import vs Direct Query
• The best practices will be applicable to Exasol as well.

Data type conversion – Temporary Fix:

Currently, there is an issue in converting data type. The Powerbi-Exasol Connector team provides a temporary fix to resolve this issue(Credits: Thomas Bestfleisch and Florian Wenzel). Run the below pre-processor script in the database,

CREATE SCHEMA IF NOT EXISTS UTIL;
--/
CREATE OR REPLACE LUA SCRIPT UTIL."FN_CONVERT_REVERSE_ORDER_REPEATED" () RETURNS ROWCOUNT AS
function processconv(sqltext)
local lasthit = 1
while (true) do
local tokens = sqlparsing.tokenize(sqltext)
local convStart = sqlparsing.find(tokens,lasthit,true,false,sqlparsing.iswhitespaceorcomment,'fn','CONVERT','(')
if (convStart==nil) then
break;
end
lasthit=convStart[3]
local convEnd = sqlparsing.find(tokens,lasthit,true,false,sqlparsing.iswhitespaceorcomment,')')
if (convEnd==nil) then
error("convert statement not ended properly")
break;
end
local comma = sqlparsing.find(tokens,lasthit+1,true,true,sqlparsing.iswhitespaceorcomment,',' )
if (comma==nil) then
error("invalid convert function")
break;
end
local convParam1=table.concat(tokens, '', lasthit+1, comma[1]-1)
local convParam2=table.concat(tokens, '', comma[1]+1, convEnd[1]-1)
local convStmt=convParam2..','..convParam1
sqltext=table.concat(tokens, '',1,lasthit)..convStmt..table.concat(tokens,'', convEnd[1])
end
return sqltext
end
/
--/
CREATE OR REPLACE LUA SCRIPT UTIL."PREPROCESSFNCONVREPEATED" () RETURNS ROWCOUNT AS
import('util.fn_convert_reverse_order_repeated', 'fn_convert_reverse_order_repeated')
sqlparsing.setsqltext(fn_convert_reverse_order_repeated.processconv(sqlparsing.getsqltext()))
/

GRANT EXECUTE ON UTIL.PREPROCESSFNCONVREPEATED TO PUBLIC;
GRANT EXECUTE ON UTIL.FN_CONVERT_REVERSE_ORDER_REPEATED TO PUBLIC;
ALTER SYSTEM SET SQL_PREPROCESSOR_SCRIPT=UTIL.PREPROCESSFNCONVREPEATED;


You should require following privileges to run this script,

• CREATE SCHEMA
• CREATE SCRIPT
• GRANT ANY OBJECT PRIVILEGE
• ALTER SYSTEM

Note: This issue observed on or before Dec 2018. The Powerbi-Exasol Connector team will fix this issue in next release or in near future. So, it might not be an issue in the future. For more details, Invalid data type in cast

Configure On-Premise Gateway:

On-premise gateway is required if your database is On-premise. The report won’t be able to access the data source without the gateway.

Install the Power BI On-Premise Gateway – follow the steps mentioned here.

Configure your gateway to Exasol database. Here is the example,

Make sure that your data source configured with the gateway. Sometimes it required two data sources with and without encryption.

Please feel free to reach out Cittabase for more details.