====== BUSINESS INTELLIGENCE LAB (2017/2018) ====== 2nd Module of Business Performance Analysis (Analisi delle prestazioni aziendali) Teacher: * **Anna Monreale** * KDD Laboratory, Università di Pisa ed ISTI - CNR, Pisa * [[http://kdd.isti.cnr.it/homes/monreale/]] * [[anna.monreale@unipi.it]] * Office hours: by appointment, Room 374/DO, Dept. of Computer Science. * Telephone +39-050-2213119 Teaching assistant: * **Roberto Pellungrini** * KDD Laboratory, Università di Pisa and ISTI - CNR, Pisa * [[roberto.pellungrini@di.unipi.it]] * Office hours: Wednesday 14:30-16:30, Room 384/DO, Dept. of Computer Science. * Telephone +39-050-2212728 ====== News ===== * ** [02-01-2018] The results of the second midterm are online: {{ :mds:lbi:results-19122017.pdf | Results 2nd midterm}}. Note that in the file you can find the date of the oral exam. ** * [22-11-2017] Exam Rules: a) Students may do the second mid-term even if they did have the first mid-term. b) Students having at least one mid-term exam may do only one part of the written exam in the exam sessions. * [13-11-2017] Instructions for the SSAS project in the Lecture of today: to avoid conflicts in deployment/process follow this steps once the solution is opened: (1) rename the project as _foodmart; (2) from project properties select 'Deployment', then rename the database as _foodmart; (3) click on the button "show all files" just above "Solution explorer" right click on "view code" on the .database file that is visualized, and then change the ID from ruggieri_foodmart into _foodmart, and finally save the file; (4) change the credentials of connection to database on SQL Server. As an alternative solution you may[[ http://technet.microsoft.com/en-us/library/ms175630.aspx#bkmk_newusingwizard|import the project]] from the SSAS server and rename it as _foodmart (step 4 is still necessary). * [24-10-2017] Instructions for using the Microsoft Software in the Computers of the LAB: * Activate the Internet connection * Start the Microsoft Software * Signin with your login of Alice: ursarname@UNIPI.it and password * [09-10-2017] In the java program XMLSample.java please substitute the two URL of the files in the main function with : "http://kdd.isti.cnr.it/sites/kdd.isti.cnr.it/files/census_elements.xml" "http://kdd.isti.cnr.it/sites/kdd.isti.cnr.it/files/census_row_0.xml" otherwise you will get an error. * [09-10-2017] This week the office hours will be on Friday 13, October at 9-11. Please, send an email within Thursday 12th if you want to come to my office on Friday. ====== Hours and Rooms ====== **Classes ** Lessons will be held at: Polo Didattico "L. Fibonacci", Via F. Buonarroti 4, Pisa. ^ Day of Week ^ Hour ^ Room ^ | Monday | 09:00 - 11:00 | LAB M | | Tuesday| 11:00 - 13:00 | LAB M | **Office hours ** Prof. Anna Monreale: by appointment, Dept. of Computer Science. ====== Learning Material ====== ===== Slides & Registration of the classes ===== * The slides used in the course will be inserted in the calendar after each class. * Registration of each lecture will be published in the calendar after each class ===== Past Exams ===== * {{ :mds:lbi:2016midterm1text.pdf |2016/17 text}}, {{ :mds:lbi:2015fallmidterm1text.pdf | 2015/16 text}} and {{ :mds:lbi:2015wintermidterm1.zip | 2015/16 solution}}, {{:mds:lbi:2015midterm1text.pdf | 2014/15 text}} and {{ :mds:lbi:2015midterm1.zip |2014/2015 solution}}, {{ :mds:lbi:2014midterm1text.pdf | 2013/14 text}},{{ :mds:lbi:2013midterm1.pdf | 2012/13 text }} and {{ :mds:lbi:2013midterm1.zip |2012/13 solution}}. ===== Software===== * [[http://www.eclipse.org/downloads/ | Eclipse IDE for Java developers]] and [[http://www.oracle.com/technetwork/java/javase/downloads/index.html|Java]] * SQL Server 2016 Developer Edition: - Mandatory: [[https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms | SQL Server 2016 Management Studio]] and [[https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt | SQL Server 2016 Data Tools]] (no need to install Microsoft Visual Studio 2015! choose option 4 "Download SSDT as an ISO image") - Optional (not recommended on laptops): SQL Server 2016 Developer Edition can be [[https://www.microsoft.com/en-us/sql-server/application-development |downloaded from Microsoft]] or can be [[http://msdnaa.di.unipi.it/ | downloaded from MSDN-AA]]. During installation, set the following [[http://pages.di.unipi.it/ruggieri/teaching/_lbi/sql2016server.png| options]] as a minimum. * Microsoft Excel * [[https://powerbi.microsoft.com/it-it/desktop/| Power BI Desktop]] ===== F.A.Q. ===== * [[http://www.sid.unipi.it/polo2/2015/03/26/connessione-alle-reti-wifi/ | Connection to wi-fi]] * [[http://www.sid.unipi.it/polo2/studenti/ | F.A.Q.s about the labs]] ====== Class calendar - (2017-2018) ====== ^ ^ Day ^ Topic ^ Slides ^ Registration ^ Data/Software ^ References ^ | | 18.09 09:00-11:00 | Canceled - The lesson will be recovered on **05 October 2017 9-11 Room M** | | | | | | | 19.09 11:00-13:00 | Canceled - The lesson will be recovered on **19 October 2017 9-11 Room M** | | | | | |1. | 25.09 09:00-11:00 | Introduction. File data access. Representation formats: CSV, FLV, ARFF, XML| {{ :mds:lbi:lbi.01.introduction.pdf | Introduction }} {{ :mds:lbi:lbi.02.bi_architectures.pdf |BI Architectures}} {{ :mds:lbi:lbi.03.file_data_access.pdf | File Data Access}} | [[http://lds.di.unipi.it/apa/video/20170925.flv|Video 25/09/2017]] For accessing the video you need login and password that we will send you once we get your email for the mailing list| {{ :mds:lbi:data1.zip | Sample data}} |-** BI technology:** [[https://cacm.acm.org/magazines/2011/8/114953-an-overview-of-business-intelligence-technology/fulltext | An Overview of Business Intelligence Technology]] - **File access:** {{ :mds:lbi:filesystem.pdf | File System Interface}} - **File Formats:** [[http://www.stat.auckland.ac.nz/~paul/ItDT | Introduction to data technologies(Chps. 5, 6)]], [[http://weka.wikispaces.com/ARFF+(stable+version)|Weka ARFF Format]], [[http://weka.wikispaces.com/XRFF|XRFF Format]] | | |26.09 11:00-13:00 |CANCELED - The lesson will be recovered on **26 October 2017 9-11 Room M** | | | | | |2.|02.10 09:00-11:00 |Java and Eclipse recap. Lab practice: maximal subsequence |{{ :mds:lbi:lbi.04-part1.pdf | Java Recap}} |[[http://lds.di.unipi.it/apa/video/20171002-1part.flv|Video 02/10/2017 - First Part]] [[http://lds.di.unipi.it/apa/video/20171002-2part.flv|Video 02/10/2017 - Second Part]] | |**- Basic Java programming:** [[http://horstmann.com/corejava.html | Core Java Vol. 1: Fundamentals. Chpts. 1-6,11-13]]**- Text/binary/xml file data access in Java:**[[http://ptgmedia.pearsoncmg.com/images/9780137081608/samplepages/013708160X.pdf|Core Java Vol. 2: Advanced Features. Chpts. 1,2]] **- Basic usage of the Eclipse IDE:** Help contents from the Help menu| |3.|03.10 11:00-13:00 |OOP in Java. Lab practice: relational algebra| | [[http://lds.di.unipi.it/apa/video/20171003.flv|Video 03/10/2017]] |{{ :mds:lbi:sample.zip |}} | | |4.|05.10 09:00-11:00 |File data access in Java. Lab practice: CSV2ARFF file format conversion. | {{ :mds:lbi:lbi.04-complete.pdf | Java Recap - Complete}} | [[http://lds.di.unipi.it/apa/video/20171005.flv|Video 05/10/2017]] |{{ :mds:lbi:lbi.utils.zip |}} | | |5.|09.10 09:00-11:00 |Lab practice: XML2CSV/CSV2JSON file format conversion | | [[http://lds.di.unipi.it/apa/video/20171009.flv|Video 09/10/2017]] |{{ :mds:lbi:lbi.file.format.zip |}} {{ :mds:lbi:lbi.relational.zip |}}| | |6.|10.10 11:00-13:00 |RDBMS access protocols: ODBC, OLE DB, JDBC. JDBC Programming. | {{ :mds:lbi:lbi.05.relational_data_access.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171010.flv|Video 10/10/2017]] |{{ :mds:lbi:lbi.jdbc.zip |}} | | |7.|16.10 09:00-11:00 |Lab practice: stratified sampling in JDBC. | | [[http://lds.di.unipi.it/apa/video/20171016.flv|Video 16/10/2017]] | | | |8.|17.10 11:00-13:00 |Introduction to SQL Server. ETL tools: SQL Server Integration Services (SSIS).| {{ :mds:lbi:lbi.06.sqlserver.pdf |}} {{ :mds:lbi:lbi.07.etlandssis.pdf |}} |[[http://lds.di.unipi.it/apa/video/20171017-1part.flv|Video 17/10/2017 first part]] [[http://lds.di.unipi.it/apa/video/20171017-2part.flv|Video 17/10/2017 second part]] | |-**SQL Server**: Management Studio: [[https://docs.microsoft.com/it-it/sql/ssms/sql-server-management-studio-ssms|documentation]] and [[https://docs.microsoft.com/it-it/sql/ssms/tutorials/tutorial-sql-server-management-studio| tutorial]]. - **SQL Server Data Tools**:[[https://msdn.microsoft.com/library/hh272686(v=vs.103).aspx|documentation]]: 1) [[http://msdn.microsoft.com/en-us/library/ms141026.aspx|SSIS documentation]]; 2)[[http://technet.microsoft.com/en-us/library/jj720568.aspx|SSIS tutorial]]; [[http://pages.di.unipi.it/ruggieri/teaching/_lbi/resources/SSIS2014.pdf| Professional SQL Server 2014 Integration Services, Chps. 1-7]]. | |9.|19.10 09:00-11:00 |SSIS samples and lab practice: pipeline, sampling. | | [[http://lds.di.unipi.it/apa/video/20171019.flv|Video 19/10/2017 ]] | {{ :mds:lbi:lbi.jdbc.sampling.zip |}}| | |10.|23.10 09:00-11:00 |SSIS samples and lab practice: surrogate keys, slowly changing dimensions.| | [[http://lds.di.unipi.it/apa/video/20171023-1part.flv|Video 23/10/2017 first part]] [[http://lds.di.unipi.it/apa/video/20171023-2part.flv|Video 23/10/2017 second part]] | | | |11.|24.10 11:00-13:00 |SSIS samples and lab practice: slowly changing dimensions and running total.| | [[http://lds.di.unipi.it/apa/video/20171024.flv|Video 24/10/2017 ]] | {{ :mds:lbi:2016ssis.zip |}}| | |12.|26.10 09:00-11:00 |Lab practice.| | |{{ :mds:lbi:solmidterm12016.zip |}} | | |13.|06.11 09:00-11:00 |Canceled | | | | | |14.|07.11 11:00-13:00 |Datawarehousing and OLAP recap. Data cubes, analytic SQL, and materialized views in SQL Server. | {{ :mds:lbi:lbi.08.dwandolap.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171107-1.flv|Video 07/11/2017-First Part]] [[http://lds.di.unipi.it/apa/video/20171107-2.flv|Video 07/11/2017-Second Part]] |{{ :mds:lbi:lbi.08.afdemo.sql.zip |}} |For DW and OLAP: [[http://pages.di.unipi.it/ruggieri/teaching/dsd/|Decision support databases]] course lecture notes. | |15.|13.11 09:00-11:00 |OLAP with SQL Server Analysis Services (SSAS): data source views, dimensions, hierarchies. Data cubes. |{{ :mds:lbi:lbi.09.ssas.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171113.flv|Video 13/11/2017]] |{{ :mds:lbi:monreale_foodmart.zip |}} **Notice:** Please read the instructions in the Section NEWS! | **1) SSAS (olap):** [[http://msdn.microsoft.com/en-us/library/bb522607.aspx|documentation]]; 2) S. Harinath et al. {{ :mds:lbi:ssas2012ch456.pdf |Professional Microsoft SQL Server Analysis Services 2012 with MDX and DAX, Wrox publisher, 2012. Chps. 4-6}}. | |16.|14.11 11:00-13:00 |OLAP explorative data analysis with Pivot Tables in Excel. | | [[http://lds.di.unipi.it/apa/video/20171114.flv|Video 14/11/2017]] | | **Pivot Tables in Excel:** G. Harvey. {{ :mds:lbi:pivottable2013bookviichpt2.pdf |Excel 2013 All-in-One For Dummies, 2013. Chp. VII-2}}. | |17.|20.11 09:00-11:00 |Calculated metrics. Parent-child hierarchies. ROLAP and MOLAP in SSAS. MDX queries. | |[[http://lds.di.unipi.it/apa/video/20171120.flv|Video 20/11/2017]] | {{ :mds:lbi:foodmartexplorative.xlsx |}} | **MDX:** 1) [[http://msdn.microsoft.com/en-us/library/bb500184.aspx|documentation]] and a [[https://www.mssqltips.com/sqlservertip/3129/order-and-sort-with-mdx-in-sql-server-analysis-services/|useful guide on ordering]]; 2) S. Harinath ed al. {{ :mds:lbi:ssas2012ch3.pdf |Professional Microsoft SQL Server Analysis Services 2012 with MDX and DAX, Wrox publisher, 2012. Chp. 3.}} | |18.|21.11 11:00-13:00 |Practice with MDX. | | [[http://lds.di.unipi.it/apa/video/20171121.flv|Video 21/11/2017]] | {{ :mds:lbi:lbi.09.mdxsample.mdx.zip |}} | | |19.|27.11 09:00-11:00 |Practice with MDX. | | [[http://lds.di.unipi.it/apa/video/20171127.flv|Video 27/11/2017]] | {{ :mds:lbi:lbi.09.mdxpractice.mdx.zip |}} | | |20.|28.11 11:00-13:00 |Practice with MDX. Reporting with [[https://powerbi.microsoft.com/it-it/desktop/|Power BI Desktop]] | {{ :mds:lbi:lbi.10.powerbi.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171128.flv|Video 28/11/2017]] | {{ :mds:lbi:lbi.09.mdxpractice.mdx.zip |}} {{ :mds:lbi:practicepast-exams.zip |}} | | |21.|04.12 09:00-11:00 |Data Mining pre-processing and classification in Weka. | {{ :mds:lbi:lbi.11.weka.pdf |}} | [[http://lds.di.unipi.it/apa/video/2017-12-04.flv|Video 04/12/2017]] | {{ :mds:lbi:weka.3.7.9.light.zip |}}{{ :mds:lbi:wekapatch.zip |}} | Weka: [[https://www.cs.waikato.ac.nz/ml/weka/downloading.html| download ]] and [[https://www.cs.waikato.ac.nz/ml/weka/documentation.html| documentation]]. | |22.|05.12 11:00-13:00 |Data Mining classification in Weka. Practice | {{ :mds:lbi:lbi.12.practice.pdf |}} {{ :mds:lbi:lbi.12.practicesolution.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171205.flv|Video 05/12/2017]] | {{ :mds:lbi:ee.zip |}}| | |23.|11.12 09:00-11:00 | All lectures at Pisa University are canceled. The lesson will be recovered on **14 December 2017 9-11 Room M** | | | | | |24.|12.12 11:00-13:00 |Association Rules in Weka and API from Java | {{ :mds:lbi:lbi.13.associationrules.pdf |}}{{ :mds:lbi:lbi.14.wekaapi.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171212.flv|Video 12/12/2017]] |{{ :mds:lbi:wekaapisample.zip |}} | | |25.|14.12 09:00-11:00 | Weka API and Practice |{{ :mds:lbi:meta-cost-classification.pdf |}} | [[http://lds.di.unipi.it/apa/video/20171214.flv|Video 14/12/2017]] |{{ :mds:lbi:exercises.zip |}} | | ====== Exams ====== ===== Mid-term exams ===== **Rule: ** Students may do the second mid-term even if they did have the first mid-term. ^ ^ Date ^ Hour ^ Room^ Notes ^ Marks ^ | |31.10.2017 | 11:00 - 14:00 | H | | {{ :mds:lbi:results-2017-names.pdf | Results 1st midterm}}| | |19.12.2017 | 14:00 - 18:00 | H | | {{ :mds:lbi:results-19122017.pdf | Results 2nd midterm}}| ===== Exam sessions ===== **Rule:** Students having at least one mid-term exam may do only one part of the written exam in the exam sessions. ^ Session ^ Date ^ Time ^ Room ^ Notes ^ Marks ^ | 1. | 11 Jan 2018 |09:00 | C1 |Oral exam for students who passed the mid-term exam. https://esami.unipi.it/ | | | 2. | 17 Jan 2018 |14:00 | H | Witten Exam. https://esami.unipi.it/ | | | 3. | 02 Feb 2018 |09:00 | H | Witten Exam. https://esami.unipi.it/| | | 4. | 11 Jun 2018 |14:00 | H | Witten Exam. https://esami.unipi.it/| | | 5. | 02 Jul 2018 |14:00 | H | Witten Exam. https://esami.unipi.it/| | | 6. | 05 Sep 2018 |09:00 | H | Witten Exam. https://esami.unipi.it/| | =====Extra sessions A.A. 2016/17===== ^ Date ^ Time ^ Room ^ Notes ^ Results ^ | 31.10.2017 |11:00 - 15:00 | H | | | | 05.04.2018 |09:00 - 13:00 | I | Witten Exam. https://esami.unipi.it/| | =====Past Editions ===== [[LBI 2017-2018]]