BI Accessing Enterprise Data Canned queries using Excel

BI: Accessing Enterprise Data “Canned” queries using Excel, VBA and SQL © Stefano Grazioli - Ask for permission for using/quoting: grazioli@virginia. edu

Critical Thinking § Let me know of absences via email in advance § Easy meter

Homework Automating Business Intelligence at Small. Bank © Stefano Grazioli - Ask for permission for using/quoting: grazioli@virginia. edu

Back at Small. Bank (better pay) Welcome back! We missed you! Your SQL work last week was so awesome that I want to automate it, so that we get reports at a click of a button. I need a way to quickly retrieve a list of our current loans to customers in VA or TX… can you do that? Easy peasy boss!

Back at Small. Bank (better pay) Sample shows customers I will even throw in some cool extra features!

You do the talking § Name, Year, Track… § Learning objectives § Things you like about the class § Things that can be improved § Attitude towards the Tournament

How do I access my data? Tables in the RDBMS on the network SQL Query In your computer memory: tables in a DATASET VBA Commands

Database “Legos” § Standard software objects stitched together § ADO. NET Active. X Data Object. It is a MSFT technology to manage enterprise data (on your CV)

ADO architecture (6 “Legos”) Client (your machine) Example connection string "Data Source=f-sg 6 m-s 4. comm. virginia. edu; Initial Catalog=Small. Bank. DB; Integrated Security=True" Example query Select * from Customer where state = ‘VA’ 1. Sql. Connection: given a connection string will connect you to a DB 2. Sql. Command: contains your SQL query 3. Sql. Adapter: the engine that moves the data. Fills tables in datasets using the query in Sql. Command 4. Data. Set that contains 5. Data. Tables 6. List. Object: shows you the data in the datatable DB server Remote RDBMS (financial data)

WINIT What Is New In Technology? © Stefano Grazioli - Ask for permission for

Suggestions § Make sure that you understand the ADO architecture diagram before coding.