SQL Server 2008 for Business IntelligenceUTS Short Course

Specializes in

C# and .NET (Java not anymore)

TestingAutomated tests

Agile, ScrumCertified Scrum Trainer

Technology aficionado • Silverlight• ASP.NET• Windows Forms

Peter Gfader

Attendance You initial sheet

Hands On Lab You get me to initial sheet

Homework

Certificate At end of 5 sessions If I say if you have completed successfully

Admin Stuff

Course Timetable & Materials

http://www.ssw.com.au/ssw/Events/2010UTSSQL/

Resources

http://sharepoint.ssw.com.au/Training/UTSSQL/

Course Website

Course OverviewSession

Date Time Topic

1Tuesday14-09-2010

18:00 - 21:00

SSIS and Creating a Data Warehouse

2Tuesday21-09-2010

18:00 - 21:00

OLAP – Creating Cubes and Cube Issues

3Tuesday28-09-2010

18:00 - 21:00

Reporting Services

4Tuesday05-10-2010

18:00 - 21:00

Alternative Cube Browsers

5Tuesday12-10-2010

18:00 - 21:00

Data Mining

Cubes

What is a cube? Measures/Facts? Dimensions?

• Hierarchies? Time Dimensions? Cube Browser?

Why?

Last week(s)

1. Where does the cube live?

2. Why do we need to provide "Impersonation Information" in our Data Source?

3. What is a dimension hierarchy?

Homework

1. What is SQL Reporting Services?

2. Steps in Creating a Report

3. Demo: Creating a Report

4. Hands on Lab

Session 3: Tonight’s Agenda

Why are we doing this?

Business intelligence (BI) is a broad category of applications and technologies for gathering,

storing, analyzing, and providing access to data to help enterprise users make better business

decisions.

Answer questions

Business Intelligence Defined

The plan

1. Create Data Warehouse

2. Copy data to data warehouse

3. Create OLAP Cubes

4. Create Reports

5. Do some Data Mining Discovering a Relationship that was not obvious Predict future events (e.g. targeting and

forecasting)

Step by step to BI

SSRS

Reporting platform

Traditional Interactive reports

Scalable and manageable server infrastructure

Integrated with

SharePoint Office applications Browser and other familiar tools

Single platform and tools for all types of structured data

Relational Hierarchical Multidimensional

What is SQL Server Reporting?

SQL Server BI Platform

Analysis ServicesOLAP & Data Mining

IntegrationServices

ETL

SQL ServerRelational Engine

Reporting Services Ma

na

ge

men

t Too

ls

De

vel

op

me

nt

Too

ls

Reporting Services is an open and extensible platform supporting the authoring, management and delivery of rich, interactive reports to the entire enterprise.

Reporting Lifecycle

Management DeliveryAuthoring

Report Authoring

Click icon to add picture

Reports are defined in Report Definition Language (RDL), a documented XML schema

Use Microsoft or 3rd party tools that support RDL

Create single reports from multiple data sources(SQL, OLE DB, ODBC, Oracle, and .NET data providers)

Report Authoring

More info on the RDL spec: www.microsoft.com/sql/reporting

Data regions Tablix (New in SQL 2008)

• Table• List (like Access)• Matrix

Chart Gauge

Subreports Images

Custom Controls

Report Authoring - Controls

Generalized layout report item

Grouped and Detail data

Tablix

Use a table to display detail data

Organize the data in row groups, or both.

The Table template contains three columns with a table header row and a details row for data.

Tablix - Table

Use a matrix to display aggregated data summaries

Grouped in rows and columns, similar to a PivotTable or crosstab.

The number of rows and columns for groups is determined by the number of unique values for each row and column groups

Tablix - Matrix

Use a list to create a free-form layout. You are not limited to a grid layout, but can place fields freely inside the list.

You can use a list to design a form for displaying many dataset fields or as a container to display multiple data regions side by side for grouped data.

Tablix - List

Charts

Charts

Charts

Gauge

Interactive reports

Start in Report Builder

Get the data you want

In report properties choose Allow users to drill to this report option

Optionally customize in Report Designer

Drill-through Reports

Document maps

Collapse / Expand

Actions

Interactivity

Report Management

Click icon to add picture

Report definitions, folders, and resources are published

and managed in a reporting web service

Managed reports can be executed either on-demand or

via schedule and can be cached for consistency and performance

Scalable & Extensible server architecture

Report Management

SQL Server Reporting Architecture

Report Manager

Configuration Manager

Report Delivery

Click icon to add picture

Traditional (paper) and interactive (web) reports

On-demand (“pull”) or event-based (“push”) delivery

Choose from multiple formats (HTML, Excel, PDF, XML, Word)

Deliver reports to many devices (e-mail, file share, etc.)

Ad-hoc Reporting

Report Delivery

SSRS and SSAS – Working Together

Detail reports

Standard reportsAd hoc reports

SSAS SSRS

– Intuitive reporting– Interactive analysis– High performance– Powerful calculations

– Detail reporting– Standard reporting– Ad hoc reporting– Flexible delivery

SSAS and Reporting Services

DB1

DB2

DB3

DW

Data Layer SSAS

UDM

`

Interactive reporting

`

Standard reporting

Presentation Layer

`

Ad hoc reporting

ETL

multiple data sources supported

• Dimensional schema = intuitive reporting• SSAS for better performance and business metrics• UDM serves many reporting needs

UnifiedDimensional

Model

Report Builder

Report Builder v2

It doesn’t ship with SQL 2008

It is part of SP1

http://www.microsoft.com/downloads/details.aspx?familyid=DBDDC9B6-6E19-4D4B-9309-13F62901B0D5&displaylang=en

It’s fantastic but….

Only for SQL Server R2

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33&displaylang=en

Report Builder v3

Report generation

Click icon to add picture

Server side

Server side

Reportviewer Control in "local mode"

ASP.NET Windows Forms Silverlight WPF

Client side

Client-side

All about the ReportViewer control

http://www.gotreportviewer.com/

Community site with reports to learn from

http://www.reportsurfer.com/

Resources

SQL Server UG

6/10/2010 - lunch time - City

Delivering BI to the Masses at Microsoft Using CBI (Consolidated BI)

Top 10 Challenges (Sanjay Soni)

http://www.sqlserver.org.au/

Usergroups

1. What is Reporting Services?

2. Steps in Creating a Report

3. Demo: Creating a Report

4. Hands on Lab

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx

Summary

3 things…

PeterGfader@ssw.com.a

u

http://blog.gfader.com/

twitter.com/peitor

Thank You!

Gateway Court Suite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA

ABN: 21 069 371 900

Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105

info@ssw.com.auwww.ssw.com.au