SQL Server Business Intelligence: Courses & Background

1. SQL ServerBusiness Intelligence:Courses & Background By George Squillace New Horizons Great Lakes Husband, Dad, Coach, MCT, MCSE, MCDBA MCITP – Database Administration MCITP – SQL 2008 Business Intelligence Development MCITP – Enterprise Messaging Administrator on Exchange 2007 MCITP – Enterprise Administrator on Windows Server 2008

2. More confidently sell high margin courses that are not provided by many vendors. • Be able to respond to customer questions more rapidly. • Make better course recommendations. • Grow in your career skills. Why should you care about Business Intelligence?

3. From Wikipedia: Business intelligence (BI) is a businessmanagement term which refers to applications and technologies which are used to gather, provide access to, and analyze data and information about company operations. • Business intelligence systems can help companies have a more comprehensive knowledge of the factors affecting their business, such as metrics on sales, production, internal operations, and they can help companies to make better business decisions. • BI is about making better decisions. • ToolsàFactsàConclusionsàChanges. Definition: What is Business Intelligence (BI)?

4. Reporting Services (SSRS) • Analysis Services (SSAS) • Integration Services (SSIS) Each one of these categories involves a significant body of knowledge. Three Categoriesof Business Intelligence Functionality in Microsoft SQL Server

5. The BI capabilities within SQL Server are included with the price of Microsoft SQL Server. • Including BI capabilities with SQL Server has produced a lot of momentum for Microsoft and this opportunity should be capitalized on while there is time. • The BI development capabilities are configured within “BIDS”, or Business Intelligence Design Studio, which is Visual Studio with BI-specific project templates. Other Microsoft BI Facts of Interest

6. Observe: the complexity of each of these interfaces in the following screenshots! Next:We look at each of the three BI areas in greater detail

7. Report Designers use Visual Studio (BIDS) with specific report development templates to design reports. This development tool is called “Report Designer”. • Users and Administrators connect to a web application through a browser to browse reports and perform administration on Reporting Services. The web application is called “Report Manager”. • Example of available report features are: • Parameterization (where users can pick from available lists, available date ranges, etc. • Navigation features, such as Document Maps, Bookmarks, and Linking • Drill-down • Drill-through • Charting and Visualizations (Gauges, and Geographical Data) • Supported Report Export Formats are: • .pdf • Excel • Webpage/HTML • Text file • XML • Others Reporting Services (SSRS) Overview

8. Report Designer (BIDS)

9. Report Manager

10. SSIS succeeds “Data Transformation Services” (DTS) in SQL 2000. • The Bulk of SSIS Is Used for “ETL”. • Extract (connect to a data source) • Transform (change data along its path as necessary) • Load (send the data to a destination) • Very common requirement for database administrators and developers to perform ETL. • Specifically useful for data warehousing. • Supports almost any data source and data destination. SQL Server Integration Services (SSIS)Overview

11. Package Designer (BIDS)

12. The Point: Better Decision Making. • Usually involves a specially designed (often humongous) relational database, called a data warehouse, populated through SSIS to perform advanced data analysis and data mining. • The Data Warehouse requires population of a special database design called a Star Schema, which is a Fact Table connected to a number Dimension Tables. The warehouse is populated from other operational databases in the organization. This population requires significant effort. SSIS can be used to populate the data warehouse. • The Data Warehouse is used to create an “OLAP Cube” (or more than one Cube). • An example of supported analysis of an OLAP Cube would permit the rapid answer to the question,… • How many Male, Married, w/Children under 13, w/College Degree, > 40 years old, Paid by VISA, ordered Product X? Analysis Services Overview – Part 1

13. OLAP cubes are non-trivialto design and implement. • May involve the use of Excel and its Pivot Table capabilities but more likely an OLAP Client program. • Analysis Services may involve learning additional query languages such as: • Multi-Dimensional Expressions (MDX) • Data Mining Extensions (DMX) • XML for Analysis (XMLA) • Data Mining is its own large functional area within Analysis Services and is used for predictive analyses. • Again, this component is included with the purchase of Microsoft SQL Server but costs $10,000 or more for other platforms like Oracle Analysis Services Overview – Part 2

14. Example of a Cube

15. Cube Designer (BIDS)

16. Notice: Cube Browsing is normally done with a special front-end called an OLAP client. Excel (2007+) can serve as an OLAP client. The cube browsing capabilities shown here only provide a reality check for your cube design. CubeBrowsing in SSAS(in SQL Server Management Studio (SSMS))

17. The figure reveals one type of data mining usage called a Tree Viewer. Data Mining in SSAS – screenshot 1

18. The figure reveals another type of data mining usage called a Decision Tree. Data Mining in SSAS – screenshot 2

19. Next:Core BI Courses(The implementation courses)

20. 6234A Implementing and Maintaining SQL Server 2008 Analysis Services • Describe how SQL Server Analysis Services can be used to implement analytical solutions. • Create multidimensional analysis solutions with SQL Server Analysis Services. • Implement dimensions in an Analysis Services solution. • Implement measures and measure groups in an Analysis Services solution. • Querya multidimensional Analysis Services solution. • Customize an Analysis Services cube. • Deploy and Secure an Analysis Services database. • Maintaina multidimensional Analysis Services solution. • Implement a Data Mining solution. Analysis Services courses

21. 2792A Implementing and Maintaining SQL Server 2005 Integration Servicesor, 6235A Implementing and Maintaining SQL Server 2008 Integration Services • Describe SQL Server Integration Services and its tools. • Create an Integration Services package. • Implement control flow in an Integration Services package. • Implement data flow in an Integration Services package. • Implement logging in an Integration Services package. • Debug and implement error handling in an Integration Services package. • Implement checkpoints and transactions in an Integration Services package. • Deploy an Integration Services package. • Manage and secure an Integration Services package. Integration Services courses

22. 2793A Implementing and Maintaining SQL Server 2005 Reporting Servicesor, 6236A Implementing and Maintaining SQL Server 2008 Reporting Services • Describe SQL Server Reporting Services and its components. • Create a Reporting Services report. • Enhance a Reporting Services report. • Create and manipulate data sets. • Use report models to implement reporting for business users. • Configure report publishing and execution settings. • Implement subscriptions for reports. • Administer Reporting Services. • Implement custom Reporting Services applications. Reporting Services courses

23. SQL 2008 BI Exams • MCTS, 70-448 • MCITP, 70-452 (Very challenging…mine was 88 lengthy story problems, three full hours long) • Remember, the BI exams cover all three functional BI areas! • See also this diagram to understand the difference between Microsoft Exams and Microsoft Courses. • See also this diagram for more info about SQL Server certification tracks. SQL BI Exams

24. Microsoft SQL Server homepage • www.microsoft.com/sql • Microsoft Learning homepage • www.microsoft.com/learning • My website • Homepage • www.e-Squillace.com (Search button, Test taking tips link) • Diagrams • www.e-Squillace.com/tech/techdiagrams (several SQL and SQL BI diagrams) • Screenshot Gallery • www.e-Squillace.com/tech/screenshotgallery (look in the SQL screenshot category; a SQL BI category exists too) • SQL Server reference homepage • www.e-Squillace.com/tech/techreference/sql(a huuuuge amount of links) • SQL Server BI reference homepage • www.e-Squillace.com/tech/techreference/sqlbi/sqlbi.htm (also a huuuuge amount of links) • My Library Books (click on this link) • SSAS Step-by-Step • SSIS Step-by-Step • SSRS Step-by-Step • Microsoft Data Warehouse Toolkit • Data Mining with SQL Server 2008 • FastTrack to MDX • User Groups • West Michigan SQL Server User Group  (WMSSUG) • Detroit Area SQL ServerUserGroup(Detroit SSUG) Resources (for you and your clients) September 17, 2011

25. Microsoft released SQL 2008 R2 (the second SQL 2008 release) in April 2010. • As of yet there are no plans to release SQL 2008 R2-specific course versions. • Miscellaneous note: Microsoft offers free versions of SQL Server and Visual Studio with smaller feature sets. These are called Express Editions. What about R2?

26. Basic Windows Server Operating System knowledge • Background database experience • (Microsoft Access experience “counts”) • SQL Server administration and possibly programming experience • Course 2780 or 6231 • Course 2779 or 6232 • Basic query writing knowledge • Course 2778 Suggested BI Course Prerequisites

27. Learn & study? • Download? • Call a client with a recommendation or new opportunity? • Other? What Are Your Action Items?

28. Thank you for your time!

29. Implementation Courses • Hands-on oriented. • Procedural. • Students leave class having had success completing a related series of steps. • Design Courses • Asked during class, “What questions do I need answers to?” • What decisions must I make? • Conceptual, minimally or rarely procedural. • Student participation and involvement (when experienced) is a big value to other students. Implementation Courses v. Design Courses:

30. 2794A Designing a Business Intelligence Solution for the Enterprise Using Microsoft® SQL Server 2005 (2 days) • 2795A Designing an ETL Solution Architecture Using Microsoft® SQL Server 2005 Integration Services (2 days) • 2796A Designing an Analysis Solution Architecture Using Microsoft® SQL Server 2005 Analysis Services (2 days) • 2797A Designing a Reporting Solution Architecture Using Microsoft SQL Server 2005 Reporting Services (2 days) • Availability will be difficult SQL 2005 BI Design Courses(Unavailable for SQL 2008)