You can translate the question and the replies:

Power BI Direct Query Sample

Looking for a Power Bi Direct Query SQL example to enter into the advanced options SQL Statement. More info: Denodo is linked to a view on MSSQL Aveva Historian Runtime.Events table. https://www.aveva.com/en/products/historian/ The MS SQL is the the front end on an underlying OEM High speed, time based, flat file Database system. This "Event" view is very fast retuning data and must include a timestamp field of the event. Our Denodo data Archetect added a default WHERE clause to be able pass to microsoft power query with failure. We need to create the query in MS power query direct query statement that includee the time frame like the working MSSQL query version below. This is a cut version of the Power Query direct to the MSSQL historian datebase without Denodo in the middle. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ USE Runtime SELECT * FROM Events WHERE Alarm_OriginationTime BETWEEN '2020-08-01 00:00:00' AND '2020-08-02 00:00:00' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
user
20-08-2020 14:08:26 -0400
code

1 Answer

Hi, I believe that you have already created a Denodo base view in the Denodo Platform to access the MSSQL event information and you are able to see the MSSQL event history information successfully while executing the base view in the Denodo Administration tool. Now you would like to access this base view from POWER BI using the Direct Query option with input parameters. The Denodo platform views can be accessed from any third-party clients (such as PowerBI, SSRS etc) similar to the way how you are accessing tables across the databases. For eg: `Select * from <dbname>.<viewname> where <fieldname> between ‘<value1> and <value2>;` where as the <dbname> denotes the Denodo virtual database name. As this is optional when the database name is the same as the one you have used to connect the Denodo in the ODBC DSN configuration wizard. <viewname> denotes the Denodo view name that is present on the Denodo database. Also, the values can be parameterized and execute them by passing runtime values from powerBI to Denodo. In order to do this, the query must be rewritten to include the placeholder to accept parameters from the powerBI as similar for other databases I have followed the steps mentioned in the user manual [Power BI custom connector-User Manual](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Power%20BI%20Custom%20Connector%20-%20User%20Manual) to connect to denodo from the power BI using the custom connector and also I was able to retrieve the data from the Denodo using the where clause. To know more about how to connect to Denodo as a Northbound connection and execute a query against them, you could refer to the Knowledge base article[ Denodo and BI tools](https://community.denodo.com/kb/view/document/Denodo%20and%20BI%20Tools?category=Northbound+Connections). Hope this helps!
Denodo Team
21-08-2020 03:03:02 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here