270 likes | 280 Views
Learn about the SQL Server 2005 Suite and how it can be used as a business intelligence solution. Agenda includes an introduction, case overview, integration services, analysis services, reporting services, and more. Practical demo and tips included.
E N D
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 Company: Technology Services Group Email: rmiller@federalit.net Responsibilities: • Application Design & Development • Data Warehouse Design & Development
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
Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up
SQL Sever 2005 Suite More than just a database engine
SQL Sever 2005 Suite More than just a database engine
Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up
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
Case Overview What do Sell-a-lot Enterprises want to report on? • Sales • Products • Cashier • Customer • Store
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)
Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up
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
SQL Server Integration Services (SSIS) Business Intelligence Studio Data Warehouse SSIS Packages Other Databases
SQL Server Integration Services (SSIS) DEMO Getting Sell-a-lot Enterprise data into the data warehouse
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
Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up
SQL Server Analysis Services (SSAS) • Data Cubes • KPIs • Data Mining Structures • Decision Trees • Clustering • …
SQL Server Analysis Services (SSAS) Business Intelligence Studio SSAS Data Cubes Data Warehouse SSIS Packages Other Databases
Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up
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
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
SQL Server Reporting Services (SSRS) DEMO Getting Sell-a-lot Enterprise data out of the data warehouse
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
Agenda • Introduction • SQL Server 2005 • Case Overview • Integration Services • Analysis Services • Reporting Services • Wrap Up
Wrap Up Questions ??? Email: rmiller@federalit.net