Being worked in the project for around 38 months, possess good knowledge in business and technical side. It is a data warehouse development project, where they used a common ETL framework. The aim of the project is to sync 2 other vendors into IBC and because of that, they may get more clients. The sources are from different vendors and we need to load source files or tables into target tables.
|Published (Last):||4 November 2010|
|PDF File Size:||8.58 Mb|
|ePub File Size:||6.82 Mb|
|Price:||Free* [*Free Regsitration Required]|
What is A Data Model? What are the Major Types of Data Models? Dimensional Modeling Dimensional Modeling DM is a modeling Technique in data warehousing, visually represented as a fact table surrounded by dimension tables. Some of the terms commonly used in this type of modeling: Dimension: A category of information. For example, the time dimension. Attribute: An individual level within a dimension. For example, Month is an attribute in the Time Dimension. Hierarchy: The specification of levels that represents the relationship between different attributes within a dimension.
Fact Table: A fact table is a table that contains the measures of interest. For example, sales amount would be such a measure. Lookup Table: The lookup table provides the detailed information about the attributes. For example, the lookup table for the Quarter attribute would include a list of all of the quarters available in the data warehouse.
Star Schema: In the star schema design, a single object the fact table sits in the middle and is radially connected to other surrounding objects dimension lookup tables as a star. A star schema can be simple or complex. A simple star consists of one fact table; a sophisticated star can have more than one fact table.
Snowflake Schema: The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. The main advantage of the snowflake schema is the improvement in query performance due to minimized disk storage requirements and joining smaller lookup tables. The main disadvantage of the snowflake schema is the additional maintenance efforts needed due to the increasing number of lookup tables.
Relational Data Model The relational data model is entirely based on the predicate logic and set theory of mathematics. The data is arranged in a relation which is visually represented in a two-dimensional The data is inserted into the table in the form of tuples which are nothing but rows. A tuple is created by one or more than one attributes, which are used as basic building blocks in the formation of various expressions that are used to derive a meaningful information.
There can be any number of tuples in the table, but all the tuple contain fixed and same attributes with varying values. The relational model is implemented in database where a table represents a relation; a row represents a tuple, an attribute is represented by a column of the table All the relational algebra operations, such as Select, Intersection, Product, Union, Difference, Project, Join, Division, Merge, etc.
Operations on the Relational Database Model are facilitated with the help of different conditional expressions, various key attributes, pre-defined constraints, etc. For example selection of information of the customer, who is living in some city for more than 20 years. Following are few terms used in relational database model: Candidate Key: Any field or a combination of fields that identifies a record uniquely is called a Candidate Key.
Primary Key: Primary key is nothing but a candidate key that identifies a record uniquely. Foreign Key: A Foreign key is a primary key for another table, in which it uniquely identifies a record. A Foreign Key defines the relation between two or more tables.
A Foreign key can contain a NULL Constraints: Constraints are logic rules that are used to ensure data consistency or avoid certainly unacceptable operations on the data. Relational Model: Summary A tabular representation of data. Straightforward and intuitive, currently the most widely used.
Integrity constraints can be specified by the DBA, based on application semantics. DBMS checks for violations. Two critical ICs: primary and foreign keys Also, we always have domain constraints. Powerful and natural query languages exist. It specifically defines which individual data elements are stored and how they relate to each other. The data model ultimately defines which business questions can be answered and thus determines the business value of the entire strategic and tactical data warehouse environment.
A Logical Data Model reflects the rules of the business. A business rule, in turn, indicates how a company does business and declares its operating principles and policies.
The Logical Data Model is a way to show the rules of the business graphically. It can function as a means of Communication and as a data management tool. Also: It serves as a roadmap for achieving data integration in an organization. It is a guide for development over the long term. It shows interlocking parts. Understanding all of the interdependent parts makes it easier to expand the model for future enhancements. It forms a foundation upon which to build applications or business views.
It allows you to recognize data redundancy and to control it. Data redundancy can lead to inaccurate and inconsistent reporting of business information. It serves as a starting point for developing the physical model amid physical database design. It aids communication between the analyst and the business user and between As a rigorous Technique. It imposes discipline on the warehouse development process and leads to the development of stable.
Long term and reliable solutions. As a communication tool. It allows you to understand what you are building before you build it. Moreover, it allows you to see what you made after you built it. It can be used as a starting point for financial institutions and insurance companies who are interested in a rapid approach for achieving an organized and integrated view of their business data.
This data model is used by any business that sells the products above and services, including banks. Insurance Companies, credit card companies, and brokerages. It is a flexible and dynamic model that you can use for the long term. This means that as your business changes e. You have a solid database design. Moreover, it is extendable. It jump-starts the data modeling step in the development life cycle.
It is a good starting point when developing your logical data model, and provides checkpoints to make sure you have covered the necessary areas in your scope. It is the assimilation of Teradata best-practices knowledge. Gained from many years of designing logical data models for financial institutions. It is the result of analyzing many customer-based data models that Teradata has developed for other industries.
It has been validated at customer engagements. It saves time. By using the experience built in this model much less time is needed.
Moreover, therefore cost less. It is cross-functional and integrated—has one customer view across all products and functions. It is a roadmap for future integration and enhancement. You can start small but grow into the model knowing that it will integrate in the future.
It is broad in scope, robust. Moreover, customer-oriented. The model tracks relationships with any party of interest to the financial institutions. It supports a customer management vision—an Environment that enables financial institutions to develop comprehensive knowledge about their customers and to optimize the profitability of each relationship.
It is third normal form. The design is pure and unconstrained by operational or physical considerations. It can be implemented as near third normal form on Teradata. A third normal form model has one fact in one place—in the right place. A third normal form model has no redundant data elements. A model with redundant data elements can lead to misinformation. It is not based on a star schema and hence is not limited to a finite collection of business questions.
A third normal form model is flexible in answering unanticipated business questions. With a star schema design.
You are limited as to the type of business questions you can ask. It accommodates changes of currency, as it is the case with the Euro. This include.
Data Modeling and the Teradata FSLDM