Connecting to BI Tools

the ampeducator data warehouse gives your institution secure access to reporting data that can be used with a wide variety of business intelligence, reporting, and analytics tools rather than connecting directly to your live ampeducator database, the data warehouse exports a curated set of reporting tables each night this allows you to perform complex queries and build dashboards without impacting the performance of your live system once enabled, everything you need to connect is available directly within reports → data warehouse data warehouse dashboard the reports → data warehouse page is your central location for connecting to the data warehouse from this page you can view connection credentials amazon athena settings amazon s3 download settings built in connection guides for popular software download instructions the data warehouse is an optional add on feature and must be enabled for your institution before it can be used if the data warehouse has not yet been enabled, you'll see a message similar to the one shown above to learn more or request access, please contact support\@ampeducator com mailto\ support\@ampeducator com connection options the data warehouse supports two methods of accessing your data amazon athena (recommended) amazon athena provides direct sql access to your reporting data and is the recommended option for most institutions benefits include query using standard sql no database server to manage fast performance works with nearly every modern bi tool access to all data warehouse reporting tables most institutions will use athena for dashboards, reports, and analytics direct s3 downloads (csv & parquet) for institutions that prefer to work directly with files, the data warehouse also exports every reporting table to amazon s3 each night files are available in both csv parquet this option is useful for custom data pipelines importing into another data warehouse python or r analysis machine learning workflows offline reporting the data warehouse page includes connection instructions for downloading files directly from s3 built in connection guides the data warehouse includes step by step connection instructions directly within ampeducator for several popular applications currently documented tools include power bi tableau dbeaver python cyberduck aws cli athena (query settings) tab showing the built in software instructions csv / download settings tab showing the s3 download instructions supported bi tools because amazon athena supports both jdbc and odbc connections, the data warehouse works with almost any modern reporting platform, including microsoft power bi tableau excel dbeaver python r metabase qlik sense looker any jdbc or odbc compatible application if your reporting software supports amazon athena, it can generally connect to the ampeducator data warehouse recommended first connection if you're connecting for the first time, we recommend using dbeaver dbeaver is free, easy to configure, and provides a simple interface for browsing tables, testing sql queries, and verifying your connection before connecting bi platforms such as power bi or tableau once your connection is working in dbeaver, connecting other reporting tools is typically straightforward nightly data refresh the data warehouse is refreshed automatically every night during the overnight process reporting tables are updated csv files are regenerated parquet files are regenerated amazon athena immediately queries the newest data this ensures reports always use the latest available information while avoiding any impact on the live ampeducator system next steps once connected, you can begin exploring the reporting tables and building your own queries continue with overview docid\ ptudjpwbbjsb18lwlhzo3 – learn how the data warehouse works tables docid\ wnunql02lsdb1o7g a8qz – browse all available reporting tables and their relationships example queries docid 2hwk9jckeqa3xs21to1ru – sample sql queries to help you get started