A SAP BW data model that provides users with information corresponding to his or her business processes, meaning a data model that allows users to look at his or her performance indicators from the point of view of how these key figures influence the company.No predefined drill down paths. The user decides how he or she wants to analyze the data.The system must be able to handle large quantities of data at an acceptable speed.The structure must support functional reporting according to each individual business function and also across all the different business functions.
Become familiar with the structure of the information that you want to model.Refer to several sources of information to help you develop a comprehensive understanding of the business processes that your data model is based on n Evaluations of users' requirements (for example, results of interviews). Descriptions of existing source system applications as well as existing data warehouse applications and reporting applications
SAP BW Business Content
Existing entity relationship models (where available)
Develop an entity relationship model (ERM)
Existing entity relationship models (where available)
Develop an entity relationship model (ERM)
Business Content has been developed by experts from many specialized areas and you should use it as a primary source of information when you are determining your specific requirements. Enhancements that are made to Business Content usually deliver sound results - it is not a question of “reinventing the wheel”.Discussions must be based on the needs and preferences of users.
Example - questions relating to a data warehouse in the area of sales and marketing:
- ŸWho bought the product (customer and customer structure)?
- ŸWho sold the product (sales organization, distribution channels, and so on)?
- ŸWhat was sold exactly (product structure)?
- ŸWhen was it sold (time structure)?
- ŸHow was it sold (contract, contact, and so on)?
- ŸWhat characteristics belong to the sale (promotion, and so on)?
Make sure that you are talking to the right person, before you start asking questions.
Develop an information model that focuses on users' analytical requirements
Use the ERM as a basis on which to develop a multidimensional model (MDM)
Create a useful relational database schema for your entities
Do not complicate the model unnecessarily
Convert the MDM to the necessary BW objects
Based on your data model, create the required data storage structures
Create a useful relational database schema for your entities
Do not complicate the model unnecessarily
Convert the MDM to the necessary BW objects
Based on your data model, create the required data storage structures
The Entity Relationship Model (ERM)
An entity relationship model allows you to better understand the structure of the business processes in your source systems.
The following steps are involved in developing an ERM:
- Determine the entities that belong to a process - your basic business terms
- Determine the relationships between these entities
- Determine information about the attributes for each entity
- Determine the degree of normalization
The concepts behind (transaction-based) OLTP systems are based on ERMs. The goal of the OLTP system is to process and update transactions quickly (sales orders, purchase orders, changes to accounts in accounting, or employee status changes in HR, for example).Normalization, or the attempt to reduce or eliminate the redundant storage of duplicate information in more than one table, means that it is necessary to link together many tables.
The relationship between strong entities is usually N:M (many-to-many).
The N:M relationship between strong entities is usually described by key figures, for example, sales.
In a business scenario with a working document, this document forms an intersection entity.
Time is not an entity in the entity relationship model.
The N:M relationship between strong entities is usually described by key figures, for example, sales.
In a business scenario with a working document, this document forms an intersection entity.
Time is not an entity in the entity relationship model.
As a result of most business processes, basic entities are combined to form an intersection entity or an intersection transaction.Normalization means that information about regions and cities is not stored with the information in the sales transactional table. This would mean that the information about a customer’s region and city would have to be repeated each time the customer placed a new order for a product. Instead, the relevant region appears in the city table and the city appears on the customer tableWe do not know from this model what the analysis requirements for sale s reports will be. For example, we do not know whether we are going to need reports based on cities or based on the locations of the sales departments.
The Multidimensional Model (MDM)
In a multidimensional model (MDM) information consists of numerical facts (or key figures in BW terminology) and dimension attributes, meaning the business terms that describe these facts.The dimension attributes are collected into groups in the various dimensions. This means that dimension attributes that belong together (in a hierarchical relationship) appear in the same dimension.
The star schema is the most common way of displaying the relational databases in an MDM and is therefore often used as a synonym for the MDM itself. The facts are collected in a separate fact table and the dimensions are collected in the dimension tables. Both types of table are linked relationally. The dimension tables are joined to the fact table by foreign key relationships and primary key relationships. The dimension attribute with the highest granularity (level of detail) is a foreign key in the fact table. In this way each data record in the fact table (including the facts themselves) are identified uniquely by a combination of the values for these foreign keys in the dimension tables.
In the analysis, the key values of the dimension attribute with the greatest level of detail are collected from each of the dimensions in the surrounding smaller dimension tables . These key values are subsequently used to access the relevant rows in the fact table. This reduces the amount of time it takes to read a large fact table.
From ERM to MDM
The result of this mapping process between the ERM and the MDM is a data model Ÿ that organizes the information according to the primary business characteristics (for example, material group, sales organization, time, and so on) - the dimensions.Ÿ that allows you to analyze the information using any combination of dimensions. that allows you to define the aggregation level and the information that is retrieved for a dimension, independently of the other dimensions. Structuring data warehouse information according to this schema guarantees a high level of efficiency in reporting and provides a flexible solution that can be adjusted easily to changing business demands.
1. The basic business processes and the identity of facts in the fact table (a fact table - an InfoCube) -> Intersection entities
2. The dimensions for each fact table -> Strong entities
3. The dimension attributes with complete descriptions and proper terminology -> Attributes of the entities
4. The granularity (level of detail) of each fact table
5. The facts, including pre-calculated facts
6. The tracking options for historical data -> Dimensions that change slowly
7. The aggregations, heterogeneous dimensions, mini-dimensions, query modes, and other decisions about the physical storage of data
8. The life-span of the database (archiving aspects)
9. The time frame in which data is extracted and loaded into the data warehouse
These decisions form the basis of the initial database design. However, BW has many more modeling options not included in this list.
Star Schema Properties
- One process is modeled at a time l A star schema optimizes the storage of data for reporting purposes
Characteristics are structured together in related branches called dimensions
The key figures and other calculations form the facts
This structure is identical for all application areas
The dimensions provide answers to questions about “who? what? and when?".
The facts answer questions such as “how much money, how many people and how much did we pay
them?” This star schema concept is used in all application areas.
Dimension Tables
From a technical point of view, the characteristics in the dimension table form the edges of the InfoCube.The dimensions are connected to the fact table by the dimension keys (DIM IDs).The data in the fact table is accessed when you select characteristics and their values from the dimension table. A corresponding SQL statement is generated, and this in turn accesses the fact table.
Fact Tables
Strong entities are the main characteristics that occur in the application that is being analyzed.The fact table contains the data (key figures) for a certain combination of characteristic values from the dimension tables.The fact table is referenced by the artificial dimension key (DIM-ID).Since artificial keys are used to connect the dimension tables with the fact table, it is relatively easy to make changes to the master data table, without having to regenerate the (natural) key every time. In an evaluation, a resulting quantity is formed initially from the selected values in the dimension tables. The artificial key selects this resulting set directly from the fact table.
Star Schema
The InfoCubes represent a multidimensional data model on the database server of a data warehouse.The facts are collected into separate fact tables and the dimensions are grouped into separate dimension tables. Both types of table are connected to one another relationally.This database schema is called a star schema. Structuring data warehouse information according to this schema guarantees highly efficient reporting and provides a flexible solution that is adjusted easily to changing business requirements.
Granularity of Data
Granularity is a term that describes how detailed a database is in a data warehousing context.Data that is “highly granular” or has “high granularity” is very detailed data, meaning that there are a large number of characteristics describing the key figures.For example, a ‘by customer’ level of granularity is less detailed than ‘by customer, by material’.Granularity is the fundamental criteria that determines the extent to which you are able to drill down on the data.Granularity also affects the size of the database. Data that is stored ‘by customer, by month’ is much more summarized than ‘by customer, by material, by day’. The quantity of data that is generated over the course of a year for the first case is much less than for the second case.
Related Posts
No comments :
Post a Comment