Transcript Rowan Millers slides for March 07

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: [email protected]
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)
Case Overview
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)
Business Intelligence
Studio
Report Builder
Report Manager
SSRS Reports
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: [email protected]