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?
Best answer by Ankit Singh
Hi
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:
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.
+1Hi
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:
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 connectionWhich is not really informative? These are models in AzureAnalysisService:
+1Hi
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:
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.
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 mHow 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
