How to do a custom query in Spotfire with jdbc:sybase database connection

[Enkeled Kanaj]

Hi,

Spotfire is connected to an ericsson database which is managed to be queried through information designer through data_sourse type jdbc:sybase.

To use custom query which option should I chose from those to connect with jdbc?

 

[David Boot-Olazabal]

Hi Enkeled,

To be able to create a custom query like you have shown for SQL Server, you first need an ODBC driver. See below example for MariaDB:

Unfortunately, there seems to be no Sybase entry in Spotfire, see also the list of supported databases: https://docs.tibco.com/pub/spotfire/general/drivers/index.htm#t=introduction%2Fintro_supported_data_sources.htm.

So, to be able to connect to a Sybase database, other than jdbc, you may try to find the ODBC driver, download and install it on your machine and create an ODBC DSN for it. You can then connect to this ODBC data source as explained here: 

As explained in that same artcle, you have to write your own SQL, which makes it a custom one.

And if nothing else works, you can always use the jdbc option via Information Designer (in the Information link), where you can change your SQL statement (as long as you have the same amount of columns as the original query):

Kind regards,

David

[Enkeled Kanaj]

Hi David,

 

Regarding edit sql in information designer I don't know if we can do complex queries

[Vanessa Virginia Sucre Gonzalez]

Hi @Enkeled Kanaj,

You should be able to write queries just as you do it on your sql client

[Enkeled Kanaj]

And how Can I do this for instance in information designer

Max of some kpi sum:

SELECT MAX(AMOUNTSUM)
FROM
(
SELECT date_of_join, ID, SHIFT_TYPE, SUM(AMOUNT) AS AMOUNTSUM
FROM TABLE1
GROUP BY date_of_join, ID, SHIFT_TYPE
) AS A

[David Boot-Olazabal]

Hi Enkeled,

If the above is a valid Sybase query, you could copy/paste it into an Information Link's SQL statement.
See an example (from SQL Server) below:

Where you have defined an Information link, containing the number of columns you want to return (1). By clicking on the SQL button (just above the number 1), you can modify the Original SQL, as long as you abide by the number of columns that have been defined (in my case, I only have one column) in the original SQL.
You then copy/paste (or modify) your SQL statement in the Modified SQL pane (2).
Finally, when you run this Information link in Spotfire, the outcome can be displayed in any visualization (3).

Hope this helps.

Kind regards,

David

[Enkeled Kanaj]

Hi David,

 

the syntax for my database is this:

100*(SUM(pmPrbUsedDlBcch)+SUM(pmPrbUsedDlDtch)+SUM(pmPrbUsedDlPcch)+SUM(pmPrbUsedDlSrbFirstTrans))/ (SUM(pmPrbAvailDl)) AS PRB_UTIL

   FROM dc.DC_E_ERBS_EUTRANCELLFDD_day

   WHERE date_id BETWEEN today(*)-15 AND today(*)-1

But lets keep the formating of info designer

SELECT
   D1."DATE_ID" AS "DATEID",
   D1."HOUR_ID" AS "HOURID",
   D1."ERBS" AS "ERBS",
   D1."EUtranCellFDD" AS "EUTRANCELLFDD",
   100*(SUM(D1."pmPrbUsedDlBcch") + SUM(D1."pmPrbUsedDlDtch") + SUM(D1."pmPrbUsedDlPcch") + SUM(D1."pmPrbUsedDlSrbFirstTrans"))/ SUM(D1."pmPrbAvailDl") AS " PRB_UTIL"
FROM
   "DC_E_ERBS_EUTRANCELLFDD_RAW" D1
WHERE
   <conditions>
GROUP BY
   D1."DATE_ID",
   D1."HOUR_ID",
   D1."ERBS",
   D1."EUtranCellFDD"
 

How to get now the max of each sum over hour_id (max of each day)

SELECT
   C1."DATEID",
   C1."ERBS",
   C1."EUtranCellFDD" 
   MAX(C1."PRB_UTIL")

FROM

  (SELECT
     D1."DATE_ID" AS "DATEID",
     D1."HOUR_ID" AS "HOURID",
     D1."ERBS" AS "ERBS",
     D1."EUtranCellFDD" AS "EUTRANCELLFDD",
     100*(SUM(D1."pmPrbUsedDlBcch") + SUM(D1."pmPrbUsedDlDtch") + SUM(D1."pmPrbUsedDlPcch") + SUM(D1."pmPrbUsedDlSrbFirstTrans"))/ SUM(D1."pmPrbAvailDl") AS "PRB_UTIL"
  FROM
     "DC_E_ERBS_EUTRANCELLFDD_RAW" D1
  WHERE
     <conditions>
  GROUP BY
     D1."DATE_ID",
     D1."HOUR_ID",
     D1."ERBS",
     D1."EUtranCellFDD") C1

GROUP BY

  C1."DATEID",
   C1."ERBS",
   C1."EUtranCellFDD" 

[Enkeled Kanaj]

I tried this as beginning and get the error:

SELECT
   D1."DATE_ID" AS "DATEID",
   D1."HOUR_ID" AS "HOURID",
   D1."ERBS" AS "ERBS",
   D1."EUtranCellFDD" AS "EUTRANCELLFDD",
   100*(SUM(D1."pmPrbUsedDlBcch") + SUM(D1."pmPrbUsedDlDtch") + SUM(D1."pmPrbUsedDlPcch") + SUM(D1."pmPrbUsedDlSrbFirstTrans"))/ SUM(D1."pmPrbAvailDl") AS " PRB_UTIL"
FROM
   "DC_E_ERBS_EUTRANCELLFDD_RAW" D1
WHERE
   <conditions>
GROUP BY
   D1."DATE_ID",
   D1."HOUR_ID",
   D1."ERBS",
   D1."EUtranCellFDD"
 

 

Error message: Could not read the selected data.

ImportException at :
Failed to read data (HRESULT: 80131500)

Stack Trace:

InformationModelException at Spotfire.Dxp.Data:
An Internal Error has occurred. com.spotfire.ws.dat.OperatorException: unexpected exception (HRESULT: 80131500)

Stack Trace:
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at Spotfire.Dxp.Internal.Utilities.SeekableStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
   at System.IO.BinaryReader.ReadUInt16()
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadMagicNumber(BinaryReader reader)
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadSectionType(BinaryReader reader, SbdfSectionTypeId expectedType)
   at Spotfire.Dxp.Data.Import.SbdfDataRowReader.DelayedInit()
   at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteInformationLink()
   at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.KnownColumnsInformationLinkDataRowReader.TryCreateCxxRepresentation(CxxSession session, Int64 exclusiveStartRowIndex, UInt64 maxRowsThatWillBeRead, PendingViewRequestsManager pendingViewRequestsManager, CxxTable& table)
   at Spotfire.Dxp.Data.Cxx.CxxDataTransfer.CreateTable(DataRowReader dataRowReader, CxxSession session, PartialDataLoadReport report, Advancer rowAdvancer, Boolean needsReset, Int64 exclusiveStartRowIndex, UInt64 maxRowsThatWillBeRead, PendingViewRequestsManager partiallyLoadedConsumers)
   at Spotfire.Dxp.Data.AsyncImport.DataSourceLoadingView.<>c__DisplayClass8_2.<CreateFullView>b__2()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
   at Spotfire.Dxp.Data.AsyncImport.DataSourceLoadingView.<>c__DisplayClass8_0.<CreateFullView>b__0(DataManager dataManager)
   at Spotfire.Dxp.Framework.DocumentModel.Snapshot`1.<>c__DisplayClass19_0.<ReadWithLimitationTarget>b__0()
   at Spotfire.Dxp.Framework.DocumentModel.ThreadTransactionInfo.EvaluateWithLimitationTarget(ILimitationTarget limitationTarget, Executor executor)
   at Spotfire.Dxp.Framework.DocumentModel.Snapshot`1.ReadWithLimitationTarget(ILimitationTarget limitationTarget, Action`1 action)
   at Spotfire.Dxp.Framework.DocumentModel.Snapshot`1.ReadWithGenerationSpan(Action`1 action, Action`1 invalidPropertyReadCallback)
   at Spotfire.Dxp.Framework.DocumentModel.Snapshot`1.Read(Action`1 action)
   at Spotfire.Dxp.Data.AsyncImport.DataSourceLoadingView.CreateFullView(DataSource dataSource, Snapshot`1 dataManagerSnapshot, CxxSession session, PendingViewRequestsManager pendingViewRequestsManager, Boolean loadingOnApplicationThread, LoadableEntityBusyToken busyToken, Boolean loadEmptySourceData)
   at Spotfire.Dxp.Data.AsyncImport.DataSourceLoadingView.<>c__DisplayClass5_0.<Start>b__0(PendingViewRequestsManager pendingViewRequestsManager)
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.<>c__DisplayClass22_0.<Start>b__0()
   at Spotfire.Dxp.Framework.ApplicationModel.MonitorableProgress.Start[T](Func`1 action)
   at Spotfire.Dxp.Data.AsyncImport.BackgroundImportLoadingView.<>c__DisplayClass14_0.<StartLoading>b__0()

InformationModelServiceException at Spotfire.Dxp.Services:
An Internal Error has occurred. com.spotfire.ws.dat.OperatorException: unexpected exception (HRESULT: 80131509)

Stack Trace:
   at Spotfire.Dxp.Services.WcfSoapService`2.InvokeService[T](Func`1 serviceMethod, String customMethodNameForLogging)
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()

[Enkeled Kanaj]

 

Hi david,

I tested a simple sum. the idea is as you said the number of columns must match with the returned columns of edited sql.

 

I created a column just for the sake of the match principle. But the names do not match. What if we have more formulas within the edited sql how to which formula correspond to the the dummy column?

 

 

[David Boot-Olazabal]

Hi Enkeled,

Looking at my own small example, I'm not sure if you need to match exactly the column names from the original SQL.
most important is to have the same amount of columns on either side.

Did your query as shown above, succeed?

What also works for me, is to run the query first in SQL developer (or any SQL tool you use on Sybase) and get rid of any problems. Then I copy/paste it into Spotfire. This is certainly useful when working with larger, more complex queries.

Kind regards,

David

[Enkeled Kanaj]

Hi david,

 

I sorted out finally. The number of dimensions and the calculated formula should match with the columns in information link. Even the order is important in the elements.

I guess that the correspondence between the calculated formula (I have not tested with more than one) and the dummy column returned is matched according to order. In the accordion below is the sql that works:

 

SELECT C1."DATEID" AS "DATEID",

C1."ERBS" AS "ERBS",

C1."EUTRANCELLFDD" AS "EUTRANCELLFDD",

MAX(C1."PRBUTIL") AS "DLPRBUTILISATION"

FROM

(SELECT

   D1."DATE_ID" AS "DATEID",

   D1."HOUR_ID" AS "HOURID",

   D1."ERBS" AS "ERBS",

   D1."EUtranCellFDD" AS "EUTRANCELLFDD",

   100*(SUM(D1."pmPrbUsedDlBcch") + SUM(D1."pmPrbUsedDlDtch") + SUM(D1."pmPrbUsedDlPcch") + SUM(D1."pmPrbUsedDlSrbFirstTrans"))/SUM(D1."pmPrbAvailDl") AS "PRBUTIL"

FROM

   "DC_E_ERBS_EUTRANCELLFDD_RAW" D1

WHERE

   <conditions>

GROUP BY

   D1."DATE_ID",

   D1."HOUR_ID",

   D1."ERBS",

   D1."EUtranCellFDD") C1

GROUP BY

   C1."DATEID",

   C1."ERBS",

   C1."EUTRANCELLFDD"

 

Edited July 30 by Enkeled Kanaj

[David Boot-Olazabal]

Hi Enkeled,

You are right, the order is also important, since it holds the information of the data types of each column.

Great to hear you have a way to make your custom query work!

Kind regards,

David