business intelligence portfolio
TRANSCRIPT
Business Intelligence PortfolioBusiness Intelligence Portfolio
Gary A. Thompson
(360) 241-3703(360) 241-3703
Table Of Contents
� Project Overview
� T-SQL Samples
� SSIS – Integration Services
� SSAS – Analysis Services� SSAS – Analysis Services
� MDX Samples
� KPI’s, Excel Services
� SSRS – Reporting Services
� PPS – Performance Point Services
Project OverviewProject Overview� Introduction: This presentation displays examples of my work (code samples and development work) using
the Microsoft Business Intelligence tools.
� Tools used:
T-SQL 2005
MS SQL Server 2005
SQL Server Integration Services 2005 - SSIS
SQL Server Analysis Services 2005 - SSAS
� Audience:
Business Executives
IT Managers
Solution Providers
� Project Goals:
Design and build a relational database schema populated by data from three different data sources. With different data types involved, performed Extract, Transform, and Load processes to prepare the data and load the tables. Each step was accompanied with a confirming email upon the success or failure of the job run. This process used SSIS in the SQL Services Management Studio, and T-SQL queries to extract the data.
Design and build an OLAP database and cube using the data from the relational database to allow for detailed data searches usingMDX queries. KPI’s allow a visual representation to be viewed showing a comparison between actual results and performance goals.
TT--SQL SamplesSQL Samples
T-SQL provides a means of extracting
data from the relational database. Many of the
queries we constructed were
designed to verify the designed to verify the load process and to
ensure the accuracy of the data within the various tables.
TT--SQLSQL SamplesSamples
Narrowing the selection criteria through the use of
the logical “WHERE” the logical “WHERE” helped ease validation of data by limiting the output to a more
rigidly defined subset.
SSIS SSIS –– SQL Server Integration Services 2005 SQL Server Integration Services 2005 (Documentation)(Documentation)
� Ask most Developers and Analysts what their least favorite and yet most important task is on a project and the answer is usually documentation. Clear, concise, and accurate documentation is critical to the functioning of a project and even more important to maintaining systems on an ongoing basis.
� Each package requires it’s own documentation about authorship, purpose of the package, maintenance records, etc., and the overall project documentation is put in place to give an overview of the higher level requirements that are met and the roadmap of the packages within the project.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
� Project: AllWorks, Inc. (SSISStudentProject)
� Author: Gary A. Thompson
� Created Date: Oct. 1, 2009
� Modified Date:
� Description: This project loads the AllWorksDBStudent database tables: Employees, EmployeeRates, County, Clients, ClientGroupings, Division, ClientGroupingsXClients, JobMaster, and JobTimesheets.
� The project involves the following packages:
� EmployeeMasterPackage: Loads Employee table from Employees.xls
� EmployeeRatesPackage: Loads EmployeeRates table from Employees.xls
� based on matching Employee table on EmployeePK.
� ClientMasterPackage: Loads Clients table from ClientGeographies.xls
� County table must load first, and be validated priorCounty table must load first, and be validated prior
� to loading Clients.
� SpecialGroupingsPackage: Loads ClientGroupings table from ClientGeographies.xls
� based on matching ClientPK in Clients table. Data
� is aggregated by grouping #.
� DivisionDefinitionsPackage: Loads Division table from ClientGeographies.xls
� ClientGroupingsXrefPackage: Loads ClientGroupingsXClients table from ClientGeographies.xls
� based on matching ClientPK in Clientstaable, and
� ClientGroupingsPK in ClientGroupings table.
� JobMasterPackage: Loads JobMaster table from ProjectMaster.xls
� JobTimesheetsPackage: Loads JobTimesheets table from ProjectMaster.xls
� based on matching JobMasterPK in JobMaster table
� and validating that the job is not closed prior to the
� workdate being submitted.
�
� These packages are included in the project (SSISStudentProject) and run from MasterPackage. Also included in the Master are four Database maintenance tasks: 1. Shrink the database. 2. Rebuild the DB indexes. 3. Update the database statistics. 4. Backup the database. Upon the successful completion of these tasks the MasterPackageprocesses through the above packages to load the tables. If the Database maintenance tasks fail, and email is sent indicating which task failed, and processing stops.
� Upon the completion of each package contained in the MasterPackage, an email is sent indicating the success or failure of the individual package run. Success emails contain a count of rows being inserted, changed, and invalid rows (where indicated in requirements). Invalid rows are written to a text file for review by the users.
�
�
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
The Control Flow provides a roadmap of the processes within the package, and also provides a step by step explanation of what the various processes will
accomplish.
This Control Flow shows the steps involved in a backup and maintenance process. Each step is dependent upon the
successful completion of the prior step.
The arrows here represent the path that the data takes and the order of execution
of each process.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
Through the use of script tasks we
are able to extend the functionality
Provided within Provided within SSIS to include customized
processes that we define using Visual Basic for
Access.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
The Data Flow Task provides tools for extracting,
transforming, and loading data from
various data sources. The sources. The
Conditional Split shown here
directed the flow of data into
different output files or databases
based on specified criteria.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
SSIS provides a graphical interface to help give a
visual representation of the steps being
taken. Each symbol represents a data represents a data
source, a transformation process, or an
output destination. The arrows are
data pipelines that show the flow of data from one
point to another.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
Containers allow for the use of
iterative processes. In this case, when this
process process completes an email indicting the success of failure of the
process is sent to the defined user
group.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
I’ve used a legend to identify
abbreviations which identify the data as it moves through the various steps. the various steps. Consistency keeps
errors at a minimum and
allows for ease in debugging when things go wrong.
SSIS SSIS –– SQL Server Integration Services 2005SQL Server Integration Services 2005
Using annotation to label the
components and data flow assists data flow assists in giving a quick idea of what takes place,
without going into great detail.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005
Good design leads to a well functioning cube
structure. Verifying the relationships, relationships,
ensuring that the naming will
display properly for the end user, and referencing keys is all part of good design.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005
The structure The structure of this cube contains four fact tables and nine total dimensions.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005
Relationships are verified in the Cube Structure area and confirmed in the Dimension Usage area.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005
Calculations are added in the SSIS interface in much the same manner as they Calculated Members are built Members are built in MDX.
This calculation allows for a default value to be inserted in the event of a
null value.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005
Building calculations help in the readability of KPI’s and allows for of KPI’s and allows for their reuse in other
areas.
Additionally, modularity of code makes for ease
of maintenance.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005
KPI’s (Key Performance Indicators) are designed to allow the end user a quick graphical representation of how their area is measuring against
their goals. They also can be used for forecasting based on trend analysis.
SSAS SSAS –– SQL Server Analysis Services 2005SQL Server Analysis Services 2005The databases are partitioned at 50% in a MOLAP format
(Multi-Dimensional OLAP) and are divided by year.
MDX: MultiMDX: Multi--Dimensional QueriesDimensional Queries
MDX gives us a means for querying a
cube structure, grabbing data from various dimension
tables to use in slicing tables to use in slicing the facts.
This query determines the labor rate for employees
on a project based on their hours worked and the total labor for the project.
MDX: MultiMDX: Multi--Dimensional QueriesDimensional Queries
Another query uses the Generate
command to create a command to create a subset of data from different sources,
then uses this newly defined subset to
organize the data for output.
MDX: MultiMDX: Multi--Dimensional QueriesDimensional Queries
Using a calculated member in this query provides formatting for data fields that contain null values.
MDX: MultiMDX: Multi--Dimensional QueriesDimensional Queries
Although this Query itself
was not long, It was not long, It required
documentation to provide
others with a clear idea of its purpose and function.
MDX: MultiMDX: Multi--Dimensional QueriesDimensional Queries
The use of multiple calculated members
expanded the expanded the functionality of
the query, while
simplifying the code for greater
readability.
KPI’s KPI’s –– Key Performance IndicatorsKey Performance Indicators
For this project, I developed several KPI’s to give the business users a quick visual
summary of the performance of performance of
selected measures against a particular goal. While KPI’s can also be used for forecasting, job requirements did not call for such measurements.
KPI’s KPI’s –– Key Performance IndicatorsKey Performance Indicators
This KPI show the increase in overhead expenses against a goal of 15% being acceptable. The goals are defined as good, marginal, and unacceptable, using a traffic light symbol to indicate the status. Green is good, yellow is
marginal, and red is unacceptable.
KPI’s KPI’s –– Key Performance IndicatorsKey Performance Indicators
The KPI’s are displayed here in Microsoft Excel.
This allows users to see performance measures without measures without requiring any special
installation of software for
viewing. I created a pivot table to hold the data and KPI information.
KPI’s KPI’s –– Key Performance IndicatorsKey Performance Indicators
The beauty of using KPI’s in pivot tables to display progress is that the measures can be adjusted on the fly. This allows the user to select
a different way to slice the data to meet their needs.
SSRS- SQL Server Reporting Services 2005
Reports are built to display information
about how a company is
performing with regard to specific
goals. In this example, goals. In this example, if the percentage of increased overhead expense exceeds a certain level, the
value is displayed in red to supply a quick visual representation
of company expenditures.
SQL Server Reporting Services 2008SQL Server Reporting Services 2008
This is a parameter driven
report that provides a look at several previous
months production.
Depending on selection criteria,
the header information information
changes to match the selections
based on using report items
within the body of the tablix.
Footer information is
developed using calculations
based on report items in the body
of the report.
SQL Server Reporting Services 2008SQL Server Reporting Services 2008
This report runs on a group level and is navigated using a document map on the top level group to create a table of contents in the PDF file.
Each item in the table is a Each item in the table is a separate report based on the groupings built in the master report. The charts are incorporated in the tablixheader so that they consistentl ymatch the data in the tablix with regard to selection group.
Shading of alternate lines is based on the producer level changing, not simply every other line in a different color.
PPS- Performance Point Services 2007
Graphs and charts come in a variety of shapes and sizes to accommodate all types of reports. Line charts give a comparison
of two or more measures in an easy to read manner.
PPS- Performance Point Services 2007
Different types of charts are combined to display different values being different values being tracked. This chart
represents the percentage of profit for an organization
and the performance broken down by county.
PPS- Performance Point Services 2007
Scorecards and dashboards can be displayed in many
fashions. One popular style of dashboard displays as a traffic dashboard displays as a traffic
signal, with green representing progress ahead
of goal, yellow being a marginal area near goal, and red indicating being below
target.