BUSINESS INTELLIGENCE FOR DECISION SUPPORT SYSTEMS P L ENIN T ECHNICAL D IRECTOR

1. BUSINESS INTELLIGENCE FOR DECISION SUPPORT SYSTEMS P LENIN TECHNICAL DIRECTOR NATIONAL INFORMATICS CENTRE MINISTRYOF COMMUNICATION & IT NEW DELHI -110 003.

2. DATA AVAILABILITY • MASSIVE AMOUNT /ELECTRONIC FORM • WIDESPREAD USE OF APPLN SYSTEMS • POWERFUL H/W, PARALLEL SYSTEMS • NEW S/W TECHNOLOGY Why Now, Not Then?

3. DEMAND FOR DATA Growing gap Growth in user demand IS ability to deliver Source: Gartner Group

4. P & V DB Admin -I Admin -II Library District States Appln Div. Purchase Projects A/CDiv StoresDiv Web Services NICSI User Min.s Contact DG Office ABCDiv. XYZDiv. LMNDivision ER MODEL

5. WHY BUSINESS INTELLIGENCE SOLUTIONS ? BI SYSTEMS  NOT A REPLACEMENT FOR RDBMS/OLTP  TO COMPLEMENT RDBMS/OLTP SYSTEMS  FOR PLANNERS AND DECISION MAKERS * USE WHEREVER AND WHENEVER /WEB BASED * MINIMUM DEPENDENCY ON ITPROFESSIONAL * EXTRACT IMPLICIT KNOWLEDGE/PATTERN

6. WHY BI/ DATA WAREHOUSE? • -NEED OF INFORMATICS REQUIREMENT IS DYNAMIC • -ALSO NEED OF EVERY DEPTT VARIES • [SECRETARY/COMMISSIONER/JT.COMM/DY.COMM.] • -INTEGRATED/SHARED INFORMATION • DESIGN NOT SUITABLE FOR BUSINESS ANALYSIS • LIMITED ANALYSIS • DATA CONTENT CHANGES • VIEWS BASED ON OBJECTIVES • REASONING [EX. PDS PUNJAB/ORISSA]

7. WHY BI TECHNOLOGY? -TO HANDLE DATA FROM MUTI-SOURCE - TO HANDLE LARGE VOLUME OF DATA - MULTIDIMENSIONAL –DRILL THRU-ANALYSIS - PRESERVE AND USE THE HISTORICAL DATA - TO EXTRACT HIDDEN KNOWLEDGE/PATTERN - TO DO AD HOC QUERY : :

8. WHAT BENEFITS YOU GET OUT OFDW? • * EASIER FOR END USERS TO NAVIGATE, • UNDERSTAND AND QUERY • ENABLE QUERIES THAT CUT ACROSS • DIFFERENT SEGMENTS • COMPLEX QUERY IN NORMALIZED DB • CAN BE BUILD EASILY • EFFICIENT WAY TO MANAGE AND REPORT • PROVIDES THE CAPABILITY TO ANALYZE LARGE • AMOUNT OF HISTORICAL DATA

9. Data Warehouse • W H INMON, • A DW IS A • SUBJECT ORIENTED (Vs APPLN. ORIENTED), • INTEGRATED, • TIME VARIANT (HISTORICAL vs. CURRENT), • NONVOLATILE (STABLE vs. CONTINUOUS CHANGE) COLLECTION OF DATA IN SUPPORT OF MANAGEMENT‘S DECISION MAKING PROCESS. Maintained separatelyfrom the organization’s operational database

10. A SAMPLE DATA CUBE /DIMENSION MODEL Total annual sales by country and product Date 2Qtr 1Qtr sum 3Qtr 4Qtr TV Product U.S.A PC DVD sum CHINA Country Total of all item sales in all countries by product Total of all product sales by country and quarter INDIA sum Total annual sales of all items by Country Total annual sales. Total of all product sales in all countries by quarter

11. CARS SOLD ORDER DATE NO SANTRO MARUTI 1 08/08/04 5 2 2 3 0 3 2 6 2 2 4 5 3 3 7 09/08/04 1 3 1 2 2 4 0 3

12. CARS SOLD Date Santro Maruti 08/08/04 15 13 09/08/04 9 8 NO OF RECORDS REDUCED BY AGGREGATION

13. Exploration / analysis SQL reporting Cleansing / normalization Transaction processing Transformation of Data Information Warehouse Data

14. Basic elements of Data warehouse OPERATIONAL SOURCE SYSTEM DATASTAGING AREA DATAPRESENTATION AREA DATAACCESSTOOLS • Services: • Clean, combine, and standardizeConform DimensionsNo user query services • Data Store: • Flat files and relational tables • Processing: • Sorting and sequential processing • Data Mart #1 • DimensionalAtomic and summary dataBased on a single business process Ad hoc query tools Report Writers Analytical Applications Modeling: Forecasting Scoring Data Mining Extract Load Access DW Bus:Conformed facts and dimensions Extract • Data Mart #2 • Similar design Extract Load Access

15. METADATA METADATA

16. THE OLAP SOLUTION A PROCESS OF FAST ANALYSIS OF SHARED MULTIDIMENSIONAL INFORMATION --ENTERPRISE-WIDE DATA ANALYSIS

17. OLAP - FAST ? - WHAT ANALYSIS? - SHARED - MULTIDIMENSIONAL ON ENTERPRISE-WIDE DATA VIEW FROM DIFFERENT ANGLE

18. LOCATION Time SOURCE Year REGION SALES TAX Quarter STATE CEN.EX Month District VAT Week City XYZ # Drill-down Process (Browsing) ** What all with OLAP?

19. BI SYSTEMS DEVELOPMENT IN NIC • SOCIO ECONOMIC DATA OF UP • BHOOMI LAND RECORDS • IRRIGATION SURVEY DATA • SPECIAL FRAUD INVESTIGATION • FERTILIZER PROD/MOVT/CONSUMPTION • EXPORT/IMPORT OF MAJOR COMM+INDEXES • FOR RIS/COMMERCE MIN. • * CUSTOMS IMPORT • --- ALL AT NIC HQR.

20. BI SYSTEMS DEVELOPMENT IN NIC • STATE UNITS: • KERALA • Treasury Expenditure • Sub-Reg Office Stamp Duty Collection • Civil Supplies Office Expense • Disease survelliance • TAMIL NADU • Child Labour • Education • MAHARASHTRA/PUNE • ANDHRA PRADESH • GUJARAT

21. CUSTOMS IMPORT DATA LIMITED INPUT CUSTOMS IMPORT DATA ITEM LEVEL DATA [Bill of Entry, Assessment value, Duty, Duty Foregone, CTH, CETH, BCD NOTFN, ….] 20 SITES ONLY TIME RANGE: APRIL 2003 TO DEC. 2004 DATA SIZE = 1.45 Cr RECORDS = 5GB

22. STORAGE AREA NETWORK (SAN) DATA SERVER (ORACLE 9i/10G) RETRIEVE ETL (DECISION STREAM) Extract CLIENT To access & change the layout of Powerplay and ReportNet reports… • COGNOS SERVER • POWERPLAY • REPORTNET • CLEMENTINE • WINDOWS 2000 SERVER • (OS) Store Load Request Response NIC DW ARCHITECTURE DATA SOURCE

23. BI FOR BHOOMI LAND RECORDS • TO DEMONSTRATE THE POWER OF BI TECH • JOINT EFFORT BY A&M DIV AND NIC-KSU • NIC KSU …DOMAIN EXPERT • A&M DIV AS BI TECH EXPERT • FOR KANNADA LANGUAGE TOOK ASSISTANCE • FROM KEYSOFT

24. BI SYSTEM PLAN FOR KSU STATE • USE THE HW/SW FACILITIES AT THE HQRS. • KEEP THE DATA AT KSU SERVER AND USE HQR • FACILITIES FOR SW • HAVE BOTH HW AND SW IN THE KSU SERVER