fl 10 b1 ron sommer portfolio
DESCRIPTION
Business Intelligence PortfolioTRANSCRIPT
Business Intelligence Portfolio
Ron Sommer
• T-SQL Samples
• SSIS – Integration Services
• SSAS – Analysis Services
• MDX Code Samples
• KPI’s, Excel Services – Samples
• MOSS/PPS – Share Point & Performance Point
Services
Contents
T-SQL Samples
• USE northwind
• SELECT categoryid, SUM(quantity) AS total_quantity
• FROM [order details] AS od
• INNER JOIN products
• ON od.productid = products.productid
• GROUP BY categoryid
• ORDER BY categoryid
• GO
• 1 9532
• 2 5298
• 3 7906
• 4 9149
• 5 4562
• 6 4199
• 7 2990
• 8 7681
SSIS Samples
• Master Control Package
– Load Employee, Clients, Job, and Timesheet
records
– Back up Database
– Compress Database
– Index Database
– Send status messages at various points in the
process
SSIS Samples
Master Control Package
SSIS Samples
Control Flow example with documentation
SSIS Samples
Data Flow Package – related to prior slide
SSIS Samples
Control Flow Package with For Each Loop Container
SSIS Samples
Data Flow Package associated with For Each Loop Container
SSAS Samples
Allworks Cube•4 Fact Tables resulting in 4 measure groups
•Labor
•Material
•Overhead
•Job Summary
•9 Additional tables consolidated into 5 dimensions•Employees
•Job Master
•Materials
•Overhead
•Calendar
SSAS Samples
SSAS Samples
Added Calculated Member with MDX Sample
SSAS Samples
KPI with goal and trend (with MDX example)
SSRS Samples
• A series of related reports
– Basic Overhead by Dates
– Labor History Chart
– Job Profitability Chart
SSRS Samples
Basic Overhead by Dates
SSRS Samples
Labor History Chart
SSRS Samples
Job Profitability Chart
PPS Samples
Hierarchical
Scorecards
Value for Selected
Quarter
Current status versus
goal for period
PPS Samples
Client material costs by Quarter
PPS Samples
Quarterly overhead by Category
Category Selectable for Multiple Categories