43 %
57 %
Information about ag_mis_bi_datamarts_v2

Published on December 19, 2008

Author: aSGuest7323


Management Information Systems (MIS) : Management Information Systems (MIS) Attorney General/Public Safety & Solicitor General Business Intelligence (BI) Datamarts Architecture Best practices Datamart paradigm vs monolithic warehouse Dimensional models & terminology Future direction Gerhardt Lepp Data Architect Ministry of Attorney General MIS Datamarts : MIS Datamarts Court Services Datamart Criminal Justice Datamart Corrections Datamart Finance Datamart A data warehouse is a collection of independent datamarts with shared architecture and Dimensions. Police Requirements : Requirements Start with the questions to be answered. Example: How many Requests to Crown Counsel are received each month by Agency, Crown Counsel Decisions and Statute. Provide Total Counts on all RCC’s, Avg Accused/RCC & Avg Counts/RCC. Requirements : Requirements Start with the questions to be answered. Example: What is the total budget in a given period for a prison or police program with regional breakdowns? How much has been spent to-date from the current fiscal’s budget and how much remains in the budget? Has the program reduced recidivism over the last decade? Which programs have the best return on investment? What do the datamarts have in common? : What do the datamarts have in common? Shared infrastructure servers, structure, software Shared architecture data flow, standards, planning Shared dimensions Shared tables that describe the business Example Dimensions: Agency, Act, Statute, File, Participant Profile, Adjudicator, Finding Implications of Adding 100 Police Officers : Implications of Adding 100 Police Officers Treasury board wants information on the implications of adding 100 police officers to the existing police forces Crime rates by province and region Number of police officers/capita Percent of crimes solved Vigilantism Trends in Remand Courts : Trends in Remand Courts Activity in remand courts is increasing across Canada History of backlog in courts Breakdown by regions Are judges making different decisions? How is the bed count affected? Is media attention affecting decisions? Why is this happening? What will it cost? How does it affect policies and planning? Transforming Data into Information : Transforming Data into Information PowerPlay Explorer PowerPlay Cube Datamart ETL Transformer Publish to Web A datamart is a subset of data extracted from an operational database. It is created for a Branch, Region or subject area. Slide 10: Ministry Portal(?) Business Intelligence (BI) Webserver Reports Ministry Data Warehouse (SQL server) User Guides Powerplay Cubes Source System Layer Warehouse Layer Presentation Layer Extract Transform Load (ETL) MIS BI/Datamart Architecture Objectives of Datamarts : Objectives of Datamarts The datamart and business intelligence (BI) tools must do the following: Make data easily accessible. Make data easy to understand and query. Support security requirements. Present information consistently. Be adaptable and resilient to change. Be a foundation for improved decision making. Consolidate data from different sources. Be accepted & driven by the business community. Prevention of Inappropriate Centralization : Prevention of Inappropriate Centralization Ralph Kimball: “A centrally planned data warehouse is as likely to be successful as a centrally planned economy. It sounds great on paper, and it appeals to the controlling instincts of IT, but a centrally planned data warehouse makes the assumptions of perfect information and control. Eventually, the problems with these assumptions, like those of a centrally planned economy, come home to roost. In the long run, a data warehouse should be a decentralized community of data marts, tied together with an architecture that makes them work together effectively, but where true control is ceded to the individual and autonomous remote departments.” Different Models for Different Purposes : Different Models for Different Purposes Entity Relationship model for maintaining data Dimensional model for exploring data Lessons Learned : Lessons Learned Many monolithic data warehousing projects have failed. Design branch or function level datamarts that meet immediate needs. Business Intelligence requirements are different from operational reporting requirements. The entity relationship models used for transactional systems are difficult for end-users to query. Dimensional models (star schemas) are more appropriate models for datamarts. Business Intelligence tools such as PowerPlay are excellent reporting tools. The Million Dollar Question : The Million Dollar Question Some questions are not restricted to a single branch of the Ministry. Monolithic data warehouse projects have a low success rate. Poor decisions can cost much more than the cost of MIS development. Is there a successful formula for MIS development? Slide 16: Dimensions Link the Datamarts Shared (Conformed) Dimensions Dimensional models are built from ER models : Dimensional models are built from ER models The Entity Relational Model is transformed into a Dimensional Model and stored in a datamart to make it easier to query. Dimensional models contain the same information in a format that is easier to query. Dimension Tables : Dimension Tables Start by building a view consisting of several source tables. Copy the view to a single physical table in the datamart. Attributes of Dimensions are used as parameters for queries. Ex. CLIENT_D.Gender Relatively stable information. Relatively small number of records. Example dimensions: Store, Product, Time, Location, Agency, Appearance Result Fact Tables : Fact Tables Fact tables contain information on the numbers we want to measure or the events we want to count. Often involve transactions, observations or events. Each fact table is related to several dimension tables in a one to many relationship. Millions of records. Fact tables: Sales, Appearances, Movements, Observations Dimensional Model Star Schema from CEIS MIS : Dimensional Model Star Schema from CEIS MIS CASE_STATUS_F TIME_D LOCATION_D ACT_D CASE_D APPEARANCE_RESULT_D In Fort Nelson, how many child custody cases last year took more than 80 days to reach completion. Dimensional Model : Dimensional Model Benefits: Understandability Fast query performance Intuitive framework It is a simplified relational model Single Star Schema : Single Star Schema CASE_STATUS_F TIME_D LOCATION_D ACT_D CASE_D APPEARANCE_RESULT_D A large ER model may be transformed into several dimensional models. CEIS Dimensional Model : CEIS Dimensional Model CASE_STATUS_F APPEARANCE_F SESSION_F DOCUMENT_F TIME_D LOCATION_D ACT_D CASE_D APPEARANCE_RESULT_D Several star schemas with shared dimensions. Justice Dimensional Model : Justice Dimensional Model POLICE CRIMINAL JUSTICE COURTS CORRECTIONS TIME LOCATION (AGENCY) STATUTE ACCOUNT CASE PARTICIPANT ORDER RCC The Dimensions are a subset of the larger model. Facts & Dimensions are views built from several tables from Justin, Cornet, CEIS and other source systems. FINANCE Slide 25: Dimensions Link the Datamarts Shared (Conformed) Dimensions Benefits of a Well DesignedCollection of Datamarts : Benefits of a Well DesignedCollection of Datamarts Easier to query data Better response time & reliability Common architecture & better support Less expensive maintenance Better access to data to more users More reliable data Ability to answer questions that span individual datamarts Questions That Span Datamarts : Questions That Span Datamarts Court room availability for both civil and criminal cases. Cost of programs. Forecasting effects of changes in legislation on the corrections system. Forecasting effects of changes to the police system on justice and corrections system. BI Datamart Success Stories : BI Datamart Success Stories Ministry of Provincial Revenue BC Building Corporation BC Assessment Authority BC Hydro BC Ferries Ministry of Education Attorney General (CJB, CSB, Corrections, Finance) Slide 29: Questions? Get more information at: Strengths of Dimensional Model : Strengths of Dimensional Model Predictable standard framework Withstands unexpected changes in user behavior Logical design largely independent of query patterns Extends gracefully to accommodate new data elements Works well with Business Intelligence reporting tools such as Cognos Powerplay. Both work on the paradigm of Dimensions and Facts/Measures . Works well with factless fact tables. Slide 34: Ministry Portal(?) Business Intelligence (BI) Webserver Reports Ministry Data Warehouse (SQL Server Database ) User Guides Cognos Powerplay PowerPlay Cubes Source System Layer Warehouse Layer Presentation Layer Extract Transform Load SQL Server Data Transformation Services Oracle PL/SQL Legacy Foxpro & MS Access AG MIS Data Warehouse Tools PowerPlay Transformer PowerPlay Enterprise Server, Upfront, IIS, Windows 2000 Server, Sun One Active Directory MS Access SQL Server Enterprise Manager Any reporting tool 3 tier reporting The Kimball Model Works : The Kimball Model Works Many data warehousing efforts have failed because they were single centrally planned projects. The current paradigm is a series of datamart projects driven by the branches and coordinated by ITSD. The CEIS MIS project is an excellent example of this. The co-operation on this project is an important breakthrough that has allowed ITSD, Court Services Branch and Fujitsu, the vendor to each contribute their best. The Kimball model works.

Add a comment

Related presentations