Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good day,
Background
I'm trying to access our database through a Web Datasource. To query the database, I must the URL in the form of
http://<server>:[<port>]/query/<database>/<language>/<query-text>[/<limit>].
To get the <query text>, I at first used the Uri.EscapeDataString() function to encode my "SELECT name FROM table" query. This of course worked great in PBI Desktop, but failed in pbi.com. This is a known issue discussed here, with a workaround. It uses the Query header, but this inserts a '?' between the URL and the <query-text>, which isn't acceptable. Similarly, I attempted the RelativePath header, but that inserts a '/' character.
No doubt, the above would have worked if <query-text> was an optional item like the [/<limit>] part.
Temporary Solution
My solution was to write the output of Uri.EscapeDataString(<query-text>) to a parameter, copy that text just before publishing and paste that static text into the Web.Contents() function to acquire the necessary output. This way, I at least have report that can be uploaded to pbi.com and get refreshed according to a schedule.
New Problem
My next goal is to merge the results from two table from two different sources, a history table stored in a data warehouse and a 'current' table queried from the transactional database. My goal is to query the last date available from the history table and merge that with a query of the transactional database like:
SELECT data FROM table WHERE date_created > max_historic_date
Obviously, applying my temporary solution above would require me to manually update the M code in the .pbix file every time the history table gets updated.
Can someone see any way to work around this, remembering that I cannot use a function to cast "max_historic_date" to before concatenating it to the encoded <query-text>?
Your input would be greatly appreciated.
Kind regards,
Petrus Kruger
Hi @PetrusJKruger,
What's the web data source? Can you create a view in the database? Then you can use the simple query like this: "SELECT name FROM VIEW".
Best Regards,
Dale
Hi @v-jiascu-msft,
Thanks for coming back to me.
The websource is a OrientDB managed database, which allows access through a RESTful HTTP protocol.
I'm unfortunately not familiar with what you mean by 'view' of the database - I'm very much learning as I go along. Perhaps I'm not answering your question, but I'll attempt to.
I suspect that
http://<server>:[<port>]/query/<database>/<language>/<query-text>[/<limit>]
is how you access the 'view'. Because it is an HTTP request the URL needs to be encoded from
"SELECT name FROM VIEW" to "SELECT%20name%20FROM%20VIEW"
I can work around that manually without the Uri.EscapeDataString() function. However, now I need to add a WHERE clause that changes dynamically and still use no functions like Date.ToText(max_historic_date). Then I need to append that to become part of the <query-text>, hoping that using & to concatenate will still allow powerbi.com to perform its static web source test.
I hope that helps, @v-jiascu-msft.
Kind regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.