Transcription
FSA Data Warehouse:Building a Web-Enabled DataWarehouse
Building a Web-Enabled DataWarehouse Jerry BramstedtProject ManagerFarm Service AgencyU.S. Department of Agriculture Lester KnutsenDatabase ConsultantAdvanced DataTools CorporationMarch 2000Advanced DataTools Corporation2
FSA Business Problem Implementation of new accounting system.– How to replace existing reports?– Significant cost in developing new reports. Data detail cannot easily be viewed withouta great deal of effort. Reconciliation can be difficult - knowingthe activity behind a balance. Standard reports are not dynamic.March 2000Advanced DataTools Corporation3
FSA Business Problem Query and analysis tools are limited. Additional reports and queries often requirethat technical staff be involved. Reports can take hours to be run, difficult toprovide reliable information quickly tousers. Time lags in distribution of information– Users of data all over countryMarch 2000Advanced DataTools Corporation4
FSA Data Warehouse Timeline Jan 1999 - Data Warehouse PilotMarch - August - BenchmarksMay 1999 - Production LoadsMay 1999 - Web Server & Go LiveJune 1999 - Operations & User TrainingAug 1999 - ETS Data WarehouseOct 1999 - CCC Data WarehouseNov 1999 - New Sun Server InstallMarch 2000Advanced DataTools Corporation5
Objectives of the DataWarehouse Efficient distribution of information via the WEB Minimize technical involvement by enabling usersto generate and maintain their own reports Create a user-friendly reporting environment Provide easy access to data from different sources Lay the foundation and develop plans for full datawarehouse development and implementation.March 2000Advanced DataTools Corporation6
A Data Warehouse is: A collection of data from various sourcesfor query, reporting and analysis Time - oriented Subject variantMarch 2000Advanced DataTools Corporation7
Data Warehouse vs Data Mart Data Warehouse focuses on enterprise widedata across many or all subject areas Data mart is restricted to a single businessprocess or single business group Union of data marts equal data warehouseMarch 2000Advanced DataTools Corporation8
Data Warehouse Storage of large volumes of dataHistorical dataLoad and save - no updatesReporting systemQuery and analysisTrends and forecastingMarch 2000Advanced DataTools Corporation9
Data Warehouse ingSystemMarch 2000DataWarehouseSystemAdvanced DataTools CorporationCharts &GraphsAd-hocQueries10
Data Warehouse ArchitectureData SourceFederalFinancialSystem(FFS)MainframeData WarehouseServerStaging Areaand LoadDatabaseData WarehouseDatabaseWebUsersWebandBrioServer
Data Warehouse Design How do you organize the data? Keep it Simple goal is fast data access goal is ease of use Star schema Fact Tables - contain the detail data Dimension Tables - look-up information Summary Tables - pre-summarized resultsMarch 2000Advanced DataTools Corporation12
Data Model - Star nFact TablecontainsdetailedinformationProgramCodesMarch 2000TimeDimensionOrganizationDimensionAdvanced DataTools Corporation13
Data WarehouseDesign Considerations Summary Tables Goal is to pre-summarize data to meet 90% of userqueries Most queries are for month-to-date totals andbalances therefore summary tables are month-todate Use Triggers and Stored Procedures to maintainFinancial Summary table. When a new record isloaded the summary table is automatically updated.March 2000Advanced DataTools Corporation14
Data WarehouseDesign Considerations What is the “Key Question” you want to askof your data? Organize and summarize your data toanswer this question quickly Allow for unexpected ad-hoc queries of thedata to provide new insight into yourbusinessMarch 2000Advanced DataTools Corporation15
Data WarehouseDesign Considerations Warehouse Size Salaries/Expense Gen Journal - 15M rows per year CCC Gen Journal - 100M - 200M rows per year Estimated 100 GB of data per year Fragmentation Plan by state/division by year and accounting period by fundMarch 2000Advanced DataTools Corporation16
Server Configuration Sun E5500– 800 GB Data Storage– 3 GB Memory– 6 CPUs Dell Web Server– NT Server– 4 CPUsMarch 2000Advanced DataTools Corporation17
Data Loading IBM mainframe “pushes” the data to thedata warehouse at the end of its nightly jobcycle (via ftp) Cron job checks for new data and starts load Loads Dimension Tables when data has changed Loads daily General Journal FileMarch 2000Advanced DataTools Corporation18
Data Loading High Performance Loader loads data into astaging database Converts IBM EBCDIC data SQL program extracts financial recordsfrom daily journal and loads into fact table SQL/shell scripts loads and rebuildsdimension tablesMarch 2000Advanced DataTools Corporation19
Data Loading Financial Summary table is updated byTriggers and SQL Other Summary tables are completelyrebuilt from Financial Summary Last step of the load is to re-generate thestandard Brio predefined query modelsMarch 2000Advanced DataTools Corporation20
Brio Query and Analysis Tools Brio Designer - High End Developer toolBrio Explorer - Power User/DeveloperBrio Insight - Web data analysisBrio Enterprise Server Brio Scheduler Server Brio OnDemand ServerMarch 2000Advanced DataTools Corporation21
Brio Web InterfaceBrioServerAd HocQueriesDataWarehouseDatabaseDaily LoadMarch BQYBriofiles)Queries(BQYBriofiles)Queries(BQY files)(BQY files)Advanced DataTools wserWebBrowserBrowser22
Web Interface Brio Insight - a web browser plug-in thatruns on Windows, NT, and Unix Allows user to analyze data Create Pivots Create Charts Create Reports Access pre-defined queries (bqy files) Create ad hoc queriesMarch 2000Advanced DataTools Corporation23
Benefits Rapid distribution of data to end-users. Ability to explore data not currently available, andto produce ad-hoc reports more quickly and easily. Establish an infrastructure for future iterations ofthe CCC Data Warehouse Knowledge and experience in building a datawarehouse that can be applied to subsequentwarehouse development efforts.March 2000Advanced DataTools Corporation24
Data WarehousePresentationMarch 2000Advanced DataTools Corporation25
March 2000Advanced DataTools Corporation26
March 2000Advanced DataTools Corporation27
March 2000Advanced DataTools Corporation28
March 2000Advanced DataTools Corporation29
March 2000Advanced DataTools Corporation30
March 2000Advanced DataTools Corporation31
Keys to Implementation Involve Users in building a PrototypeDevelop a Production SystemGo LiveTrain the Users and Operations StaffRefine, Refine, RefineMarch 2000Advanced DataTools Corporation32
Implementation PlanPhase 1Phase 2Creating a Data Procuring theWarehouseDataPrototypeWarehouseEquipment andConsultingServicesPhase 3Phase 4Phase 5Phase 6Developing theDataWarehouseSoftware andConverting theInitial DataInstalling theDataWarehouseHardware,Software andConverted DataTraining theDataWarehouseUsers andOperationalStaffRefining theDataWarehouseData, Queriesand ReportsDataWarehousePrototypeContracts and OperationalImplementation Software,PlanInitial Queries,Reports andDataDataWarehouseGoes LiveSoftwareRevisedDocumentation Queries andand UserReportsManuals6 Weeks1 Months1 Months1 Month3 Months3 Months
Prototype PlanWeek 1Week 2Week 3Week 4Week 5Week 6Creating theVision andObjectives of theData WarehouseDeveloping theData WarehouseDesignConverting theData for theData WarehousePrototypeConverting theData for theData WarehousePrototypeConfiguringand Loading theData WarehousePrototypeUsing the DataWarehouseTools andPresenting theResultsData WarehouseLaunch ScopeDocument - UserRequirementsData WarehouseLaunch PlanDocument Design andSpecificationsConverted DataConverted DataPrototypeDatabaseData WarehouseLaunchSummaryDocument Project Plan
Workshop Method GroupParticipation Brainstorm Summarize Prioritize
Prototype Results Converted ActualData Sample Queries and Reports Implementation Plans A System You can Evaluate
March 2000Advanced DataTools Corporation37
March 2000Advanced DataTools Corporation38
Lester KnutsenAdvanced DataTools CorporationPhone: datatools.comJerry BramstedtFarm Service AgencyU.S. Department of AgriculturePhone: 816-926-6866gfbramstedt@kcc.fsa.usda.gov
Brio Query and Analysis Tools Brio Designer -High End Developer tool Brio Explorer -Power User/Developer Brio Insight -Web data analysis Brio Enterprise Server Brio Scheduler Server Brio OnDemand Server. March 2000 Advanced DataTools Corporation 22 Brio Web Interface Data Warehouse Database Web Browser
