1 / 28

Tracking Computer Inventory for Kaiser Permanente

Tracking Computer Inventory for Kaiser Permanente. Presenters Wilson Pranoto Muliawati Mandiro Marlina Kosasih. Overview. In the next 10 minutes… Company Background and Purpose EER Relational Schema Normalization Queries in SQL and in Access Query Snapshots IEOR Method Conclusion.

fbrockman
Download Presentation

Tracking Computer Inventory for Kaiser Permanente

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. Tracking Computer Inventory for Kaiser Permanente Presenters Wilson Pranoto Muliawati Mandiro Marlina Kosasih

  2. Overview In the next 10 minutes… • Company Background and Purpose • EER • Relational Schema • Normalization • Queries in SQL and in Access • Query Snapshots • IEOR Method • Conclusion

  3. The Organization Established in 1950s by Dr. Sidney Garfield Now … • America’s largest non-profit health organization • Committed to provide the best health service; thus efficient database is needed! What Kaiser Need? • Efficient & Reliable Database

  4. Current Setup (Simplified) Printer Server Computer # 1 Speed (Mhz), RAM (Mb), HD (Gb), Printer, etc . . . Computer # 2 Computer # N

  5. Raw Data Spreadsheet

  6. Installed_On Works_on Date End Start Time (0,N) (0,M) (0,N) (1,M) (0,N) Connected_To Retailer Manufacturer Distributed_By (1,1) Port Ordered_By (0,N) (0,N) (0,N) (1, M) Has 2 (0,M) (1, M) Made_By Located_At Planner Bought_from (0,1) (1,1) (0,N) (0,N) (1, M) (1, M) (1,1) Planner ID Has 1 Employee Room Connected_To should (1, M) (0,N) (0,N) (1,1) (0,N) Time Schedule Inventory Mainframe Located_At Date (1,1) (1,1) (1,1) Dept. (1,N) O User Used_By (1,N) Connected_To works Date (1,1) (1,N) (1,N) (0,1) O Owner Router Owned_By Maintainer O Hardware Maintainer (0,N) (0,M) Repaired_By RAM Price HD Price Printer (1,1) (1,1) Computer Software Maintainer (1,M) costs costs (1,1) (1,1) Components Includes (0,N) Type (0,1) (1,N) Type Include (0,N) (1,M) (0,N) (0,N) Software Experience_In (1,N) Includes (0,1) Skill level (1,N) d OS Runs_on (1,1) Utility

  7. Relational Schema

  8. M-N Relationship

  9. Normalization • Employee (SSN EID Lastname MI FirstName Birthday Sex Salary Position Hiredate Comment) • Repaired_by Rb (MaintEIDIIDRepair_date Reason Repairable Cost_of_replacement ReasonID) Rb1 (MaintEIIDIIDRepair_dateReasonID Repairable Cost_of_replacement) Rb2 (ReasonID Reason)

  10. Access Interface

  11. Hardware Upgrade Query PurposeTo help IT managers in deciding which computer needs to be upgraded by providing some information about the computer’s utilization and their upgrading price. SELECTDISTINCT [UPGRADING].IID, UPGRADING.RAM_REQ, UPGRADING.RAM_UPGRADE, RAM_PRICE.price AS RAM_PRICE, RAM_PRICE.RName AS BOUGHT_RAM_FROM, UPGRADING.HD_REQ, UPGRADING.HD_UPGRADE, HD_PRICE.price AS HD_PRICE, HD_PRICE.RName AS BOUGHT_HD_FROM, (RAM_PRICE.[price]+HD_PRICE.[price]) AS [TOTAL PRICE], UPGRADING.UTILIZATION FROM UPGRADING, HD_PRICE, RAM_PRICE WHERE (((RAM_PRICE.price) In (SELECT MIN(RAM_PRICE.price) FROM RAM_PRICE WHERE(RAM_PRICE.type)=UPGRADING.[RAM_UPGRADE])) AND ((HD_PRICE.price) In (SELECT MIN(HD_PRICE.price) FROM HD_PRICE WHERE (HD_PRICE.type)= UPGRADING.[HD_UPGRADE]))) ORDER BY UPGRADING.UTILIZATION DESC;

  12. Hardware Upgrade Query (con’t) UPGRADING QUERY (Sub Query) SELECTCOMPUTER.[IID], =IIf([Enter RAM min req]-COMPUTER.[RAM]>0, [Enter RAM min req]-COMPUTER.[RAM],0) AS RAM_REQ, =IIf([Enter RAM min req]-COMPUTER.[RAM]>0, (1+Int(([Enter RAM min req]- COMPUTER.[RAM])/32))*32,0) AS RAM_UPGRADE, =IIf([Enter HardDisk min req]-COMPUTER.[HardDisk]>0,[Enter HardDisk min req]-COMPUTER.[HardDisk],0) AS HD_REQ, =IIf([Enter HardDisk min req]-COMPUTER.[HardDisk]>0,(1+Int(([Enter HARDDISK min req]-COMPUTER.[HardDisk])/60))*60,0) AS HD_UPGRADE Sum(USER.[Logout_time]-USER.[Login_time])/(24*365) AS UTILIZATION FROM COMPUTER INNER JOIN USER ON COMPUTER.[UserID]=USER.[EID] WHERE(((COMPUTER.[RAM])<[Enter RAM min req])) Or (((COMPUTER.[HardDisk])<[Enter HardDisk min req])) GROUP BYCOMPUTER.[IID], COMPUTER.[HardDisk], COMPUTER.[RAM];

  13. In Access …

  14. Schedule Requirement Query PurposeTo have the ability to change or update the scheduled requirements as well as the maintainer’s schedules coinciding with the needed personnel for such departments like the ‘emergency room”. This also determines whether they need to hire more part time or full time employees to supply the possible demand of maintainers. SELECTPLANNER.PDay, PLANNER.[PStarting Time], PLANNER.[PEnding Time] FROMPLANNER WHERE(((NOT EXISTS (SelectWORKS.Day, WORKS.[Starting Time], WORKS.[Ending Time] FromWORKS Where PLANNER.PDay = WORKS.Day AND PLANNER.[PStarting Time] = WORKS.[Starting Time] AND PLANNER.[PEnding Time] = WORKS.[Ending Time]))=False));

  15. In Access …

  16. Bad Component Query Purpose To help the management decide which brand they should buy or avoid to replenish their inventory. COMPONENT1 QUERY (Sub Query) SELECTDISTINCT REPAIRED_BY.[IID], COMPONENT.[CIID], COMPONENT.[CBrand], Count(COMPONENT.[IID]) AS NoOfRepairs FROM COMPONENT, REPAIRED_BY WHERE (((COMPONENT.[IID])=REPAIRED_BY.[IID]) And (([enter year here])>COMPONENT.[Year bought] And ([enter year here])<=(COMPONENT.[Year bought]+1))) GROUP BYREPAIRED_BY.[IID], COMPONENT.[CIID], COMPONENT.[CBrand] HAVING (((Count(COMPONENT.IID))>2)); COMPONENT_Bad QUERY (Sub Query) SELECTCOMPONENT1.[CBrand], Count(COMPONENT1.[IID]) AS NoOfBadIID FROMCOMPONENT1 GROUP BYCOMPONENT1.[CBrand];

  17. Bad Component Query (con’t) COMPONENT_Total QUERY (Sub Query) SELECTCOMPONENT.[CBrand], Count(COMPONENT.[IID]) AS NoOfTotalIID FROM COMPONENT GROUP BYCOMPONENT.[CBrand]; MAIN QUERY SELECT DISTINCT C2.CBrand AS BRAND,(C3.NoOfBadIID/C2.NoOfTotalIID) AS RATIO FROM COMPONENT_Total AS C2, COMPONENT_Bad AS C3;

  18. In Access …

  19. Forecasting Query Purpose To help forecast the needed maintainers, the number of needed repairs, and it can also predict the budget needed for IT Department. SELECTNB.Department, Sum(NB.NoBreakdown) AS TotalBreakdown, Avg(NB.NoBreakdown) AS ExpectedValueOfBreakdowns, Avg(NB.[NoBreakdown])+(1.96*StDev(NB.[NoBreakdown]))AS UB, =IIf(Avg(NB.[NoBreakdown])- (1.96*StDev(NB.[NoBreakdown]))>0, Avg(NB.[NoBreakdown])-(1.96*StDev(NB.[NoBreakdown])),0) AS LB FROM Number_of_Breakdown AS NB GROUP BY NB.Department; NUMBER OF BREAKDOWN Query (Sub Query) SELECTI.[IID], I.[Department], Count(RB.[IID]) AS NoBreakdown FROMINVENTORY AS I INNER JOIN REPAIRED_BY AS RB ON I.[IID]=RB.[IID] WHERE (((I.[IIDType])="Computer") And ((RB.[Repairable])=Yes) And ((I.[Used_date]) Between #1/1/2001# And #12/31/2001#)) Or (((RB.[Repairable])=No)) GROUP BYI.[IID], I.[Department];

  20. Forecasting Report

  21. ‘Point’ For Workers Query PurposeTo know worker’s utilization and their contributions. In addition, the management can decide which employees to be promoted (such as bonuses or awards) or terminated. SELECTMP.[MaintEID], Sum(MP.[Points]) AS [Total Points] FROMMAINTAINERPOINT AS MP GROUP BYMP.[MaintEID] ORDER BYMP.[MaintEID]; MAINTAINER POINT QUERY SELECTMaintEID, Points1, Points2, Points1*Points2 AS Points FROMREPAIRED_BY_POINT UNION ALL SELECTMaintEID, Points1, Points2, Points1*Points2 AS Points FROMWORKS_ON_POINT;

  22. ‘Point’ For Workers Query (con’t) REPAIRED BY POINT QUERY SELECT M.MaintEID, =IIf(([RB.ReasonID])<5,1,2) AS Points1, =IIf(([RB.End_Time]- [RB.Start_Time])<0.04167,2,1) AS Points2 FROM Repaired_By AS RB, (SELECT DISTINCT M.MaintEID FROM Maintainer AS M) WHERE (((M.MaintEID)=RB.MaintEID) And (RB.Repair_Date Between [Start Date (for example 01/01/2002)] And [End Date (for example 12/31/2002)])); WORKS ON POINT QUERY SELECT M.MaintEID, =IIf(([WO.ReasonID])<5,1,2) AS Points1, =IIf(([WO.End_Time]- [WO.Start_Time])<0.04167,2,1) AS Points2 FROM Works_On AS WO, (SELECT DISTINCT M.MaintEID FROM Maintainer AS M) WHERE (((M.MaintEID)=WO.MaintEID) And (WO.Work_Date Between [Start Date (for example 01/01/2002)] And [End Date (for example 12/31/2002)]));

  23. In Access …

  24. Linear Programming Application • We want to use queries to do LP for maximizing utilization of remaining budget that IT department has for the year. • We use database to help estimate the remaining budget.

  25. Variables • X1R: # Employee Hours needed for regular • X1C: # Employee Hours needed for On Call • X2: # Software Upgraded • X3: # Purchasing new computer • X4: # Computer Repaired • X5: # buying new printer • X6: # purchasing router • X7: # building/creating/expanding network • X8J: # purchasing component (J = 1… n)

  26. Linear Program • Max s.t • (X1 * Salary) + (X2 * Upgrading Cost) + (X3 * computer_cost) + (X4 * (average_repair_cost-employee_cost)) + (X5 * printer cost) + (X6 * new router cost) + (X7 * Cost building new network) + ( X81 * monitor cost) + ( X82 * new keyboard cost) ≤ remaining budget • X1 ≤ (# employee needed to satisfy maintainer_requirement) • X2 ≤ (requested upgrading) • X3 ≤ (sum computer requirement from each department) • X4 ≤ (Forecasting # of breakdown for remaining time) • X5 ≤ (# required) – (# available) + (# working computer) • X6 ≤ requirement • X7 ≤ (# purchased computer) + (# current working computer)

  27. Thank You!!

More Related