Solved

Azure Analysis Services JDBC Driver does not return any tables/schema data

  • 5 December 2023
  • 4 replies
  • 65 views
Q
Userlevel 3
Badge

Hi team,

 

I have connected to AAS successfully using jar file:
 

 

But when I try to fetch data, nothing is returned. For example getting the metadata:

ResultSet columns = databaseMetaData.getColumns(null,null,null,null)

Or select from system tables:

statement.execute("SELECT * FROM [sys_schemas]");

They all return nothing, empty result set.

Why?

icon

Best answer by Ankit Singh 11 December 2023, 12:11

View original

4 replies

Ankit Singh
Userlevel 5
Badge +1

Hi @quangdutran809 

Have you tried setting up the logging with high verbosity to see what is causing this and if any error was captured? Also, can you share the sample request that you made to the CData Azure Analysis JDBC Driver?

To enable logging, set following connection properties:

  • LogFile = <any file path of your system>;
  • Verbosity = 5;

 

That Solutions Guy
Q
Userlevel 3
Badge

This is the content of the log: 

2023-12-08T14:31:20.011+0700	2	[2|CONN]	[HTTP|Res: 2] HTTP/1.1 200 OK, 1374 Bytes Transferred
2023-12-08T14:31:20.011+0700	2	[2|CONN]	[HTTP|Res: 2] Request completed in 1,681 ms.
2023-12-08T14:31:20.012+0700	2	[2|Q-Id]	[META|Schema] Executed  catalog query Success: 0 results (1,686 ms)
2023-12-08T14:31:20.012+0700	2	[2|Q-Id]	[META|Schema] Retrieved catalog from LiveServer Success: 0 results
2023-12-08T14:31:20.012+0700	3	[ |Q-Id]	[META|MemSto] Cached catalogs, count: 0.
2023-12-08T14:31:20.012+0700	2	[2|Q-Id]	[META|Schema] Inserted catalog into MetaCache Success: 0 results
2023-12-08T14:31:20.012+0700	3	[ |Q-Id]	[META|MemSto] Retrieved cached catalogs, count: 0.
2023-12-08T14:31:20.012+0700	2	[2|Q-Id]	[META|Schema] Retrieved catalog from MetaCache Success: 0 results
2023-12-08T14:31:20.014+0700	2	[2|Q-Id]	[EXEC|Messag] Executed query: [SELECT * FROM sys_tablecolumns] Success: (1,710 ms)
2023-12-08T14:31:20.022+0700	5	[ |Q-Id]	[META|Schema] Engine Invalid object name 'sys_disconnect'
2023-12-08T14:31:20.022+0700	4	[2|Q-Id]	[INFO|Connec] Executed sys_disconnect: Success: (0 ms)
2023-12-08T14:31:20.023+0700	1	[2|Q-Id]	[INFO|Connec] Closed AAS connection

Which is not really informative? These are models in AzureAnalysisService:

 

 

Ankit Singh
Userlevel 5
Badge +1

Hi @quangdutran809 

From the logs, it seems the connection is successfully established. In that case, I believe the challenge is really with how you extract the data from Azure Analysis Services.

We need to be mindful that Azure Analysis Services is an OLAP database that exposes data as cubes, which you query with MDX (multidimensional expressions). The driver models these cubes in relational views that you can query with SQL-92. 

Ideally, the following mapping is for the layout of the model:

  • Catalog - Displayed in the driver as a Catalog.
  • Cube - Displayed in the driver as a Schema.
  • Measure - Available in the driver under the special Measures view.
  • Dimension - Each dimension is exposed as a view.
  • Level - Each individual level of a hierarchy is exposed as a column on the appropriate dimension view.

To understand how to extract data from AAS using Metrics and Dimensions, you can refer to the Help documentation here: https://cdn.cdata.com/help/OAJ/jdbc/pg_retrievingdata.htm.

You can use the query examples to create your own SQL query that you can use in a prepared statement to fetch the data.

That Solutions Guy
Q
Userlevel 3
Badge

Thanks Ankit,

In the document the sample query is like this:

 

SELECT m.[Customer Count], c.[City]
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

How could I know the name of the Catalog and Schema of Azure Analysis Service?

On Azure Portal, the Analysis Service just shows the Models list, the name of the models 

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings