# Business Intelligence and Reporting - SPARQL Query Templates
# Collection of 15 essential queries for BI and analytics operations

PREFIX bi: <https://ggen.ai/ontology/bi-reporting#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>

# Query 1: Data source inventory and refresh status
SELECT ?source ?sourceName ?sourceType ?lastRefresh ?refreshFreq ?status
WHERE {
    ?source a bi:DataSource ;
            bi:sourceName ?sourceName ;
            bi:sourceType ?sourceType ;
            bi:lastRefresh ?lastRefresh ;
            bi:refreshFrequency ?refreshFreq ;
            bi:connectionStatus ?status .
}
ORDER BY ?lastRefresh

# Query 2: Dimensional model structure
SELECT ?model ?factTable ?dimension ?dimensionType ?hierarchy
WHERE {
    ?model a bi:DimensionalModel ;
           bi:hasFactTable ?fact ;
           bi:hasDimension ?dim .
    ?fact bi:tableName ?factTable .
    ?dim a ?dimensionType ;
         bi:tableName ?dimension .
    OPTIONAL {
        ?dim bi:hasHierarchy ?hier .
        ?hier bi:hierarchyName ?hierarchy
    }
}

# Query 3: OLAP cube metrics and dimensions
SELECT ?cube ?cubeName ?measure ?measureValue ?aggregationType
WHERE {
    ?cube a bi:OLAPCube ;
          bi:cubeName ?cubeName ;
          bi:hasMeasure ?measureObj .
    ?measureObj bi:measureName ?measure ;
                bi:measureValue ?measureValue ;
                bi:aggregationType ?aggregationType .
}
ORDER BY ?cubeName ?measure

# Query 4: Dashboard inventory with widgets
SELECT ?dashboard ?dashboardName ?widget ?widgetTitle ?widgetType ?position
WHERE {
    ?dashboard a bi:Dashboard ;
               bi:dashboardName ?dashboardName ;
               bi:hasWidget ?widget .
    ?widget bi:widgetTitle ?widgetTitle ;
            bi:widgetType ?widgetType ;
            bi:position ?position .
}
ORDER BY ?dashboardName ?position

# Query 5: KPI scorecard with targets and trends
SELECT ?kpi ?kpiName ?currentValue ?targetValue ?variance ?trendDirection ?percentChange
WHERE {
    ?kpi a bi:KPI ;
         bi:kpiName ?kpiName ;
         bi:currentValue ?currentValue ;
         bi:hasTarget ?target ;
         bi:hasTrend ?trend .
    ?target bi:targetValue ?targetValue .
    ?trend bi:trendDirection ?trendDirection ;
           bi:percentageChange ?percentChange .
    BIND((?currentValue - ?targetValue) AS ?variance)
}
ORDER BY DESC(ABS(?variance))

# Query 6: Report catalog with data sources
SELECT ?report ?reportName ?reportType ?created ?lastModified ?creator ?dataSource
WHERE {
    ?report a ?reportType ;
            bi:reportName ?reportName ;
            bi:createdDate ?created ;
            bi:lastModified ?lastModified ;
            bi:createdBy ?creatorObj ;
            bi:usesDataSource ?source .
    ?creatorObj foaf:name ?creator .
    ?source bi:sourceName ?dataSource .
    FILTER(?reportType IN (bi:TabularReport, bi:ChartReport, bi:PivotReport))
}
ORDER BY ?lastModified

# Query 7: Scheduled reports and next run times
SELECT ?report ?reportName ?schedule ?frequency ?nextRun ?enabled ?subscribers
WHERE {
    ?report a bi:Report ;
            bi:reportName ?reportName ;
            bi:hasSchedule ?scheduleObj .
    ?scheduleObj bi:scheduleName ?schedule ;
                 bi:frequency ?frequency ;
                 bi:nextRun ?nextRun ;
                 bi:enabled ?enabled .
    {
        SELECT ?report (COUNT(?subscription) AS ?subscribers)
        WHERE {
            OPTIONAL {
                ?subscription bi:subscribedToReport ?report
            }
        }
        GROUP BY ?report
    }
}
ORDER BY ?nextRun

# Query 8: Alert rules and threshold monitoring
SELECT ?alert ?kpiName ?condition ?threshold ?currentValue ?triggered ?notifyUsers
WHERE {
    ?kpi a bi:KPI ;
         bi:kpiName ?kpiName ;
         bi:currentValue ?currentValue ;
         bi:hasAlert ?alert .
    ?alert bi:alertCondition ?condition ;
           bi:hasThreshold ?thresholdObj ;
           bi:notifyUsers ?users .
    ?thresholdObj bi:thresholdValue ?threshold .
    ?users foaf:name ?notifyUsers .
    BIND(IF(?condition = "greater_than" && ?currentValue > ?threshold, true, false) AS ?triggered)
}
ORDER BY DESC(?triggered)

# Query 9: ETL process monitoring
SELECT ?process ?processName ?step ?stepType ?stepOrder ?status ?lastRun ?duration
WHERE {
    ?process a bi:ETLProcess ;
             bi:processName ?processName ;
             bi:processStatus ?status ;
             bi:lastRun ?lastRun .
    {
        ?process bi:hasExtraction ?step .
        BIND("extraction" AS ?stepType)
    } UNION {
        ?process bi:hasTransformation ?step .
        BIND("transformation" AS ?stepType)
    } UNION {
        ?process bi:hasLoading ?step .
        BIND("loading" AS ?stepType)
    }
    ?step bi:stepOrder ?stepOrder ;
          bi:executionDuration ?duration .
}
ORDER BY ?processName ?stepOrder

# Query 10: Hierarchy drill-down paths
SELECT ?dimension ?hierarchy ?level ?levelName ?levelOrder ?parentLevel
WHERE {
    ?dim a bi:DimensionTable ;
         bi:tableName ?dimension ;
         bi:hasHierarchy ?hier .
    ?hier bi:hierarchyName ?hierarchy ;
          bi:hasLevel ?level .
    ?level bi:levelName ?levelName ;
           bi:levelOrder ?levelOrder .
    OPTIONAL {
        ?level bi:parentLevel ?parent .
        ?parent bi:levelName ?parentLevel
    }
}
ORDER BY ?hierarchy ?levelOrder

# Query 11: Measure calculations and aggregations
SELECT ?measure ?measureName ?dataType ?aggregationType ?formula ?sourceMeasures
WHERE {
    ?measure a bi:CalculatedMeasure ;
             bi:measureName ?measureName ;
             bi:dataType ?dataType ;
             bi:aggregationType ?aggregationType ;
             bi:formula ?formula .
    OPTIONAL {
        ?measure bi:dependsOnMeasure ?source .
        ?source bi:measureName ?sourceMeasures
    }
}

# Query 12: Chart visualization configuration
SELECT ?chart ?title ?chartType ?xAxis ?yAxis ?series ?dataSource
WHERE {
    ?chart a bi:Chart ;
           bi:widgetTitle ?title ;
           bi:chartType ?chartType ;
           bi:xAxis ?xAxis ;
           bi:yAxis ?yAxis ;
           bi:series ?series ;
           bi:usesDataSource ?source .
    ?source bi:sourceName ?dataSource .
}

# Query 13: Query performance and execution stats
SELECT ?query ?queryText ?executionTime ?rowsReturned ?cached
WHERE {
    ?query a bi:Query ;
           bi:queryText ?queryText ;
           bi:executionTime ?executionTime ;
           bi:rowsReturned ?rowsReturned ;
           bi:isCached ?cached .
    FILTER(?executionTime > 1000)
}
ORDER BY DESC(?executionTime)

# Query 14: Data warehouse storage metrics
SELECT ?warehouse ?datamart (SUM(?tableSize) AS ?totalSize) (COUNT(?table) AS ?tableCount)
WHERE {
    ?warehouse a bi:DataWarehouse ;
               bi:hasDataMart ?datamart .
    ?datamart bi:containsTable ?table .
    ?table bi:tableSize ?tableSize .
}
GROUP BY ?warehouse ?datamart
ORDER BY DESC(?totalSize)

# Query 15: User access and report subscriptions
SELECT ?user ?userName ?dashboard ?report ?subscription ?frequency
WHERE {
    ?user foaf:name ?userName .
    {
        ?user bi:hasAccessTo ?dashboard .
        ?dashboard a bi:Dashboard ;
                   bi:dashboardName ?dashboardName .
    } UNION {
        ?subscription bi:subscriber ?user ;
                      bi:subscribedToReport ?report ;
                      bi:deliveryFrequency ?frequency .
        ?report bi:reportName ?reportName .
    }
}
ORDER BY ?userName
