Hi Team,
We use quicksight extensively through various analysis. We are trying to keep a track of the KPIs used in the analysis.
As in any other BI tool, behind the visual for each KPI, a SQL query gets executed to fetch the values for the visuals. We want a way to get that for a particular sheet / analysis. This will help in keeping track of the KPI definitions in a centralized repository.
TLDR ; Is there a way to get the SQL query that is executed for each KPI in a particular visual / sheet
Hi @Sanjay1
Quick Sight doesn’t expose the underlying language it uses to define each visual or KPI.
SPICE datasets run entirely in memory, after ingestion no SQL is sent to your database at runtime, only the ingestion query is logged.
For Direct Query datasets, the SQL statements that are fired can be captured in your source engine’s query logs.
Custom SQL used in a dataset is retrievable using list datasets with ListDataSets, call DescribeDataSet, read the CustomSql field, and save it to S3 for cataloging.
Hi @Xclipse thank you for the prompt response. So there is no way for me to get the SQL of my KPIs for SPICE datasets?
Eg: If I have a line chart showing sales by years is it somehow possible to get - label of the KPI (Sales numbers), KPI name (sales), aggregration (Sum), group by year and visual identifier (any id or some sorts)
The reason for this is we are not able to track the metric definitions leading to gaps and Quick sight does not have any meta data table that gives these info. Any help here would be greatly appreciated.
Hi @Sanjay1
You can extract all that metadata by listing analyses with ListAnalyses and then calling DescribeAnalysisDefinition for each one, the JSON you get back includes the label, metric name, aggregation, group-by field, and visual IDs. Parse it once and store it.
Hi @Xclipse thank you! Will check the API and reach out in case of any other questions.