[MUSIC] Hi, this week we will know how to design and implement an analytical database. We will be very aware of the important of data cleansing during instruction transformation and load process and data mining. And you will be able to program and execute OLAP queries to the data warehouse. On the first place we need to learn the basis of multidimensional analysis, and how to convert from a manual TP system, to an OLAP system using a star or snowflake model. There are two main strategies to design a data warehouse. [MUSIC] The one defended by Inmon, which proposes to define a corporate data warehouse as from the data warehouse construct, the analysis models for the different levels and departments from the organization. This is called a top-down strategy. And the one defended by Kimball which consists of building different data [INAUDIBLE] that cover the different needs of the organization without a need to be a data warehouse. The analysis and design of data warehouse is accomplished by a multidimensional model which has its origins on the entity relationship model. Therefore, we need to remember that the components of an entity relationship model are entities, attributes and relations. Well, the components of a multidimensional model for business intelligence are dimensions, attributes, attribute elements, relations, hierarchies, and facts or metrics. An entity concept does not exist in the multidimensional model. However, the entities from the entity relationship model will be mapped to attributes in the multidimensional model. The attributes will be physically converted to tables. As I mentioned, it's a set of attributes related to each other by the line of business. Facts are the values used to measure the behavior of the business, the facts table is in the center for the multidimensional data model. The attribute elements are atomic components of the model. The hierarchies are a logical ordering of attributes within a dimension. The multidimensional model consists of three main steps. First, define an integrated data model for the corporation. Second, define the scope of the data warehouse. Third, produce a multidimensional data model to serve as basis for the warehouse schema. Understanding and integrating operations from relevant departments allows us to define the model of the corporation. The second step corresponds to defined scope fo the data warehouse, and it requires to answer questions such as web portion of the business will be included in the model,? To which community of users will the warehouse model serve? Which entities will be excluded? Which entities should be modeled but not introduced in the first phases of implementation? [MUSIC] The third step of the multidimensional model will be obtained from the data modeling process and issue containing dimensions, attributes, relations, and metrics of facts. Well, let's start modelling with an example. Consider a retail business. There is a supermarket retail chain that needs to analyze data to know how to control their inventory. How to accommodate their products within each store are how to increase product sales by rating store and departments. A retained model presents all the necessary facts and measures by store, product, and time, the three business dimensions. Within each dimension, the user must select the level of specialization for a report, as well as the qualifiers for the resulting data set. The analysis consists of the defining the level of aggregation and rating the set of results independently of all other dimensions. Let's understand some concepts. A dimension is a thematic area or business line. It provides a general method of organizing corporate information. It consists of one or more attributes. It provides multiple data perspectives for analysis. The attributes are elements logically grouped in a dimension. There are levels within a dimension. For example, day and week are attributes of the time dimension. Day is a child attribute of the week attribute, so days are related to weeks in the same way. The attribute values are the atomic components of the model. When attributes are translated into tables, they are called a block of tables. The attribute relationships are the logical associations of attributes within the hierarchies. The multidimensional model uses to one to many and many to many relationships, such as the entity relationship model. The attribute hierarchies are the logical order of the attributes within dimensions. There may be several hierarchies within a dimension. The attribute hierarchies are the means for data navigation. In other words, they define data driven. As a modeling convention, the hierarchies should appear vertically in the diagram. The lowest leveled attributes must appear below the diagram, with many to many relationships. >> Different tables contain different measurements, also called facts. These measurements are a necessary data for business analysis. They are significant for monitoring the performance of the business, or key business factors. So we've got a base that [INAUDIBLE] and that the [INAUDIBLE] also called compound metric. Examples of basic facts can be [INAUDIBLE], kilometers and [INAUDIBLE]. They will exist as [INAUDIBLE] and come from different source systems. They can have different levels of granularity. For example, sales per day, but inventory is analyzed per week. Examples of derived facts are the average sale price, which is the ratio between sales and sales units. In the case of complicated calculations, which requires some logic, facts can be calculated in a batch process and stored in a fact table instead of being calculated at the time of the report. We have learned the main concepts of the multidimensional modeling. Now, we will check how to construct a multidimensional model from a multi-PC system. See you then. [MUSIC]
