1 / 27

SQL Server 2005 Business Intelligence

SQL Server 2005 Business Intelligence. Aseel Mansour Product Technology Specialist Microsoft - South Gulf. “How you gather, manage, and use information will determine whether you win or lose.” Bill Gates. Integration Services (DTS) Simple package to create Operational Data Store

eliza
Download Presentation

SQL Server 2005 Business Intelligence

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 2005Business Intelligence Aseel Mansour Product Technology Specialist Microsoft - South Gulf

  2. “How you gather, manage, and use information will determine whether you win or lose.” Bill Gates

  3. Integration Services (DTS) Simple package to create Operational Data Store Analysis Services Simple Cube (UDM) Data Source View (DSV) to simulate star schema Reporting Services MDX-based Report Agenda

  4. BI Architecture OLTP Database Data Warehouse OLAP Cubes UI

  5. Integration Services Purpose Packages Import/Export Wizard Integration Services Designers Demo: Integration Services

  6. More than simple ETL (Extract, Transform, Load) Platform for development Developers can create new Tasks and Transforms Efficient Pipeline Minimize load and read operations Better than sequential SQL set operations No programming required Integration Services Overview

  7. Unit of execution Control Flow manages task sequence Data flow manages data within one task Create or edit by using DTS Designer Custom Code (DTS API) Packages

  8. Create new packages Default to run in SQL Management Studio Default to save in BI Development Studio Import/Export Wizard

  9. Design Package Properties Pane, Editors Toolbox Package Browser Design Surfaces Control Flow, Data Flow Additional Windows Variables, Connections UI Output, Watch Execute in Debug Mode Integration Services Designers

  10. Analysis Services (OLAP) Purpose Analysis Services Designers Data Source Dimension Structure Attributes and Hierarchies Demo: Analysis Services UDM

  11. Highly scalable additive calculations Multiple dimensions, attributes, hierarchies Millions of fact table rows Aggregations with very little data explosion Highly sophisticated analytical calculations Complex calculations Balance data explosion and slow queries_ Analysis Services (OLAP) Overview

  12. Design Dimensions and Cubes Properties Pane, Editors Hot linked to XML (code) Deploy directly to server Browse dimensions and cubes Analysis Services Designers

  13. Connection to relational database Possible to Create Cube with no Database (create relational database later) Data Source is compatible with DTS, but not with Reporting Services Data Source

  14. Relational Dimension One primary key: May be logically defined, must be unique Other columns in same or joined tables are relational attributes Analysis Services Dimension All dimensions are shared (in AS2K sense) Relational column becomes Analysis Services attribute An Analysis Services attribute is Key, Parent, or Regular Dimension Structure

  15. Attribute Hierarchies Each attribute gets hierarchy (unless blocked) Two levels: All Level and Leaf Level Basis of “real” cube User hierarchies Purely for navigation Multiple users hierarchies are fine Ultimately just map to Attribute hierarchy members Attributes and Hierarchies

  16. Data Source View (DSV) Purpose Customizing a DSV Server Date Dimension Demo: Analysis Services DSV

  17. Define schema once for all tables, not independently Buffer discrepancies between logical and physical data Create “views” even if no source database permissions Create logical primary key/relationship with views Create cube first and then source database Work in AS with no connection to data source Multiple developers can work simultaneously Data Source View

  18. Two ways to customize source table Add calculated columns Replace with Named Query Named Query is better No worse performance More flexible – add joins, filters When convert to Named Query, doesn’t bring calculated columns along Customizing a DSV

  19. Most cubes have date dimension Source of dimension Dimension table (if available) Server supplied Don’t use fact table as source! Server Date Dimension

  20. Reporting Services Purpose RS Release Roadmap MDX Query Builder Integrated Environment Demo: Reporting Services

  21. Enterprise reporting platform for traditional and interactive reports Scalable, manageable and embeddable web services infrastructure Integrated with SharePoint and other tools All types of structured data (relational, hierarchical, multidimensional) Extensible platform Reporting Services Overview

  22. Analysis Services as data source Builder create MDX Beta 2 MDX is leaf-level Reports use Sum function Future plans Multi-level MDX Reports use pre-calculated value MDX Query Builder

  23. Consistent with Analysis Services and Integration Services BI Development Studio for development SQL Server Management Studio for management Integrated Environment

  24. Integration Services (DTS) Simple package to create Operational Data Store Analysis Services Simple Cube (UDM) Data Source View (DSV) to simulate star schema Reporting Services MDX-based Report Summary

  25. BI Architecture OLTP Database Data Warehouse OLAP Cubes UI

More Related