Transcript Integrating Business Intelligence with the Enterprise

Integrating Business
Intelligence with the Enterprise
Peter Thanisch
Overview: Monday
Format
Time
Lecture 10:00 - 10:45
Demo
Lab
10:45 - 11:30
12:15 - 13:00
Lab
13:00 - 13:45
Lab
14:30 - 15:15
Lecture 15:15 - 16:00
Description
Overview and Introduction to
Reporting Services
Reports and Report design
Practical session:
Creating a Basic Report
Practical session: Adding
grouping, sorting and formatting
Exercise on Reporting Services
Observations about design for
Reporting
Overview: Tuesday
Format
Lecture
Time
10:00 - 10:45
Description
Introduction to OLAP and
Analysis Services
Demo
10:45 - 11:30 Dimensional modelling
Lab
12:15 - 13:00 Practical session: Defining a
data source and defining and
deploying a cube
Lab
13:00 - 13:45 Practical session: Modifying
measures, attributes and
hierarchies
Lecture
14:30 - 15:15 Observation about design for
OLAP and Reporting
Discussion 15:15 - 16:00 Wrap-up: questions and
feedback
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!!)
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
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.
My definition of BI
Decision Maker
Possesses
Knowledge
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
Stores data
BI
Report
Wants to
make
decisions
Computer
Returns
Information
Extracts
Data
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
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.
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
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.
Sales and Marketing Scenario
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.
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
DEMO: Reporting
Example 1: Product Catalog
• Document map
• Search
Example 2: Company Sales
• matrix data region,
• drilldown
Example 3: Employee Sales
Summary
• charts,
• tables,
• dynamic parameters
Example 4. Product Line Sales
(Top Sales People)
• calculated fields,
• drillthrough
Example 5. Territory Sales
Drilldown
• drilldown from summary data into detail
data by showing/hiding rows
Example 6. Sales Reasons
Comparisons
• use of an OLAP cube as a data source.
• multi-valued parameters
Example 7. Sales Order Detail
• Accessed from drilldown
Practical Session
Creating a Basic Report
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
SalesOrderHeader (Sales)
SalesOrderID
RevisionNumber
OrderDate
DueDate
ShipDate
Status
OnlineOrderFlag
SalesOrderNumber
PurchaseOrderNu...
AccountNumber
CustomerID
ContactID
SalesPersonID
Contact (Person)
ContactID
NameStyle
Title
FirstName
MiddleName
LastName
Suffix
EmailAddress
EmailPromotion
Phone
PasswordHash
PasswordSalt
AdditionalCont...
Employee (HumanResourc
EmployeeID
NationalIDNumber
ContactID
LoginID
ManagerID
Title
BirthDate
MaritalStatus
Gender
HireDate
SalariedFlag
Practical Session
Adding grouping, sorting and
formatting
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
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
Observations about Design for
Reporting
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.
Requirements for Reporting
• Reporting is needed at various levels:
– Strategic. The executive’s 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.
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)
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
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.ProductSubcategoryID INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID =
PC.ProductCategoryID
WHERE
(PC.ProductCategoryID = @ProductCategory) AND
(PS.ProductSubcategoryID IN (@ProductSubcategory)) AND
(SOH.OrderDate > @StartDate) AND (SOH.OrderDate < @EndDate)
GROUP BY C.LastName, C.FirstName, E.EmployeeID, PC.ProductCategoryID,
PS.ProductSubcategoryID
ORDER BY SUM(SOH.SubTotal) DESC
What makes Reporting Difficult? (2)
• Donald Rumsfeld’s 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.