DW Basic Knowledge2

The metadata of the DW refers to all information except the data itself.

around DBMS- side metadata can be described as table definitions, partition settings, indexed view definitions, and DBMS -level security privileges

and authorization and other content.

on any occasion, ODS is either a third-party physical system between OLTP and DW, or a specialized management of DW

hot Zones (to support real-time interactions, data queries have a fixed form of structure)?

A row of the fact table corresponds to a measure, and a measure is one row of the fact table, and all measures of the fact table must have the same

grain size.

Additive Facts: Sales

Semi-additive Facts: Inventory balances (dimensions other than time can be used for aggregation)

Non-additive facts: ratios

Measurement facts can theoretically be in the form of text, but this rarely happens, and designers should try to

The text measure is converted to a dimension because the dimension can be more effectively correlated with other text dimension attributes and consumes

Much less space.

You cannot store redundant text information in a fact table unless the text is unique to each row of the fact table.

All fact table granularity belongs to one of three categories: transaction, cycle snapshot, and cumulative snapshot.

The fact table itself usually has its own primary key, which is often referred to as a compound or

Connect keywords. In other words, each table in the dimension model that represents a many-to-many relationship is a fact table, and all other tables

are all dimension tables.

Typically, only one word set that is part of the fact table compound keyword is required to ensure that the row has a unique character.

So, there is no merit in introducing a unique ROWID keyword into the fact table as the primary key keyword, which will only

Let the fact table be larger, of course, except when the business requirements of the special case are needed, (you need to load the same records into such a table,

such as the When the Fact table is made TYPE2)

Dimension attributes are the basic source of query constraints, groups, and report label generation.

The best attributes are text and discrete, and attributes should be real text instead of some shorthand symbols.

Sometimes it is often possible to analyze a numeric field from a data production source as a fact or dimension attribute.

the decision , that is, whether the field contains many values and participates in the operation (as a matter of fact) or a little change and participate in the constraint

Description of the discrete value of the condition (when the dimension attribute is regarded).

For example, the standard cost of a product looks like a constant attribute, but because of the constant change, each transaction can be a different value, so

Should eventually be treated as a measure.

Dimension tables typically describe hierarchical relationships in a business, such as products that contain trademarks and classifications, employees have departmental information to which they belong. Storage Hierarchy Description information appears

Very redundant, but so easy to use.

for the dimension model, it is an open design that can add a new dimension, as long as its value exists for the existing in the definition of uniqueness on the line, the same,

New facts can be added to the fact table as long as the granularity is consistent with the existing fact table.

DW Basic Knowledge2