nancy soule business intelligence potfolio
DESCRIPTION
Highly motivated professional with twenty years of experience in Business Analysis and Systems Development. . Performed various roles including project management, planning, analysis, design, and implementation of business intelligence solutions. Proven leadership, managed project teams in various manufacturing environments.TRANSCRIPT
Nancy SouleBusiness Intelligence Portfolio
[email protected] (336) 861-7303
Project SummaryBusiness Intelligence Project Work This portfolio contains selected examples of my development skills in the Microsoft Business Intelligence arena.
Core technologies covered:• Microsoft SQL Server 2005 T-SQL • Microsoft SQL Server 2005 MDX • Microsoft SQL Server 2005 • Microsoft SQL Server 2005 Integration Services (SSIS) • Microsoft SQL Server 2005 Analysis Services (SSAS) • Microsoft SQL Server 2005 Reporting Services (SSRS) • Microsoft Office SharePoint Server 2007 (MOSS) • Microsoft Office Performance Point Server (PPS)
Specifications: Design and build a Business Intelligence solution for a simulated construction company to track employee, clients, job order, timesheet information, material type and overhead.
Project Goals: • Define a Star Schema database using Visio (four fact tables) • Create a Staging Database (Visio created the DDL) • Create an ETL solution to update the SQL Server 2005 database from Excel and flat file sources using
SSIS • Create a Star Schema Analysis Services cube with SSAS • Write MDX queries based on specifications • Define Calculated Members and business Key Performance Indicators (KPIs) in SSAS • Use Excel Services 2007 to display the cube data and the KPIs, displaying statuses and trends • Produce detail and summary reports using SSRS • Create score cards using MS Office Performance Point • Implement business intelligence dashboards using MOSS 2007 (SharePoint)
04/10/23 Nancy Soule 2009 BI Portfolio 2
SQL Server Integration Services (SSIS)SQL Server Integration Services (SSIS)
04/10/23 Nancy Soule 2009 BI Portfolio 3
SSIS ProjectSpecifications:• Create packages to transfer the data from raw data source (*.CSV files). Data
sources are both normalized and non-normalized. • Do a full load of data into MS SQL Server 2005. • Perform validation to detect errors (e.g. child records with invalid parent
records).• Generate emails with the results, including rows inserted, updated. Errors are
redirected to log files which become email attachments. • Create a separate master package to run individual packages nightly to import/update
any new records, re-index and shrink the database, as well as perform nightly backups of the database.
• Run the scheduled master package nightly at a specified time.
Screenshots: • This particular package required reading multiple CSV files and totaling record
counts for all files. Following are the control flow and data flow screenshots for the Job Time Sheets Package and the control flow for the Master Package.
04/10/23 Nancy Soule 2009 BI Portfolio 4
04/10/23 Nancy Soule 2009 BI Portfolio 5
Job Time Sheet Package Control Flow
The Job Time Sheet control flow has a for each loop container that loops through each file name and processes the job time sheet files. At the completion of the task it sends an email notification whether load was successful or failed and adds any invalid record files as email attachments.
04/10/23 Nancy Soule 2009 BI Portfolio 6
Job Time Sheet Package Data Flow
The Job Time Sheet data flow task reads data from a CSV file and verifies the Employee Master and Job Master exists. The task also verifies the Job Master record is not closed or work date is greater than job closed date. If the record fails validation then the time sheet record is written to an invalid file. The task performs a lookup for the job time sheet to find a match. If the data is not found then a new row is added to the table. If the data is found and the data is different then it updates the existing row.
Master Package
The Master Package executes 8 Packages in a Sequence Container for moving source to the database. The final three Packages execute a backup of the database, rebuild indexes and shrink the database.
04/10/23 Nancy Soule 2009 BI Portfolio 7
SQL Server Analysis Services (SSAS)SQL Server Analysis Services (SSAS)
04/10/23 Nancy Soule 2009 BI Portfolio 8
SSAS ProjectSpecifications:• Create a Data Source using the Allworks database.• Create a Data Source View using the following tables:
– Job Summary Facts– Job Overhead Summary Facts– Job Materials Facts– Job Labor Facts– AllWorks Calendar– Clients– Client Groupings– County– Division– Employees– Job Master– Material Type– Overhead
• Create the appropriate relationships in the Data Source View.• Create the All Works Cube using the wizard to produce four fact tables and five dimensions.• Create two partitions for each of the four fact tables, one for data before 2005 and one
for data after 2005.• Create 24 MDX Queries.• Create 5 KPI’s
Screenshots: • This particular package required reading multiple CSV files and totaling record counts for
all files. Following are the control flow and data flow screenshots for the Job Time Sheets Package a
04/10/23 Nancy Soule 2009 BI Portfolio 9
All Works Data Source View
04/10/23 Nancy Soule 2009 BI Portfolio 10
All Works Cube
04/10/23 Nancy Soule 2009 BI Portfolio 11
Job Master Dimension
04/10/23 Nancy Soule 2009 BI Portfolio 12
MDX Queries
04/10/23 Nancy Soule 2009 BI Portfolio 13
Sample MDX QueryJob Summary
04/10/23 Nancy Soule 2009 BI Portfolio 14
Sample MDX Queries
04/10/23 Nancy Soule 2009 BI Portfolio 15
More Sample MDX Queries
04/10/23 Nancy Soule 2009 BI Portfolio 16
KPI’s
04/10/23 Nancy Soule 2009 BI Portfolio 17
Open Receivables KPIwith MDX
04/10/23 Nancy Soule 2009 BI Portfolio 18
Open Receivables KPI
04/10/23 Nancy Soule 2009 BI Portfolio 19
Performance Point, SSRS, Excel Services and Share Point Project
Specifications:• Create a SharePoint Site Collection, configure the site for use with SQL Server 2005
Reporting Service and Excel Services.• Create four Document Libraries:
– Excel Documents (Document Library)– Generated Reports (Document Library)– PPS Dashboards (Report Library)– SSRS Reports (Report Library)
• Create the PPS Dashboard:– Create the Scorecard– Create four Charts/Analytical Grids
• Create two SSRS Reports, and deploy them to Share Point.• The Overhead Category report with an automatic shared schedule in Share Point to run
every day at 8:00 AM as a Web Page in the Generated Reports Document Library. • Create three Excel Documents:
– Basic Overhead by Dates (Pivot Table)– Labor History Chart (Pivot Table)– Job Profitability Chart (Pivot Table)
• All Excel Documents are published to Share Point in the Excel Documents.
04/10/23 Nancy Soule 2009 BI Portfolio 20
Performance Point
04/10/23 Nancy Soule 2009 BI Portfolio 21
PPS Dashboards
04/10/23 Nancy Soule 2009 BI Portfolio 22
Scorecard
04/10/23 Nancy Soule 2009 BI Portfolio 23
Weekly Overhead Chart
04/10/23 Nancy Soule 2009 BI Portfolio 24
Employee Labor AnalysisChart with % of Labor Dollars by Product
04/10/23 Nancy Soule 2009 BI Portfolio 25
SQL Server Reporting Services (SSRS)
04/10/23 Nancy Soule 2009 BI Portfolio 26
Employee Jobs SSRS Performance Point
04/10/23 Nancy Soule 2009 BI Portfolio 27
Employee Jobs Reportin Layout Mode (Design)
04/10/23 Nancy Soule 2009 BI Portfolio 28
Overhead Category SSRS Performance Point
04/10/23 Nancy Soule 2009 BI Portfolio 29
Excel Services and Share Point
04/10/23 Nancy Soule 2009 BI Portfolio 30
Sample Excel ServicesShare Point
04/10/23 Nancy Soule 2009 BI Portfolio 31