OLAP Modeling And Application Of Tobacco BI System

With the development of decision-making analysis technology, moreand more enterprises decision-makers are aware of a flood of corporate datahidden in the immense business opportunities. One of main problem is howto make the data become to be useful information for decision. Traditionalenterprisedatabasesystemison-linetransactionprocessingsystems(OLTP)asadatamanagementtool,mainlyfortransactionprocessing,butithasthepoor support of analytical processing. Therefore, it was gradually trying toreprocessingdataofOLTPdatabase,forminganintegratedorientedanalysisand better decision-making support for the BI System. Data warehouse andOn-Line Analytical Processing (OLAP) technology play a crucial role increatingBIsystem.Thispaperhasthreemainpiecesofwork.First piece of work: research data warehouse architecture - CorporateInformationFactory.Corporation information factory is a general architecture of datawarehouse. The objective is to use its operations data, providing intelligentbusiness management and decision-making support. It provides a logicframeworkandmethodologyoforganizationalandmanagementinformationsystem for integrating the data warehouse, OLAP, data marts, operationaldata storage(ODS) and ETL technologies to meet the actual businessdecision-makingneeds.Theprojectisdevelopedcollaborativelybyfourgroups.1,DataExtract-Transformation-Load(ETL)usingInformaticaPowerCenter8.1.2,DatawarehouseadoptSYBASEIQdatabase.3, Data query, analysis and decision support used MicroStrategy which iscurrently very popular BI show tool. According to the data in the datawarehouse to establish OLAP application model and store metadatainformationintoDB2database.4,Developed information resources management system in the framework ofJ2EEtomanageandmaintainresourcesofdatacentre.The whole process is to extract data from the operational sourcesystemstothedatastagingarea.Thedatastagingareaofthedatawarehouseis both a storage area and a set of processes commonly referred to asextract-transformation-load. To load the data into the data warehouse; Tocreate OLAP model in accordance with the actual business logic; To use ofBI tools for data display and analysis to provide decision support.Thestructuremadeoperationalsourcesystemsandpiecesofdatawarehouseintoorganization, thereby creating an integrated comprehensive solution of datacenter.Secondpieceofwork:researchmodelingdatawarehouse.DimensionalModelingisademandforuser-oriented,easytounderstand,visit highly efficient data warehouse modeling method. Fact table anddimension table are two basic elements of Dimension Modeling. A facttable is the primary table in a dimensional model where the numericalperformance measurements of the business are stored. We use the term factto represent a business measure. A measurement is taken at the intersectionof all the dimensions (day, product, and zone). The most useful facts arenumeric and additive such as sales amount. Additivity is crucial becausedata warehouse applications almost never retrieve a single fact table row.Rather, they bring back hundreds, thousands, or even millions of fact rowsat a time, and the most useful thing to do with so manyrows is to add themup. All fact tables have two or more foreign keys, as designated by the FKnotation that connect to the dimension tables'primary keys. For example,the product keyin the fact table always will match a specific product key inthe product dimension table. We access the fact table via the dimensiontablesjoinedtoit.Dimensiontables are integral companions to a fact table. Thedimensiontables contain the textual descriptors of the business. In a well-designeddimensional model, dimension tables have many columns or attributes.These attributes describe the rows in the dimension table. It is notuncommon for a dimension table to have 50 to 100 attributes. Dimensiontablestendtoberelativelyshallowintermsofthenumberofrows(oftenfar fewer than 1 million rows) but are wide with many large columns. Eachdimension is defined by its single primary key, designated by the PKnotation, which serves as the basis for referential integrity with any givenfacttabletowhichitisjoined.Dimension attributes serve as the primary source of query constraints,groupings, and report labels. For example, when a user states that he or shewants to see sales amount by month by brand, month and brand must beavailableasdimensionattributes.Dimensiontableattributesplayavitalrolein the data warehouse. Since they are the source of virtually all interestingconstraints and report labels, theyare the keyto makingthe data warehouseusable and understandable. In many ways, the data warehouse is only asgood as the dimension attributes. The power of the data warehouse isdirectly proportional to the quality and depth of the dimension attributes.Robust dimension attributes deliver robust analytic slicing and dicingcapabilities. People observation data from a particular point (a dimension)can also exist in varying details portrayal (time dimension: the date, month,quarter,year).This paper introduces the four-step dimensional Design method. 1.Select the business process to model. 2. Declare the grain of the businessprocess. 3. Choose the dimensions that apply to each fact table row. 4.Identifythenumericfactsthatwillpopulateeachfacttablerow.Data Warehouse architecture model includes star schema and snowflakeschema. In the star schema, the fact table in the middle, around dimensiontables which similar to the stars. In the snowflakes schema, fact table in themiddle, around dimensions table can be connect to affiliated dimensiontables to express a clear dimension hierarchy. Consider from analysisdemandof OLAP system andtheprocessingefficiencyof ETL: star schemaaggregated fast, high-efficiency analysis. The structure of snow schema isclear, facilitate interactive with OLTP systems. Therefore, in the actualproject, we will be integrated use of star schema and snowflake schema todesigndatawarehouse.Thirdpieceofwork:modelingOLAPanddevelopingBIreport.MSTR is BI tools used in the data center project. First, this paper introduced environment configuration of MSTR development. Environmentconfigurationincludedthebuildingofproject source,thebuildingofprojectand the building of examples of data. After finished above work,datawarehouse dimensions table and the fact table will be added to the itemstoragedirectory.Then we will be able to enter the stage of MSTR OLAP modeling.Attributes of MSTR come from dimensions tables of data warehouse. Theproject included five categories attribute: financial, public, tobacco,marketing, monopoly of sale. And facts of MSTR come from fact tables ofdata warehouse. The same to attributes, facts also can be divided into fivecategories.ThenIoperatedthefactstocreateavarietyofmetrics.We drag metrics and attribute can finish a simple report. However, theactual BIreports also needtousesomepublicobjects, suchas filter,customgroup, consolidation, prompt. In this way, we can achieve feature-rich BIreport.This paper introduced modeling data warehouse and OLAP modelingmethods have been proved in the data centre project which is a viabletobaccoBIsystemsolution.