BI development process and ETL Introduction

BI Development process and ETL introduction BI Development process

1. Building Dimension-Fact model
2. Build data warehouses (dimensions, facts) based on dimension-fact model
3. Data extraction (ETL)
4. Analysis model topics for building sales information
5. Build report analysis, instrument panel

The BI Business intelligence system, according to the enterprise needs to solve the problem, helps the enterprise to establish the corresponding analysis topic and the analysis index, extracts the necessary data from the business system basic database, according to the predetermined business model carries on the analysis decision-making, the analysis result displays the intuition, the image. Decision makers can get the information they need from a powerful sales analysis tool in business intelligence by simply taking a simple location.

E-> (Ods->sde->sil) (Powerful ETL tool) in the ETL <=>etl L->dw->biee (RPD physics)-> Biee (RPD logic) in T-> ETL- > Biee (RPD Show)-> report

You can also roll out tables in the ODI interface based on the report, and for individual tables you can reverse the table by manually using the data acquisition platform derivative.

To achieve ETL, first of all to achieve the process of ETL conversion. It can be centrally embodied in the following areas:

1, the null value processing can capture the field null value, carries on the load or replaces with other meaning data, and can realize the shunt loading according to the field null value to the different target library.

2, the normalized data format can realize the definition of field format constraints, for the data source time, value, characters and other data, you can customize the load format.

3, split the data according to the business needs of the field can be decomposed. Example, the main station 861082585313-8148, can be used for region code and phone number decomposition.

4, verify the correctness of the data can use the lookup and split function for data validation. For example, the main call 861082585313-8148, after the area code and phone number decomposition, can use lookup to return to the main call gateway or switch recorded in the main area, for data validation.

5, data replacement for business factors, can achieve invalid data, missing data replacement.

6, lookup seized the loss of data lookup implementation subquery, and returned by other means to obtain the missing fields, to ensure the integrity of the field.

7, establish the ETL process of the main foreign key constraints on the illegal data without dependencies, can be replaced or exported to the error data file, to ensure that the primary key unique record loading.

Extract extracts source data through interface, such as ODBC, private database interface and flat file extractor, and determines the data extraction and extraction method by reference to metadata.

The Transform developer will extract the data, convert it to the target data structure according to the business needs, and implement the Rollup.

Load loads converted and aggregated data into the target data Warehouse for SQL or bulk loading.

Whole process Problem Lookup

--Missing Field lookup

--From the report---RPD---ODI (from the Sil-sde-ods main view of the relationship-f-fs-v)---View corresponds to its logic (the view finds its most basic table to log, view the relationship)

--Missing record logarithm

The most important thing is to find the logical view, see if the view is right, looking for a source table that does not go to the view, ultimately determines whether there are numbers, if the source table has a few and the views are few, you need to look at the ODS layer and its filters, including the fact table and dimension table, whether the main filters and logic are correct or not.

The overall system architecture for the implementation of the common Oracle BI system is as follows:

The main sources of the BI system implementation data are:

1. ORACLE EBS (ERP system, get actual data).

2. Hyperion Planning (Hyperion budget system, get budget data).

3. Hyperion Financial Management (Hyperion financial statement merging system, offset number).

4. Data acquisition platform (get the Item, Mapping, universal permissions).

The overall system architecture implemented by the Oracle Essbase BI system is as follows:

Essbase supports both modes of application, one is ASO mode (Aggregatestorage Option),

The other is the BSO mode (Block Storage Option). There is complementarity between the two modes, and the following are two modes of

To compare:

The usual meaning of bi analysis is more than OLAP, as detailed in OLAP and OLTP.