1 / 27

HDNUG 27-March-2007

HDNUG 27-March-2007. SQL Server 2005 Suite as a Business Intelligence Solution. Agenda. Introduction SQL Server 2005 Case Overview Integration Services Analysis Services Reporting Services Wrap Up. Who Am I. Name: Rowan Miller Position: Analyst Programmer

oscar-walsh
Download Presentation

HDNUG 27-March-2007

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. HDNUG 27-March-2007 SQL Server 2005 Suite as a Business Intelligence Solution

  2. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  3. Who Am I Name: Rowan Miller Position: Analyst Programmer Company: Technology Services Group Email: rmiller@federalit.net Responsibilities: • Application Design & Development • Data Warehouse Design & Development

  4. What Am I Presenting • SQL Server 2005 BI architecture • What does each component do • How do we use each component • Practical Demo • Tips, tricks and observations

  5. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  6. SQL Sever 2005 Suite More than just a database engine

  7. SQL Sever 2005 Suite More than just a database engine

  8. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  9. Case Overview Company: Sell-a-lot Enterprises Market: Multi-National Grocery Retailer Issue: Lack of group wide reporting Solution: Implement an Enterprise Data Warehouse (EDW) and Reporting Layer Tools: SQL Server 2005

  10. Case Overview What do Sell-a-lot Enterprises want to report on? • Sales • Products • Cashier • Customer • Store

  11. Case Overview Where does the data come from? • Product data comes from central SQL Server Database • Products are identified by a barcode that is consistent across all stores • Each store writes out two csv files each morning • One contains sales data • Files are prefixed with store code and trading date(HOB_20070326_sales.csv) • One contains customer data • Files are prefixed with store code (HOB_customers.csv)

  12. Case Overview

  13. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  14. SQL Server Integration Services (SSIS) • Getting data from one store to another • The younger (and better looking) brother of SQL 2000 Data Transformation Service (DTS) • Drag & drop design • Scriptable components • Real-time Debugging

  15. SQL Server Integration Services (SSIS) Business Intelligence Studio Data Warehouse SSIS Packages Other Databases

  16. SQL Server Integration Services (SSIS) DEMO Getting Sell-a-lot Enterprise data into the data warehouse

  17. SQL Server Integration Services (SSIS) Tips, Tricks & Observations • Don’t underestimate the efficiency of a data flow task • Avoid using Execute SQL Tasks to do inserts (use a dataflow task instead) • Use ADO.Net when using Execute SQL Tasks (named parameters) • Slow Changing Dimension component is useful for many other tasks • Use SQL Server Agent to schedule execution • Deploying to SQL Server is easier to manage than File System • Use configuration files for File System Deployment, Use “Set Value” tab in SQL Agent for SQL Server Deployment • Security model is painful, use windows authentication for connections and use “do not store sensitive data” for package security • Watchout for RSI from the mouse

  18. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  19. SQL Server Analysis Services (SSAS) • Data Cubes • KPIs • Data Mining Structures • Decision Trees • Clustering • …

  20. SQL Server Analysis Services (SSAS) Business Intelligence Studio SSAS Data Cubes Data Warehouse SSIS Packages Other Databases

  21. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  22. SQL Server Reporting Services (SSRS) • Microsoft's reporting tool • Developer built dynamic reports • Ad-hoc user created reports • Sits on top of almost any data store • Report Models provide a “Business” view of the data • Web based interface • SSRS 2000 good but incomplete • SSRS 2005 much better product

  23. SQL Server Reporting Services (SSRS) Report Builder Report Manager SSRS Reports Business Intelligence Studio SSRS Report Models SSAS Data Cubes Data Warehouse SSIS Packages Other Databases

  24. SQL Server Reporting Services (SSRS) DEMO Getting Sell-a-lot Enterprise data out of the data warehouse

  25. SQL Server Reporting Services (SSRS) Tips, Tricks & Observations • Try to base reports on a Report Model • Use stored procedures to extract data • Avoid excessive use of the toggle visibility function(Use drill through reports instead) • Keep layout simple so reports export to Excel nicely • Use a drop down list for dates in SSRS 2000 (American date format) • Get rid of SSRS 2000… install SSRS 2005 • Have an administrator for the report server folder structure • Use the SSRS web parts for integration with Sharepoint

  26. Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up

  27. Wrap Up Questions ??? Email: rmiller@federalit.net

More Related