Databases and Data Warehouses with SAS DI Studio

optional courses

Elementary knowledge of computer science.

The course aims to familiarize participants with the creation, modification and management of databases, using SQL language, which is the standard used in databases such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, etc. The classes also include knowledge of data warehousing as a conceptual tool, technology and research techniques used for economic analysis. Students will also learn SAS ETL intuitive tools that support the process of obtaining information from the data, the multidimensional analysis and presentation. The basis of such analysis is the possibility to study the relationship between the numerical values representing the data, and a set of parameters called dimensions. In addition, during the course there will be presented example of data warehouse used to analyze the labor market in Poland.

NOTE: Classes are conducted according to DMCP programme, after the completion of which students can obtain a SAS certificate.

1. Database systems. Database management systems in business organizations. Basic actions in relational algebra. Relational model.

2-3. SQL (Structured Query Language). Queries in SQL: a simple, qualified, with aggregates, nested, with connections. The architecture of a database management system (DBMS)

4. The architecture of a data warehouse as a part of a decision support system (DSS)

5. Role of data warehouse in terms of benefit analysis and economic losses for an enterprise.

6. The data warehouse architecture in SAS.

7. Data sources and data model in data warehouse (metadata).

8. Information analysis (data analysis), from the data selection based on parameters to multi-level drill data (drill down, drill through) - "from general to specific" approach, the technique of going from the most synthetic to increasingly detailed.

9. Aggregation of data and the formulation of measures.

10. Multidimensional tables analysis. Fact matrices processing.

11.Multidimensional analytical data processing - On Line Analytical Processing (OLAP).

12. Interactive analytical reports creating based on OLAP data sources, tables and data

13. Using data warehouses in the National Labour Market Monitoring System.

14. Data models - definition and types. Entity relationship model. Relational and object-oriented models. Basic actions in relational algebra.

15. Relational model. Normalization of relational data model. Defining a relational database schema, relationships and prospects.Updating operations. Physical database organization.

- P.Beynon_Davies, "Systemy baz danych", Wydawnictwo Naukowo-Techniczne, Warszawa, 2003

- Richard Stones, Neil Matthew, Bazy Danych i MySQL, Helion, Gliwice 2003

- Microsoft SQL Server 2005 od środka: Mechanizm składowania danych, APN Promise, Warszawa 2007

- Microsoft SQL Server 2005 od środka: Zapytania w języku T-SQL, APN Promise, Warszawa 2006 Ewa Łuszczyk, Mirosława Koperkowska, Ćwiczenia z Access 2003 - wersja polska, wyd. Mikom, Warszawa 2004

- Ben Forta, Poznaj SQL w 10 minut, Intersoftland, Warszawa 2000

- Adam Majczak, SQL od podstaw, wyd. I, Translator s.c., Warszawa 2001

-Prague, Cary N., Access 2003 PL: biblia, Helion, Gliwice 2004

- Vidette Poe, Patricka Klauer, Stephen Brobst - Tworzenie hurtowni danych, WNT, Warszawa 1999 - Chris Todman

– Projektowanie hurtowni danych, WNT, Warszawa 2003

- Inmon W.H. Building the Data Warehouse, Third Edition

After the course the student is able to:

- present knowledge of data warehouse architecture and database management,

- distinguish between multidimensional data warehouse structure

- describe how to create a data warehouse

- create, modify and manage multi-dimensional data structures

- analyze information based on the parameters selection through multi-level drill data,

- build and modify the structure of queries to databases and measurement of fact table,

- process the data through the ETL tools in order to obtain structures for multidimensional analysis OLAP

- assess the correctness of the queries in SQL (Structured Query Language),

- interpret the databases queries.

SU05, SU06, SK01, SK03, SU04, SU03, SU02, SU01, SW03, SW02, SW01, SW04, SW05, SK02, SK04

Students receive evaluation grade based on the test conducted on last class. Test will take place at the computers on the base of conducted activities - participants will be challenged to create a data-analyzing processes.