Having issues with Connect to Oracle database

[RakeshP]

Hi ALL,

I am trying to connect to oracle database from Power BI Desktop(PBID), but it is throwing below error.

Unable to connect

we encountered an error while trying to connect

Details: "The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the offical provider."

Please help me.

[gusdcruz]

Just want to share my experience here. Since I work in a coorporation everytime that I had to update Power BI I had to get admin rights, so I looked this issue up and someone here in the community recommended to download Power BI though Microsoft Store, so I did that so I didnt have to ask for admin rights, since then I started having problems connecting to Oracle Database, so this morning I decide to install Power BI through the web, and guess what, the problem went away. Dont ask me why, but this solve my problem.

[anitaberg]

Hi! I have issue with the original issue. Details: "The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider." 

I have installed a complete test environment on my windows virtual PC, with Oracle database and Oracle business intelligence running.

Now I have installed the corresponding ODAC to the version of oracle database I have, I believe.

When I installed the ODAC software it created a client_2 in adition to the client_1 I hve in the c:app.... folder

My guess is that Power BI is looking in the client_1 folder for the dll to use, when it should have looked in client_2 as that was created with the ODAC 64 bit install.

Any help or tips on this? 

[anitaberg]

I solved my issues with having two clients by this thread: https://community.powerbi.com/t5/Desktop/Unable-to-connect-using-Oracle-database-even-after-ODAC-ins...

[pqian · Accepted solution]

You need the latest ODP.net library to use Oracle. Please download it here:http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

[teljoy]

this solution alone was not enough to fix this problem - had to combine the accepted solution with the comment from "gusdcruz" to fix this problem   For multiple users in my organisation, the solution was to :
1. Download and install latest ODAC from oracle as listed (in my case was no need to uninstall prev version)
2. Uninstall all Power BI application/s installed via MS Store previously
3. Go to https://powerbi.microsoft.com/en-us/downloads/ , choose Advanced download options(do NOT install via MS Store!) , then download x64/x86 as applicable and install
4. Error fixed!

[leeann_g]

I'm having the same problem and even after I download the Oracle client that corresponds to my Orable DB version and install it am receiving the following error:

Details: "The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the offical provider." 

Are there other steps we should follow to complete the connection? Or other things that need to be modified? If so, where and what files need to be modified? I get to the point of entering my DB credentials and then it fails with the error. This feels like a significant bug.

[pqian]

@leeann_g the client version need not match the server version. I think this is the version you want:

ODAC 12c Release 4 and Oracle Developer Tools for Visual Studio (12.1.0.2.4)
Download the File [Released October 5, 2015]
Download ODTwithODAC121024.zip 304 MB (319,813,071 bytes)

[leeann_g]

That worked after removing all other versions, and making sure that I had the x64 version, of course. Still encountering some other issues at the query execution and filtering level, but that helped me over the connection hurdle. Thank you!

This would be a great thing to have included in the Prerequisites checklist or in the installation package for PowerBI itself.

[lynchc]

Where did you delete the old versions?

[gusdcruz]

I simply unistalled the software and installed the lastest version from the Power BI website.

[pqian]

@leeann_g What are some of the query execution issues? We'd be interested to hear.

Unfortunately we cannot bundle Oracle drivers in PBI desktop (licensing issues). However, we'll be updating the FW link you get when the driver is missing, so that it points to the right address.

[leeann_g]

This is an example of an issue/error I am experiencing -- when filtering a date field from my data source, then selecting Month > Last Month. It appears to be unable to handle the date format we are using.

I'm also attemting to execute the same query from the Added Column "Date" using the Date Filter = Month > Last Month.

Error Message:

Microsoft.Mashup.Engine1.Runtime.Library+Date+StartOfMonthFunctionValue

Stack Trace:

Microsoft.Mashup.Evaluator.Interface.ErrorException: Microsoft.Mashup.Engine1.Runtime.Library+Date+StartOfMonthFunctionValue ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Microsoft.Mashup.Engine1.Runtime.Library+Date+StartOfMonthFunctionValue ---> System.InvalidOperationException: Microsoft.Mashup.Engine1.Runtime.Library+Date+StartOfMonthFunctionValue
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.Visit(Object node)
at Microsoft.Mashup.Engine1.Library.Oracle.OracleAstCreator.CreateToDate(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.CreateLogicalOperation(BinaryLogicalOperator predicate, IBinaryExpression binary)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitBinary(IBinaryExpression binary)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.Visit(Object node)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.CreateConditionExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitBinary(IBinaryExpression binary)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.CreateListSelect(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node)
at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.Compile(Query originalQuery, IExpression expression, LogicalAstToCachedTypeflowResultCursor cursor)
at Microsoft.Mashup.Engine1.Library.Common.QueryResultTableValue.get_Type()
at Microsoft.Mashup.Engine1.Library.Common.ExternalQueryProcessor.ApplyTake(Query originalQuery, Value input, IExpression takeQuery)
at Microsoft.Mashup.Engine1.Library.Common.ExternalQueryProcessor.Invoke(Query originalQuery, IExpression expression)
at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.ExpressionTableValue.Take(Query query, RowCount count)
at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.ExpressionTableValue.SkipTake(SkipTakeQuery query)
at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSkipTake(SkipTakeQuery query)
at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.Fold(Query query)
at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.GetEnumerator()
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass32`1.<OnBeginGetResult>b__2f()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

Invocation Stack Trace:

at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

[pqian]

@leeann_g Can you post the Query's full expression here? (View->Advance Query Editor)

[leeann_g]

I was able to get this to work by manually changing the Date/Time data type and format to the Date data type. It seems that the Date/Time format type we are using in our Oracle system (date time (G)) was not working with the tool and resulted in that error message. I can't provide the full query detail here unless I strip all connection related info. Would that be enough for you to work with?

[mcguija]

Hi,

I am having issues with the issue orginally logged in this thread.  I do have the correct version of Oracle client installed.

I also added the install directory to my PATH.

 d:\oracle;D:\oracle\bin\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft\Web Platform Installer\

Rebooted to ensure if caugth

I also connected to the DB using Oracle SQL developer no problem.

HELP!

Here is the error.

Unable to connect: We encountered an error while trying to connect to CDS01_12c_DEV. Details: "Cannot connect to the mashup data source. See error details for more information."Hide details
Activity ID: d50939a9-69f5-4195-a414-43db4f5e60ef
Request ID: 68d6c7f3-0e04-1481-8410-b46b3215f469
Cluster URI: https://wabi-canada-central-redirect.analysis.windows.net
Status code: 400
Error Code: DM_GWPipeline_Gateway_MashupDataAccessError
Time: Fri Dec 16 2016 11:05:24 GMT-0500 (Eastern Standard Time)
Version: 13.0.1700.811

Underlying error code: -2147467259
Underlying error message: The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

Troubleshoot connection problems

[mcguija]

Hi,

I am having issues with the issue orginally logged in this thread.  I do have the correct version of Oracle client installed.

I also added the install directory to my PATH.

 d:\oracle;D:\oracle\bin\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft\Web Platform Installer\

Rebooted to ensure if caugth

I also connected to the DB using Oracle SQL developer no problem.

HELP!

Here is the error.

Unable to connect: We encountered an error while trying to connect to CDS01_12c_DEV. Details: "Cannot connect to the mashup data source. See error details for more information."Hide details
Activity ID: d50939a9-69f5-4195-a414-43db4f5e60ef
Request ID: 68d6c7f3-0e04-1481-8410-b46b3215f469
Cluster URI: https://wabi-canada-central-redirect.analysis.windows.net
Status code: 400
Error Code: DM_GWPipeline_Gateway_MashupDataAccessError
Time: Fri Dec 16 2016 11:05:24 GMT-0500 (Eastern Standard Time)
Version: 13.0.1700.811

Underlying error code: -2147467259
Underlying error message: The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

Troubleshoot connection problems

[leeann_g]

I was spending excessive amounts of time getting through data filtering items. And each time I created a data filter on a column it would begin to refresh all data before allowing me to move on. This is extremely time consuming and disruptive if someone has multiple filters to apply.

I think maybe some of the issues with editing queries, filtering, and inter-step data refresh might be solved with the latest PBI release. Need to test and will update thread with results!