bi portfolio
TRANSCRIPT
![Page 2: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/2.jpg)
Business Intelligence - Now SQL Server Integration Services - SSIS SQL Server Analysis Services - SSAS Multi-Dimensional eXpressions - MDX SQL Server Reporting Services – SSRS Excel Services PerformancePoint Server – PPS
MS Office SharePoint Server – MOSS 2007
Table of Contents
2
![Page 3: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/3.jpg)
Now more than ever before, you need to get the right information to the right people faster. Business Intelligence (BI) solutions can help everyone in your organization quickly access the timely, relevant, and accurate information that drives better decision making and impacts your bottom line.
Business Intelligence (BI) solutions can help your organization:◦ Get real-time information◦ Find profitable customers◦ Pivot on a dime◦ Find inefficiencies◦ Save money
Business Intelligence - Now
3
![Page 4: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/4.jpg)
Develop relational data model based upon company’s needs Develop SQL Server database based upon ERD Integrate all external data into SQL Server database
via SQL Server Integration Services Using the SQL Server database as a data source, create OLAP cube Build KPIs to analyze and measure AllWorks profitability and costs Write MDX queries to provide results Physically partition the data for better performance Expose KPI results in Excel, PerformancePoint and MOSS 2007 Create score cards and dashboards via PerformancePoint Dashboard Designer
and deploy to MS Office SharePoint sites Implement business intelligence dashboards using MOSS 2007 Report Center and
Excel Services producing summary results based on user view and role membership
AllWorks, Inc. project
4
![Page 5: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/5.jpg)
SQL Server Integration Services
SSIS
5
![Page 6: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/6.jpg)
Job Master Data Flow Task: Load data from source file & convert to SQL format Perform lookups to determine processing pipeline Insert/update data or write error log record Capture input/output record counts for process control
6
![Page 7: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/7.jpg)
Employee Master Data Flow Task: Load data from source file & convert to SQL format Perform lookups to determine processing pipeline Insert/update data or write error log record Capture input/output record counts for process control
7
![Page 8: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/8.jpg)
Job Time Sheets Control Flow Task: Prep error logs for current day process Read all timesheets for period VB Script required to capture iterative counts (see slide 10) Report input/output record counts for process control
8
![Page 9: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/9.jpg)
Job Time Sheets Data Flow Task: Load data from source file & convert to SQL format Perform lookups to determine processing pipeline Insert/update data or write error log record Capture input/output record counts for process control
9
![Page 10: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/10.jpg)
VB Script for Time Sheets Control Flow Task: Capture input/output record counts at the end of each iterative loop for process control reporting
10
![Page 11: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/11.jpg)
Nightly Update Master Control Flow Task: Process each package to load/update source data Shrink database Rebuild indexes Update database statistics for improved performance Back up database
11
![Page 12: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/12.jpg)
SQL Server Agent Scheduled Nightly Update: Utilize SQL Agent to schedule Master Package nightly update
12
![Page 13: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/13.jpg)
SQL Server Analysis Services
SSAS
13
![Page 14: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/14.jpg)
AllWorks OLAP Cube: 4 Fact tables 9 Dimension tables
14
![Page 15: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/15.jpg)
Client Hierarchies: Create multiple independent client hierarchies to support drilling up and down
15
![Page 16: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/16.jpg)
KPIs – Key Performance Indicators Used to help a company define and measure progress toward organizational goals. See Calculated Measure expression on next page. See KPI graphic results on page 28, left side.
16
![Page 17: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/17.jpg)
Calculated Measures: Use the power of OLAP cubes to establish calculated measures to accurately reflect company metrics. See KPI utilization on previous page.
17
![Page 18: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/18.jpg)
Partitions: Physically partition data to obtain better response. Typically partitioned by date criteria to segregate older data which is usually accessed less as it matures.
18
![Page 19: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/19.jpg)
Multi-Dimensional eXpressions
MDX
19
![Page 20: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/20.jpg)
MDX: Show all employees in alphabetical order for 2005 Q4 and four periods ago for total hours worked in the Quarter.
20
![Page 21: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/21.jpg)
MDX: For 2005, show the jobs and the top 3 employees by job, who worked the most hours. Show the jobs in job order and within each job, show the employees in hours worked order.
21
![Page 22: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/22.jpg)
SQL Server Reporting Services
SSRS
22
![Page 23: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/23.jpg)
SSRS Report deployed to SharePoint Server If Overhead Category has increased quarter to quarter, illustrate change in RED MDX used to default to last quarter of available data Fiscal year quarter (FY Qtr) selection criteria provided for prior quarter analysis
23
![Page 24: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/24.jpg)
SSRS Report deployed to SharePoint Server Example of Cascading parameter. MDX code provides available weeks of labor data, based upon Employee selected. Report is sub-totaled by week ending and totaled by employee selected.
24
![Page 25: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/25.jpg)
Excel Services
25
![Page 26: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/26.jpg)
Excel Reports deployed to SharePoint Server via Excel Services/PerformancePoint Basic Overhead is an Excel PivotTable with year selection capability. Job Profitability is an Excel Pivotchart with multiple county selection capability.
26
![Page 27: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/27.jpg)
PerformancePoint ServerPPS
MS Office SharePoint Server
MOSS 2007
27
![Page 28: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/28.jpg)
PerformancePoint Dashboard page with OLAP sourced KPIs Final deployment to SharePoint Server Overhead Trend allows selection of quarter and presents by category what the percentage change is. Desired result is less than 10%. Greater than 15% is critical status. Client Job Financials consists of 3 separate KPIs, all stacked on 1 scorecard; Open Receivables as a Percentage of Invoice, Profit Percentage and Overhead as a Percentage of Total Costs.
28
![Page 29: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/29.jpg)
PerformancePoint Dashboard page with OLAP sourced dataFinal deployment to SharePoint ServerOverhead Line Chart allows selection of multiple overhead categories and presents all available quarters of data
29
![Page 30: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/30.jpg)
PerformancePoint Dashboard page with OLAP sourced dataFinal deployment to SharePoint Server Dual Y-axis bar chart and Analytic Grid on same page. Single Employee selection drives both results. MDX required to derive result sets.
30
![Page 31: BI Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062514/558a2931d8b42a3e028b45b9/html5/thumbnails/31.jpg)
31
This represents just a sampling of my capabilities.Very flexible in location and travel considerations.For further information or to schedule an interview:
Terry Comeaux 713-553-9148