Forum Discussion
Helper IUnable to see Oracle Materialised views in Oracle Database Connection
So here's the conundrum. I have two different users, with the exact same permissions on this MV on the database. Both have a valid install of their oracle client on their machines, with the correct TNS files setup. Compared side by side.
User A can see the MVs user B cannot see the MVs.
On user B's computer we have completely removed Oracle (deleting ALL registry entries and ALL associated install files on their machine) then reinstalled using OUI.
Here's the kicker, on User B's computer, if they connect via ODBC rather than Oracle database connector (in Power BI), they can see the MVs! So permissions on database are perfectly fine.
My last step, which I am doing right now, is to completely remove Power BI from User B's computer and reinstall to see if that fixes the issue. If anyone has encountered this problem before, any help would be appreciated.
***
edit: Reinstall of Power BI did not resolve issue.
edit2: I've tried with both 64bit and 32bit versions of PowerBI (with both oracle versions installed) and this is still the case.
edit3: A migration of the data source into ODBC does allow data connection to the view (e.g. using the procedures here: https://www.decisivedata.net/blog/change-a-power-bi-data-source-type ). So this reconfirms permissions are correct.
8 Replies
mcclurejOooooooooooookay... well...I figured it out...
So, turns out, and I apologise because I did not know this was a thing, Power BI stores permissions globally.
E.g. the first time you connect to a data source, that's the connection schema you will connect with henceforth.
Since I had previously used schema that did not have access to those MVs OF COURSE I wouldn't be able to connect to them.What clicked for me today was this. I tried to create a view, that pointed to the MV, using said disallowed Schema. As soon as the creation string failed, all dominoes fell, and I knew what was going on.
Sigh...well at least if anyone else has this issue they can not make the same mistake as me.
So here's how to change the global string:
File > Options and Settings > Data Source Settings > Global Permissions > Fix yo junk- Greg_Deckler
Community Champion
You could check the Issues forum here:
https://community.powerbi.com/t5/Issues/idb-p/Issues
And if it is not there, then you could post it.
If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET". HI mcclurej,
If you ask them to switch the test device and log on their account on different devices, did user B success connect to the data source?
I'd like to suggest you confirm if this issue is related to a specific device. (notice: port, service, firewall rule, device/service policy also affect connection status)
Regards,Xiaoxin Sheng
- mcclurej
Anonymous I can confirm it is not a network issue, port issue, or permissions issue, as they can connect using the Oracle connector in Tableau to the Materalised view. The issue is purely Power BI not showing Materalised views using the Oracle connector for this user for some reason.
HI mcclurej,
Hmm, perhaps you can consider to contact power bi support to report this connector issues if you can confirm nothing wrong on permission and other related settings.
Regards,
Xiaoxin Sheng
- If I recall correctly (and it's been a year or so so please forgive my foggy brain) Oracle Database Connector doesn't support Materialized Views. It also has issues with Psuedonyms for Schema at times (but that is a different topic).
In addition I hate to be the one to tell you that even if you do finally get connected the Oracle Database Connector is very slow. (If this is not the case for you I would love to hear from you to see what you did to work around this). You'll end up needing Ole DB or ODBC with some connection string modifications to havr it pull data at any speed faster than snail.- mcclurej
Anonymous I've not had any issues with the Oracle Databse connector before, but then I am always using it in import mode. Also, the largest data set we generally work on is around 84million tuples, so not super large data sets.
In regards to MV, we just gave up and are using it via ODBC for both users, but it seems odd that ORacle Database connector would work for one and not the other.
