Integrating Business Intelligence with the Enterprise Peter Thanisch

1
Integrating Business Intelligence with the
EnterprisePeter Thanisch

2
Overview Monday

• Format Time Description
• Lecture 1000 - 1045 Overview and
  Introduction to Reporting Services
• Demo 1045 - 1130 Reports and Report
  design
• Lab 1215 - 1300 Practical session
• Creating a Basic Report
• Lab 1300 - 1345 Practical session
  Adding grouping, sorting and formatting
• Lab 1430 - 1515 Exercise on
  Reporting Services
• Lecture 1515 - 1600 Observations about
  design for
• Reporting

3
Overview Tuesday

• Format Time Description
• Lecture 1000 - 1045 Introduction to OLAP
  and Analysis Services
• Demo 1045 - 1130 Dimensional
  modelling
• Lab 1215 - 1300 Practical session
  Defining a data source and defining and
  deploying a cube
• Lab 1300 - 1345 Practical session
  Modifying measures, attributes and
  hierarchies
• Lecture 1430 - 1515 Observation about
  design for OLAP and Reporting
• Discussion 1515 - 1600 Wrap-up questions and
  feedback

4
Kinds of BI (from Wikipedia)

• Scorecarding, Business Performance Measurement,
  Customer Relationship Management, Data mining,
  Decision Support Systems, Forecasting, Document
  Management, Enterprise Management systems,
  Executive Information Systems, Knowledge
  Management, Mapping, Information visualization,
  and Dashboarding, Management Information Systems,
  Geographic Information Systems, Online Analytical
  Processing, multidimensional analysis,
  Statistics and Technical Data Analysis, Supply
  Chain Management/Demand Chain Management, Trend
  Analysis, Reporting, Web Mining, Text mining.
• (I left a lot of them out!!)

5
Background to my definition of Business
Intelligence

• That there are known knowns, there are things we
  know that we know,
• There are known unknowns, that is to say there
  are things that we now know, we don't know.
• But there are also unknown unknowns, there are
  things we do not know we don't know and each year
  we discover a few more of those unknown unknowns.
• Donald Rumsfeld

6
My definition of BI

• When somebody is about to make a decision, BI is
  what he/she uses to find out more about known
  unknowns, hopefully turning them into known
  knowns.
• As an added bonus, sometimes (but not very often)
  BI can actually make the decision maker aware of
  what had hitherto been an unknown unknown.

7
My definition of BI
BI comprises the facilities that allows a
decision maker to use his/her knowledge to
transform data into information that can be used
directly to inform the decision
8
Introduction to Reporting Services

• Microsoft SQL Server Reporting Services (SSRS).
• SSRS is a set of tools and interfaces for
  reporting. The tool set includes
• Development tools
• Report Designer, Model Designer, Report Builder
• Administration tools
• Report Manager

9
Report Server

• Report Server provides infrastructure for
  processing and rendering reports.
• Report server comprises
• (1) Web service exposes a set of programmatic
  interfaces that client applications can use to
  access report servers.
• (2) Windows service provides initialization,
  scheduling and delivery services, and server
  maintenance.

10
(No Transcript)
11
Example used in this presentation

• AdventureWorks
• Fictional company.
• Example tables, reports, OLAP cubes, etc.
  distributed by Microsoft with SQL Server 2005.
• Two separate databases
• AdventureWorks (OLTP)
• AdventureWorksDW

12
Adventure Works Cycles Business

• Adventure Works Cycles manufactures and sells
  metal and composite bicycles to North American,
  European and Asian commercial markets.
• Its base operation, in Bothell, Washington, has
  290 employees.
• Regional sales teams are located throughout their
  market base.
• In 2000, Adventure Works bought Importadores
  Neptuno, located in Mexico. Importadores Neptuno
  manufactures subcomponents for the Adventure
  Works Cycles product line. These subcomponents
  are shipped to the Bothell location for final
  product assembly.
• In 2001, Importadores Neptuno, became the sole
  manufacturer and distributor of the touring
  bicycle product group.
• Coming off a successful fiscal year, Adventure
  Works wants to broaden its market share by
  targeting sales to their best customers,
  extending their product availability through an
  external Web site, and reducing their cost of
  sales through lower production costs.

13
Sales and Marketing Scenario
14
Customers Types

• Individuals. These are consumers who buy products
  from the Adventure Works Cycles online store.
• Stores. These are retail or wholesale stores that
  buy products for resale from Adventure Works
  Cycles sales representatives.

15
AdventureWorks Tables

• The Customer table contains one record for each
  customer.
• The column CustomerType indicates whether the
  customer is an individual consumer (CustomerType
  'I') or a store (CustomerType 'S').
• Data specific to these customer types is
  maintained in the Individual and Store tables,
  respectively

16
DEMO Reporting
17
Example 1 Product Catalog

• Document map
• Search

18
(No Transcript)
19
Example 2 Company Sales

• matrix data region,
• drilldown

20
(No Transcript)
21
Example 3 Employee Sales Summary

• charts,
• tables,
• dynamic parameters

22
(No Transcript)
23
Example 4. Product Line Sales (Top Sales People)

• calculated fields,
• drillthrough

24
(No Transcript)
25
Example 5. Territory Sales Drilldown

• drilldown from summary data into detail data by
  showing/hiding rows

26
(No Transcript)
27
Example 6. Sales Reasons Comparisons

• use of an OLAP cube as a data source.
• multi-valued parameters

28
(No Transcript)
29
Example 7. Sales Order Detail

• Accessed from drilldown

30
(No Transcript)
31
Practical Session

• Creating a Basic Report

32
Creating a Basic Report

• Lesson 1 Creating a Report Server Project
• Lesson 2 Creating a Report
• Lesson 3
• Setting Up Connection Information
• Lesson 4 Defining a Query for the Report
• Lesson 5 Adding a Table Data Region
• Lesson 6 Previewing the Basic Report

33
(No Transcript)
34
Practical Session

• Adding grouping, sorting and formatting

35
Adding Grouping, Sorting, and Formatting to a
Basic Report

• Lesson 1 Opening the Tutorial Project
• Lesson 2 Adding a Group
• Lesson 3 Adding a New Column
• Lesson 4 Sorting the Detail Data
• Lesson 5 Adding a Subtotal
• Lesson 6 Applying Formatting and Style
• Lesson 7 Previewing the Updated Report

36
Exercise On Reporting Services

• In tutorial 1, you cut-and-paste the SQL that
  retrieves the data from the database.
• There is also a graphical query builder available
  in Report Designer
• Use the graphical query builder to construct a
  similar query.
• Make notes on any problems that you encounter

37
(No Transcript)
38
(No Transcript)
39
Observations about Design for Reporting
40
So what is a Report?

• A report is made up of three components
• Data specifies how to extract information from
  backend data sources and information on the
  structure of that data.
• Layout how the information is to be presented.
• Properties parameters, interactions, etc.
• Typically, the report is re-used at intervals.
• It picks up the current data from the data
  sources.
• The report definition may be stored in XML.
• An XML report template can be used to define a
  family of related reports.
• So far, interaction is very limited.

41
Requirements for Reporting

• Reporting is needed at various levels
• Strategic. The executives view.
• Tactical. E.g. information to support a marketing
  campaign.
• Operational. E.g. investigation of a suspected
  fraud.
• Spectrum of requirements
• Ad hoc sudden (and transient) need for
  particular information
• Permanent there is a long term requirement for
  the same information, e.g. for regulatory
  purposes.

42
The reporting cycle

• A business user needs to make a decision, but
  there are known-unknowns
• Business user asks the analyst to produce a
  report.
• Business user runs the report.
• On examining the output, there are additional
  known-unknowns, preventing the decision.
• The business user asks the analyst to change the
  report. (Iteration)

43
What makes Reporting Difficult? (1)

• In other design areas, the designer has more
  control.
• E.g. in entity-relationship modelling, the data
  modeller chooses the entities and models the
  relationship.
• In reporting, the report designer has to work
  with an existing information system that was not
  designed for his/her requirements

44
SQL for Product Line Sales

• SELECT TOP 5 C.LastName, C.FirstName,
  E.EmployeeID, SUM(SOH.SubTotal)
• AS SaleAmount
• FROM Sales.SalesPerson SP INNER JOIN
• HumanResources.Employee E ON
  SP.SalesPersonID E.EmployeeID INNER JOIN
• Person.Contact C ON E.ContactID
  C.ContactID INNER JOIN
• Sales.SalesOrderHeader SOH ON
  SP.SalesPersonID SOH.SalesPersonID INNER JOIN
• Sales.SalesOrderDetail SOD ON
  SOH.SalesOrderID SOD.SalesOrderID INNER JOIN
• Production.Product P ON SOD.ProductID
  P.ProductID INNER JOIN
• Production.ProductSubcategory PS
• ON P.ProductSubcategoryID PS.ProductSubcategor
  yID INNER JOIN
• Production.ProductCategory PC ON
  PS.ProductCategoryID PC.ProductCategoryID
• WHERE (PC.ProductCategoryID
  _at_ProductCategory) AND (PS.ProductSubcategoryID IN
  (_at_ProductSubcategory)) AND
• (SOH.OrderDate gt _at_StartDate) AND
  (SOH.OrderDate lt _at_EndDate)
• GROUP BY C.LastName, C.FirstName,
  E.EmployeeID, PC.ProductCategoryID,
  PS.ProductSubcategoryID
• ORDER BY SUM(SOH.SubTotal) DESC

45
What makes Reporting Difficult? (2)

• Donald Rumsfelds view of the world is VERY
  over-simplified.
• It is not just a question of turning known
  unknowns into knowns, etc.
• After the first report is given to the users,
  they realise that they were looking at the
  problem in the wrong way.