Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mcclurej
Helper I
Helper I

Unable 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 8
mcclurej
Helper I
Helper I

Oooooooooooookay... 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

Anonymous
Not applicable

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.

@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.

az38
Community Champion
Community Champion

Hi @mcclurej 

I've got no problem to connect with MV in import mode

Usually, in Oracle issues are grant access to MV or usage scheme name in select statement, like SELECT * FROM SCHEME.yourVM


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.