sql server business intelligence courses background n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server Business Intelligence: Courses & Background PowerPoint Presentation
Download Presentation
SQL Server Business Intelligence: Courses & Background

Loading in 2 Seconds...

play fullscreen
1 / 30

SQL Server Business Intelligence: Courses & Background - PowerPoint PPT Presentation

  • Uploaded on

SQL Server Business 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SQL Server Business Intelligence: Courses & Background' - latif

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql server business intelligence courses background

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

why should you care about business intelligence

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?
definition what is business intelligence bi

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)?
three categories of business intelligence functionality in microsoft sql server

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
other microsoft bi facts of interest

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
reporting services ssrs overview

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
sql server integration services ssis overview

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
analysis services overview part 1

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
analysis services overview part 2

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
cube browsing in ssas in sql server management studio ssms


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))
analysis services courses

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
integration services courses

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
reporting services courses

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
sql bi exams

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
resources for you and your clients

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

what about r2

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?
suggested bi course prerequisites

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
what are your action items

Learn & study?

  • Download?
  • Call a client with a recommendation or new opportunity?
  • Other?
What Are Your Action Items?
implementation courses v design courses

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:
sql 2005 bi design courses unavailable for sql 2008

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)