1 / 53

SQL Server Business Intelligence on Oracle

SQL Server Business Intelligence on Oracle. Douglas McDowell douglas@solidqualitylearning.com. Microsoft Business Intelligence on Oracle. Douglas McDowell douglas@solidqualitylearning.com. Poll. Are familiar with “business intelligence”? Are familiar with Microsoft’s BI platform?

mulan
Télécharger la présentation

SQL Server Business Intelligence on Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related