ODBC Error with Cache DB

[Randyr]

Hello,

I am brand new to Power BI and I am trying to pull data in from Avaya Aura Contact Centre which is an Intersystems Cache DB.  I am able to see the tables however when I select one of the tables to actually view the data, I am getting the following error.

DataSource.Error: ODBC: ERROR [42000] [Cache ODBC][State : 37000][Native Code 1]
[C:\Program Files\Microsoft Power BI Desktop\bin\Microsoft.Mashup.Container.NetFX40.exe]
[SQLCODE: <-1>:<Invalid SQL statement>]
[Location: <Prepare>]
[%msg: < IDENTIFIER expected, LITERAL ('') found^select "Timestamp" , "ApplicationID" , "Application" , "CallsAbandoned" , "CallsAbandonedAftThreshold" , "CallsAbandonedDelay" , "CallsAnswered" , "CallsAnsweredAftThreshold" , "CallsAnsweredDelay" , "CallsAnsweredDelayAtSkillset" , "CallsConferencedIn" , "CallsCo
Details:
    DataSourceKind=Odbc
    DataSourcePath=dsn=CCMS)CACHE

I am able to look at this data using Crystal Reports so I am not sure what what I am doing wrong.  I updated the ODBC driver (x64) but that did not work. I appreciate any assistance you can provide.

Thank you! 

RR

[Randyr]

Hello,

When you are setting your data source, under 'advanced options' you can add an SQL statement. I simply added

select * from iAgentPerformanceStat  

or

select * from mApplicationStat  

Depending on what data you are looking for. (Agent or Application data)

I hope this is helpful

RR

[kmilus]

I have de same problem.

How did you solve?

[kmilus]

I have de same problem.

Haw did you solve?

[hugoberry]

Try replacing " in your select query...

select "Timestamp" , "ApplicationID" , "Application" , "CallsAbandoned" , "CallsAbandonedAftThreshold" , "CallsAbandonedDelay" , 

[Randyr]

Thank you Hugoberry, this makes perfect sense to me however I do not see anywhere in the Query editor where I can change this select query??

Sorry for being a newbie

[hugoberry]

It is hard to tell without having access to your query.

If you loaded the data from Get Data>ODBC  there is an Advanced option which you can use

[Randyr]

Thank you, yes that is what I am doing.  The Advanced options are blank, and if I leave them that way it will show me the tables but I get the error when I try to preview the data.

If I go back and start again and try adding 

select "Timestamp" , "ApplicationID" , "Application" , "CallsAbandoned" , "CallsAbandonedAftThreshold" , "CallsAbandonedDelay" ,  as you suggested, I see this now....before getting to the 'Navigator" window.

[hugoberry]

What happens if you escape the quotes?

select ""Timestamp"" , ""ApplicationID"" , ""Application"" , ""CallsAbandoned"" , ""CallsAbandonedAftThreshold"" , ""CallsAbandonedDelay"" ,

[Randyr]

Thank you, adding escape quotes did not work unfortunately.

[Randyr]

I got it!   I just changed it to a simple SQL query.   SELECT * from iAgentPerformanceStat

Thank you for the help!!

[v-yuezhe-msft · Accepted solution]

Hi @Randyr,

Glad to hear the issue is solved. You can accept your reply as answer, that way, other community members would benefit from your solutions once they get same issues.

Thanks,
Lydia Zhang

[hugoberry]

Aha, so probably using the query without quotes would have worked as well:)
Glad that you've sorted this