SQL Server Business Intelligence on Oracle

1. SQL Server Business Intelligence on Oracle Douglas McDowell douglas@solidqualitylearning.com

2. Microsoft Business Intelligence on Oracle Douglas McDowell douglas@solidqualitylearning.com

3. Poll • Are familiar with “business intelligence”? • Are familiar with Microsoft’s BI platform? • Did you know it rocks using Oracle data?!

4. In this Session • The Anatomy of BI • Enterprise ETL • OLAP, Data Mining and beyond • Presenting BI Information • Final Comments / Next Steps This session does not cover the Oracle BI Suite or include Microsoft/Oracle product comparisons

5. Goal for BI… Transform data into relevant information, and make it available to decision-makers fast enough for them to positively impact the business

6. The Anatomy of BI • Source Systems • Extraction Transformation & Loading (ETL) • Data Warehouse • Relational Database (RDBMS) • Multi-Dimensional Databases (OLAP) • Data Mining — Alerting Engine • Presentation • Enterprise Reporting • Analysis, Ad-hoc • Scorecards/Dashboards/Portals • Alert Delivery • Embedded Applications LOB ETL

7. The Anatomy of Microsoft BI • Source Systems • You name it!, Oracle, SQL Server, XML, legacy/flat file, Proprietary • ETL – SQL Server Integration Services • Data Warehouse • RDBMS: SQL Server or Oracle • OLAP and Data Mining: SQL Server Analysis Services • Alerting: SQL Server Notification Services • Presentation • Enterprise Reporting: SQL Server Reporting Services • Analysis, Ad-hoc: ProClarity, Excel, SQL Server Reporting Services • Scorecards/Dashboards/Portals: Microsoft Office Business Scorecard Manager, Microsoft Office SharePoint Server

8. Comprehensive and Complimentary Business Scorecarding (Business Scorecard Manager 2005) Scorecards, Analytics, Planning (PerformancePoint Server 2007) AdvancedAnalytics (ProClarity 6) Performance Management Applications Collaboration and Content (Office SharePoint Server 2007) End-user Analysis (Excel 2007) Analysis Analysis Services Reporting Reporting Services Integration Integration Services BI Platform SQL Server 2005 RDBMS

9. Before we go on…BI Development Studio • Complete, integrated tool for the development of BI applications • One tool, multiple technologies: Relational, OLAP, DM, SSIS, Reporting, Code, Web pages… • Enterprise software development environment • Integrated with Visual Studio • Team development, source control, versioning, developer isolation, resource independent coding • Development cycle lifetime support: Develop, Test, Deploy, Modify, Test… • Breakthrough ease-of-use

10. Data Warehouse Ready And before we go on…Relational Data Warehousing • Partitioned Tables and Indexes • Delivers enhanced scalability and concurrency • Simplifies data management • Online Index Operations • Database Snapshots and Snapshot Isolation Level • Readers don’t block Writers • Insert and Load improvements • T-SQL enhancements • VLDB – piecemeal backup/restore

11. Distributor Transactional Replication Subscribers Oracle Publishing • Designed specifically for Oracle Publishers • v8+ on any operating system • Administered like SQL Server, from SQL Server • No Oracle side software install necessary • Requires minimal knowledge of Oracle • Leverages existing SQL Server skills • Standard Transactional and Snapshot Publications • http://msdn2.microsoft.com/en-us/library/ms151229.aspx

12. Agenda • The Anatomy of BI • Enterprise ETL • OLAP, Data Mining and beyond • Presenting BI Information • Final Comments / Next Steps

13. DEMO: Integrating Your Data Integration Services in Action

14. Integration Services • Merge data from heterogeneous data stores: • Text files, Mainframes, Spreadsheets, Multiple RDBMS • Refresh data in data warehouses/data marts • High-speed load of data into online transaction processing (OLTP) and online analytical processing (OLAP) databases • Send status notifications on success/failure • Build BI into a data transformation process without the need for redundant staging environments • Automate data-administrative functions • Cleanse data before loading to remove errors • Fuzzy lookup, fuzzy matching • Handling of History – Slowly Changing Dimension (SCD) wizard

15. Breakthrough ETL Capabilities • Enterprise ETL platform • High performance • High scale • More trustworthy and reliable • Best in class usability • Rich development environment • Source control • Visual debugging of control flow and data • Great range of transforms out-of-the-box • Highly extensible • Custom tasks • Custom enumerations • Custom transformations • Custom data sources

16. Oracle Tip: Using the SQL Server Import and Export Wizard • Microsoft OLE DB Provider for Oracle does not support the Oracle BLOB, CLOB, NCLOB, BFILE, and UROWID data types, therefore the OLE DB source cannot extract data from tables that contain columns with these data types. http://msdn2.microsoft.com/en-us/library/ms141209.aspx

17. Agenda • The Anatomy of BI • Enterprise ETL • OLAP, Data Mining and beyond • Presenting BI Information • Final Comments / Next Steps

18. OLAP $6,745 Country Time France January Canada Australia Australia February February March April “For Australia, show me the Sales for February” May Sales Sales Budget Qty Cost Measures

19. DEMO: Analyzing Your Information SQL Server Analysis Services

20. Analysis Services • Powerful business information modeling • Cross platform data integration • Integrated Relational & OLAP views • Attribute-based dimensions • The best of MOLAP to ROLAP • Proactive caching • Data enrichment and advanced analytics • BI wizards • Key Performance Indicators & Perspectives • Real-time, high performance • Real-time data in OLAP Cubes • Very fast and flexible analytics • XML standards for Data Access and Web Services integration • Cost and time savings for customers integrating with other systems

21. UDM XML/A or OLE DB-OLAP High-level Architecture Analysis Services Spreadsheets SQL Server DataMart BI Front Ends DW Oracle Ad Hoc Reports Rich Reports SQL Server LOB Cache Dashboards

22. KPI Debugger in BIDSPreview server-side KPI designs

23. DEMO: Data Mining SQL Server Analysis Services

24. Data Mining • Ten Algorithms • Visualizations • Enterprise tools • Custom visualizations • Deep Integration • OLAP, Integration Services, and Reporting Integration • .NET programming model • Completely extensible framework

25. Decision Trees Clustering Naïve Bayes Time Series Sequence Clustering Neural Net Association Logistic Regression Complete Set of Data Mining Algorithms Linear Regression Text Mining

26. Data Mining Lift Chart DMM Lift ChartEasily determine which algorithm yield best results for you

27. Oracle Tip: Connecting to Oracle Across all platforms: • Install Oracle OCI, make sure its location is in the path • Restart AS service & its tools to pick up path changes • Use Oracle managed provider (.Net) in data source - this ensures platform compatibility (x86/x64/ia64) x86 only: • Install Oracle OCI, make sure its location is in the path • Restart AS service & its tools to pick up path changes • Use MSDAORA (Microsoft's OLE DB for Oracle) or Oracle managed provider (.Net) in your data source 

28. Agenda • The Anatomy of BI • Enterprise ETL • OLAP, Data Mining and beyond • Presenting BI Information • Final Comments / Next Steps

29. DEMO: Presenting Your Information SQL Server Reporting Services

30. Enterprise Reporting Capabilities • Scalable Server • Rich, enterprise reporting platform (static and interactive) • Multiple data sources with multiple delivery options • Scalable, manageable and embeddable Web Services architecture • Scheduling, Caching, Snapshots, more… • Advanced Authoring Tools • Visual Studio IDE • XML specification (RDL) • 3rd party extensibility • Strong Management • SOAP Web Service APIs • Report Manager portal • Extensible security model • Integration with SSAS, SSIS, management tools • End-user, Ad-hoc Reporting

31. Easily embed reporting functionality into applications Windows Forms (rich client) and Web Forms (ASP.NET) control Local processing mode (no server) or remote processing mode Can be used for complex custom security architectures Report Controls

32. Report Builder • Extending the Reach of Reporting Services • Ad-hoc Reporting for the End-User • 1-Click Install • With Report Builder: • Report off a Business Model • Modify a Report • Build a New Report • Report on Relational or OLAP data • Support for Oracle in SQL Server SP2

33. Oracle Tip:Defining Report Datasets Connection string example specifies Oracle database on server Oracle9 using Unicode The ServerName must match what is defined in the tnsnames.ora config file: Data Source="Oracle9"; Unicode="True“ http://msdn2.microsoft.com/en-us/library/ms365174.aspx

34. DEMO: Presenting Your Information Microsoft ProClarity

35. Microsoft Office Business Scorecard Manager empowers organizations with Advanced score-carding Deep contextual insight Collaborative group analysis and action End-user empowerment Extensible platform SQL Server 2005 provides Business Scorecard Manager with Centralized management of metrics and business logic Multi-dimensional scorecards KPI interoperability across end-user applications Data integration and security through UDM Business Scorecard Manager Articulate Goals Monitor Metrics Analyze Issues Collaborate and Act

36. BSM 1

37. BSM 3

38. BSM 2

39. PerformancePoint Server Align Strategy & Execution Accelerate Decision Making Enable Broad Performance Management Improve Business Performance • Microsoft Office PerformancePoint Server empowers organizations with • Advanced scorecarding • Powerful analytics and reporting • Synchronized planning and budgeting • Workflow driven forecasting • Robust financial consolidation • Microsoft Office PerformancePoint Server • Reaches all business users with familiar Office environment • Empowers business users to define and own business rules • Handles real-world complexity with model driven approach • Takes advantage of Microsoft SQL Server

40. Simplifies how decision makers receive information and gain insight into their business Provides organizations with the tools they need to conduct powerful analytic modeling Works with a company’s existing business and technical infrastructure and adapts to ongoing needs and changes Fully integrated with SQL Server, Business Scorecard Manager, SharePoint and Office applications ProClarity 6 Advanced Analytics for every decision maker

41. DEMO: Presenting Your Information Microsoft Office Excel

42. Excel 2007 • Analyze information with powerful and familiar tools that are easier to use • New PivotTable, PivotChart and data visualization capabilities • Formula based access to enterprise data integrates BI with spreadsheets • Harness the power of SQL Server Analysis Services directly from within Excel

43. Excel Services • Save spreadsheets to the server and share them over the Web • Broadly share spreadsheets securely and maintain control with document management and auditing capabilities • Server based spreadsheet calculation with pure HTML (zero foot-print) interactive Excel experience

44. Excel Services Screen Excel Services Zero-footprint, interactive, browser access to spreadsheets

45. Office SharePoint Server 2007 • Secure, manageable business data, and hosted documents • Report Center: One place for all reports • Dashboard site templates, Filter Web Parts, KPI Web Parts • Integrate BI within the portal: Enterprise Content Management, collaboration, search and business process

46. SharePoint 1 Report CenterCentralize storage of business reports

47. SharePoint 2 Dashboards and Web PartsEasily create powerful BI portals

48. Agenda • The Anatomy of BI • Enterprise ETL • OLAP, Data Mining and beyond • Presenting BI Information • Final Comments / Next Steps

49. Microsoft BI: Faster Return on Information • Microsoft offers a Complete and Integrated BI Offering: • BI Platform (SQL Server) • End User Tools (Office) • Analytic Applications (Office) • Intelligence where users want it: • Manage data “once” in the platform • Ease of use and familiarity of Office for interaction • Ready for the Enterprise: • Server tools built for Enterprise scale • Priced and packaged so Enterprises can afford it!

50. Trustworthy Computing Secure byDefault Secure by Design Secure inDeployment Trustworthy Computing is built on three pillars: • Security • Privacy • Reliability http://www.microsoft.com/twc