1 / 100

Business Intelligence Portfolio

Business Intelligence Portfolio. SetFocus Business Intelligence Master’s Program Jeff Jacob, MBA P: (312) 772-6142 E1: Jeffrey.K.Jacob@gmail.com E2: Jeffrey.Jacob@SetFocus.com. What is Set Focus? Bio: Jeff Jacob Transact-SQL Project ( Slide 5 ) September, 2010

tannar
Download Presentation

Business Intelligence Portfolio

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Business Intelligence Portfolio SetFocus Business Intelligence Master’s Program Jeff Jacob, MBA P: (312) 772-6142 E1: Jeffrey.K.Jacob@gmail.com E2: Jeffrey.Jacob@SetFocus.com

  2. What is Set Focus? Bio: Jeff Jacob Transact-SQL Project (Slide 5) September, 2010 SSIS /ETL Project (Slide 17) October-November, 2010 SSAS OLAP Cube Project (Slide 42) January, 2011 MDX Supplement (Slide 61) SSRS – PerformancePoint – Excel Services (MOSS 2007 publishing) Project (Slide 69) March, 2011 Table of Contents

  3. The SetFocus Business Intelligence Master’s Program is an intensive, hands–on, project-oriented program providing knowledge and experience in putting the BI skill set to use in a simulated work environment. I have received over 400 hours of in-depth, hands-on experience - focused on the Microsoft Business Intelligence stack; equivalent to about 1 - 1.5 years of experience. SetFocus projects are real world projects that are distributed just as I would receive in a BI position. I received project specifications and was expected to identify best courses of action - with deadlines set for completion. What follows is a sample of what I can do using the MS BI stack tools. What is Set Focus?

  4. Bio: Jeff Jacob • 5+ years experience in quantitative analytics, project management, marketing strategy(corporate HQ level) • 4+ years experience in client-facing editing and translation consulting • 1997: B.A. German, Philosophy (magna cum laude) • 3.7 (out of 4.0) cumulative GPA • 2009: M.B.A. - Accounting concentration (with distinction) • 3.9 (out of 4.0) cumulative GPA • Career objective: BI Analyst/Developer • Relevant Business Interests: Accounting/Finance, Economics, Statistics, Marketing, Strategy

  5. Transact-SQL ProjectSQL Server 2008 Management Studio SetFocus Business Intelligence Master’s Program Jeff Jacob, MBA P: (312) 772-6142 E1: Jeffrey.K.Jacob@gmail.com E2: Jeffrey.Jacob@SetFocus.com TOC

  6. Query – Wildcard Search (CI Collation)A summary of order detail-linetotal by product – for products that contain ‘Washer’ in their name; 2003 data only. USE [AdventureWorks2008]; GO SELECT Name, SUM(LineTotal) AS TotDollars FROM Purchasing.PurchaseOrderDetail pod INNER JOIN Purchasing.PurchaseOrderHeader poh ON poh.PurchaseOrderID = pod.PurchaseOrderID INNER JOIN Production.Productprd ON prd.ProductID = pod.ProductID WHERE Name LIKE '%washer%‘ AND YEAR(OrderDate) = 2003 GROUP BY Name ORDER BY TotDollars DESC

  7. Query – HAVING clauseA count of orders by product subcategory– for those with at least 10 orders; 2004 data only. USE [AdventureWorks2008]; GO SELECT psc.Name, COUNT(poh.PurchaseOrderID) AS NumOrders FROM Purchasing.PurchaseOrderHeader poh INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.PurchaseOrderID = poh.PurchaseOrderID INNER JOIN Production.ProductprdON prd.ProductID = pod.ProductID INNER JOIN Production.ProductSubcategorypscON prd.ProductSubcategoryID= psc.ProductSubcategoryID WHERE YEAR(OrderDate) = 2004 GROUP BY psc.Name HAVING COUNT(poh.PurchaseOrderID) >=10 ORDER BY NumOrdersDESC

  8. Query – Correlated Subquery of exclusionA list of the vendors for which there were no orders in 2003. USE [AdventureWorks2008]; GO SELECT Name FROM Purchasing.Vendor WHERE NOT EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader WHERE YEAR(OrderDate) = 2003 AND Vendor.BusinessEntityID = PurchaseOrderHeader.VendorID)

  9. Query – OUTER JOIN syntaxA summary of freight charges by shipper for all shippers; Q1 of 2003 data only. USE [AdventureWorks2008]; GO SELECT Name, SUM(Freight) AS TotFreight FROM Purchasing.ShipMethodsm LEFT OUTER JOIN Purchasing.PurchaseOrderHeader poh ON poh.ShipMethodID = sm.ShipMethodID AND YEAR(OrderDate) = 2003 AND DATEPART(q,OrderDate) = 1 GROUP BY Name ORDER BY TotFreightDESC

  10. Query – Chronological Sort A summary of total charges due by specific employee national ID numbers, sorted chronologically within each employee; 2003 data only. USE [AdventureWorks2008]; GO SELECT NationalIDNumber, LoginID, DATENAME(MONTH, OrderDate) AS MonthName, YEAR(OrderDate) as YearName, SUM(TotalDue) AS SumTotalDue FROM Purchasing.PurchaseOrderHeaderpoh INNER JOIN HumanResources.Employeeemp ON emp.BusinessEntityID = poh.EmployeeID WHERE NationalIDNumber IN (792847334, 407505660, 482810518, 466142721, 367453993) AND YEAR(OrderDate) = 2003 GROUP BY NationalIDNumber, LoginID, DATENAME(MONTH, OrderDate),DATEPART(MONTH, OrderDate) ,YEAR(OrderDate) ORDER BY NationalIDNumber, DATEPART(MONTH, OrderDate)

  11. Query – UNION queryA union of two view-based queries to obtain contact information of both vendors and employees, sorted first by postal code and then by name. USE [AdventureWorks2008]; GO SELECT 'Vendor' AS RecordType, Name, AddressLine1, ISNULL(AddressLine2,'') AS AddressLine2, City, StateProvinceName, PostalCode FROM Purchasing.vVendorWithAddresses UNIONALL SELECT 'Employee' AS RecordType, LastName + ', ‘ + FirstName + ' ‘ + ISNULL(MiddleName, ''), AddressLine1, ISNULL (AddressLine2,''), City, StateProvinceName, PostalCode FROM HumanResources.vEmployee ORDER BY PostalCode, Name

  12. Query – Stored ProcedureA stored procedure designed to summarize freight, tax amount and subtotal by vendor and shipper based on supplied account number and date parameters; with example usage. USE [AdventureWorks2008]; GO CREATEPROCEDUREdbo.usp_GetVendorOrders @AcctNonvarchar(15), @StartDatedatetime, @EndDatedatetime AS SET NOCOUNT ON; SELECT Ven.Name AS VendorName, Ship.Name AS ShipperName, SUM(Freight) as TotFreight, SUM(TaxAmt) AS TotTaxAmt, SUM(SubTotal) AS TotSubTot FROM Purchasing.PurchaseOrderHeaderpoh JOIN Purchasing.VendorVen ON poh.VendorID = Ven.BusinessEntityID JOIN Purchasing.ShipMethod ship ON ship.ShipMethodID = poh.ShipMethodID WHERE OrderDate >= @StartDate and OrderDate <= @EndDate AND AccountNumber = @AcctNo GROUP BY Ven.Name, Ship.Name GO EXEC dbo.usp_GetVendorOrders N'ADVANCED0001', '1-1-2003', '12-31-2003'

  13. Query – PIVOT tableA pivot table of shipper data for total charges due in 2003 summarized by Saturdays with a summary rank of the Top 5 based on grand total. ;WITH ShipperTotDueCTE AS ( SELECT sm.Name, dbo.SatDatesConverter(OrderDate) AS WeekEnding, TotalDue FROM Purchasing.PurchaseOrderHeaderpoh INNER JOIN Purchasing.ShipMethodsm ON sm.ShipMethodID = poh.ShipMethodID WHERE YEAR(OrderDate) = 2003 ), SumByWeekEndCTE AS ( SELECT Name AS Shipper, WeekEnding, SUM(TotalDue) AS GrandTot FROM ShipperTotDueCTE GROUP BY Name, WeekEnding ), PivCTE AS ( SELECT WeekEnding, [XRQ - Truck Ground] AS XRQ, [Cargo Transport 5] AS Cargo, [Overnight J-Fast] AS Overnight, [ZY - Express] AS ZY, [Overseas - Deluxe] AS Overseas, ISNULL([XRQ - Truck Ground],0) + ISNULL([Cargo Transport 5],0) + ISNULL([Overnight J-Fast],0) + ISNULL([ZY - Express],0) + ISNULL([Overseas - Deluxe],0) AS GrandTot FROM SumByWeekEndCTE PIVOT ( SUM(GrandTot) FOR Shipper IN ([XRQ - Truck Ground],[Cargo Transport 5], [Overnight J-Fast],[ZY - Express], [Overseas - Deluxe]) ) AS P ) SELECT TOP 5 WeekEnding, GrandTot, RANK() OVER (ORDER BY GrandTot DESC) AS WeekRank, XRQ, ZY, Overseas, Overnight, Cargo FROM PivCTE ORDER BY WeekRank; USE [AdventureWorks2008]; GO CREATEFUNCTION [dbo].[SatDatesConverter] (@orderdatedatetime) RETURNS datetime AS BEGIN DECLARE @satdatedatetime SET @satdate = (SELECT DateAdd(d, 7- datepart(dw, @OrderDate), @OrderDate)) RETURN @satdate END; GO

  14. Query – SP, CTE-only approachA stored procedure for a ranking of Top N vendors by Total Due amount; and, within each vendor, the Top N products based on Line Total (Due) for specified start/end dates. USE [AdventureWorks2008]; GO CREATEPROCEDUREdbo.usp_VendorProductRank @TopNint, @TopYint, @BeginDatedatetime, @EndDatedatetime AS SET NOCOUNT ON; ;WITH VendCTE AS ( SELECT Ven.BusinessEntityID, Ven.Name AS VendorName, SUM(TotalDue) AS TotalDue, DENSE_RANK () OVER (ORDER BY SUM(TotalDue) DESC ) AS VendorRank FROM Purchasing.VendorVen INNER JOIN Purchasing.PurchaseOrderHeaderpoh ON poh.VendorID = Ven.BusinessEntityID WHERE OrderDate >= @BeginDate AND OrderDate <= @EndDate GROUP BY BusinessEntityID, Name ), ProdCTEAS ( SELECT Ven.BusinessEntityID, prd.Name AS ProductName, SUM(LineTotal) AS ProductTotalDue, DENSE_RANK() OVER (PARTITION BY Ven.BusinessEntityID ORDER BY SUM(LineTotal) DESC) AS ProductRank FROM Purchasing.Vendorven INNER JOIN Purchasing.PurchaseOrderHeaderpoh ON poh.VendorID = ven.BusinessEntityID INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.PurchaseOrderID = poh.PurchaseOrderID INNER JOIN Production.Productprd ON prd.ProductID = pod.ProductID WHERE OrderDate >= @BeginDate AND OrderDate <= @EndDate GROUP BY Ven.BusinessEntityID, prd.Name) SELECT VC.VendorName, VC.VendorRank, CAST(VC.TotalDue AS NUMERIC (14,2)) AS TotalDue, PC.ProductName, PC.ProductRank, CAST(PC.ProductTotalDue AS NUMERIC (14,2) ) AS ProductTotalDue FROM VendCTE VC INNER JOIN ProdCTE PC ON PC.BusinessEntityID = VC.BusinessEntityID WHERE VendorRank <= @TopN AND ProductRank <= @TopY ORDER BY VendorRank, ProductRank GO EXECdbo.usp_VendorProductRank 5,5,'2003-01-01', '2004-06-30'

  15. Query – SP, CTE/Function approach A stored procedure for a ranking of Top N vendors by Total Due amount; and, within each vendor, the Top N products based on Line Total (Due) for specified start/end dates. CREATEPROCEDURE dbo.usp_TestProc1 @TopNint, @TopYint, @BeginDatedatetime, @EndDatedatetime AS SET NOCOUNT ON; ;WITHxCTEas ( SELECT TOP (@TopN) WITH TIES poh.VendorID, ven.Name AS VendorName, SUM(poh.TotalDue) AS TotalDue, DENSE_RANK() OVER (ORDER BY SUM(poh.TotalDue) DESC) AS VendorRank FROM Purchasing.PurchaseOrderHeaderpoh INNER JOIN Purchasing.Vendorven ON ven.BusinessEntityID = poh.VendorID WHERE OrderDate >= @BeginDate AND OrderDate <= @EndDate GROUP BY poh.VendorID, ven.Name ) SELECT xCTE.VendorName, xCTE.VendorRank, CAST(xCTE.TotalDue AS NUMERIC (14,2)) AS TotalDue, C.ProductName, C.ProductRank, CAST(C.ProductLineTotal AS NUMERIC (14,2) ) AS ProductLineTotal FROM xCTE CROSS APPLY dbo.TestFn1 (xCTE.VendorID, @TopY, @BeginDate, @EndDate) AS C GO EXEC dbo.usp_TestProc1 5, 5,'2003-01-01', '2004-06-30' • USE [AdventureWorks2008]; • GO • CREATEFUNCTION dbo.TestFn1 • (@VendorIDint, @TopYint, @StartDatedatetime, @EndingDatedatetime) • RETURNS TABLE • AS • RETURN • SELECT TOP (@TopY) WITH TIES poh.vendorid, prd.Name AS ProductName, • DENSE_RANK() OVER (PARTITION BY poh.vendorID ORDER BY • SUM(pod.LineTotal) DESC) AS ProductRank, • SUM(pod.LineTotal) AS ProductLineTotal • FROM Production.Productprd • INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.productid = prd.productid • INNER JOIN Purchasing.PurchaseOrderHeaderpoh • ON pod.PurchaseOrderid = poh.purchaseorderid • WHERE poh.VendorID = @VendorID AND OrderDate >= @StartDate • AND OrderDate <= @EndingDate • GROUP BY poh.vendorID, prd.Name • ORDER BY ProductLineTotal DESC • GO

  16. Query – Effective (‘As of’) Dates: Scalar UDF A function which provides the commensurate standardproductcost based on a specified productIDnumber and a desired ‘effective date.’ Example included. USE [AdventureWorks2008]; GO CREATEFUNCTIONdbo.ProdStdCostAsOfDate (@ProductIDint, @AsOfDatedatetime ) RETURNS MONEY AS BEGIN DECLARE @StndCost money SET @StndCost = ( SELECT StandardCost FROM Production.ProductCostHistorypch WHERE ProductID = @ProductID AND StartDate = (SELECT MAX(StartDate) FROM Production.ProductCostHistory WHERE ProductID = @Productid AND StartDate <= @AsOfDate ) ) RETURN @StndCost END; GO ;WITH ProdStdCostCTE AS ( SELECT ProductNumber, Name, CAST( dbo.ProdStdCostAsOfDate (Product.ProductID, '2002-01-01') AS NUMERIC (14,2) ) AS StandardCost FROM Production.Product INNER JOIN Production.ProductCostHistorypch ON pch.ProductID = Product.ProductID ) SELECT ProductNumber, Name, StandardCost FROM ProdStdCostCTE WHERE StandardCost IS NOT NULL AND StandardCost > 700 ORDER BY StandardCost DESC;

  17. SSIS/ETL ProjectSQL Server Integration Services (BIDS) 2008 SetFocus Business Intelligence Master’s Program Jeff Jacob, MBA P: (312) 772-6142 E1: Jeffrey.K.Jacob@gmail.com E2: Jeffrey.Jacob@SetFocus.com TOC

  18. SSIS/ETL Project: AllWorks, Inc.Documentation Objectives: Based on source data, design a 3NF database for AllWorks, Inc. Create the database, tables, and constraints in T-SQL Develop a SSIS Project file with ETL packages to bring data from source files into the AllWorksOLTP database; use a master package to run the ETL packages in proper succession and to effectuate database maintenance tasks Deploy the SSIS Project file to SQL Server and schedule the job to run nightly at midnight

  19. 1. Consider the details of the source data.

  20. 2. Design a relational (3NF) database. * note: LaborMaster name chosen because table contains data about both employees and contractors. NOT USED

  21. 3. Script the creation of the relational (3NF) database AllWorksOLTP and its tables. USE [master] GO IF EXISTS(SELECT name FROM sys.databasesWHERE name= N'AllWorksOLTP') DROP DATABASE [AllWorksOLTP] GO CREATE DATABASE AllWorksOLTP; USE AllWorksOLTP; GO SAMPLE ONLY Create Table dbo.CountyMaster( CountyPKint primary key clustered, CountyDescriptionvarchar(100) not null ); Create Table dbo.ClientMaster( ClientPKint primary key clustered, ClientNamevarchar(100) not null, ProjectManagervarchar(100) not null, Cityvarchar(100) not null, [State] char(2) not null, ZipCodevarchar(50) not null, CountyPKintnot null, ClientImagevarchar(150), Constraint FK_CustMaster_CountyMaster Foreign Key (CountyPK) References dbo.CountyMaster(CountyPK) ); Create Table dbo.JobMaster( JobPKint primary key clustered , JobDescriptionvarchar(50) , ClientPKintnot null, MaterialMarkupPctdecimal (10,2) , AdditionalOverheadPctdecimal (10,2) , JobClosedFlagbit not null , JobClosedDatedatetime, JobStartedDatedatetime, Constraint FK_JobMast_ClientMast Foreign Key (ClientPK) References dbo.ClientMaster (ClientPK) );

  22. SSMS Database Diagram: AllWorksOLTP Database

  23. SSIS Project File (SSISStudentProject.JeffJacob.sln) details of common variables, source files and error log files: • Common Variables for ETL Packages - SSIS project: • Run-time Variables • changedRowCounter (rows updated) • newRowCounter (rows inserted) • errorRowCounter (FK-violating rows written to error log) • unchangedRowCounter (redundant/unused rows) • Configuration Variables • Variable NameDefault (debugging) Value • DBServerlocalhost • smtpServer sfexch0003 • EmailRecipient jeffrey.jacob@setfocus.com • SOURCE FILES: • TimeSheetFiles (Pkg: TimeSheets [uses CSV files]) • EmpTime*.csv • EmployeeExcelSourceFile (Pkgs: EmployeeMaster, EmployeeRates) • Employees.xlsx • OverheadExcelSourceFile (Pkgs: OverheadMaster, OverheadRates) • Overhead.xlsx • ClientGeographiesExcelSourceFile (Pkgs: CountyMaster, ClientMaster) • ClientGeographies.xlsx • ProjectMasterExcelSourceFile (Pkg: JobMaster) • ProjectMaster.xlsx • MaterialPurchasesSourceFile [uses XML file] (Pkg: Material Purchases) • MaterialPurchases.xml / .xsd (no Connection Variable) • InvoicesExcelSourceFile (Pkgs: Invoices, InvoiceXJobMaster, InvoiceReceipts) • Invoices.xlsx • ERROR LOG FILES: • (None - no FKs) (Pkgs: EmployeeMaster, OverheadMaster, CountyMaster) • no FK violations possible • EmployeeRatesErrorLogFile (Pkg: EmployeeRates) • EmployeeRatesErrorLog.txt • OverheadRatesErrorLogFile (Pkg: OverheadRates) • OverheadRatesErrorLog.txt • ClientMasterErrorLogFile (Pkg: ClientMaster) • ClientMasterErrorLog.txt • JobMasterErrorLogFile (Pkg: JobMaster) • JobMasterErrorLog.txt • TimeSheetsErrorLogFile (Pkg: TimeSheets) • TimeSheetsErrorLog.txt • MaterialPurchasesErrorLogFile (Pkg: MaterialPurchases) • MaterialPurchasesErrorLog.txt • InvoiceErrorLogFile (Pkg: Invoices) • InvoicesErrorLog.txt • InvoiceXJobMasterErrorLogFile (Pkg: InvoiceXJobMaster) • InvoicesXJobMasterErrorLog.txt • InvoiceReceiptsErrorLogFile (Pkg: InvoiceReceipts) • InvoiceReceiptsErrorLog.txt

  24. SSIS Project File (SSISStudentProject.JeffJacob.sln) details on project naming convention (ETL packages only): Project Naming Convention– Control Flow and Data Flow Objects: ETL Package Names <Function/Target Table> DataLoad (.dtsx) ETL Control Flow Objects Object Type Name Data Flow Task Read <data description> into (AllWorksOLTP) DB Send Mail Task Email successful (unsuccessful) ETL processing of <DB target table> Script Task Collect count of items processed (TimeSheetsDataLoad ETL package only) File System Task Delete Error File From File System (TimeSheetsDataLoad ETL package only) Foreach Loop Container Loop through TimeSheet data source files (TimeSheetsDataLoad ETL package only) ETL Data Flow Objects Object Type Name Excel Source Get Excel source data Flat File Source Get TimeSheet CSV files XML Source Get Material Purchases data Data Conversion Convert data to DB-compatible data types Lookup Transformation (FKs) Validate incoming FK values against <parent table> Lookup Transformation (PKs) Validate incoming PK values against <parent table> Conditional Split (update check) Split out real update rows from redundant rows Conditional Split (error split) Remove errors from main pipeline Row Count Transformation (new) Count new rows Row Count Transformation (changed) Count changed rows Row Count Transformation (unchanged) Count unchanged rows Row Count Transformation (errors) Count error rows OLE DB Destination Insert new rows Audit Transformation Add Processing-info audit trail fields - package name and time of processing OLE DB Command Transformation Update <Target Table> data Flat File Destination Send errors to error log Derived Column Transformation (No naming convention – named according to usage)

  25. SSIS Project File (SSISStudentProject.JeffJacob.sln) additional details on project naming convention (ETL packages only): Project Naming Convention– Data Flow columns : Data Conversion Field Lookup Column Field Derived Table Field Transformed<source field name> Target<source field name> (purpose-dependent: no naming convention) Samples of Data Conversion, Lookup and Derived Column Transformations in project:

  26. Sample of Email Task customization

  27. Sample of Lookup task to check for Foreign Key violating data rows; such rows are errors and are counted and sent to an error file (below).

  28. Sample of Lookup task to check Primary Key values in incoming data rows; new Primary Key values are new rows to be inserted and pre-existing Primary Key values are update candidates which are checked for redundancy; inserts and updates are split, counted and sent for DB Insert/Update commands respectively.

  29. Overview of LaborMaster ETL package data flow

  30. Overview of CountyMaster ETL package data flow

  31. Overview of JobMaster ETL package data flow

  32. C# script to enable file and row count totals: MaterialPurchases data ETL package: Control Flow and Variables

  33. Overview of TimeSheets ETL package data flow

  34. Source of ClientPK data: Invoices data ETL package: Control Flow and Variables

  35. Overview of Invoices ETL package data flow

  36. ALL ETL Packages are configured with these parent package variables

  37. Overview of the Master Package

  38. Sequence Container detail view

  39. Project deployment to SQL Server SSMS view of deployed project:

  40. Setting up a SQL Agent Job to execute the AllWorks OLTP master package every night at midnight.

  41. Confirmation of successful Agent job / project execution

  42. OLAP Cube ProjectSQL Server Analysis Services (BIDS) 2008 SetFocus Business Intelligence Master’s Program Jeff Jacob, MBA P: (312) 772-6142 E1: Jeffrey.K.Jacob@gmail.com E2: Jeffrey.Jacob@SetFocus.com TOC

  43. Project Objectives • Enhance a relational data warehouse with a fact and dimension table structure (preparing the ‘staging area’) for AllWorks • Create and define an OLAP cube according to end-user specifications using the enhanced data warehouse as the staging area data source • Relate dimensions in accordance with PK-FK relationships • Surface attributes and build hierarchies in dimensions • Define calculated members and dynamic named sets according to end-user specifications (MDX) • Implement a Standard Action allowing end-users to view Bing maps of the respective county (dimensional attribute) • Create MOLAP partitions for each fact table • an archive partition for all data prior to June 16, 2005 • an active partition for all data on or after June 16, 2005 • develop MOLAP aggregations for a 50% retrieval performance increase • Design cube perspectives around measure groups for end-user ease • Define KPIs to provide additional, useful data viewing (MDX) • Document the OLAP Project (this slide-deck)

  44. Fact and Dimension Table Structure Scalar functions to covert dates to corresponding Saturdays to get a corresponding Date dimension table key value USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatDatesConverter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[SatDatesConverter] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[SatDatesConverter] (@orderdatedatetime) RETURNS datetime AS BEGIN DECLARE @satdatedatetime SET @satdate = (SELECT DATEADD(d, 7- DATEPART(dw, @OrderDate), @OrderDate)) RETURN @satdate END; GO USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfGetDateKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[udfGetDateKey] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udfGetDateKey] (@entrydatedatetime) RETURNS int AS BEGIN DECLARE @datekeyint SET @datekey = ( SELECT WeekendKey FROM DimDates WHERE SUBSTRING (DATENAME(mm, dbo.SatDatesConverter(@entryDate)),1,3) +' '+ DATENAME(dd, dbo.SatDatesConverter(@entrydate)) + ', ' + DATENAME(yy, dbo.SatDatesConverter(@entryDate)) = DimDates.WeekendName ) RETURN @datekey END; GO

  45. Fact and Dimension Table Structure Scalar functions to obtain hours worked by an employee on a job to get the applicable employee pay rate based on date and ID USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfGetEmpHoursWorkedByProject]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[udfGetEmpHoursWorkedByProject] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udfGetEmpHoursWorkedByProject] (@EmployeePKint, @JobPKint, @workdatedatetime) RETURNS decimal(10,2) AS BEGIN DECLARE @hours decimal(10,2) SET @hours = ( SELECT HoursWorked FROM JobTimeSheets WHERE EmployeePK = @EmployeePK and JobMasterPK = @JobPK and WorkDate = @WorkDate ) RETURN @hours END; GO USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfGetEmployeeRate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[udfGetEmployeeRate] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udfGetEmployeeRate] (@employeepkint , @workdatedatetime ) RETURNS decimal(18,2) AS BEGIN DECLARE @Rate decimal(18,2) SET @rate = ( SELECT hourlyrate FROM EmployeeRateser WHERE EmployeePK = @EmployeePK AND EffectiveDate = (SELECT MAX(effectivedate) FROM EmployeeRates WHERE EmployeePK = @EmployeePK AND EffectiveDate <= @workdate) ) RETURN @rate END; GO

  46. Fact and Dimension Table Structure Table-valued function to obtain the set of Overhead Keys and Rates to apply to ‘hours worked’ based on the work date and employee flag • USE [AllWorksDW] • GO • IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfGetOHRatesAndPKs]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) • DROP FUNCTION [dbo].[udfGetOHRatesAndPKs] • GO • SET ANSI_NULLS ON • GO • SET QUOTED_IDENTIFIER ON • GO • CREATE FUNCTION [dbo].[udfGetOHRatesAndPKs] • (@workdatedatetime, @EmpFlag bit) • RETURNS TABLE • AS • RETURN • SELECT ohr.OverheadPK, ohr.HourlyRate • FROM OverheadRatesohr • WHERE (UseEmployeeFlag = 1 AND @empflag = 1 • AND EffectiveDate = (SELECT MAX(effectivedate) FROM OverheadRatesohs • WHERE EffectiveDate <= @workdate • AND UseEmployeeFlag = 1 • AND ohs.overheadpk = ohr.overheadpk) ) • OR (UseContractorFlag = 1 AND @empflag = 0 • AND EffectiveDate = (SELECT MAX(effectivedate) FROM OverheadRatesohx • WHERE EffectiveDate <= @workdate • AND UseContractorFlag = 1 • AND ohx.overheadpk = ohr.overheadpk ) ) • GO

  47. Fact and Dimension Table Structure Stored Procedure – DimDates table to create a permanent date dimension table to populate the date table with the needed date range USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CreateDimDates]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_CreateDimDates] GO CREATE PROCEDURE [dbo].[usp_CreateDimDates] AS BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDates]') AND type in (N'U')) DROP TABLE [dbo].[DimDates] CREATE TABLE DimDates (WeekendKeyint IDENTITY PRIMARY KEY CLUSTERED, WeekEndNamevarchar(30), QuarterKeyint, QuarterNamevarchar(30), [Year] int) DECLARE @StartDate Date, @EndDate date SET @StartDate = '12-27-2003' -- ensures all 2004 weekends included SET @EndDate = '1-5-2008' --ensures all 2005-2007 weekends included WHILE @StartDate <= @EndDate BEGIN INSERT INTO DimDates ( WeekEndName, QuarterName, [Year], QuarterKey ) VALUES ( DATENAME(mm, dbo.SatDatesConverter(@StartDate)) +' ' + DATENAME(dd, dbo.SatDatesConverter(@StartDate)) +', ' + DATENAME(yy, dbo.SatDatesConverter(@StartDate)), 'Q' + CAST(DATEPART(qq,@StartDate) AS varchar(1)) + ' ' + CAST(YEAR(@StartDate) as varchar(4)) , YEAR(@StartDate) , DATEPART(qq,@StartDate) ) SET @StartDate = DATEADD(day, 7, @StartDate) END -- end loop END -- close procedure GO

  48. Fact and Dimension Table Structure View – DimProject dimension table to create a database view with Project data USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[DimProject]')) DROP VIEW [dbo].[DimProject] GO CREATE VIEW dbo.DimProject AS SELECT JobMasterPK AS ProjectKey, [Description] AS ProjectName, JobMaster.ClientPK AS ClientKey, ClientName AS ClientName, Clients.CountyPK AS CountyKey, CountyName AS CountyName, CASE WHEN jobmaster.JobClosed = 1 THEN dbo.udfGetDateKey(jobmaster.JobClosedDate) ELSE dbo.udfGetDateKey('2004-10-02') END as JobClosedDateKey FROM JobMaster INNER JOIN Clients ON Clients.ClientPK = JobMaster.ClientPK INNER JOIN County ON County.CountyPK = Clients.CountyPK; GO

  49. Fact and Dimension Table Structure View – FactSummary fact table to create a database view, with summary measures for all projects/jobs regardless of activity USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FactSummary]')) DROP VIEW [dbo].[FactSummary] GO CREATE VIEW dbo.FactSummary AS SELECT jm.JobMasterPK AS ProjectKey, CAST( SUM( (AdditionalOverheadPct/100) * dbo.udfGetEmployeeRate(jts.EmployeePK, WorkDate) * dbo.udfGetEmpHoursWorkedByProject(jts.EmployeePK, jts.JobMasterPK, WorkDate) ) AS DECIMAL(14,2)) AS LaborProfit, CAST( SUM( (PurchaseAmount) * (MaterialMarkupPct/100) ) AS DECIMAL(14,2)) AS MarkupProfit, (SELECT SUM(AdditionalLabor) FROM InvoiceXJobMasterixjm WHERE ixjm.JobMasterPK = jm.JobMasterPK) AS AdditionalLaborProfit, (SELECT SUM(InvoiceAmount) FROM InvoiceXJobMasterixjm WHERE ixjm.JobMasterPK = jm.JobMasterPK) AS InvoiceAmount, (SELECT SUM(AmountPaid) FROM InvoiceReceiptsir WHERE ir.JobMasterPK = jm.JobMasterPK) AS ReceivedAmount FROM JobMasterjm LEFT JOIN JobTimeSheetsjts ON jm.JobMasterPK = jts.JobMasterPK LEFT JOIN JobMaterialPurchasesjmp ON jmp.JobMasterPK = jm.JobMasterPK GROUP BY jm.JobMasterPK GO

  50. Fact and Dimension Table Structure View – FactOverhead fact table to create a database view, only with Overhead measures actually observed USE [AllWorksDW] GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FactOverhead]')) DROP VIEW [dbo].[FactOverhead] GO CREATE VIEW dbo.FactOverhead AS SELECT ProjectKey, WeekendKey, OverheadType, CAST(SUM(OverheadCost) AS money) AS OverheadCost FROM( SELECT jts.jobmasterpk AS ProjectKey, dbo.udfGetDateKey(jts.workdate) AS WeekendKey, x.overheadpk AS OverheadType, (jts.HoursWorked * x.HourlyRate ) AS OverheadCost FROM JobTimeSheetsjts INNER JOIN Employees emp ON emp.employeepk = jts.employeepk CROSS APPLY dbo.udfGetOHRatesAndPKs(jts.WorkDate, Emp.EmployeeFlag) x ) dt GROUP BY ProjectKey, WeekendKey, OverheadType GO

More Related