Integrating Business Intelligence with the Enterprise Peter Thanisch

1. Integrating Business Intelligence with the EnterprisePeter Thanisch

2. Overview: Monday Format Time Description Lecture 10:00 - 10:45 Overview and Introduction to Reporting Services Demo 10:45 - 11:30 Reports and Report design Lab 12:15 - 13:00 Practical session: Creating a Basic Report Lab 13:00 - 13:45 Practical session: Adding grouping, sorting and formatting Lab 14:30 - 15:15 Exercise on Reporting Services Lecture 15:15 - 16:00 Observations about design for Reporting

3. Overview: Tuesday Format Time Description Lecture 10:00 - 10:45 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

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. Computer Decision Maker Possesses Knowledge Stores data BI Report Wants to make decisions Returns Information Extracts Data 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.

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

19. Example 2: Company Sales • matrix data region, • drilldown

21. Example 3: Employee Sales Summary • charts, • tables, • dynamic parameters

23. Example 4. Product Line Sales (Top Sales People) • calculated fields, • drillthrough

25. Example 5. Territory Sales Drilldown • drilldown from summary data into detail data by showing/hiding rows

27. Example 6. Sales Reasons Comparisons • use of an OLAP cube as a data source. • multi-valued parameters

29. Example 7. Sales Order Detail • Accessed from drilldown

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

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

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 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.

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.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

45. 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.